Converting .xlsm to .odt
how come, that when I have an .xlsm file, and I convert it to an .ods file using LibreOffice Calc, that the file becomes much bigger and slow to load?
The increase in file size and slower loading times when converting an .xlsm file to an .ods file in LibreOffice Calc can be attributed to several factors:
File Format Differences
- .xlsm (Excel Macro-Enabled Workbook) is optimized for Microsoft's proprietary format, with a focus on efficient storage and loading
.ods (OpenDocument Spreadsheet) uses a more verbose XML-based format, which can inflate file sizes due to less efficient encoding.
- Embedded VBA Macros: .xlsm stores macros in a compressed binary format
LibreOffice converts VBA macros into its format, often requiring more space or embedding them as plain text for compatibility.
- Formula Conversion: Excel and LibreOffice handle formulas differently, and converting them may add complexity, which increases size and processing overhead.
- Styles and Formatting: LibreOffice sometimes expands style definitions and formatting during conversion, leading to larger files
Extra metadata and redundant styles can accumulate in the .ods file.
- Compression Algorithms: .xlsm uses an efficient compression algorithm optimized by Microsoft
.ods relies on the ZIP format, which might not achieve the same level of compression for the same content.
- Additional Compatibility Layers: LibreOffice may include extra data for compatibility purposes, especially when trying to preserve features unique to Excel, such as named ranges, charts, or pivot tables.
Solutions
- Compress the .ods file manually by re-saving it with optimized settings
- After opening the file, copy-paste contents to a new file, without any unwanted attributes, and safe again → See case below (2024.12)
- Clean up unnecessary styles or metadata in LibreOffice
- If macros are not needed, remove them before converting
- If Excel is available, try saving the file directly in .ods format from Excel, which may yield smaller files.
Case: Amz report (2024.12)
This is about an Amazon Category Listings Report:
- Original .xlsm file: 2.9MB
- Corresponding .ods file: 24.3MB
- Only the template sheet as .ods file: 22.4MB - Loading this file, takes several minutes
- Copy-pasted the contents of this sheet to a new file: 583kB - File loads instantly.