Freezing Row/Column Panes In Excel XML

Posted January 9, 2007 at 2:36 PM by Ben Nadel

Tags: ColdFusion

I have been tinkering around with creating Excel files using ColdFusion and XML and have been figuring out how different Excel features are defined. There is an XML Schema that you can download and install, but it doesn't seem to be working on my computer. So, this is just trial and error until I can get that documentation up and running.

One of the things that is highly useful is freezing a pane (number of rows / columns) in an Excel document such that the rest of the document scrolls and the frozen cells stay in place. This can be most useful for header rows (frozen row) or ID columns (frozen column). Here is how I have been doing this - this demonstrates only a given XML node of the overall XML document. To see how this fits into the rest of the XML, please see my entry on creating Excel documents using ColdFusion and XML.

It seems that all freezing is defined in the WorksheetOptions XML node.

Horizontal Freeze

This splits the document into two panes - top and bottom. The top pane is frozen in place and the bottom pane can scroll freely:

  • <WorksheetOptions
  • xmlns="urn:schemas-microsoft-com:office:excel">
  •  
  • <Selected/>
  • <FreezePanes/>
  • <FrozenNoSplit/>
  •  
  • <!--- Bottom row number of top pane. --->
  • <SplitHorizontal>1</SplitHorizontal>
  •  
  • <!---
  • Offset row of bottom frame. This is not the actual row
  • number of the overall Excel document, but rather the
  • index of the available rows for this pane. This number
  • cannot exclude rows, it can merely set the offset
  • scroll of this pane. (1) scrolls to the top of the
  • frame (first row).
  • --->
  • <TopRowBottomPane>1</TopRowBottomPane>
  •  
  • </WorksheetOptions>

Vertical Freeze

This splits the document into two panes - left and right. The left pane is frozen in place and the bottom pane can scroll freely:

  • <WorksheetOptions
  • xmlns="urn:schemas-microsoft-com:office:excel">
  •  
  • <Selected/>
  • <FreezePanes/>
  • <FrozenNoSplit/>
  •  
  • <!--- Right-most column number of left pane. --->
  • <SplitVertical>1</SplitVertical>
  •  
  • <!---
  • Offset column of right frame. This is not the actual
  • column number of the overall Excel document, but rather
  • the index of the available column for this pane. This
  • number cannot exclude columns, it can merely set the
  • offset scroll of this pane. (1) scrolls to the left of
  • the frame (first column).
  • --->
  • <LeftColumnRightPane>1</LeftColumnRightPane>
  •  
  • </WorksheetOptions>

Horizontal And Vertical Freeze

This splits the Excel document into four panes. The top and left panes are frozen in place and the bottom-right pane can scroll freely:

  • <WorksheetOptions
  • xmlns="urn:schemas-microsoft-com:office:excel">
  •  
  • <Selected/>
  • <FreezePanes/>
  • <FrozenNoSplit/>
  •  
  • <!--- Defines the horizontal split (see above). --->
  • <SplitHorizontal>1</SplitHorizontal>
  • <TopRowBottomPane>1</TopRowBottomPane>
  •  
  • <!--- Defines the vertical split (see above). --->
  • <SplitVertical>1</SplitVertical>
  • <LeftColumnRightPane>1</LeftColumnRightPane>
  •  
  • </WorksheetOptions>

So anyway, that's what I learned today. I am going to be posting more on these types of exploration, so if you are not interested, don't bother checking out any blog posts that seem Excel-XML related.



Reader Comments

Rohit
Mar 6, 2009 at 9:37 AM // reply »
1 Comments
12 Points

when i use the below code for freezing panes.it 's working fine..but i am not able to scroll the (xml+XSLT) Excel using mouse.

<WorksheetOptions
xmlns="urn:schemas-microsoft-com:office:excel">

<Selected/>
<FreezePanes/>
<FrozenNoSplit/>

<!--- Right-most column number of left pane. --->
<SplitVertical>1</SplitVertical>

<!---
Offset column of right frame. This is not the actual
column number of the overall Excel document, but rather
the index of the available column for this pane. This
number cannot exclude columns, it can merely set the
offset scroll of this pane. (1) scrolls to the left of
the frame (first column).
--->
<LeftColumnRightPane>1</LeftColumnRightPane>

</WorksheetOptions>


pjg
Jun 30, 2009 at 6:50 PM // reply »
1 Comments
11 Points

Rohit,

Try a ActivePane element with a content of 2 before closing the WorksheetOptions element. It fixes a horizontal freeze.


Anna
Mar 16, 2012 at 7:36 AM // reply »
1 Comments
10 Points

Thank you, guys! Horizontal freeze works :)


Abhisek
Jul 3, 2012 at 3:10 AM // reply »
1 Comments
9 Points

The vertical split has been set to the 2nd column from Left. Can you let me know how to remove the Vertical Split?

Thanks,
Abhisek


Post A Comment

Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.

Please review the following issues:

Author Name:
Author Email:
Author Website:
Comment:
Supported HTML tags for formatting: <strong>bold</strong>   <em>italic</em>   <code>code</code>







  • Help Wanted - Find Your Next ColdFusion Job
Ben Nadel's Company - Epicenter Consulting Recent Blog Comments
Joel
May 22, 2013 at 5:35 PM
Script Tags, jQuery, And Html(), Text() And Contents()
This is still an issue 2 years later. jQuery is supposed to remediate these cross browser issues, no? I have been unable to find any statement from the jQuery team calling this behavior "by de ... read »
Phillip Senn
May 22, 2013 at 12:44 PM
Ask Ben: Query Loop Inside CFScript Tags
In cf10, if you call a function that has: local.result = {}; local.result.msg = ""; local.svc = new query(); local.svc.setSQL("SELECT * FROM..."); local.obj = local.svc.exe ... read »
Dan G. Switzer, II
May 22, 2013 at 12:29 PM
Strange Interaction Between DeserializeJson(), ArrayContains(), And Database Values In ColdFusion
@Ben: What version of Java are you using? Also, did you test users.id to see what Java reports as the data type? I wonder if it's not a Java primitive data type, but getting returned as something ... read »
Ben Nadel
May 22, 2013 at 11:47 AM
Strange Interaction Between DeserializeJson(), ArrayContains(), And Database Values In ColdFusion
@Dana, Awesome - so it looks like this bug was fixed in ColdFusion 10. Thanks so much for double-checking that. ... read »
DanaK
May 22, 2013 at 11:37 AM
Strange Interaction Between DeserializeJson(), ArrayContains(), And Database Values In ColdFusion
When I c&p and run on cf10, I get: Selected User IDs: 1,4 User 1 selected: YES - YES User 2 selected: NO - NO User 3 selected: NO - NO User 4 selected: YES - YES User 5 selected: NO - ... read »
Ben Nadel
May 22, 2013 at 11:27 AM
Strange Interaction Between DeserializeJson(), ArrayContains(), And Database Values In ColdFusion
@Tom, Good thought, but no dice. Both of these still exhibit the same behavior: users.id[ users.currentRow ] users[ "id" ][ users.currentRow ] It's just something whacky happening with ... read »
Tom Eldredge
May 22, 2013 at 11:07 AM
Strange Interaction Between DeserializeJson(), ArrayContains(), And Database Values In ColdFusion
Could your problem be that "users.id" is actually an ARRAY, not a single value? Perhaps try it again with "users.id[1]" (I only have CF8 here at work). ... read »
mrsean2k
May 22, 2013 at 7:52 AM
Nested Views, Routing, And Deep Linking With AngularJS
Hi, Just a quick thank you. As it happens, for my own purposes, the pending ui-router work being done in native angular is likely the one I'll adopt, but your exploration, code and documentation of ... read »
InVision App - Prototyping Made Beautiful With Prototyping Tools