1.5 MB *.xlsm files saved from Excel (MSO Pro Plus 2013) are 3.4 MB *.ods files saved from LibreOffice (7.6.6.3).
Why are the ods files more that double the size and what can I do for them to be about the same size?
I know there is a thumbnail added to the ods documents. But these files are large enough that the tn shouldn’t be a significant portion of the file size.
There are about a dozen sheets in these workbooks.
Each sheet has about 4 to 6 charts.
Thanks
How we can examine the symptom without a sample file?
Why even worry about 3.4MB …
Well, that’s ten 360 KB floppy disks…
.xlsm and .ods.
Both of them are .zip archives really. Rename two copies of the files to .zip filename extension, and then unzip them. Then you will able to check the contents, and the size of the contents.
Right. Capable unzip tools don’t even need renaming to .zip, just make sure to extract the files in a new temporary directory to not clobber the current working directory.
How we can examine the symptom without a sample file?
Content is highly sensitive confidential information. So probably have to provide investigative instruction.
Why even worry about 3.4MB …
Because it’s not just one 3.4 MB file.
Also of note, after running macro to update data, there is ~3 second delay when saving. Very annoying. Suspect that may be related.
.xlsm and .ods.
Both of them are .zip archives really. Rename two copies of the files to .zip filename extension, and then unzip them. Then you will able to check the contents, and the size of the contents.
Unzipped the files into ODS and XLMS folders.
XLMS is 8.9 MB, 138 files, 11 folders. Seems reasonable.
ODS is 34.4 MB, 303 files, 86 folders. Seems unnecessarily large.
What stands out most in the ODS extraction is 66 object folders (Object 1 - 66: 3.37 MB, 198 files, 66 folders) and ObjectReplacements folder (19.9 MB, 66 files, 0 folders).
Remaining is 11.1 MB, 39 files, 19 folders.
The big offender is the object replacements. Whatever those are.
Here’s an approximate description of the workbook.
dozen sheets
Per sheet:
200 rows by 20 columns of data
dozen merged cells per row
6 charts per sheet
2 form control push buttons
Macros:
6 macro modules (~2000 lines)
1 dialog module
Save as:
xlsm: 1343 KB
ods: 3397 KB
LibreOffice 7.6.6.3
ObjectReplacements/ contains images of embedded OLE objects. They are generated because OLE is a Windows-only technology, which LibreOffice internally handles to some degree, but most other software does not so they can display the replacements if the actual OLE object isn’t handled. Something Microsoft doesn’t even care about and hence doesn’t store.
As you mentioned charts, those are embedded objects as well and get such preview images generated.
Thanks. That is very good to know.
Not bringing anything in from external documents. And not providing anything to other documents. Only uses it’s own internal data.
Everything should be self contained in the workbook. And nothing from the workbook is provided externally.
Counted a total of 64 charts and 2 form control push buttons (66).
Guessing those are what’s resulting in the OLE objects being saved.
So do I need OLE support?
Is there a Calc option to not save the OLE?
Brief search didn’t yield an answer.
You misunderstood. You don’t need OLE support. AFAIK there’s also no option to not save the ObjectReplacements GDI metafiles of which replacement views can be rendered.
If you are that concerned about size, you could delete the replacement files from the archive using
zip -d filename.ods 'ObjectReplacements/Object*'
but then you should also remove their entries from the META-INF/manifest.xml that read like
<manifest:file-entry manifest:full-path="ObjectReplacements/Object 1" manifest:media-type="application/x-openoffice-gdimetafile;windows_formatname="GDIMetaFile""/>
to not confuse reading applications, and rezip that edited file. That can be automated like
unzip filename.ods META-INF/manifest.xml
sed -i -E -e '/^\s*<manifest:file-entry\s+.*manifest:full-path="ObjectReplacements\/Object.+\/>$/d' META-INF/manifest.xml
zip filename.ods META-INF/manifest.xml
Repair when opening seems to restore the chart OLEs. Then when saving it’s back to the previous size.
Seems like LibreOffice Calc uses the chart OLEs rather than constructing the charts from a saved definition.
Too bad there is not an option to turn off OLE.
Would like to eliminate the unnecessary space. But would have to be a consistent, smooth and well working process. Not seeing any light at end of tunnel.
Try setting AddReplacementImages to false in Options → LibreOffice → Advanced → Expert Configuration.
Edit: doesn’t work. A bug report is needed.
Thanks for the suggestion. But saving still recreates the OLE replacement objects.
Have you tried this with success? Maybe I’m doing something incorrectly?
BTW, this is the option I found; in case it’s the not the correct one.
LibreOffice -> Tools -> Options
LibreOffice -> Advanced
Open Expert Configuration
Preference Name Property Type Value
org.openoffice.Office.Common
Save
Graphic
Graphic AddReplacementImages Boolean true (changed to false)
I’m not so sure that option is for OLE replacement objects. The name seems to indicate a graphic, as in picture, item.
Do you know otherwise?
Replacement image is, yes, an image that is used for replacement. And I’m not so sure what makes you think that the replacement image used for an OLE object is different from replacement images used for other kinds of objects - in respect to the discussed setting. I also don’t really understand why people hesitate to file bug reports, when anything just could be possibly wrong - letting developers to decide. I even had a patch ready to fix that; I even asked you explicitly to file a bug. But why should I care about a problem that the user refuses to even file.
tdf#160915
Hey dude. Chill.