Como congelar a linha do cabeçalho em uma planilha do Excel exportada do ASP.NET

Question

Feb 25, 2010, 04:27 PM

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