我正在使用ASP.Net输出HTML / XML数据,以便可以通过MS Excel打开它。当然的想法是能够将任何表格数据发送到Excel。我有它的工作,但真的想在列标题的第一行上实现一个冻结窗格。从我阅读的内容来看,这是有可能的,并且我尝试实现我所见过的一些代码,但是仍然没有骰子,而且我发现对此内容的了解很少。

这是类似的帖子。我实际上是在执行建议的代码。
How to freeze the header row in an Excel spreadsheet exported from ASP.NET

这是用于生成输出的代码:

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)


这是一张图片,显示我每次在Excel中得到的内容(我正在使用Excel 2007):


如您所见,Excel正在使用数据,但顶部没有冻结窗格,而且看起来有些XML WorkSheetOptions变量正在单元格A1中打印。

注意:我几乎不知道窗格及其编号如何参与所有这些工作,而且我在查找文档方面花费了很多时间。我很高兴阅读和学习是否可以找到一些东西。

知道我在做什么错吗?



编辑:以为我会发表最终解决我的问题的方法,以防其他可怜的人需要它。只花了整整一天的摆弄。

第一个功能为最终的XML准备要通过Response.Write导出的XML,如果安装了XML,则可以在Excel的客户端计算机上打开该XML。

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


结束功能

希望这可以对将来的人有所帮助。干杯。

这是一个用于格式化Excel XML表数据的函数,其结果将馈送到上述函数中。

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


这是通过Response.Write导出的完成的Excel的图像。您可以看到它具有标题行的样式,冻结窗格的工作方式,并且设置了列宽以正确显示。

最佳答案

终于找到了可行的解决方案。我在上面的原始问题区域中发布了详细信息。

07-28 01:22
查看更多