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)
"";
System.IO.StringWriter oStringWriter =
new System.IO.StringWriter();System.Web.UI.HtmlTextWriter oHtmlTextWriter =
new System.Web.UI.HtmlTextWriter(oStringWriter);this.ClearControls(dg);
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.
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
paragp
Member
118 Points
31 Posts
Export to Excel Page Orientation
Jan 25, 2008 10:52 AM|LINK
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
Export to Excel landscape page orientation
Mikesdotnetting
All-Star
134583 Points
16893 Posts
Moderator
MVP
Re: Export to Excel Page Orientation
Jan 27, 2008 08:42 AM|LINK
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.
Beginning ASP.NET Web Pages with WebMatrix | My Site | Twitter
paragp
Member
118 Points
31 Posts
Re: Export to Excel Page Orientation
Jan 28, 2008 03:24 PM|LINK
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 orientationresponse.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.
export to excel landscape page orientation
rzhong
Member
2 Points
1 Post
Re: Export to Excel Page Orientation
Jul 28, 2008 11:17 PM|LINK
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?
[:D]
-Rick
flyby35
Member
134 Points
370 Posts
Re: Export to Excel Page Orientation
Oct 24, 2008 07:54 PM|LINK
hi,
I have my data from Gridview. Can you show me how to call this export to excel function? Thank you for your help
flyby35
Member
134 Points
370 Posts
Re: Export to Excel Page Orientation
Oct 28, 2008 08:43 PM|LINK
Could you please post the complete codes for me? I could not get mine to work
Thanks