@immiran
Summary
Large Excel workbooks can be reduced in size before they are placed in a ZIP archive. By cleaning up unused rows/columns, removing unused styles and names, and recompressing embedded images, you keep all formulas, charts, and formatting while producing a smaller .xlsx file that the ZIP process can compress more efficiently.
Why it happens
- Unused cells / styles / names – Excel stores every cell that has ever been touched and retains styles and defined names even if they are no longer used. This extra XML inflates the workbook size.
- Embedded images – Pictures are kept in the original format (often BMP or high‑resolution PNG) and are not re‑compressed when the workbook is zipped.
- Default ZIP settings – The generic ZIP algorithm does not apply Excel‑specific optimisations such as removing empty parts or using the highest compression level.
What to do next
- Load the workbook
// Load the original workbook
var workbook = new Aspose.Cells.Workbook(@"C:\Input\LargeFile.xlsx");
- Delete empty rows and columns (outside the used range)
foreach (var ws in workbook.Worksheets)
{
ws.Cells.DeleteEmptyRowsAndColumns();
}
- Remove unused objects
// Styles, defined names and custom XML parts that are not referenced
workbook.RemoveUnusedStyles();
workbook.RemoveUnusedNames();
workbook.RemoveUnusedCustomXmlParts(); // available in recent versions
- Re‑compress embedded pictures
foreach (var ws in workbook.Worksheets)
{
foreach (var pic in ws.Pictures)
{
// Convert to JPEG with 80 % quality (or keep PNG for lossless)
pic.ImageFormat = Aspose.Cells.Drawing.ImageFormat.Jpeg;
pic.ImageQuality = 80;
}
}
- Save with maximum ZIP compression
var saveOptions = new Aspose.Cells.OoxmlSaveOptions
{
CompressionLevel = 9, // 0 = none, 9 = maximum
EnableZip64 = true, // supports files > 4 GB
// The following options are default for XLSX, but can be set explicitly
PreserveFormulas = true,
PreserveCharts = true,
PreserveFormatting = true
};
string cleanedPath = @"C:\Temp\LargeFile_Cleaned.xlsx";
workbook.Save(cleanedPath, saveOptions);
- Create the ZIP archive (standard .NET compression is sufficient)
using (var zip = new System.IO.Compression.ZipArchive(
System.IO.File.OpenWrite(@"C:\Output\LargeFile.zip"),
System.IO.Compression.ZipArchiveMode.Create))
{
zip.CreateEntryFromFile(
cleanedPath,
"LargeFile.xlsx",
System.IO.Compression.CompressionLevel.Optimal);
}
- Validate the result
- Extract the ZIP file.
- Open LargeFile.xlsx in Excel and verify that all formulas, charts, images, and formatting appear as expected.
- (Optional) Call
workbook.CalculateFormula(); before saving if you want to ensure volatile formulas are up‑to‑date.
Additional tips
| Setting / technique |
Effect |
When to use |
workbook.Settings.EnableMemoryOptimization = true; |
Reduces memory consumption while processing very large files. |
Workbooks larger than ~200 MB. |
workbook.DefaultFont = "Arial"; |
Provides a fallback font and avoids embedding unnecessary font data. |
When custom fonts are not required. |
OoxmlSaveOptions.CompressionLevel = 9 + EnableZip64 = true |
Maximises ZIP compression and allows archives > 4 GB. |
When the final ZIP must stay under a size limit or be emailed. |
Following these steps will keep all Excel features (formulas, charts, images, conditional formatting, etc.) intact while delivering a considerably smaller, zip‑ready file. The workflow can be scripted and integrated into any build or upload pipeline to ensure every large workbook is optimised before archiving or transfer.
I could not fully verify this explanation against the retrieved sources. Please double-check the behaviour in your environment and confirm the exact SDK/version.