Beefy Boxes and Bandwidth Generously Provided by pair Networks
Do you know where your variables are?
 
PerlMonks  

Re: Spreadsheet::WriteExcel large files

by jmcnamara (Monsignor)
on Jan 02, 2012 at 17:54 UTC ( #945940=note: print w/ replies, xml ) Need Help??


in reply to Spreadsheet::WriteExcel large files

The most likely explanation for the size difference is the file format after the file has been saved is. Is the 6 MB file in xls or xlsx format?

Excel's xls binary BIFF format doesn't use compression. However, it does use some space optimizations which Spreadsheet::WriteExcel doesn't include so in general a file re-saved in xls format by Excel may be 10-20% smaller, depending on the data it contains and the arrangement of the data.

Excel's xlsx format is compressed XML data so it can be significantly smaller than the equivalent xls file even if both are created by Excel.

If you would like to generate files in the xlsx format try Excel::Writer::XLSX. It is API compatible with Spreadsheet::WriteExcel so you should be able to just change the module names and re-run your program. If you do, let us know what the xlsx output file size is.

Here is some sample data based on the output of the bigfile.pl example program:

bigfile1.xls 10,379,264 # Original from Spreadsheet::WriteExcel. + 100% bigfile2.xls 10,525,184 # Resaved by Excel in xls format. + 102% bigfile3.xlsx 2,733,546 # Resaved by Excel in xlsx format. + 27% bigfile4.xlsx 2,325,559 # Original from Excel::Writer::XLSX. + 23% bigfile5.zip 1,888,573 # bigfile1.xls zipped for comparison. + 18%

Results for other files may vary significantly depending on the ratio of string/number data and some other factors.

--
John.


Comment on Re: Spreadsheet::WriteExcel large files
Download Code
Re^2: Spreadsheet::WriteExcel large files
by mrguy123 (Hermit) on Jan 03, 2012 at 08:45 UTC
    Thanks for your answer
    I re-saved the files as XLS (not XLSX) so this isn't the problem.
    The funny thing is, I just ran the bigfile.pl example you mentioned, and when I resaved the file there was no compression!
    This means I must have done something different, but I'm not sure what
    Will investigate and update if I find something
    Guy

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://945940]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others examining the Monastery: (9)
As of 2014-12-23 00:56 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    Is guessing a good strategy for surviving in the IT business?





    Results (133 votes), past polls