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'>" + 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
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.
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
加载中,请稍侯......
精彩评论