8
2
Excel compresses its files using Zip-Deflate and stores it. However, the compression level is far from the best. To reduce the size and send it across I have to unzip and re-zip it manually everytime. Is there a way to change the compression level setting in excel so that it compresses and stores the file using best method possible?
OrangeRind
Posted 2014-04-17T19:56:57.603
Reputation: 642
2
You can also through a VBA macro replace the zip engine.
A macro that uses 7Zip to do the compression can be found in the article:
Zip Activeworkbook, Folder, File or Files with 7-Zip (VBA).
Another method which usually gets about 50% improvement in size is
by saving the file in the xlsb format that was introduced in Excel 2007.
Still another one is to Save As the file, which sometimes reduces its size, presumably
by defragmenting.
From the post In which case should we use the xlsm or the xlsb format? :
The difference with xlsb seems to be that the components are not XML-based but are in a binary format: supposedly this is beneficial when working with large files. source
.xlsx loads 4 times longer than .xlsb and saves 2 times slower and has 1.5 times a bigger file. I tested this on a generated worksheet with 10'000 rows * 1'000 columns = 10'000'000 (10^7) cells of simple chained =…+1 formulas
There are also generic methods for reducing the size of an Excel file. See for example:
How to Reduce Size of Excel Files
How to reduce a huge excel file
How to reduce excel file size
harrymc
Posted 2014-04-17T19:56:57.603
Reputation: 306 093
2XLSX -> 712MB (uncompressed), 78MB (native), 42MB (rezip). XLSB -> 498MB (uncompressed), 45MB (native), 30MB (rezip). – OrangeRind – 2014-05-09T15:16:54.713
2Point is, the binary format does help in huge files, but it is still suffering from the same problem of inefficient ZIP compression because of a speed tradeoff. Perhaps some add-in or macro can help out to save to the higher compression level by default? – OrangeRind – 2014-05-09T15:18:33.167
The code you linked to does not appear to replace the zip engine at all but just provides a macro interface to zip or 7zip to compress an already created workbook which does not seem to do what OP was actually asking. It's compressing after Excel has done it's poor job... – Mokubai – 2014-05-12T06:49:36.240
@Mokubai: That's what he does now and wants to avoid - he is asking for an easier way than he currently does : rename-unzip-rezip-rename. – harrymc – 2014-05-12T08:06:11.380
0
A workaround is to recompress the XLSX ZIP files.
You could use the AdvanceCOMP advzip utility for this. To use the 7z compressor run:
advzip --recompress --shrink-extra huge.xlsx
If you have the time, use the zopfli compressor:
advzip --recompress --shrink-insane huge.xlsx
You can also increase the number of iterations for the compression by using the --iter option.
Cristian Ciupitu
Posted 2014-04-17T19:56:57.603
Reputation: 4 515
2Perhaps just save is as a binary file (XLSB) instead? – Ƭᴇcʜιᴇ007 – 2014-04-17T20:10:09.593
@Techie How converting to binary helps? – EliadTech – 2014-04-17T20:41:13.657
The file size will be much smaller. Try it and let us know how it goes. – Ƭᴇcʜιᴇ007 – 2014-04-17T21:01:15.783
Saving to binary doesn't help significantly. Not nearly as significant as unzip and rezip. Perhaps there is an option for default compression level somewhere? – OrangeRind – 2014-04-28T05:41:44.217
What version of excel and windows are you using? How are you zipping it with excel (filetype and whatnot)? – Raystafarian – 2014-05-08T18:05:52.633
Be aware that the order of files in the compressed stream is relevant. Just unpacking and repacking might result in unreadable files. – Daniel B – 2014-05-08T20:03:14.917
Why do you need to zip it, how big is it? – Toby Allen – 2014-05-08T21:08:55.787
Too bad there's no
WorkbookBeforeOpenevent on the Excel.Application object, or you could write a C# COM addin to 7-zip the workbook on save and un-7zip it on open... – allquixotic – 2014-05-08T21:26:02.743@Raystafarian - Win7/8 Excel2010/13 and 7-zip. I'm not zipping it with excel - the XLSX format is a zip format – OrangeRind – 2014-05-09T15:20:17.453
@TobyAllen - Default xlsx file size goes above 80MB. – OrangeRind – 2014-05-09T15:20:51.787
@allquixotic - I agree, something of that sort could have helped! – OrangeRind – 2014-05-09T15:21:14.023
By the way, AFAIK, excel file size increases drastically if you have too many worksheets. So, if possible, try to use lesser worksheets if you have too many of them. – tumchaaditya – 2014-05-09T19:05:19.073
I don't think you can achieve what you want... The work around you have is the only way to achieve this. http://datapigtechnologies.com/blog/index.php/how-to-compress-xlsx-files-to-the-smallest-possible-size/comment-page-1/ ... This may also help http://stackoverflow.com/questions/17527917/how-to-reduce-a-huge-excel-file
– Dave – 2014-05-12T08:27:47.980