How can I create a freeze pane in Excel when exporting via ASP.Net using XML?

Question

I'm using ASP.Net to output HTML/XML data so that it can be opened by MS Excel. The idea of course is to be able to send any tabular data to Excel. I have it working, but really want to implement a freeze pane on the top row for the column headings. From what I have read it's possible, and I've tried to implement bits of code I've seen, but still no dice, and I've found very little good reading on this.

Here's a similar post. I'm actually implementing the suggested code. How to freeze the header row in an Excel spreadsheet exported from ASP.NET

Here's the code used to generate the output:

Dim html_response As String = ""

  html_response &= "<html xmlns:x=""urn:schemas-microsoft-com:office:excel"">" & vbCrLf
  html_response &= "<head>" & vbCrLf
  html_response &= "  <meta http-equiv=""Content-Type"" content=""text/htmlcharset=windows-1252"">" & vbCrLf
  html_response &= "  <!--[if gte mso 9]>" & vbCrLf
  html_response &= "  <xml>" & vbCrLf
  html_response &= "  <x:ExcelWorkbook>" & vbCrLf
  html_response &= "    <x:ExcelWorksheets>" & vbCrLf
  html_response &= "      <x:ExcelWorksheet>" & vbCrLf
  html_response &= "        <x:Name>WorkSheet Name</x:Name>" & vbCrLf
  html_response &= "        <x:WorksheetOptions>" & vbCrLf
  html_response &= "          <x:Selected/>" & vbCrLf
  html_response &= "          <x:FreezePanes/>" & vbCrLf
  html_response &= "          <x:FrozenNoSplit/>" & vbCrLf
  html_response &= "          <x:SplitHorizontal>1</SplitHorizontal>" & vbCrLf
  html_response &= "          <x:TopRowBottomPane>1</TopRowBottomPane>" & vbCrLf
  html_response &= "          <x:Panes>" & vbCrLf
  html_response &= "            <x:Pane>" & vbCrLf
  html_response &= "              <x:Number>3</x:Number>" & vbCrLf
  html_response &= "            <x:Pane>" & vbCrLf
  html_response &= "            <x:Pane>" & vbCrLf
  html_response &= "              <x:Number>2</x:Number>" & vbCrLf
  html_response &= "            <x:Pane>" & vbCrLf
  html_response &= "          </x:Panes>" & vbCrLf
  html_response &= "          <x:ActivePane>2</x:ActivePane>" & vbCrLf
  html_response &= "          <x:ProtectContents>False</x:ProtectContents>" & vbCrLf
  html_response &= "          <x:ProtectObjects>False</x:ProtectObjects>" & vbCrLf
  html_response &= "          <x:ProtectScenarios>False</x:ProtectScenarios>" & vbCrLf
  html_response &= "        </x:WorksheetOptions>" & vbCrLf
  html_response &= "      </x:ExcelWorksheet>" & vbCrLf
  html_response &= "    </x:ExcelWorksheets>" & vbCrLf
  html_response &= "  </x:ExcelWorkbook>" & vbCrLf
  html_response &= "  </xml>" & vbCrLf
  html_response &= "  <![endif]-->" & vbCrLf
  html_response &= "</head>" & vbCrLf
  html_response &= "<body>" & vbCrLf

  'Tabular data retrieved from Session var
  html_response &= table_data

  html_response &= "</body>" & vbCrLf
  html_response &= "</html>" & vbCrLf

  'Browser/header stuff
  Dim filename As String = Session("UserLoggedIn") & "_" & DateTime.Now().ToFileTime() & ".xls"
  Response.Clear()
  Response.ContentType = "application/vnd.ms-excel"
  Response.AddHeader("content-disposition", "attachment filename=" & filename)

  Response.Write(html_response)

And here's a picture showing what I get each time in Excel (I'm using Excel 2007): excel no freeze frame

As you can see, Excel is consuming the data, but no freeze pane at the top, AND it appears that some of the XML WorkSheetOptions variables are being printed in cell A1.

NOTE: I have almost no idea how the panes and their numbers play into all this, and I'm having a terrible time finding documentation. I'd be glad to read and learn if I could find something.

Any idea what I'm doing wrong here?

EDIT: Thought I would post what finally solved my issue, in case some other poor soul needs it. Only took an entire day of fiddling around.

This first function prepares the final XML for export via Response.Write, which is opened on the client machine in Excel, if they have it installed.

Public Shared Function FormatBasicSpreadsheet(excel_table_data As String, worksheet_name As String) As String
Dim html As String = ""

'The first two lines make sure Windows opens this document with Excel
html &= "<?xml version=""1.0""?>" & vbCrLf
html &= "<?mso-application progid=""Excel.Sheet""?>" & vbCrLf
html &= "<Workbook xmlns=""urn:schemas-microsoft-com:office:spreadsheet""" & vbCrLf
html &= " xmlns:o=""urn:schemas-microsoft-com:office:office""" & vbCrLf
html &= " xmlns:x=""urn:schemas-microsoft-com:office:excel""" & vbCrLf
html &= " xmlns:ss=""urn:schemas-microsoft-com:office:spreadsheet""" & vbCrLf
html &= " xmlns:html=""http://www.w3.org/TR/REC-html40"">" & vbCrLf

'Add styling for rows/cells
html &= "<Styles>" & vbCrLf
html &= "  <Style ss:ID=""Default"" ss:Name=""Normal"">" & vbCrLf
html &= "    <Alignment ss:Vertical=""Bottom""/>" & vbCrLf
html &= "    <Borders/>" & vbCrLf
html &= "    <Font ss:FontName=""Calibri"" x:Family=""Swiss"" ss:Size=""11"" ss:Color=""#000000""/>" & vbCrLf
html &= "    <Interior/>" & vbCrLf
html &= "    <NumberFormat/>" & vbCrLf
html &= "    <Protection/>" & vbCrLf
html &= "  </Style>" & vbCrLf
html &= "  <Style ss:ID=""header_bold"">" & vbCrLf
html &= "    <Alignment ss:Vertical=""Bottom"" ss:WrapText=""0""/>" & vbCrLf
html &= "    <Font ss:FontName=""Calibri"" x:Family=""Swiss"" ss:Size=""11"" ss:Color=""#000000"" ss:Bold=""1""/>" & vbCrLf
html &= "    <Borders>" & vbCrLf
html &= "      <Border ss:Position=""Bottom"" ss:LineStyle=""Continuous"" ss:Weight=""1""/>" & vbCrLf
html &= "      <Border ss:Position=""Left"" ss:LineStyle=""Continuous"" ss:Weight=""1""/>" & vbCrLf
html &= "      <Border ss:Position=""Right"" ss:LineStyle=""Continuous"" ss:Weight=""1""/>" & vbCrLf
html &= "      <Border ss:Position=""Top"" ss:LineStyle=""Continuous"" ss:Weight=""1""/>" & vbCrLf
html &= "    </Borders>" & vbCrLf
html &= "    <Interior ss:Color=""#F2F2F2"" ss:Pattern=""Solid""/>" & vbCrLf
html &= "  </Style>" & vbCrLf
html &= "  <Style ss:ID=""all_borders"">" & vbCrLf
html &= "    <Borders>" & vbCrLf
html &= "      <Border ss:Position=""Bottom"" ss:LineStyle=""Continuous"" ss:Weight=""1""/>" & vbCrLf
html &= "      <Border ss:Position=""Left"" ss:LineStyle=""Continuous"" ss:Weight=""1""/>" & vbCrLf
html &= "      <Border ss:Position=""Right"" ss:LineStyle=""Continuous"" ss:Weight=""1""/>" & vbCrLf
html &= "      <Border ss:Position=""Top"" ss:LineStyle=""Continuous"" ss:Weight=""1""/>" & vbCrLf
html &= "    </Borders>" & vbCrLf
html &= "    <Interior ss:Color=""#F2F2F2"" ss:Pattern=""Solid""/>" & vbCrLf
html &= "  </Style>" & vbCrLf
html &= "</Styles>" & vbCrLf

html &= "<Worksheet ss:Name=""" & worksheet_name & """>" & vbCrLf

'Tabular retrieved from Session var
'NOTE: MUST be in proper Excel XML table format.
html &= excel_table_data & vbCrLf

'Worksheet options - freeze panes, etc.
html &= "<WorksheetOptions xmlns=""urn:schemas-microsoft-com:office:excel"">" & vbCrLf
html &= "  <Selected/>" & vbCrLf
html &= "  <FreezePanes/>" & vbCrLf
html &= "  <FrozenNoSplit/>" & vbCrLf
html &= "  <SplitHorizontal>1</SplitHorizontal>" & vbCrLf
html &= "  <TopRowBottomPane>1</TopRowBottomPane>" & vbCrLf
html &= "  <ActivePane>2</ActivePane>" & vbCrLf
html &= "  <Panes>" & vbCrLf
html &= "    <Pane>" & vbCrLf
html &= "     <Number>3</Number>" & vbCrLf
html &= "    </Pane>" & vbCrLf
html &= "    <Pane>" & vbCrLf
html &= "     <Number>2</Number>" & vbCrLf
html &= "     <ActiveRow>0</ActiveRow>" & vbCrLf
html &= "    </Pane>" & vbCrLf
html &= "  </Panes>" & vbCrLf
html &= "  <ProtectObjects>False</ProtectObjects>" & vbCrLf
html &= "  <ProtectScenarios>False</ProtectScenarios>" & vbCrLf
html &= "</WorksheetOptions>" & vbCrLf

html &= "</Worksheet>" & vbCrLf
html &= "</Workbook>" & vbCrLf

Return html

End Function

Hopefully, this might help someone down the road. Cheers.

Here's a function for formatting Excel XML table data, the result of which is fed to the function above.

Public Shared Function CreateExcelXmlTableFromSQL(ByVal SQL As String, IncludeHeaderStyling As Boolean) As String
  Dim html As String = ""

  'These variables are for setting the column width declarations
  Dim column_width_template As String = "<Column ss:Width=""$$$""/>"
  Dim column_character_pixel_constant As Single = 5.5 'Pixels per character
  Dim column_list As New Generic.Dictionary(Of Int32, Int32)

  'Open dbase connection
  Dim conn As SqlConnection = dbase.CreateSqlConnection()

  'Fill the datareader
  Dim dr As SqlDataReader = dbase.ReturnDataReader(conn, Sql)

  'Check the datareader
  If (dr Is Nothing) OrElse (dr.IsClosed) Then
    Return ""
  End If

  'Begin the table
  html &= "<Table>" & vbCrLf

  'Put columns placeholder in
  html &= "[COLUMNS]" & vbCrLf

  'NOTE: The datareader object (unlike the old ADODB recordset) is forward only.  This means
  '      means that there is no reset of the pointer.  When you read the first row to get
  '      the column names, you MUST also generate the first row of content, or you'll be missing
  '      one row.

  'Read the first row of the datareader for the column headings, PLUS the first row of data.
  While (dr.Read)
    'Write table headers
    'NOTE: This contains XML style tags
    Dim header_row_style As String = " ss:StyleID=""all_borders"""
    Dim header_cell_style = " ss:StyleID=""header_bold"""

    If (Not IncludeHeaderStyling) Then
      header_row_style = ""
      header_cell_style = ""
    End If

    html &= "<Row" & header_row_style & ">" & vbCrLf

    For i As Int16 = 0 To dr.FieldCount - 1
      'NOTE: you see the styleID I included here.  In billing_export_report where I 
      'build the overall Excel XML doc, I define this.
      html &= "<Cell" & header_cell_style & "><Data ss:Type=""String"">&#160;" & dr.GetName(i) & "</Data></Cell>" & vbCrLf
      column_list.Add(i, dr.GetName(i).Length)
    Next

    html &= "</Row>" & vbCrLf

    'Write the first row of data
    html &= "<Row>" & vbCrLf

    For i As Int16 = 0 To dr.FieldCount - 1
      html &= "<Cell><Data ss:Type=""String"">&#160;" & dr(i).ToString() & "</Data></Cell>" & vbCrLf

      If (column_list.Item(i) < dr(i).ToString().Length) Then
        column_list.Item(i) = dr(i).ToString().Length
      End If
    Next

    html &= "</Row>" & vbCrLf

    'Exit this loop after first row
    Exit While
  End While

  'Write the content, starting at the second row, switching row colors.
  While (dr.Read)
    'Start row
    html &= "<Row>" & vbCrLf

    'Write the row contents
    For i As Int16 = 0 To dr.FieldCount - 1
      html &= "<Cell><Data ss:Type=""String"">&#160;" & dr(i) & "</Data></Cell>" & vbCrLf

      If (column_list.Item(i) < dr(i).ToString().Length) Then
        column_list.Item(i) = dr(i).ToString().Length
      End If
    Next

    html &= "</Row>" & vbCrLf
  End While

  html &= "</Table>" & vbCrLf

  'Now put the column declarations in, so that
  'the widths are correct.
  Dim columns_html As String = ""

  For Each k As Generic.KeyValuePair(Of Int32, Int32) In column_list
    Dim pixel_value As Single = (k.Value * column_character_pixel_constant) + 10
    columns_html &= column_width_template.Replace("$$$", pixel_value.ToString()) & vbCrLf
  Next

  html = html.Replace("[COLUMNS]", columns_html)

  dr.Close()
  dr = Nothing
  conn.Close()
  conn.Dispose()
  conn = Nothing

  'Return the completed table
  Return html
End Function

Here's an image of the finished Excel exported via Response.Write. You can see it has styling of the header row, freeze pane works, and column widths are set for proper display.

correct_excel

Ducain · Accepted Answer

Finally discovered a workable solution. I posted details in the original question area above.

How can I create a freeze pane in Excel when exporting via ASP.Net using XML?

Tags:

asp.net

export-to-excel

Ducain

1 Answers

Ducain

Recent Activity

Donate For Us

How can I create a freeze pane in Excel when exporting via ASP.Net using XML?

Tags:

asp.net

export-to-excel

Ducain

1 Answers

Ducain

Related questions

Recent Activity

Donate For Us