Freezing Row/Column Panes In Excel XML

By Ben Nadel

Published 2007-01-09 in ColdFusion — Comments (4)

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.

Short link: https://bennadel.com/467