Need help with Excel Exporting

[Yazster]

Hi all,

Hoping someone can shed some light on this one...

We have a web page that exports the contents of a datareader to Excel, using the attached code. All works well most of the time, except for larger extracts. For some reason, the resulting spreadsheet is quite large (ie 11.5mb). When we go to save it in Excel, the default type is set to WEB PAGE (*.htm,*.html). When we change this to Excel, and save, the resulting file is now 5mb.

Is there any reason why the produced file from the web page would be so much larger, and not defaulting to type .XLS?

Any help would be greatly appreciated.

Thanks in advance,
Yazster

 Public Shared Sub ConvertToExcel(ByVal dr As SqlDataReader, ByVal response As HttpResponse)

        response.Clear()
        response.Charset = ""
        response.ContentType = "application/vnd.ms-excel"
        response.AddHeader("Content-Disposition", "attachment;filename=MyExport.xls")

        Dim objSB As StringBuilder = New StringBuilder("")
        Dim intFieldCnt As Integer

        objSB.Append("<style>")
        objSB.Append(".text {mso-number-format:\@;}")
        objSB.Append("</style>")

        objSB.Append("<Table cellspacing=""0"" cellpadding=""0"" border=""1"" bordercolor=""#000000"" width=""100%"" style=""font-size:8pt;"">")

         'Header captions
        objSB.Append("<TR bgcolor=""#D0D0D0"">")
        For intFieldCnt = 0 To dr.FieldCount - 1
            objSB.Append("<TH>" & dr.GetName(intFieldCnt) & "</TH>")
        Next
        objSB.Append("</TR>")

        'Data fields
        Dim strTextFields As String = AvalancheFunctions.GetExcelFormatFields("TEXT")
        While dr.Read()
            objSB.Append("<TR>")
            ...
            objSB.Append("</TR>")
        End While
        objSB.Append("</Table>")

        response.Write(objSB.ToString())
        response.End()

    End Sub

 

[jbenson001]

Are you saying the file gets truncated when you save it as an xls file?

 

[Yazster]

The resulting file once I save as XLS looks exactly the same, it's just about half the size.

Unfortunately, this is happening on a large scale (hundreds of exports/day) and is definitely hurting our servers. I don't know why the file being sent is so much larger, and not defaulting to XLS, even though it does open in Excel, and looks normal.

 

[jbenson001]

I'm still confused as to what you mean the file being sent is so much larger and that it is hurting your servers.
Saving as an xls will be smaller than a text or html file because it is a more "compressed" format.

 

[Yazster]

The result of executing the routine we have to export the contents of a datareader to Excel (previously attached code), is a File Download window coming up, with the options to Download, Save, and Cancel.

The file type listed on the window is Microsoft Excel Worksheet, 11.5mb

I hit download, and the 11.5 mb is transfered from the web server to the client, launching Excel and displaying the file.

Once in Excel, if I save the workbook, the current file type displayed is WEB PAGE (*.htm,*.html). If I change this to excel and save, the resulting file is 5mb.

So I'm wondering why the original transfer from the web server to the client isn't 5mb intead of 11.5mb. Multiply this by hundreds of downloads, and it appears as though were are sending larger files over the wire than we could be.

 

[jbenson001]

Ok now I understand.. That is very strange.. and this only happens on larger files... Have you checked MSDN?

 

[Yazster]

It happens all the time, just more of an obvious issue on the larger files.

Haven't specifically searched MSDN, been googling everywhere for any kind of info, seems difficult to find.

I'll try MSDN to see if I have any luck.

Thanks!

 

[jbenson001]

Post back if you find anything..