locked
Size of Exported Excel file is very large RRS feed

  • Question

  •  

    Hi,

    I have a report that has around 10000 rows and 5 columns. When I export to excel, the excel file is very big, around 15MB.

    But If the file is opened, and saved again , the size reduces to around 13MB.

    Any suggestions on how to reduce the file size of the exported Excel file. I would prefer to export it to excel rather than CSV format.

    Any help is appreciated.

     

     

    Thanks....

    ViVa1

    Wednesday, November 5, 2008 8:04 AM

Answers

  • Hi ViVa1,

     

    Unforunately, the size of your Excel output is due to the large number of rows your report has.  One thing you can do is to try and minimize the amount of merged cells in the output by ensuing your cells and report items are lined up neatly.  But you are not able to control the size explicitly, short of modifying the dataset that is returned.

     

    -Gerry

     

    Wednesday, November 5, 2008 10:26 PM
    Answerer

All replies

  • Hi ViVa1,

     

    Unforunately, the size of your Excel output is due to the large number of rows your report has.  One thing you can do is to try and minimize the amount of merged cells in the output by ensuing your cells and report items are lined up neatly.  But you are not able to control the size explicitly, short of modifying the dataset that is returned.

     

    -Gerry

     

    Wednesday, November 5, 2008 10:26 PM
    Answerer
  • Hi Gerry,

    Thanks for your response.....  This really helped. But still the size seems to be very big.

    Also When I remove the formatting from the Excel file, the file shrinks to a very small size.....

    I would like to know if there is any way to export only the values and not the formatting to excel...

    Thanks.....

     

     

     

     

     

    Tuesday, November 11, 2008 2:44 AM
  • Hi, actually as you have probably noticed the size grows a lot due to the use of different styles and formatting.
    If you do not require any formatting then I would recommend you to consider a CSV file format again because it will result in the smallest file size which you can achieve with exporting.
    Also for the future referencing SQL Server 2008 R2 exports reports only in XLS format, but SQL Server 2012 by default will export into a new XLSX file format which is a compressed (zipped XML based format) so the resulting files will be significantly smaller.
    For example I created a small project to test exporting of 10000 x 5 data to an Excel file. I used this .NET Excel component to create various spreadsheet files, it has a straightforward API for exporting DataTable into an Excel file in .NET so you can test this out yourself with ease. The output files, without any formatting applied to them, have the following sizes: XLS (~1MB), XLSX (~300KB) and CSV (~250KB).

    • Edited by Cosettear Tuesday, March 18, 2014 9:15 AM
    Tuesday, March 18, 2014 9:11 AM
  • Also, you might consider converting to the format xlsb (the 'b' stands for binary).  This will shrink your file size even more than xlsx will, and will still let you retain formatting and other Excel functionality that a csv will not.

    For example, I just took a xls file and converted it a couple times.  These were the results:

    xls  = 3,060 kb

    xlsx = 510 kb

    xlsb = 237 kb


    BrainE

    Tuesday, March 18, 2014 5:26 PM