Export to Excel with landscape orientation

Posted on 2006-11-07
Medium Priority
Last Modified: 2012-05-05
Hi folks,

At the moment I have the following code that exports fine to Excel but I need to change the orientation to landscape and fit to 1 page (width).  Can I do this in code-behind - can someone please show me how?

thank you......!

Private Sub WriteToExcel(ByRef gv As GridView)
        If gv.Rows.Count > 0 Then
            'Reset headings etc
            gv.HeaderStyle.BackColor = Drawing.Color.LightGray
            gv.HeaderStyle.Font.Bold = True
            gv.GridLines = GridLines.Both
            Dim myTextWriter As New System.IO.StringWriter
            Dim myHtmlWriter As New HtmlTextWriter(myTextWriter)

            Response.Expires = 0 ' prevent caching
            Response.Buffer = True
            Response.ContentType = "application/vnd.ms-excel"
            Dim strFileName As String = "Report.xls"
            Response.AddHeader("Content-Disposition", "attachment;Filename=" & strFileName)

            'Page Starts here

            'Output content of datagrid


            'Suppress the render to avoid the .NET generated HTML interfering with that manually generated
            m_blnSuppressRender = True

            If Not myHtmlWriter Is Nothing Then myHtmlWriter.Close()
            If Not myTextWriter Is Nothing Then myTextWriter.Close()
            ClientScript.RegisterClientScriptBlock(Me.GetType(), "warning", "<script language=""javascript"">" & vbCrLf & "alert('No Records found');" & vbCrLf & "</script>")
            'lblErrorMessage.Text = "The client selected returned no results"
            'lblErrorMessage.Visible = True
        End If
    End Sub

Question by:newbie_girl
  • 3
  • 2

Expert Comment

ID: 17897026

from my experience, you have 2 options :

1) com interop (executing excel on the server and let it create an excel with landscape setting)

example out of my head :

(Excel 2003, Primary Interop Assebly should be installed from setup)

// code snippet begin
using Microsoft.Office.Interop.Excel;

object miss = Type.Missing;
ApplicationClass oXL = new ApplicationClass();
oXL.Visible = false;

//Get a new workbook.
Workbook oWB = (Workbook) oXL.Workbooks.Add(miss);
oXL.Visible = true;

Worksheet oSheet = (Worksheet) oWB.ActiveSheet;
oSheet.PageSetup.Orientation= XlPageOrientation.xlLandscape; <-- tadaaaa
oSheet.PageSetup.PaperSize = XlPaperSize.xlPaperLegal;
// code snippet end

from my experience this solution is slow, hard to get it working and not recommended by MS

2) i really recommend to buy a license from this component http://www.aspose.com/Wiki/default.aspx/Aspose.Cells/ProductOverview.html which does not need excel installed on the server and is much faster and stable.

kind regards, Tom.

Author Comment

ID: 17902438
Cheers Tom, the code i've got is working ok so I'm hoping to edit that if anyone has any other suggestions?  There are some examples of xml written in the aspx page.  Surely there's a simple way to add the bare minimum to the response.write ?

thanks a lot


Expert Comment

ID: 17904726
can you elaborate more in what you are trying to do ?
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.


Author Comment

ID: 17935073
Hey Tom

I'm using a gridview in .NET V2 to render the report on the screen. The sub (above) works absolutely fine to export and open and display in excel.  I have to imagine that given all the examples on the web written in VB to excel that I would need to add or alter what is written in the 'response.write' but I just can't find any examples.

That is, unless I create an aspx page and write xml in that.  This is my last option as the code above is a lot cleaner and quicker.

ideas? Anyone else with a suggestion?


Author Comment

ID: 18024387
I resolved it using a session. Here's the code if it helps anyone. I ended up putting the gridview (SQL is generated at runtime) into a session and then used a response.redirect to a new aspx page.  vb code like so:

  Private Sub WriteToExcel(ByRef gv As GridView)
        If gv.Rows.Count > 0 Then
            'Reset headings etc
            gv.HeaderStyle.BackColor = Drawing.Color.LightGray
            gv.HeaderStyle.Font.Bold = True
            Dim myTextWriter As New System.IO.StringWriter
            Dim myHtmlWriter As New HtmlTextWriter(myTextWriter)
            Session("WriteExcelFile") = myTextWriter.ToString()
            Dim strFileName As String = Replace(gv.ID, "gv", "")
            Session("FileName") = strFileName & " Report_" & Day(Now()) & Month(Now()) & Right(Year(Now()), 2) & ".xls"

Then, in the new aspx page I called the session in the body of the html, like so: The output from the vb page seems to overwrite any style set in the new aspx page.  

<% Response.ContentType = "application/vnd.ms-excel"
    Response.AddHeader("Content-Disposition", "attachment;filename=" & Session("FileName"))
<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel"
    <meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
    <meta name="ProgId" content="Excel.Sheet">
    <meta name="Generator" content="Microsoft Excel 10">
     {margin:.5in .5in .5in .5in;
    <!--[if gte mso 9]><xml>
<body link="blue" vlink="purple">
<%  Response.Write(Session("WriteExcelFile"))%>

Sorry, Tommynator, no points, but thanks a lot for the suggestions

Accepted Solution

Computer101 earned 0 total points
ID: 18171156
PAQed with points refunded (500)

EE Admin

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In an ASP.NET application, I faced some technical problems. In this article, I list them out and show the solutions that I found.  I hope it will be useful. Problem: After closing a pop-up window, the parent page should be refreshed automaticall…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Integration Management Part 2
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question