How can I break the maximum properties limit of json object #2845

New Issue

963334657 · 2023-01-03T09:28:55Z

963334657 commented

2023-01-03 09:28:55 +00:00

const ws: WorkSheet ={
    '!merges': [],
    '!cols': []
};
const wb: WorkBook = {
    SheetNames: ['sheet1'],
    Sheets: {
        sheet1: ws
    }
};
for(let i = 0; i < 50000; i++) {
    for(let j = 0; j < 200; j++) {
        const current = XLSX.utils.encode_cell({
            r: i,
            c: j
        });
        ws[current] = {
            t: 's',
            v: 'aaaaaa'
        };
    }
}
XLSX.writeFile(wb, "C:\\Users\\AA\\Desktop\\Excel.xlsx");

I create a worksheet json object, it has 50,000 lines, 200 columns, but before write it to file, code blocked, After searching online, I found that if the properties of the JS object are string, it can create up to 2^23 properties.
https://cmdcolin.github.io/posts/2021-08-15-map-limit

So how can I create a Worksheet json object and write more than 2^23 cells to a excel sheet

``` const ws: WorkSheet ={ '!merges': [], '!cols': [] }; const wb: WorkBook = { SheetNames: ['sheet1'], Sheets: { sheet1: ws } }; for(let i = 0; i < 50000; i++) { for(let j = 0; j < 200; j++) { const current = XLSX.utils.encode_cell({ r: i, c: j }); ws[current] = { t: 's', v: 'aaaaaa' }; } } XLSX.writeFile(wb, "C:\\Users\\AA\\Desktop\\Excel.xlsx"); ``` I create a worksheet json object, it has 50,000 lines, 200 columns, but before write it to file, code blocked, After searching online, I found that if the properties of the JS object are string, it can create up to 2^23 properties. https://cmdcolin.github.io/posts/2021-08-15-map-limit So how can I create a Worksheet json object and write more than 2^23 cells to a excel sheet

sheetjs commented

2023-01-03 12:18:53 +00:00

Use "dense mode": https://docs.sheetjs.com/docs/csf/sheet#cell-storage (note that you may need to upgrade, see https://docs.sheetjs.com/docs/getting-started/#installation
for more details)

For the specific example:

/* this is your dataset */
const data: any[][] = Array.from({ length: 50000 }, (_, i) => Array.from({ length: 200 }, ($, j) => "aaaaaa"));

/* create worksheet */
const ws: WorkSheet = XLSX.utils.aoa_to_sheet(data, {dense: true});

/* create workbook */
const wb: WorkBook = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(wb, ws, "Sheet1");

/* write file */
XLSX.writeFile(wb, "C:\\Users\\AA\\Desktop\\Excel.xlsx");

Live demo: https://jsfiddle.net/dLonc16g/

There's definitely room for performance improvements (it takes 14 seconds locally) but it should scale up to ~50M cells

Use "dense mode": https://docs.sheetjs.com/docs/csf/sheet#cell-storage (note that you may need to upgrade, see https://docs.sheetjs.com/docs/getting-started/#installation for more details) For the specific example: ```js /* this is your dataset */ const data: any[][] = Array.from({ length: 50000 }, (_, i) => Array.from({ length: 200 }, ($, j) => "aaaaaa")); /* create worksheet */ const ws: WorkSheet = XLSX.utils.aoa_to_sheet(data, {dense: true}); /* create workbook */ const wb: WorkBook = XLSX.utils.book_new(); XLSX.utils.book_append_sheet(wb, ws, "Sheet1"); /* write file */ XLSX.writeFile(wb, "C:\\Users\\AA\\Desktop\\Excel.xlsx"); ``` Live demo: https://jsfiddle.net/dLonc16g/ There's definitely room for performance improvements (it takes 14 seconds locally) but it should scale up to ~50M cells

sheetjs closed this issue

2023-01-03 12:18:53 +00:00

Sign in to join this conversation.