Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Export to Excel with landscape orientation

Posted on 2006-11-07
7
Medium Priority
?
4,653 Views
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)
            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

0
Comment
Question by:newbie_girl
[X]
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
7 Comments
 

Expert Comment

by:Tommynator
ID: 17897026
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
 

Author Comment

by:newbie_girl
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

0
 

Expert Comment

by:Tommynator
ID: 17904726
can you elaborate more in what you are trying to do ?
0
Industry Leaders: 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

by:newbie_girl
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?

cheers
0
 

Author Comment

by:newbie_girl
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)
            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
 
LVL 1

Accepted Solution

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

Computer101
EE Admin
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I have developed many web applications with asp & asp.net and to add and use a dropdownlist was always a very simple task, but with the new asp.net, setting the value is a bit tricky and its not similar to the old traditional method. So in this a…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

609 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