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.
