tags:

views:

811

answers:

2
+1  Q: 

How to freeze the header row in an Excel spreadsheet exported from ASP.NET

I'm exporting an ASP.NET gridview to Excel using the following function. The formatting is working really well, except I need to freeze the header row in Excel on the export. I'm really trying to avoid using a 3rd party Excel plugin for this, but unless there's some archaic excel markup in my AddExcelStyling function. 

    Public Sub exportGrid(ByVal psFileName As String)

        Response.Clear()
        Response.Buffer = True
        Response.Cache.SetCacheability(HttpCacheability.NoCache)
        Response.ContentType = "application/vnd.ms-excel"
        Response.AddHeader("content-disposition", "attachment;filename=PriceSheet.xls")
        Response.Charset = ""
        Me.EnableViewState = False
        Dim sw As New StringWriter()
        Dim htw As New HtmlTextWriter(sw)
        sfggcPriceSheet.RenderControl(htw)
        Response.Write("<meta http-equiv=Content-Type content=""text/html; charset=utf-8"">" + Environment.NewLine)
        Response.Write(AddExcelStyling())
        Response.Write(sw.ToString())
        Response.Write("</body>")
        Response.Write("</html>")
        Response.End()

    End Sub

And the formatting black magic:

   Private Function AddExcelStyling() As String


    Dim sb As StringBuilder = New StringBuilder()
    sb.Append("<html xmlns:o='urn:schemas-microsoft-com:office:office'" + Environment.NewLine + _
    "xmlns:x='urn:schemas-microsoft-com:office:excel'" + Environment.NewLine + _
    "xmlns='http://www.w3.org/TR/REC-html40'&gt;" + Environment.NewLine + _
    "<head>")
    sb.Append("<style>" + Environment.NewLine)

    sb.Append("@page")
    sb.Append("{margin:.25in .25in .25in .25in;" + Environment.NewLine)

    sb.Append("mso-header-margin:.025in;" + Environment.NewLine)
    sb.Append("mso-footer-margin:.025in;" + Environment.NewLine)

    sb.Append("mso-page-orientation:landscape;}" + Environment.NewLine)
    sb.Append("</style>" + Environment.NewLine)

    sb.Append("<!--[if gte mso 9]><xml>" + Environment.NewLine)
    sb.Append("<x:ExcelWorkbook>" + Environment.NewLine)

    sb.Append("<x:ExcelWorksheets>" + Environment.NewLine)
    sb.Append("<x:ExcelWorksheet>" + Environment.NewLine)

    sb.Append("<x:Name>PriceSheets</x:Name>" + Environment.NewLine)
    sb.Append("<x:WorksheetOptions>" + Environment.NewLine)

    sb.Append("<x:Print>" + Environment.NewLine)
    sb.Append("<x:ValidPrinterInfo/>" + Environment.NewLine)

    sb.Append("<x:PaperSizeIndex>9</x:PaperSizeIndex>" + Environment.NewLine)
    sb.Append("<x:HorizontalResolution>600</x:HorizontalResolution" + Environment.NewLine)

    sb.Append("<x:VerticalResolution>600</x:VerticalResolution" + Environment.NewLine)
    sb.Append("</x:Print>" + Environment.NewLine)

    sb.Append("<x:Selected/>" + Environment.NewLine)
    sb.Append("<x:DoNotDisplayGridlines/>" + Environment.NewLine)

    sb.Append("<x:ProtectContents>False</x:ProtectContents>" + Environment.NewLine)
    sb.Append("<x:ProtectObjects>False</x:ProtectObjects>" + Environment.NewLine)

    sb.Append("<x:ProtectScenarios>False</x:ProtectScenarios>" + Environment.NewLine)
    sb.Append("</x:WorksheetOptions>" + Environment.NewLine)

    sb.Append("</x:ExcelWorksheet>" + Environment.NewLine)
    sb.Append("</x:ExcelWorksheets>" + Environment.NewLine)

    sb.Append("<x:WindowHeight>12780</x:WindowHeight>" + Environment.NewLine)
    sb.Append("<x:WindowWidth>19035</x:WindowWidth>" + Environment.NewLine)

    sb.Append("<x:WindowTopX>0</x:WindowTopX>" + Environment.NewLine)
    sb.Append("<x:WindowTopY>15</x:WindowTopY>" + Environment.NewLine)

    sb.Append("<x:ProtectStructure>False</x:ProtectStructure>" + Environment.NewLine)
    sb.Append("<x:ProtectWindows>False</x:ProtectWindows>" + Environment.NewLine)

    sb.Append("</x:ExcelWorkbook>" + Environment.NewLine)
    sb.Append("</xml><![endif]-->" + Environment.NewLine)

    sb.Append("</head>" + Environment.NewLine)
    sb.Append("<body>" + Environment.NewLine)

    Return sb.ToString()

End Function
A: 

Perhaps you can use a trick that I employ when I am trying to do "specialty" formatting, etc...

Create an XLS with all the row and cell formats you'd like already expressed. Then COPY it, add your data, and SAVE it.

In this manner you do not need any payed-for plug-ins, and you ALWAYS get EXACTLY whatever formatting you want...

Need more than one specialty? Create another my_MT_Excel.xls with the needed formatting.

You could have a folder full of these things all ready to go without resorting to the more intricate programming techniques.

tob

tobrien  2010-02-25 17:14:37
+2  A: 

Modify the WorksheetOption element to something like the following:

<x:WorksheetOptions>
     <x:Selected/>
     <x:FreezePanes/>
     <x:FrozenNoSplit/>
     <x:SplitHorizontal>1</x:SplitHorizontal>
     <x:TopRowBottomPane>1</x:TopRowBottomPane>
     <x:ActivePane>2</x:ActivePane>
     <x:Panes>
      <x:Pane>
       <x:Number>3</x:Number>
      </x:Pane>
      <x:Pane>
       <x:Number>2</x:Number>
      </x:Pane>
     </x:Panes>
     <x:ProtectContents>False</x:ProtectContents>
     <x:ProtectObjects>False</x:ProtectObjects>
     <x:ProtectScenarios>False</x:ProtectScenarios>
    </x:WorksheetOptions>

Notice the FreezePanes element. I cut this out of a spreadsheet that I had saved as HTML with the first row frozen. When this file is opened with Excel, the first row is frozen.

EDIT: To have the header row print on each page, you will need something like this:

<x:ExcelName>
  <x:Name>Print_Area</x:Name>
  <x:SheetIndex>1</x:SheetIndex>
  <x:Formula>=Sheet1!$A$2:$F$97</x:Formula>
 </x:ExcelName>
 <x:ExcelName>
  <x:Name>Print_Titles</x:Name>
  <x:SheetIndex>1</x:SheetIndex>
  <x:Formula>=Sheet1!$1:$1</x:Formula>
 </x:ExcelName>

You will need to modify the values in the formula dynamically for your data.

DaveB  2010-02-25 19:47:32
Awesome! That's exactly what I was looking for. Any idea how to get it to print that row on each page? I've tried your trick of saving as HTML then looking at it, but I'm doing something wrong and don't get any of the excel options when I do that.
fr0man  2010-02-26 15:23:25
Hmmm, looks like I have to save it as XML spreadsheet to get those tags. The rows to repeat for printing option appears to used named ranges rather than options in the <x:Print> elements. I might be stuck.
fr0man  2010-02-26 15:48:56
I added a bit to my answer to address the issue of printing the header row on each page. You will need to set the print_area dynamically. Hope this helps.
DaveB  2010-02-26 16:48:47

related questions

What are the most important functional differences between C# and VB.NET?
Setting Group Type for new Active Directory Entry in VB.NET
ASP.Net Datagrid: in Footer calculate Avg or Sum for column
Opening a file in my application from File Explorer
HTML Email Editor in a Windows Forms Application
Datatables, Dataviews and distincts! (ASP.Net/VB)
Naming convention for VB.NET private fields
Visual Studio - new "default" property values for inherited controls
Change the width of a scrollbar
Is String.Format as efficient as StringBuilder
Default Form Button in FireFox
VB.NET - IsNothing versus Is Nothing
'Break' equivalent keyword for VB
What is the best way to wrap time around the work day?
Best SVN Client Ignore Pattern for VB.NET Solutions?
Long-time LAMP Developer moving to VB.NET
What's the best way to find long-running code in a Windows Forms Application
What is the best way to deploy a VB.NET application?
'Best' Diff Algorithm
Setting Objects to Null/Nothing after use in .NET
CSV File Imports in .Net
How do you migrate a large app from VB6 to VB .net ?
Floating Point Number parsing: Is there a Catch All algorithm?
Decoding T-SQL CAST in C#/VB.net
Reliable Timer in a Console Application