SheetJS generated excel workbook size is too large #2859

New Issue

anupganatra · 2023-01-19T18:15:32Z

anupganatra commented

2023-01-19 18:15:32 +00:00

I am using xlsx to prepare the excel workbook through AWS lambda. I need to read the workbook from S3 and add the multiple sheets. I am using below code to read the excel workbook from S3 but final workbook size is too large.

xlsx.read(buffer, { cellStyles: true })

If I remove cellStyles: true then it is generating workbook with correct size but it removes the formatting (date, number, cell width etc)

I am writing workbook to S3 with ContentType application/vnd.openxmlformats-officedocument.spreadsheetml.sheet

I am using [xlsx](https://www.npmjs.com/package/xlsx) to prepare the excel workbook through AWS lambda. I need to read the workbook from S3 and add the multiple sheets. I am using below code to read the excel workbook from S3 but final workbook size is too large. `xlsx.read(buffer, { cellStyles: true })` If I remove `cellStyles: true` then it is generating workbook with correct size but it removes the formatting (date, number, cell width etc) I am writing workbook to S3 with ContentType `application/vnd.openxmlformats-officedocument.spreadsheetml.sheet`

sheetjs commented

2023-01-19 18:19:49 +00:00

If you are writing files, you can pass the option compression: true to force ZIP DEFLATE compression (https://docs.sheetjs.com/docs/api/write-options more details). By default it is disabled.

If you are writing files, you can pass the option `compression: true` to force ZIP DEFLATE compression (https://docs.sheetjs.com/docs/api/write-options more details). By default it is disabled.

anupganatra commented

2023-01-19 18:41:09 +00:00

I have tried passing compression: true and I was able to reduce the file size to 50% but still it was too large.

My file size was 52 MB without compression and 23 MB with compression. But if I remove cellStyles: true then it gets reduced to less than 2 MB

I have tried passing `compression: true` and I was able to reduce the file size to 50% but still it was too large. My file size was 52 MB without compression and 23 MB with compression. But if I remove `cellStyles: true` then it gets reduced to less than 2 MB

sheetjs commented

2023-01-19 18:42:57 +00:00

That sounds unexpected, can you share the file or data? If it can't be shared publicly, email hello@sheetjs.com

anupganatra commented

2023-01-24 05:54:23 +00:00

I won't be able to share the actual files. I have generated dummy files using dummy script and shared the files at hello@sheetjs.com. Please note that I have generated the files without compression.

Size with cellStyles: 2.54
Size without cellStyles: 2.28

I won't be able to share the actual files. I have generated dummy files using dummy script and shared the files at hello@sheetjs.com. Please note that I have generated the files without compression. Size with cellStyles: 2.54 Size without cellStyles: 2.28

sheetjs commented

2023-01-24 08:08:11 +00:00

Thanks for following up! It looks like an issue with AWS S3/Lambda. The demos https://docs.sheetjs.com/docs/demos/cloud/aws were last tested in August 2022 but should still work. Please follow them exactly.

sheetjs closed this issue

2023-01-24 08:08:11 +00:00

anupganatra commented

2023-01-24 21:51:11 +00:00

I have followed the demo exactly but the file size is still the same. After analyzing the theme files, I have found that below four font scripts are creating issue. Also, attached screenshot of font scripts from theme files.

Jpan
Hang
Hans
Hant

Seems non-English typeface are creating the issue. Is there a way to remove/disable the above four font scripts?

I have followed the demo exactly but the file size is still the same. After analyzing the theme files, I have found that below four font scripts are creating issue. Also, attached screenshot of font scripts from theme files. 1. Jpan 2. Hang 3. Hans 4. Hant Seems non-English typeface are creating the issue. Is there a way to remove/disable the above four font scripts?

theme_file_for_cellStyles_false_regular_workbook.JPG

166 KiB

theme_file_for_cellStyles_true_large_workbook.JPG

365 KiB

theme_file_size_for_cellStyles_false_regular_workbook.JPG

17 KiB

theme_file_size_for_cellStyles_true_large_workbook.JPG

18 KiB

theme_file_size_for_cellStyles_false_regular_workbook.JPG

anupganatra reopened this issue

2023-01-24 21:51:11 +00:00

sheetjs commented

2023-01-24 22:08:56 +00:00

Can you log the buffer sizes when you write them to S3 and log the reconstructed buffer size when you receive the data? If the sizes are the same, can you compute a checksum (e.g. CRC32 using https://github.com/sheetjs/js-crc32) to compare the data as it flows through S3?

Basically, this is either a bug in the library or an issue with the S3 integration code or a corruption issue within S3 itself.

Can you log the buffer sizes when you write them to S3 and log the reconstructed buffer size when you receive the data? If the sizes are the same, can you compute a checksum (e.g. CRC32 using https://github.com/sheetjs/js-crc32) to compare the data as it flows through S3? Basically, this is either a bug in the library or an issue with the S3 integration code or a corruption issue within S3 itself.

anupganatra commented

2023-01-24 23:22:29 +00:00

Is there a way to remove/disable the font scripts(Jpan, Hang, Hans, Hant) which are creating the issue? I have found a file where font scripts are available but my node_modules folder is generated automatically during deployment.

sheetjs commented

2023-01-24 23:24:09 +00:00

themeXLSX option when writing https://docs.sheetjs.com/docs/api/write-options -- you can specify your own theme data.

`themeXLSX` option when writing https://docs.sheetjs.com/docs/api/write-options -- you can specify your own theme data.

anupganatra commented

2023-01-24 23:42:51 +00:00

themeXLSX option when writing https://docs.sheetjs.com/docs/api/write-options -- you can specify your own theme data.

I am using 0.18.5 version of the library. I don't have themeXLSX option available for XLSX.write. Below are the options available to me.

WritingOptions

type
bookSST
bookType
compression
ignoreEC
Props
numbers

CommonOptions

WTF
bookVBA
cellDates
sheetStubs
cellStyles
password

SheetOption

sheet

Also, I have gone through the repo https://github.com/SheetJS/sheetjs but unable to find the themeXLSX option.

> `themeXLSX` option when writing https://docs.sheetjs.com/docs/api/write-options -- you can specify your own theme data. I am using 0.18.5 version of the library. I don't have `themeXLSX` option available for `XLSX.write`. Below are the options available to me. **WritingOptions** * type * bookSST * bookType * compression * ignoreEC * Props * numbers **CommonOptions** * WTF * bookVBA * cellDates * sheetStubs * cellStyles * password **SheetOption** * sheet Also, I have gone through the repo https://github.com/SheetJS/sheetjs but unable to find the `themeXLSX` option.

anupganatra closed this issue

2023-01-24 23:51:30 +00:00

anupganatra reopened this issue

2023-01-24 23:51:33 +00:00

anupganatra commented

2023-01-26 19:05:03 +00:00

Is there any update on how to do the themeXLSX changes? I am unable to find the themeXLSX option in current version (0.18.5) of the library.

Is there any update on how to do the `themeXLSX` changes? I am unable to find the `themeXLSX` option in current version (0.18.5) of the library.

Sign in to join this conversation.