Best was to read Excel in a Typescript Function

Ask the Community
1

I am using the ‘xlsx’ npm package and it’s been awesome so far with files only <1mb, but in trying to read a file which is just under 4mb (still tiny) but with lots of sheets (maybe 40 or so) it just times out after 60 seconds trying to read it:

                console.log(1)

                const attachmentData: Blob = await doc.attachment!.readAsync();

                // Convert the Blob to an ArrayBuffer
                const arrayBuffer = await attachmentData.arrayBuffer();

                console.log(2)

                // Read the Excel file using the xlsx library
                const workbook = XLSX.read(arrayBuffer, { type: 'buffer'});

                console.log(3)

The time between it showing 1 and 2 is minimal, but then takes a minute on the read statement and times out.

I manually removed some of the sheets as there are a lot in it and got it to the point it’ll work and load fine, but obviously we don’t want people to have to butcher their Excels before they attach / load them.

I can’t see a way (via google searches etc) of limiting the sheets used when loading it (it is a secondary call to get the sheet once the whole lot has loaded), or any other method to selectively reduce what you are trying to do in the read.

So, I wondered if there were other better packages than ‘xlsx’ that you guys recommended?

Thanks

2

From more of a search apparently you can use the sheets option to specify the sheets to load, but that seems to do nothing:
Neither:
const workbook = XLSX.read(arrayBuffer, { type: 'buffer', sheets: 0});
nor
const workbook = XLSX.read(arrayBuffer, { type: 'buffer', sheets: "Sheet1"});
seems to make a difference. On the smaller file it still loads all sheets and on the larger file it still times out after 60 seconds.

What are the libraries anyone else has used to read Excel Files in a Typescript Function?

3

This was happening because the larger file, even though only 4mb, was massively compressed and when read expanded beyond memory limit of the functions run time.
For now, we will go for an option of manually reducing the file size (removing erroneous sheets etc).