Export to Excel Page Orientation

Home ›

ASP.NET Forums » General ASP.NET » Data Presentation Controls » Export to Excel Page Orientation

Shortcuts

Previous Next

Export to Excel Page Orientation

Rate It (1)

Last post 10-28-2008 4:43 PM by flyby35. 5 replies.

Sort Posts:

Export to Excel Page Orientation

01-25-2008, 6:52 AM

Contact

118 point Member
paragp
Member since 03-02-2005, 4:22 AM
Posts 31

Hi all,

I have a method export to excel the contents of a datagrid. This work fine, however I need to set the page orientation of the excel sheet to landscape. How can I do this?

My export to excel method is as below:

public void ExportDataGridToExcel(System.Web.UI.WebControls.DataGrid dg, System.Web.HttpResponse response)

{

response.Clear();

response.Buffer= true;

response.ContentType = "application/vnd.ms-excel";

response.AddHeader("content-disposition", "attachment;filename=Projects.xls");

response.Charset = "";

System.IO.StringWriter oStringWriter = new System.IO.StringWriter();System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);this.ClearControls(dg);

dg.RenderControl(oHtmlTextWriter);

response.Write("<meta http-equiv=Content-Type content=\"text/html; charset=utf-8\">");

response.Write(oStringWriter.ToString());

response.End();

}

any help greatly appreciated

Filed under: Export to Excel, landscape, page orientation

Re: Export to Excel Page Orientation

01-27-2008, 4:42 AM

Contact

63,746 point All-Star
Mikesdotnetting
Member since 12-11-2006, 8:43 PM
UK
Posts 9,693

I suspect this can't be done via ASP.NET, as the orientation is set typically as one of the printer properties. You have no access to the printer on the client. Also, you are not actually dealing with an Excel file. You are creating an HTML file that Excel is happy to recognise up to a point.

You may be able to achieve what you want by creating an Excel document as a kind of template and putting it on the server, then using the Jet OleDb provider to write the values to it.

Regards Mike
[MVP - ASP/ASP.NET]
My site Please help - URGENT!!! What ASP.NET can and can't do

Re: Export to Excel Page Orientation

01-28-2008, 11:24 AM

Contact

Answer

118 point Member
paragp
Member since 03-02-2005, 4:22 AM
Posts 31

I have managed to get it work as follows:

public void ExportDataGridToExcel(System.Web.UI.Control ctrl, System.Web.HttpResponse response)

{

response.Clear();

response.Buffer= true;

response.Cache.SetCacheability(HttpCacheability.NoCache);

response.ContentType = "application/vnd.ms-excel";

response.AddHeader("content-disposition", "attachment;filename=Projects.xls");

response.Charset = "";

this.EnableViewState = false;

System.IO.StringWriter oStringWriter = new System.IO.StringWriter();

System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);

this.ClearControls(ctrl);

ctrl.RenderControl(oHtmlTextWriter);

// set content type and character set to cope with european chars like the umlaut.

response.Write("<meta http-equiv=Content-Type content=\"text/html; charset=utf-8\">\n");

// add the style props to get the page orientation

response.Write(AddExcelStyling());

response.Write(oStringWriter.ToString());

response.Write("</body>");response.Write("</html>");

response.End();

}

private string AddExcelStyling()

{

StringBuilder sb = new StringBuilder();

sb.Append("<html xmlns:o='urn:schemas-microsoft-com:office:office'\n" +

"xmlns:x='urn:schemas-microsoft-com:office:excel'\n" +

"xmlns='http://www.w3.org/TR/REC-html40'>\n" +

"<head>\n");

sb.Append("<style>\n");

sb.Append("@page");

sb.Append("{margin:.5in .75in .5in .75in;\n");

sb.Append("mso-header-margin:.5in;\n");

sb.Append("mso-footer-margin:.5in;\n");

sb.Append("mso-page-orientation:landscape;}\n");

sb.Append("</style>\n");

sb.Append("<!--[if gte mso 9]><xml>\n");

sb.Append("<x:ExcelWorkbook>\n");

sb.Append("<x:ExcelWorksheets>\n");

sb.Append("<x:ExcelWorksheet>\n");

sb.Append("<x:Name>Projects 3 </x:Name>\n");

sb.Append("<x:WorksheetOptions>\n");

sb.Append("<x:Print>\n");

sb.Append("<x:ValidPrinterInfo/>\n");

sb.Append("<x:PaperSizeIndex>9</x:PaperSizeIndex>\n");

sb.Append("<x:HorizontalResolution>600</x:HorizontalResolution\n");

sb.Append("<x:VerticalResolution>600</x:VerticalResolution\n");

sb.Append("</x:Print>\n");

sb.Append("<x:Selected/>\n");

sb.Append("<x:DoNotDisplayGridlines/>\n");

sb.Append("<x:ProtectContents>False</x:ProtectContents>\n");

sb.Append("<x:ProtectObjects>False</x:ProtectObjects>\n");

sb.Append("<x:ProtectScenarios>False</x:ProtectScenarios>\n");

sb.Append("</x:WorksheetOptions>\n");

sb.Append("</x:ExcelWorksheet>\n");

sb.Append("</x:ExcelWorksheets>\n");

sb.Append("<x:WindowHeight>12780</x:WindowHeight>\n");

sb.Append("<x:WindowWidth>19035</x:WindowWidth>\n");

sb.Append("<x:WindowTopX>0</x:WindowTopX>\n");

sb.Append("<x:WindowTopY>15</x:WindowTopY>\n");

sb.Append("<x:ProtectStructure>False</x:ProtectStructure>\n");

sb.Append("<x:ProtectWindows>False</x:ProtectWindows>\n");

sb.Append("</x:ExcelWorkbook>\n");

sb.Append("</xml><![endif]-->\n");

sb.Append("</head>\n");

sb.Append("<body>\n");

return sb.ToString();

}

the resulting worksheet has its page orientation set to Landscape.

Filed under: export to excel, landscape, page orientation

Re: Export to Excel Page Orientation

07-28-2008, 7:17 PM

Contact

2 point Member
rzhong
Member since 07-28-2008, 11:11 PM
Posts 1

Thanks it works great on Excel!

Do you happen to know how to do it in ms-word/powerpoint? or where can I find the answer?

Big Smile
-Rick

Re: Export to Excel Page Orientation

10-24-2008, 3:54 PM

Contact

112 point Member
flyby35
Member since 09-02-2008, 2:25 PM
Posts 325

hi,

I have my data from Gridview. Can you show me how to call this export to excel function? Thank you for your help

Re: Export to Excel Page Orientation

10-28-2008, 4:43 PM

Contact

112 point Member
flyby35
Member since 09-02-2008, 2:25 PM
Posts 325

Could you please post the complete codes for me? I could not get mine to work

Thanks

Page 1 of 1 (6 items)

RSS Available