MathNya / umya-spreadsheet

A pure rust library for reading and writing spreadsheet files
MIT License
239 stars 41 forks source link

Updating existing excel blows up the size of the excel #108

Closed akashkhan25 closed 1 year ago

akashkhan25 commented 1 year ago

Thanks a lot for the library! It's been very useful šŸ˜„

I've noticed that using the writer to update existing excel will increase the excel size by over 10X even if there's very small amount of changes made.

Steps to reproduce:

  1. Create an empty excel (Note the size. For me empty excel is ~ 49KB)
  2. Execute the following to add some columns to the excel and write it

    let headers = vec!["column_a","column_b"];
    let mut book = umya_spreadsheet::reader::xlsx::read(path).unwrap();
    let worksheet = book.get_sheet_by_name_mut("Sheet 1").unwrap();
    let col_count = worksheet.get_highest_column() + 1;
    worksheet.insert_new_column_by_index(&col_count, &(headers.len() as u32));
    
    for (i, header_val) in headers.iter().enumerate() {
        let col_index = col_count + (i as u32);
        let cell = worksheet.get_cell_by_column_and_row_mut(&col_index, 2);
        cell.set_value(header_val);
    }
    umya_spreadsheet::writer::xlsx::write(&book, path)
  3. The size of the excel is now 630KB.

Note: Opening the file in Excel and saving without any changes will fix the file size back to expected size.

Originally I noticed it on adding even 1 column on an input of size 500KB blew up the file size to 5.6MB. Reading it back caused memory usage to spike

akashkhan25 commented 1 year ago

After debugging a bit, it seems to be due to using Stored compression method . Applying the change from #106 seems to resolve the issue . So if that PR is okay to merge I'll close this šŸ™‡

MathNya commented 1 year ago

Thank you for your report. I have merged #106. We will release it as ver 0.9.1 soon.