ASP.NET

ASP.NET RSS

Excel Export

Please support our ASP.NET advertiser: Intel Parallel Studio Home

•

Join Date: May 2008

Posts: 1

Reputation: RichLangford is an unknown quantity at this point

Solved Threads: 0

RichLangford RichLangford is offline

Offline

Newbie Poster

Excel Export

Jan 13th, 2009

Hi everyone

Here is my problem. I have been tasked with creating an excel export from my asp page which I have done successfully. The problem comes when the file is being downloaded. The excel spreadsheet contains about 3,000 records but the file size is over 11MB. When I attempt to save the file, excel tries to save it as a web page which is why I think the file size is so big. When the file is saved as an excel file, the file size shrinks dramatically to about 700k which is more realistic...

I have investigated the file content code in my asp page but I can't see anything that would cause excel to think that the spreadsheet is a web page. My code is below, if anyone has any ideas I would really appreciate your comments.

 Help with Code Tags 
 asp.net Syntax (Toggle Plain Text) 
Response.Clear()
            Response.Buffer = True
            Response.ContentType = "application/vnd.ms-excel"
            Response.AddHeader("content-disposition", "attachment;filename=Management Report.xls")
            Response.Charset = ""
            EnableViewState = False
Response.Clear()
            Response.Buffer = True
            Response.ContentType = "application/vnd.ms-excel"
            Response.AddHeader("content-disposition", "attachment;filename=Management Report.xls")
            Response.Charset = ""
            EnableViewState = False

'Create a string builder to store the header of the file including printer set, titles, style, and sheet layout

 Help with Code Tags 
 asp.net Syntax (Toggle Plain Text) 
Dim sb As New StringBuilder
            sb.Append("<html xmlns:x=""urn:schemas-microsoft-com:office:excel"">" & vbCrLf)
            sb.Append("<head><style>@page {mso-header-data:""" & "Management Report" & """; mso-footer-data:""&C&P of &N""; mso-page-orientation:landscape;}" & vbCrLf)
            sb.Append("table {font-size:10.0pt; font-family:Arial, sans-serif;}" & vbCrLf)
            sb.Append(".xlTitleCell {font-size:10.0pt; font-weight:bold; colour:blue; back-colour:red; text-align:middle}" & vbCrLf)
            sb.Append(".xlNumberCell {text-align:center; mso-number-format:""\#\,\#\#0\.00""}" & vbCrLf)
            sb.Append(".xlPercentCell {text-align:center; mso-number-format:""0\.00%""}" & vbCrLf)
 
            sb.Append("</style><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name>Report </x:Name><x:WorksheetOptions><x:FreezePanes/><x:FrozenNoSplit/>" & vbCrLf)
Dim sb As New StringBuilder
            sb.Append("<html xmlns:x=""urn:schemas-microsoft-com:office:excel"">" & vbCrLf)
            sb.Append("<head><style>@page {mso-header-data:""" & "Management Report" & """; mso-footer-data:""&C&P of &N""; mso-page-orientation:landscape;}" & vbCrLf)
            sb.Append("table {font-size:10.0pt; font-family:Arial, sans-serif;}" & vbCrLf)
            sb.Append(".xlTitleCell {font-size:10.0pt; font-weight:bold; colour:blue; back-colour:red; text-align:middle}" & vbCrLf)
            sb.Append(".xlNumberCell {text-align:center; mso-number-format:""\#\,\#\#0\.00""}" & vbCrLf)
            sb.Append(".xlPercentCell {text-align:center; mso-number-format:""0\.00%""}" & vbCrLf)

            sb.Append("</style><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name>Report </x:Name><x:WorksheetOptions><x:FreezePanes/><x:FrozenNoSplit/>" & vbCrLf)

'Render the table as html and write the response

 Help with Code Tags 
 asp.net Syntax (Toggle Plain Text) 
Dim sw As New StringWriter
            Dim hw As New HtmlTextWriter(sw)
            Page.FindControl("tblExcelExport").RenderControl(hw)
            sb.Append("<x:SplitHorizontal>" + intRowsAdded.ToString() + "</x:SplitHorizontal><x:SplitVertical>6</x:SplitVertical><x:TopRowBottomPane>" + (5 + intRowsAdded).ToString() + "</x:TopRowBottomPane><x:LeftColumnRightPane>17</x:LeftColumnRightPane><x:ActivePane>0</x:ActivePane><x:Panes><x:Pane><x:Number>0</x:Number></x:Pane></x:Panes>" & vbCrLf)
            sb.Append("<x:Print><x:Scale>65</x:Scale><x:ValidPrinterInfo/><x:PaperSizeIndex>9</x:PaperSizeIndex></x:Print></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook><x:ExcelName><x:Name>Print_Titles</x:Name><x:SheetIndex>1</x:SheetIndex><x:Formula>=!$1:$7</x:Formula></x:ExcelName></xml></head><body>" & vbCrLf)
            sb.Append(sw.ToString() & "</body></html>")
            Response.Write(sb.ToString())
            Response.End()
Dim sw As New StringWriter
            Dim hw As New HtmlTextWriter(sw)
            Page.FindControl("tblExcelExport").RenderControl(hw)
            sb.Append("<x:SplitHorizontal>" + intRowsAdded.ToString() + "</x:SplitHorizontal><x:SplitVertical>6</x:SplitVertical><x:TopRowBottomPane>" + (5 + intRowsAdded).ToString() + "</x:TopRowBottomPane><x:LeftColumnRightPane>17</x:LeftColumnRightPane><x:ActivePane>0</x:ActivePane><x:Panes><x:Pane><x:Number>0</x:Number></x:Pane></x:Panes>" & vbCrLf)
            sb.Append("<x:Print><x:Scale>65</x:Scale><x:ValidPrinterInfo/><x:PaperSizeIndex>9</x:PaperSizeIndex></x:Print></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook><x:ExcelName><x:Name>Print_Titles</x:Name><x:SheetIndex>1</x:SheetIndex><x:Formula>=!$1:$7</x:Formula></x:ExcelName></xml></head><body>" & vbCrLf)
            sb.Append(sw.ToString() & "</body></html>")
            Response.Write(sb.ToString())
            Response.End()

Last edited by peter_budo; Jan 13th, 2009 at 5:41 pm. Reason: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks) and [icode] (inline code) tags.

•

Join Date: Jan 2009

Posts: 12

Reputation: mr.white is an unknown quantity at this point

Solved Threads: 0

mr.white

Offline

Newbie Poster

Re: Excel Export

Jan 13th, 2009

Hello,

why don't you just put Response.ContentType = "application/vnd.ms-excel" at the top of the document and use html table to build it simply?

that's what I used to do and never had any problems.

though I'm not a pro or anything, just trying to help.

•

Join Date: Jan 2007

Posts: 130

Reputation: sedgey is on a distinguished road

Solved Threads: 8

sedgey

Offline

Junior Poster

Re: Excel Export

Jan 13th, 2009

I would suggest downloading one of the express versions of Visual Studio 200X and registering it, after registering you get access to registration benefits, one of these is a .NET component called SpreadsheetGear 2006, it's free and makes real Spreadsheets, admittedly there is a learning curve, but essentially you will be providing clients with a real .xls file instead of tricking the clients OS into saving html or CSV as a .xls file.

It enables you to do this:

Help with Code Tags

(Toggle Plain Text)

		DataTable dt = /*get your data table here*/
		IWorkbook workbook = Factory.GetWorkbook();
		IWorksheet worksheet = workbook.Worksheets["Sheet1"];
		IRange cells = worksheet.Range;
cells.CopyFromDataTable(dt, SpreadsheetGear.Data.SetDataFlags.NoColumnHeaders);
		// Auto size all worksheet columns which contain data
		worksheet.UsedRange.Columns.AutoFit();

		// Stream the Excel spreadsheet to the client in a format
		// compatible with Excel 97/2000/XP/2003/2007.
		Response.Clear();
		Response.ContentType = "application/vnd.ms-excel";
		Response.AddHeader("Content-Disposition", "attachment; filename=" yourfilename.xls");
		workbook.SaveToStream(Response.OutputStream, FileFormat.XLS97);
		Response.End();

David Ridgway: so little daylight, too much caffeine
MCSD MCAD MCSE
http://web2asp.net