如何冻结从 ASP.NET 导出的 Excel 电子表格中的标题行 [英] How to freeze the header row in an Excel spreadsheet exported from ASP.NET
问题描述
我正在使用以下函数将 ASP.NET gridview 导出到 Excel.格式工作得非常好，除了我需要在导出时冻结 Excel 中的标题行.我真的试图避免为此使用第 3 方 Excel 插件，但除非我的 AddExcelStyling 函数中有一些陈旧的 excel 标记.
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
还有格式化黑魔法:
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'>" + 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
推荐答案
将 WorksheetOption 元素修改为如下所示:
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>
注意 FreezePanes 元素.我把它从我保存为 HTML 的电子表格中剪下来，第一行被冻结.当这个文件用 Excel 打开时，第一行被冻结.
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.
要在每一页上打印标题行，您需要这样的东西:
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.
这篇关于如何冻结从 ASP.NET 导出的 Excel 电子表格中的标题行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
