Export to Excel and landscape

jj297

SOLVED CLOSED ACTIVE ANSWERED
User: jj297
Posted: on Feb 02, 2012 07:00 AM
Forum: ASP.Net Web Forms
Answer: 1
Views: 11740

Hello I'm back again...When the page exports to excel I would like for the page to be set up as landscape rather than the user having to make that decision when they print the page. Is that possible?

Protected Sub BtnExportExcel_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click
        Response.Clear()
        Response.AddHeader("content-disposition", "attachment;filename=Area.xls")
        Response.Charset = ""
        Response.Cache.SetCacheability(HttpCacheability.NoCache)
        Response.ContentType = "application/vnd.xls"
        Dim stringWrite As New System.IO.StringWriter()
        Dim htmlWrite As System.Web.UI.HtmlTextWriter = New HtmlTextWriter(stringWrite)
        GridView1.HeaderRow.Attributes.Add("style", "font-size:10px")
        GridView1.Caption = "T16 DI Area and District Pending Claims Listings"
       For i As Integer = 0 To GridView1.Rows.Count - 1
            Dim row As GridViewRow = GridView1.Rows(i)
            'Change Color back to white
            row.BackColor = System.Drawing.Color.White
            'Apply text style to each Row
            row.Attributes.Add("class", "textmode")        Next
        Me.RemoveControls(GridView1)        Dim swriter As New StringWriter()
        Dim hwriter As New HtmlTextWriter(swriter)
        Dim frm As New HtmlForm()        Me.GridView1.Parent.Controls.Add(frm)
        frm.Attributes("runat") = "server"        frm.Controls.Add(GridView1)
        frm.RenderControl(hwriter)        Response.Write(swriter.ToString())
        Response.[End]()    End Sub

Download FREE API for Word, Excel and PDF in ASP.Net

jj297

0 11

Reply
User: jj297
Replied: on Feb 02, 2012 07:45 AM
Report

Sorry here's the code:

 Private Function AddExcelStyling() As String
        Dim sb As New StringBuilder()
        sb.Append("<html xmlns:o='urn:schemas-microsoft-com:office:office'" & vbLf & "xmlns:x='urn:schemas-microsoft-com:office:excel'" & vbLf & "xmlns='http://www.w3.org/TR/REC-html40'>" & vbLf & "<head>" & vbLf)
        sb.Append("<style>" & vbLf)        sb.Append("@page")
        sb.Append("{margin:.0in .0in .0in .0in;" & vbLf)
        sb.Append("mso-header-margin:.5in;" & vbLf)
        sb.Append("mso-footer-margin:.5in;" & vbLf)
        sb.Append("mso-page-orientation:landscape;}" & vbLf)
        sb.Append("mso-header-text:wrap;}" & vbLf)
        sb.Append("</style>" & vbLf)
        sb.Append("<!--[if gte mso 9]><xml>" & vbLf)
        sb.Append("<x:ExcelWorkbook>" & vbLf)
        sb.Append("<x:ExcelWorksheets>" & vbLf)
        sb.Append("<x:ExcelWorksheet>" & vbLf)
        sb.Append("<x:Name>Projects 3 </x:Name>" & vbLf)
        sb.Append("<x:WorksheetOptions>" & vbLf)
        sb.Append("<x:Print>" & vbLf)
        sb.Append("<x:ValidPrinterInfo/>" & vbLf)
        sb.Append("<x:PaperSizeIndex>9</x:PaperSizeIndex>" & vbLf)
        sb.Append("<x:HorizontalResolution>600</x:HorizontalResolution" & vbLf)
        sb.Append("<x:VerticalResolution>600</x:VerticalResolution" & vbLf)
        sb.Append("</x:Print>" & vbLf)
        sb.Append("<x:Selected/>" & vbLf)
        sb.Append("<x:DoNotDisplayGridlines/>" & vbLf)
        sb.Append("<x:ProtectContents>False</x:ProtectContents>" & vbLf)
        sb.Append("<x:ProtectObjects>False</x:ProtectObjects>" & vbLf)
        sb.Append("<x:ProtectScenarios>False</x:ProtectScenarios>" & vbLf)
        sb.Append("</x:WorksheetOptions>" & vbLf)
        sb.Append("</x:ExcelWorksheet>" & vbLf)
        sb.Append("</x:ExcelWorksheets>" & vbLf)
        sb.Append("<x:WindowHeight>12780</x:WindowHeight>" & vbLf)
        sb.Append("<x:WindowWidth>19035</x:WindowWidth>" & vbLf)
        sb.Append("<x:WindowTopX>0</x:WindowTopX>" & vbLf)
        sb.Append("<x:WindowTopY>15</x:WindowTopY>" & vbLf)
        sb.Append("<x:ProtectStructure>False</x:ProtectStructure>" & vbLf)
        sb.Append("<x:ProtectWindows>False</x:ProtectWindows>" & vbLf)
        sb.Append("</x:ExcelWorkbook>" & vbLf)
        sb.Append("</xml><![endif]-->" & vbLf)
        sb.Append("</head>" & vbLf)
        sb.Append("<body>" & vbLf)        Return sb.ToString()
    End Function

Mudassar

0 12

Reply
User: Mudassar
Replied: on Feb 02, 2012 07:48 AM
Report

What's this? Is your problem resolved. if yes let me know how?

jj297

0 13

Reply
User: jj297
Replied: on Feb 02, 2012 08:11 AM
Report

Yes I am now able to have the page come up in landscape with a stringbuilder.

I added a private function called AddExcelStyling and called it in the BtnExportExcel_Click. This makes the page come in as landscape:

The bold section makes the page comes in as landscape. What I have bold underneath I am trying to get the text to wrap but it's not doing it.

Private Function AddExcelStyling() As String Dim sb As New StringBuilder() sb.Append("<html xmlns:o='urn:schemas-microsoft-com:office:office'" & vbLf & "xmlns:x='urn:schemas-microsoft-com:office:excel'" & vbLf & "xmlns='http://www.w3.org/TR/REC-html40'>" & vbLf & "<head>" & vbLf) sb.Append("<style>" & vbLf) sb.Append("@page") sb.Append("{margin:.0in .0in .0in .0in;" & vbLf) sb.Append("mso-header-margin:.5in;" & vbLf) sb.Append("mso-footer-margin:.5in;" & vbLf) sb.Append("mso-page-orientation:landscape;}" & vbLf) sb.Append("mso-header-text:wrap;}" & vbLf) sb.Append("</style>" & vbLf) sb.Append("" & vbLf) sb.Append("</head>" & vbLf) sb.Append("<body>" & vbLf) Return sb.ToString() End Function

Mudassar

0 14

Reply
User: Mudassar
Replied: on Feb 02, 2012 08:14 AM
Report

Where is the GridView HTML in this?

jj297

0 15

Reply
User: jj297
Replied: on Feb 02, 2012 08:20 AM
Report

It's here the bold part:

 Protected Sub BtnExportExcel_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click
        response.Clear()        response.Buffer = True
        response.Cache.SetCacheability(HttpCacheability.NoCache)
        Response.AddHeader("content-disposition", "attachment;filename=Projects.xls")
        Response.Charset = ""
        Response.ContentType = "application/vnd.ms-excel"
        Me.EnableViewState = False
        'Dim oStringWriter As New System.IO.StringWriter()
        Dim sw As New IO.StringWriter()        Dim hw As New HtmlTextWriter(sw)
        GridView1.AllowPaging = False        GridView1.DataBind()
        'adding spaces to the left and right of the title
        GridView1.Caption = "T16 DI Area Pending Claims Listings"
        GridView1.HeaderRow.Attributes.Add("style", "font-size:10px")
        For i As Integer = 0 To GridView1.Rows.Count - 1
            Dim row As GridViewRow = GridView1.Rows(i)
            'Change Color back to white
            row.BackColor = System.Drawing.Color.White
            'Apply text style to each Row
            row.Attributes.Add("class", "textmode")        Next
        Me.RemoveControls(GridView1)        GridView1.RenderControl(hw)
        Dim style As String = "<style> .textmode { mso-number-format:\@; text-wrap; } </style>"
        Response.Write(style)
        ' set content type and character set to cope with european chars like the umlaut.
        Response.Write("<meta http-equiv=Content-Type content=""text/html; charset=utf-8"">" & vbLf)
        ' add the style props to get the page orientation
        response.Write(AddExcelStyling())        Response.Write(sw.ToString())
        Response.Flush()        Response.Write("</body>") Response.Write("</html>")  response.[End]()    End Sub

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html

Export to Excel and landscape

Company

Explore

Follow us

Contact Us