Como congelar a linha do cabeçalho em uma planilha do Excel exportada do ASP.NET
Estou exportando um gridview do ASP.NET para o Excel usando a seguinte função. A formatação está funcionando muito bem, exceto que eu preciso congelar a linha do cabeçalho no Excel na exportação. Estou realmente tentando evitar o uso de um plug-in do Excel de terceiros, mas a menos que haja alguma marcação arcaica do excel na minha função AddExcelStyling.
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
E a formatação de magia negra:
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