Member Avatar for RichLangford

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.

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

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

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()
  • 3 Contributors
  • 3 Replies
  • 212 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by sedgey

Recommended Answers

All 3 Replies

Member Avatar for mr.white

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.

Member Avatar for sedgey

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:

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();
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.