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 :)


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
InVision App - Prototyping Made Beautiful With Prototyping Tools Ben Nadel's Company - Epicenter Consulting Recent Blog Comments
Kévin
May 21, 2012 at 1:58 AM
Updated: Converting A ColdFusion Query To CSV Using QueryToCSV()
Hi Ben, why do you need to have so many double quotes when adding the field and field name to the row data? ----------------------------------------- <cfset LOCAL.RowData[ LOCAL.ColumnIndex ] = ... read »
AXL
May 21, 2012 at 1:24 AM
URL Rewriting And ColdFusion's WriteToBrowser Image Functionality (CFFileServlet)
@Mounir, Open your lower case URL Rewrite rule and add the following condition. Condition input: {REQUEST_URI} Check if input string: Does Not Match the Pattern Pattern: ^/CFFileServlet/_cf_ca ... read »
John
May 20, 2012 at 4:28 AM
Understanding The Complex And Circular Relationships Between Objects In JavaScript
@Will Vaughn I tried your javascript example but got this error:- foo.print is not a function ... read »
Perl hacker
May 19, 2012 at 5:37 AM
A Graphical Explanation Of Javascript Closures In A jQuery Context
Thanks for this article, but I fear you missed an important point. If variables in the outer context change, these changes affect the inner anonymous functions as well. That means: if you change the ... read »
Alexander
May 18, 2012 at 3:39 PM
Parsing CSV Data With An Input Stream And A Finite State Machine
Can you use file upload button with this? and read live? or does the file have to already be on the server saved? ... read »
horoskop
May 18, 2012 at 1:06 AM
VIRGO (Aug. 23-Sept. 22): Dead On The Money!
A friend of mine and I were arguing about astrology and she told me that he believes in astrology. She hasn't provided me with any evidence that the belief makes any sense to me. She she been telling ... read »
Manuel
May 17, 2012 at 11:32 PM
Using ColdFusion to Handle 404 Errors (Page Not Found) On Development Server
Very easy the configuration. I read a lot pages and I can't find the solution. I open the administrator and change this Administrator/server settings/Error Handlers/Missing Template Handler and p ... read »
Curt Self
May 17, 2012 at 3:13 PM
LOCAL Variables Scope Conflicts With ColdFusion Query of Queries
I never cease to be amazed that almost EVERY random CF issue I come across lands me on your site. Thank you for documenting your findings for the world. ... read »