Cómo congelar la fila del encabezado en una hoja de cálculo de Excel exportada desde ASP.NET
Estoy exportando una vista de cuadrícula ASP.NET a Excel usando la siguiente función. El formato funciona muy bien, excepto que necesito congelar la fila del encabezado en Excel en la exportación. Realmente estoy tratando de evitar el uso de un complemento de Excel de terceros para esto, pero a menos que haya algún marcado arcaico de Excel en mi función 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
Y el formato de la 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