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
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 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 ?

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.


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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

In .NET 2.0, Microsoft introduced the Web Site.  This was the default way to create a web Project in Visual Studio 2005.  In Visual Studio 2008, the Web Application has been restored as the default web Project in Visual Studio/.NET 3.x The Web Si…
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.
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…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

800 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