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
