8

I'm working on a spreadsheet together with another person, sending new versions forth and back by email. It contains nothing fancy. While he uses Windows I'm on Ubuntu 12.04.4 LTS. The first version he sent was only 30 k. When I opened it in LibreOffice 3.5.7.2 (current Ubuntu version of OpenOffice) and just saved it again (still using the Excel format) it was 2.6 MB! After my edits it was 5 MB large which is not acceptable.

What file has to say about the original Microsoft Office file is: Composite Document File V2 Document, Little Endian, Os: Windows, Version 6.2, ... Name of Creating Application: Microsoft Excel, ... Security: 0

After saving as Excel from LibreOffice, file says almost the same. Differences are: Version 1.0 instead 6.2, Code page: -535 instead 1252, Revision Number: 1 (not present in original file), Name of Creating Application and Security: no longer there.

Is there a way to reduce the file size?

3
  • ...and what does file tell you about the 5MB version? And are you saving as Excel (from LibreOffice), or in its native format? (And as an aside: f there's really nothing fancy in the spreadsheet, then: do you know the Google Drive spreadsheet functions? Nice collaboration, but not an answer to your question of course.)
    – Arjan
    Jul 5, 2014 at 11:11
  • I'm saving, not saving as, so LibreOffice keeps the file format and file says almost the same. Differences are: Version 1.0 instead 6.2, Code page: -535 instead 1252, Revision Number: 1 (not present in original file), Name of Creating Application and Security: no longer there.
    – user829755
    Jul 5, 2014 at 11:21
  • Any chance LibreOffice formats all cells, and then basically creates the maximum sheet size? Not sure how to easily check that, but if it happens then it might be related to fonts in the Excel file that your Ubuntu does not have, which might somehow trigger some formatting of empty cells as well? So: any strange font used in the Excel file? And does the size decrease if you remove all formatting?
    – Arjan
    Jul 5, 2014 at 12:55

2 Answers 2

11

I found the problem. There were 4 x 65536 (mainly unused) rows with a custom row height. I selected all unused rows, invoked Format > Row > Optimal Height and pressed OK. This shrinked the file from 5 MB to 10 k.

For discovering this I saved the file as .xlsx (Excel 2007/2010 XML or as OfficeOpen XML Spreadsheet, makes no difference). This is a zip format containing xml files. I unzipped it and found huge files like xl/worksheets/sheet2.xml. I formatted one of them using xmllint --format and found 65536 lines looking like <row collapsed="false" customFormat="false" customHeight="true" hidden="false" ht="12.8" outlineLevel="0" r="65536"/>.

3
  • Nice. And just curious: whenever saved in the "real" Excel, does the problem come back?
    – Arjan
    Jul 5, 2014 at 18:27
  • yes it does, at least with Excel 2013 which I have access to. I verified this with a minimal spreadsheet where I didn't even change any formatting!
    – user829755
    Jul 5, 2014 at 19:45
  • 1
    to select unused rows, use Ctrl+Shift+ArrowDown then ArrowLeft, then invoked Format > Row > Optimal Height and pressed OK. Thanks. It's save my day. Jul 10, 2014 at 10:28
0

If really nothing special in your file, are you sure not being adding some objects like pictures or other documents embedded in it ? I'm editing calc files with lots of data for a while, and always keep understandable size (using LibreOffice 4.x).

Some other ideas: - zip/gzip the file before sending may save a lot - if issue is mailbox usage, you may use other file sharing services.

1
  • 2
    Are you saving as Excel, like in the question? Opening the Excel and then saving right away already increases the size from 30k to 2.6MB. There shouldn't be any additional objects in the spreadsheet then.
    – Arjan
    Jul 5, 2014 at 12:52

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .