How to write big data from database to xlsx file #2975

New Issue

shadoworion · 2023-06-30T13:49:33Z

shadoworion commented

2023-06-30 13:49:33 +00:00

Is it possible to write big data ~500k+ rows from sql database to xlsx file as stream or may be better way?

I see stream write only in csv or json...

Is it possible to write big data ~500k+ rows from sql database to xlsx file as stream or may be better way? I see stream write only in csv or json...

sheetjs commented

2023-06-30 20:58:39 +00:00

currently there is no streaming write for XLSX.

Based on a simple test of 500K records x 3 columns: https://jsfiddle.net/f5vhzjLo/

console.time("make");
/* header names */
var header = ["Name", "value1", "value2"]

/* create worksheet from header row */
var ws = XLSX.utils.aoa_to_sheet([header], {dense: true});

/* add 500K rows */
for(var i = 0; i < 500000; ++i) XLSX.utils.sheet_add_json(ws, [{Name: String(i), value1: i+Math.random(),  value2: 2*i}], {
  header, // this ensures the header order matches the column names
  origin: -1, // add to the end of the worksheet
  skipHeader:1 // do not write header row
});

/* create workbook */
var wb = XLSX.utils.book_new(); XLSX.utils.book_append_sheet(wb, ws, "Sheet1");
console.timeEnd("make");

/* generate and download file */
console.time("write");
XLSX.writeFile(wb, "issue2975.xlsx");
console.timeEnd("write");

Locally it takes ~0.8s to build the worksheet object and ~3.1s to write the file.

currently there is no streaming write for XLSX. Based on a simple test of 500K records x 3 columns: https://jsfiddle.net/f5vhzjLo/ ```js console.time("make"); /* header names */ var header = ["Name", "value1", "value2"] /* create worksheet from header row */ var ws = XLSX.utils.aoa_to_sheet([header], {dense: true}); /* add 500K rows */ for(var i = 0; i < 500000; ++i) XLSX.utils.sheet_add_json(ws, [{Name: String(i), value1: i+Math.random(), value2: 2*i}], { header, // this ensures the header order matches the column names origin: -1, // add to the end of the worksheet skipHeader:1 // do not write header row }); /* create workbook */ var wb = XLSX.utils.book_new(); XLSX.utils.book_append_sheet(wb, ws, "Sheet1"); console.timeEnd("make"); /* generate and download file */ console.time("write"); XLSX.writeFile(wb, "issue2975.xlsx"); console.timeEnd("write"); ``` Locally it takes ~0.8s to build the worksheet object and ~3.1s to write the file.

sheetjs commented

2023-09-25 20:53:08 +00:00

If "Dense Mode" (https://docs.sheetjs.com/docs/demos/bigdata/stream#dense-mode) does not help, please reopen with some information about the size of the dataset (how many columns? are they primary numeric or text?) and we can take a closer look.

sheetjs closed this issue

2023-09-25 20:53:08 +00:00

Sign in to join this conversation.