我正在使用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""> " & 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""> " & 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""> " & 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的图像。您可以看到它具有标题行的样式,冻结窗格的工作方式,并且设置了列宽以正确显示。
最佳答案
终于找到了可行的解决方案。我在上面的原始问题区域中发布了详细信息。