Export to Excel with landscape orientation

Posted on 2006-11-07
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/"
            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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 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 ?
Independent Software Vendors: 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!


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/"
    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

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

Lots of people ask this question on how to extend the “MembershipProvider” to make use of custom authentication like using existing database or make use of some other way of authentication. Many blogs show you how to extend the membership provider c…
A quick way to get a menu to work on our website, is using the Menu control and assign it to a web.sitemap using SiteMapDataSource. Example of web.sitemap file: (CODE) Sample code to add to the page menu: (CODE) Running the application, we wi…
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

688 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