Exporting to Excel

We've noticed that reports exported to excel are much larger than they need to be. Example, report generated and exported (and saved in Excel) shows size of 14,111 KB. However, if same file is saved (using SaveAs in Excel) file is only 4,762KB. Any idea as to why there would be such a size difference? Does RS pass some overhead when exporting to Excel? We are hoping to eliminate any overhead since the time cost during export is rising.

(there are no images being passed, just ~33,000 rows of data)

Thanks.

What method are you using to create the excel files?

|||

We have an ASP front-end that handles some security (report access, restricting what data a user can pass in via parameters) and makes the actual call to the report server for the given report. For most reports, the report parameter is passed to force the report to be exported to Excel (instead of rendering in IE).

So, I run the report, Excel launches and displays the data, and I simply do File/SaveAs... (and rename the file)

Here are some tests I ran against other reports that auto-export to Excel:

ReportA

Test1

Original Spreadsheet: 852k

SaveAs with no changes: 390k

Test2

Original Spreadsheet: 99k

SaveAs with no changes: 52k

Test3

Original Spreadsheet: 365k

SaveAs with no changes: 169k

ReportB

Test1

Original Spreadsheet: 245k

SaveAs with no changes: 104k

Test2

Original Spreadsheet: 365k

SaveAs with no changes: 169k

ReportC

Test1

Original Spreadsheet: 35k

SaveAs with no changes: 34k

Other tests revealed similar results

ReportD

Test1

Original Spreadsheet: 53k

SaveAs with no changes: 49k

Other tests revealed similar results

ReportE

Test1

Original Spreadsheet: 432k

SaveAs with no changes: 293k

Test2

Original Spreadsheet: 14k

SaveAs with no changes: 17k

Test3

Original Spreadsheet: 15k

SaveAs with no changes: 18k

Test4

Original Spreadsheet: 42k

SaveAs with no changes: 38k

Test5

Original Spreadsheet: 51k

SaveAs with no changes: 44k

I find it interesting that the larger the report output size, the greater the difference in sizes when resaved. Also, for very small file sizes, resaved size is actually bigger. Any ideas?

|||

I did some additional research - looks like this is just how Report Server interacts with MS Excel. Report Server cannot take advantage of Excel's file compression process(es) when it exports. Work arounds include exporting first to CSV and then opening the file in Excel OR scheduling the report so that the users don't have to wait. (Another suggestion is to wait for SQL Server 2008 and hope the issue is addressed there.)

I will mark this issue as resolved, but if you have any additional information, please post it here. Thanks.