sheet js xlsx writeFile callback

Question

I am trying to write multiple csv files from a set of data that I have loaded using the sheet js library. My first attempt was like:

    for (let i = 0; i < dataSetDivided.length; i++) {
      let exportSet = dataSetDivided[i]
      console.log(exportSet)
      let ws = XLSX.utils.json_to_sheet(exportSet, {header: finalHeaders})
        let wb = XLSX.utils.book_new()
        XLSX.utils.book_append_sheet(wb, ws, "SheetJS")

      let todayDate = this.returnFormattedDate()

      let originalFileName = this.state.fileName
      let exportFileName = 'import_' + originalFileName + '_' + todayDate + '(part_' + (i + 1) + ').csv'

      XLSX.writeFile(wb, exportFileName)
}

With this code only some files are written I guess because the for loop doesn't wait for the file to be written before continuing.

So I am trying to write each file within a promise like below:

Promise.all(
  dataSetDivided.map((exportSet, i) => {
    return new Promise((resolve, reject) => {

      console.log(exportSet)
      let ws = XLSX.utils.json_to_sheet(exportSet, {header: finalHeaders})
      let wb = XLSX.utils.book_new()
      XLSX.utils.book_append_sheet(wb, ws, "SheetJS")

      let todayDate = this.returnFormattedDate()

      let originalFileName = this.state.fileName
      let exportFileName = 'import_' + originalFileName + '_' + todayDate + '(part_' + (i + 1) + ').csv'

      XLSX.writeFile(wb, exportFileName, (err) => {
        if (err) {
          console.log(err)
          reject(err)
        } else {
          console.log('Created ' + exportFileName)
          resolve()
        }
      })
    })
  })
)
.then(() => {
  console.log('Created multiple files successfully')
})
.catch((err) => {
  console.log('ERROR: ' + err)
})

But... this isn't working, again only some files are written and nothing is logged to the console. Can anyone give me any ideas how to make this work or a better way to achieve the goal of writing multiple files like this? There is a XLSX.writeFileAsync method but I can't find any examples of how it works and I'm not sure if that is what I need.

With thanks,

James

UPDATE:

I am now using setTimeout to delay the next writeFile call... this is working for my test cases but I am aware it isn't a good solution, would be much better to have a callback when the file is successfully written:

    writeFileToDisk(dataSetDivided, i) {

    if (dataSetDivided.length > 0) {

      let exportSet = dataSetDivided[0]
      let ws = XLSX.utils.json_to_sheet(exportSet, {header: finalHeaders})
        let wb = XLSX.utils.book_new()
        XLSX.utils.book_append_sheet(wb, ws, "SheetJS")

      let todayDate = this.returnFormattedDate()

      let originalFileName = this.state.fileName
      let exportFileName = 'import_' + originalFileName + '_' + todayDate + '(part_' + i + ').csv'

      XLSX.writeFile(wb, exportFileName)

      dataSetDivided.splice(0, 1)

      i += 1

      setTimeout(() => {this.writeFileToDisk(dataSetDivided, i)}, 2500)
    }
  }

  this.writeFileToDisk(dataSetDivided, 1)

Any suggestions how to get this working without simulating the file write time would be much appreciated.

Answer 1

I just tried this (first time) XLSX code and can confirm that it writes the expected workbooks and runs synchronously...

'use strict'

const XLSX = require('xlsx');

let finalHeaders = ['colA', 'colB', 'colC'];
let data = [
    [ { colA: 1, colB: 2, colC: 3 }, { colA: 4, colB: 5, colC: 6 }, { colA: 7, colB: 8, colC: 9 } ],
    [ { colA:11, colB:12, colC:13 }, { colA:14, colB:15, colC:16 }, { colA:17, colB:18, colC:19 } ],
    [ { colA:21, colB:22, colC:23 }, { colA:24, colB:25, colC:26 }, { colA:27, colB:28, colC:29 } ]
];

data.forEach((array, i) => {
    let ws = XLSX.utils.json_to_sheet(array, {header: finalHeaders});
    let wb = XLSX.utils.book_new()
    XLSX.utils.book_append_sheet(wb, ws, "SheetJS")
    let exportFileName = `workbook_${i}.xls`;
    XLSX.writeFile(wb, exportFileName)
});

Running this yields workbook_0.xls, workbook_1.xls, and workbook_2.xls, each with a single sheet entitled "SheetJS". They all look good in excel, for example, workbook_0 has...

I think you should do the writing asynchronously, and would suggest the following adaptation of the above ...

function writeFileQ(workbook, filename) {
    return new Promise((resolve, reject) => {
        // the interface wasn't clearly documented, but this reasonable guess worked...
        XLSX.writeFileAsync(filename, workbook, (error, result) => {
            (error)? reject(error) : resolve(result);
        })
    })
}


let promises = data.map((array, i) => {
    let ws = XLSX.utils.json_to_sheet(array, {header: finalHeaders});
    let wb = XLSX.utils.book_new()
    XLSX.utils.book_append_sheet(wb, ws, "SheetJS")
    let exportFileName = `workbook_${i}.xls`;
    return writeFileQ(wb, exportFileName)
});

Promise.all(promises).then(result => console.log(result)).catch(error => console.log(error));

Running this async code, I found that it produced the same expected results and did so asynchronously.

So your original loop looks right, and should work synchronously. The fact that you aren't getting expected results must be caused by something apart from timing (or maybe some timing issue induced by react?).

In any event, if you do want to use the async approach, which I highly recommend, I've shown how to do that (but I worry that might not fully solve the problem unless you sort out what's happening with your first attempt).

Answer 2

XLSX.writeFileAsync does have a callback with the following syntax.

xlsx.writeFileAsync(workbookName, workbook, (err) => {
   // It's a callback
});

But this will handle only writing one file asynchronously.

Your case is typical, if you want to do a series things in which each item is asynchronous, then you should not just use iterative methods like loops/map/forEach.

One best library I would suggest for this is 'async'. 'async.parallel' function which takes a array of functions which execute asynchronously and calls callback after all of them finished.

https://caolan.github.io/async/docs.html#parallel

Answer 3

If the concern is to use the library asynchronously for not blocking the server, you should know that this library implementation seems to be synchronous and you should check out the library's server demos README since it has several proposals to workaround this problem: https://github.com/SheetJS/sheetjs/tree/master/demos/server