Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.
I am the chief technical officer at InVision App, Inc - a prototyping and collaboration platform for designers, built by designers. I also rock out in JavaScript and ColdFusion 24x7.
Meanwhile on Twitter
Loading latest tweet...
Ben Nadel at jQuery NYC (Oct. 2009) with:

Freezing Row/Column Panes In Excel XML

Posted 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.

Tweet This Great article by @BenNadel - Freezing Row/Column Panes In Excel XML Thanks my man — you rock the party that rocks the body!


Reader Comments

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>

Reply to this Comment

Rohit,

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

Reply to this Comment

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

Reply to this Comment

Post A Comment

?
You — Get Out Of My Dreams, Get Into My Comments
Live in the Now
Oops!
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.