Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Post an Article
Welcome To Ask or Share your Answers For Others

Categories

vb.net - How to freeze the header row in an Excel spreadsheet exported from ASP.NET

0 votes
960 views
in Technique[技术] by (71.8m points)

vb.net - 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
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙；凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙；凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界，一个免费开放的程序员编程交流平台！开放，进步，分享！让技术改变生活，让极客改变未来！ Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

Just Browsing Browsing

1.4m articles

1.4m replys

5 comments

57.0k users

Most popular tags

Snow Theme by Q2A Market
Powered by Question2Answer
...