none
Excel export file size TOO large, How to compress

    Question

  • I am having issue that when i do export to excel it does creates file 34 MB but when i do Re save(manually) it, it gets compressed to 14 MB data
    But we cant manually open and save the copy while u have to deal lots of file every day so is there any way to compressed the EXPORTED Out put file
    i have searched a lot AND this is a last hope from MSDN.
    I have also found same question But it gives me No hint How and where i will have to configure to do this

    http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/26910f57-8500-4128-939f-70a70daa2188
    Now this is making my choice to choose SSRS 2005 is fading since i cant use any third party tool

    Any help is highly Appreciated
    dsu
    Friday, May 01, 2009 4:57 AM

Answers

  • Hi

    We had similar issue, the file size for the report/s were kind of huge, but there were only a few reports like that.

    If the reporting format not really required then best option is to use CSV export rather than Excel.
    But if we have to export in a compressed file format, we need to go different route.
    Creating and adding a new report rendering option so that it appears in the drop-down for the report export - is a longer way.

    I can think of another way is:

    1. Create a report that simply runs a XP ComdShell which basically is a batch file, that goes into predefined folder where all the exported excel files are located, loops through all the files and sends them into compressed folder / run a winZip so that all those files are converted into compressed file. Or using Visual Studio database project, write a stored proc in .NET that does more complex task like open Excel file and save as, reference this in a SQL database and call that in SSRS report.

    2. Call the above after all reports have been generated and exported to Excel.

    Not tried the above approach. Let me know what you think.

    Shailesh

    Thursday, May 21, 2009 10:17 PM
  • Hi


    It works! In a sense that call a stored proc from report to compress an xlsx file to rar. 

    On the similar lines, a VBScript file can also be run from xp_cmdshell, and within VBScript file one can actually open Excel application and save the exported xls file as xlsx
    The best way is to get the Excel Rendering extension (ExcelRendering.dll) updated so that when a report is exported using Excel option, it actually exports in xlsx and not xls. But we have to wait for that from Microsoft I guess. But meanwhile, a scheduled report exported to Excel can be converted into xlsx by running a VBScript code at the end. 

    Here is what I did:

    1. Create a sample bat file that has the following code, this code just archives all the xlsx files from the current folder to an archive folder

    @REM ------- BEGIN ----------------
    @setlocal
    @echo off
    set path="C:\Program Files\WinRAR\";%path%
    cd\SampleFolder
    WinRAR a c:\SampleFolder\Archive\Archive.rar *.xlsx

    REM ------- END -----------------

    2. Put this bat file on the SQL server box
    3. Call this file from xp_cmdshell to test
    4. Call xp_cmdshell stored proc from the SSRS report.
    5. Run the report to test


    Shailesh

    Friday, May 22, 2009 9:38 PM
  • A better solution to this problem is to:

    1. Disable export to XLS
    2. Add your own export button
    3. Use the ReportViewer Control or ReportingServices Web Service to capture the stream
    4. Compress the stream using your favorite .NET compression library
    5. Send the stream back to the client as a Zip file(or whatever format you choose)

    We give our clients a "direct to excel/zip" and a "generate report" button so the client can choose how they want the report.

    Sunday, May 24, 2009 5:40 AM
  • For added bonus points replace 4 and 5 with:
    4. use your favorite .NET excel library to convert stream from XLS to XLSX or to convert the strings to single byte from double byte Unicode.
    5. Send stream back to user as XLSX or single byte XLS.

    I strongly recommend against the above solution because a certain percentage of clients will not have Office 2007 or you might have strings that need Unicode(depends greatly on scenario)

    You also might want to ask yourself if the report is user friendly, or if it would be better broken into several sub reports. 

     

     

    Sunday, May 24, 2009 5:55 AM

All replies

  • any one

    dsu
    Wednesday, May 06, 2009 12:32 AM
  • Hi

    We had similar issue, the file size for the report/s were kind of huge, but there were only a few reports like that.

    If the reporting format not really required then best option is to use CSV export rather than Excel.
    But if we have to export in a compressed file format, we need to go different route.
    Creating and adding a new report rendering option so that it appears in the drop-down for the report export - is a longer way.

    I can think of another way is:

    1. Create a report that simply runs a XP ComdShell which basically is a batch file, that goes into predefined folder where all the exported excel files are located, loops through all the files and sends them into compressed folder / run a winZip so that all those files are converted into compressed file. Or using Visual Studio database project, write a stored proc in .NET that does more complex task like open Excel file and save as, reference this in a SQL database and call that in SSRS report.

    2. Call the above after all reports have been generated and exported to Excel.

    Not tried the above approach. Let me know what you think.

    Shailesh

    Thursday, May 21, 2009 10:17 PM
  • It wont work.
    I would suggest you do a prototype and then check whether it works or not, it will not.
    Format is important otherwise i wont post it here and what ever you are trying to say i know that all, It will do nothing but add more complexity to handle the data basically all are the strings which stores in similiar fashion. I know that we can doing this by other ways like converting this into xml or csv
    But that will not provide the solution. Which means every day you create a new report into csv and then deal with unicode and non unicode strings
    will add a huge complexity and furthermore this task is going to be hugely maintanceable because any time you can have error.




    dsu
    Friday, May 22, 2009 2:18 PM
  • Hi


    It works! In a sense that call a stored proc from report to compress an xlsx file to rar. 

    On the similar lines, a VBScript file can also be run from xp_cmdshell, and within VBScript file one can actually open Excel application and save the exported xls file as xlsx
    The best way is to get the Excel Rendering extension (ExcelRendering.dll) updated so that when a report is exported using Excel option, it actually exports in xlsx and not xls. But we have to wait for that from Microsoft I guess. But meanwhile, a scheduled report exported to Excel can be converted into xlsx by running a VBScript code at the end. 

    Here is what I did:

    1. Create a sample bat file that has the following code, this code just archives all the xlsx files from the current folder to an archive folder

    @REM ------- BEGIN ----------------
    @setlocal
    @echo off
    set path="C:\Program Files\WinRAR\";%path%
    cd\SampleFolder
    WinRAR a c:\SampleFolder\Archive\Archive.rar *.xlsx

    REM ------- END -----------------

    2. Put this bat file on the SQL server box
    3. Call this file from xp_cmdshell to test
    4. Call xp_cmdshell stored proc from the SSRS report.
    5. Run the report to test


    Shailesh

    Friday, May 22, 2009 9:38 PM
  • A better solution to this problem is to:

    1. Disable export to XLS
    2. Add your own export button
    3. Use the ReportViewer Control or ReportingServices Web Service to capture the stream
    4. Compress the stream using your favorite .NET compression library
    5. Send the stream back to the client as a Zip file(or whatever format you choose)

    We give our clients a "direct to excel/zip" and a "generate report" button so the client can choose how they want the report.

    Sunday, May 24, 2009 5:40 AM
  • For added bonus points replace 4 and 5 with:
    4. use your favorite .NET excel library to convert stream from XLS to XLSX or to convert the strings to single byte from double byte Unicode.
    5. Send stream back to user as XLSX or single byte XLS.

    I strongly recommend against the above solution because a certain percentage of clients will not have Office 2007 or you might have strings that need Unicode(depends greatly on scenario)

    You also might want to ask yourself if the report is user friendly, or if it would be better broken into several sub reports. 

     

     

    Sunday, May 24, 2009 5:55 AM