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.
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.
Share Improve this question edited Apr 13, 2018 at 9:05 Jimme asked Apr 12, 2018 at 14:00 JimmeJimme 2412 gold badges3 silver badges7 bronze badges 10-
I think you've misdiagnosed the problem with the first loop. Doc says that the write function is synchronous, so wrapping it in a promise will just confuse the matter. You should investigate why the first loop is writing some, but not all files. (what's different about those 'some' cases?, maybe log
exportFileName?). Having figured that out, definitely, definitely use theXLSX.writeFileAsync. I can give you advice on how to wrap that with a promise, but first walk, then fly. – danh Commented Apr 12, 2018 at 14:56 - The fact that some writes happen successfully means you're close. Now the task is just nose-to-the-grindstone debugging. – danh Commented Apr 12, 2018 at 14:57
- Thanks for your reply. Is it not the case that the for loop will run and get to XLSX.writeFile(wb, exportFileName) and the mand is sent for this file to be written. Then the loop continues and potentially before the last file is written the second call to XLSX.writeFile(wb, exportFileName) is made.. and this is causing the file writing to sometimes plete and sometimes not? So I need to have a callback that tells me when the file is written and then move on to the next one? – Jimme Commented Apr 12, 2018 at 15:26
- I think the write is happening synchronously in your first loop. See if it returns something and log that. Also please log the filenames. Can you create a small version of the input, maybe one with just two or three exportSets that each have just two or three objects (rows)? Please post log results from that. I'm pretty confident you're not (yet) confronting any synch/async issue. (You'll need to get to that next). (Oh, also, can you pull react out of the mix? Lets just see the data get handled without any UI plications) – danh Commented Apr 12, 2018 at 15:39
- The filenames all console.log correctly in the loop, like: example_part_1, example_part_2 etc... The files that get written often have example_part_1 as a greyed out file name in the downloads folder, but this only happens sometimes. And there are usually a couple of files missing, it is quite random which file it is that is missing, it could be example_part_3 or example_part_4. I might have misunderstood what you are asking me to console.log... how do I see if the write returns something? – Jimme Commented Apr 12, 2018 at 16:09
3 Answers
Reset to default 9I 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 remend, 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).
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
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./SheetJS/sheetjs/tree/master/demos/server