Export to Excel with landscape orientation

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)
            gv.RenderControl(myHtmlWriter)

            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
            Response.Write("<html>")
            Response.Write("<body>")

            'Output content of datagrid

            Response.Write(myTextWriter.ToString())
            Response.Write("</body></html>")

            '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()
        Else
            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

newbie_girlAsked:
Who is Participating?
 
Computer101Commented:
PAQed with points refunded (500)

Computer101
EE Admin
0
 
TommynatorCommented:
hi,

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.
0
 
newbie_girlAuthor Commented:
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

0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
TommynatorCommented:
can you elaborate more in what you are trying to do ?
0
 
newbie_girlAuthor Commented:
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?

cheers
0
 
newbie_girlAuthor Commented:
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)
            gv.RenderControl(myHtmlWriter)
            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"
            Response.Redirect("ExportReport.aspx")

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"
    xmlns="http://www.w3.org/TR/REC-html40">
<head>
    <meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
    <meta name="ProgId" content="Excel.Sheet">
    <meta name="Generator" content="Microsoft Excel 10">
    <style>
<!--table
     {mso-displayed-decimal-separator:"\.";
     mso-displayed-thousand-separator:"\,";}
@page
     {margin:.5in .5in .5in .5in;
     mso-header-margin:.5in;
     mso-footer-margin:.5in;
     mso-page-orientation:landscape;}
tr
     {mso-height-source:auto;}
col
     {mso-width-source:auto;}
br
     {mso-data-placement:same-cell;}
-->
</style>
    <!--[if gte mso 9]><xml>
 <x:ExcelWorkbook>
  <x:ExcelWorksheets>
   <x:ExcelWorksheet>
    <x:Name>Sheet1</x:Name>
    <x:WorksheetOptions>
     <x:FitToPage/>
     <x:Print>
      <x:ValidPrinterInfo/>
      <x:HorizontalResolution>600</x:HorizontalResolution>
      <x:VerticalResolution>600</x:VerticalResolution>
     </x:Print>
     <x:Selected/>
     <x:ProtectContents>False</x:ProtectContents>
     <x:ProtectObjects>False</x:ProtectObjects>
     <x:ProtectScenarios>False</x:ProtectScenarios>
    </x:WorksheetOptions>
   </x:ExcelWorksheet>
   <x:ExcelWorksheet>
    <x:Name>Sheet2</x:Name>
    <x:WorksheetOptions>
     <x:ProtectContents>False</x:ProtectContents>
     <x:ProtectObjects>False</x:ProtectObjects>
     <x:ProtectScenarios>False</x:ProtectScenarios>
    </x:WorksheetOptions>
   </x:ExcelWorksheet>
   <x:ExcelWorksheet>
    <x:Name>Sheet3</x:Name>
    <x:WorksheetOptions>
     <x:ProtectContents>False</x:ProtectContents>
     <x:ProtectObjects>False</x:ProtectObjects>
     <x:ProtectScenarios>False</x:ProtectScenarios>
    </x:WorksheetOptions>
   </x:ExcelWorksheet>
  </x:ExcelWorksheets>
  <x:WindowHeight>9345</x:WindowHeight>
  <x:WindowWidth>11340</x:WindowWidth>
  <x:WindowTopX>480</x:WindowTopX>
  <x:WindowTopY>60</x:WindowTopY>
  <x:ProtectStructure>False</x:ProtectStructure>
  <x:ProtectWindows>False</x:ProtectWindows>
 </x:ExcelWorkbook>
</xml><![endif]-->
</head>
<body link="blue" vlink="purple">
<%  Response.Write(Session("WriteExcelFile"))%>
</body>
</html>

Sorry, Tommynator, no points, but thanks a lot for the suggestions
Cheers
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.