This is similar to question: Excel to JSON javascript code?, but using React and Dropzone.
I either need to upload the Excel file to the server and parse it there, or parse it in the browser to JSON, then upload the JSON.
I have written React (frontend) and Server side (backend) to process a CSV file and store to MongoDB. The React side is below.
My two issues have been:
If I do it on the client side, how to use XSLX or sheetjs to read and process a file dropped using DropZone
If I do it on the server, how to properly upload the Excel file (I've read posts about setting Mime-Extension for Office/Excel). I've been able to upload a file, but I can never open it in Excel after it has been uploaded. It's usually double the size, probably UTF-8 to UTF-16 conversion happening.
import React, {useCallback, useState} from 'react';
import {useDropzone} from 'react-dropzone' // use hooks
import Dropzone from 'react-dropzone'
import XLSX from 'xlsx'
//import logo1 from './logo.svg'; // original
import logo2 from './images/ShedMadeOfWood_small.jpg'; // demo logo
import './App.css';
function App() {
// Edit <code>src/App.js</code> and save to reload.
// const [files, setFiles] = useState([])
const currDateTime1 = new Date().toISOString()
console.warn(currDateTime1 + " Starting React/App: function App()")
const onDrop = useCallback(acceptedFiles => {
// Do something with the files
const currDateTime2 = new Date().toISOString()
console.log(currDateTime2 + " trying to read file")
acceptedFiles.forEach((file) => {
const reader = new FileReader()
reader.onabort = () => console.log('file reading was aborted')
reader.onerror = () => console.log('file reading has failed')
reader.onload = (data) => {
// Do what you want with the file contents
//console.log("file size:", data.length);
//const binaryStr = reader.result
//const fileContents = reader.readAsText
const fileContents = data.target.result
const currDateTime3 = new Date().toISOString()
console.log(currDateTime3 + " Text print of file contents:")
// console.log(fileContents)
// This fetch worked for CSV file
fetch('http://localhost:3001/api/filedata', {
method: 'POST',
headers: {
'Accept': 'application/json',
'Content-Type': 'application/json'
},
// TODO - could add userid, datetime here, filename, etc...
body: JSON.stringify({
"fileContents": fileContents
//, secondParam: 'yourOtherValue',
})
})
// another example passed formData to the body: https://stackoverflow.com/questions/41025078/react-dropzone-how-to-upload-image
/*
fetch('http://localhost:3001/api/filedatabinary', {
method: 'POST',
body: fileContents
})
*/
}
reader.readAsText(file)
//reader.readAsBinaryString(file)
})
}, [])
const {getRootProps, getInputProps, isDragActive} = useDropzone ({onDrop})
const [fileNames, setFileNames] = useState([]);
const handleDrop = acceptedFiles =>
setFileNames(acceptedFiles.map(file => file.name));
return (
<div align="center">
<div className="App" {...getRootProps()}>
<header className="App-header">
<img src={logo2} className="App-logo" alt="logo" />
</header>
<h4>CSV Files here</h4>
<input {...getInputProps()} />
{
isDragActive ?
<p>Drop the files here ...</p> :
<div>
<p>Drag and Drop a csv file here,<br />
or click to select files.</p>
</div>
}
</div>
<h4>Drop Excel Files Here</h4>
<Dropzone onDrop={handleDrop}>
{({ getRootProps, getInputProps }) => (
<div {...getRootProps({ className: "dropzone" })}>
<input {...getInputProps()} />
<p>Drag and drop Excel files, or click to select files</p>
</div>
)}
</Dropzone>
<div>
<strong>Excel File(s):</strong>
<ul>
{fileNames.map(fileName => (
<li key={fileName}>{fileName}</li>
))}
</ul>
</div>
<hr />
<br /><br />
Thanks for using our company!
</div>
);
}
export default App;
My server side code splits and loops through the rows of a CSV and saves them to MongoDB as JSON. I upload the contents of the CSV file as a single value of a JSON variable, then parse it on the server. My most recent change was to add two drop zones, one for CSV and one for Excel, using two different techniques. I can use two different REST methods on the server, one for the CSV and one for the binary, if needed. Or I can convert to JSON on the client, and just have one REST method on the server.
Got XLSX working on the client based on this post: Parse XLSX with Node and create json.
In other words, the browser side converts the Excel to JSON, and posts the JSOn to the server. I'm assuming there will just be one worksheet, and convert the first one only if there are more than one.
I'm not entirely sure why he need both types of readAsBinaryString and readAsArrayBuffer, but it's working fine.
const handleExcelDrop = (acceptedFiles) => {
console.log("handleExcelDrop")
setFileNames(acceptedFiles.map(file => file.name));
acceptedFiles.forEach((file) => {
console.log("handleExcelDrop:forEach(file)")
// See https://stackoverflow.com/questions/30859901/parse-xlsx-with-node-and-create-json
const reader = new FileReader()
const rABS = !!reader.readAsBinaryString; // !! converts object to boolean
reader.onabort = () => console.log('file reading was aborted')
reader.onerror = () => console.log('file reading has failed')
reader.onload = (e) => {
// Do what you want with the file contents
var bstr = e.target.result;
var workbook = XLSX.read(bstr, { type: rABS ? "binary" : "array" })
var sheet_name_list = workbook.SheetNames[0];
var jsonFromExcel = XLSX.utils.sheet_to_json(workbook.Sheets[sheet_name_list], {
raw: false,
dateNF: "MM-DD-YYYY",
header:1,
defval: ""
})
console.log("jsonFromExcel object=")
console.log(jsonFromExcel)
console.log("jsonFromExcel string=")
console.log(JSON.stringify(jsonFromExcel))
fetch('http://localhost:3001/api/filedataExcel', {
method: 'POST',
headers: {
'Accept': 'application/json',
'Content-Type': 'application/json'
},
// TODO - could add userid, datetime here, filename, etc...
jsonFromExcel
})
}
if (rABS) reader.readAsBinaryString(file);
else reader.readAsArrayBuffer(file);
})
}
and of course have the import at the top:
import XLSX from 'xlsx'
Later I will have to add logic to see what happens if they drop something other than an Excel file.