Skip to main content
Ben Nadel at cf.Objective() 2011 (Minneapolis, MN) with: Angela Buraglia and Dan Short
Ben Nadel at cf.Objective() 2011 (Minneapolis, MN) with: Angela Buraglia ( @aburaglia ) Dan Short ( @danshort )

Freezing Row/Column Panes In Excel XML

By on
Tags:

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.

Want to use code from this post? Check out the license.

Reader Comments

1 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>

1 Comments

Rohit,

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

1 Comments

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

I believe in love. I believe in compassion. I believe in human rights. I believe that we can afford to give more of these gifts to the world around us because it costs us nothing to be decent and kind and understanding. And, I want you to know that when you land on this site, you are accepted for who you are, no matter how you identify, what truths you live, or whatever kind of goofy shit makes you feel alive! Rock on with your bad self!
Ben Nadel