For my latest react web application, I wanted to create an app to assist a workflow at my construction project. Because '.xlsx' files are very common on the project, I needed to be able to work with data from these files. My plan was to import data from excel sheet to create objects in the back end that would show up in my app.
I found a javascript package xlsx that would allow me to read the xlsx data once I selected the file. To setup the module:
In the command line:
npm install xlsx
At the top of the project:
var xlsx = require("xlsx")
With this, I would now be able to call xlsx.read() which would convert the raw file data into javascript code.
So I need a react component that can upload the file, and parse the data into usable js code. This js code would be used to POST objects to the backend.
First, to upload the file, my basic react component would be:
function Upload() {
function handleFile(e) {
e.preventDefault()
if (e.target.files) {
// read xlsx data
}
}
return(
<form>
<input type="file" onChange={}></input>
</form>
)
}
To read the file data, I used FileReader() to access the contents of the files selected with the input.
const reader = new FileReader()
reader.onload = (e) => {
const data = e.target.result
const workbook = xlsx.read(data, {type: "array})
// organize xlsx data into desired format
}
reader.readAsArrayBuffer(e.target.files[0])
The workbook has many ways to parse the information. I iterate through the sheets:
workbook.SheetNames.forEach((sheet)=>{
const worksheet = workbook.Sheets[sheet]
// format object
})
There are many ways to go from here: worksheet will return an object with keys for all the cells in the xlsx file. These cell labels are based on the x and y position: A,B,C... for the x axis and 1,2,3... for the y axis.
To get the value of cell 'B1' in the worksheet, call:
worksheet.B1.v
or
worksheet['B1']['v']
With this kind of data structure, it's possible to go through the xlsx sheet dynamically. A nested for loop to go through the data row by row or column by column.
for (let row = 1; row < lastRow ; row++) {
for (let column = 'A'; column < lastColumn; column++) {
cellValue = worksheet[`${column}${row}`]['v']
// do something with cell value
}
}
While this sounds easy, the code block above wouldn't work for a couple reasons:
lastRow and lastColumn aren't known values
column++ does not work since the increment operator doesn't work on 'A'
Taking a look at number 1 first:
Getting the value of the lastRow and lastColumn could be done using a custom max function. Javascript can interpret string comparisons like:
'B' > 'A' ==> true
'A' > 'B' ==> false
'A1' > 'D1' ==> false
'D2' > 'D1' ==> true
With this logic, a maxCell function could be created:
function maxCell(cell1, cell2) {
if (cell1 > cell2) {
return cell1
} else {
return cell2
}
}
And to extend this over an array:
function maxArray(array) {
var max = ''
array.forEach((elem)=>{
max = maxCell(max, elem)
})
return max
}
Since the keys of worksheet are all the cell values, calling:
maxArray(Object.keys(worksheet))
would return the largest cell label. And using the largest cell label, the string can be spliced to get the last row and column:
const largestCell = 'E8'
const lastRow = largestCell.slice(1)
const lastColumn = largestCell.slice(0,1)
Taking a look at number 2:
While it isn't as simple as 'A' + 1 = 'B', using the String object methods this is pretty easy. Strings have a method charCodeAt() that will return their UTF-16 character code. So I just need to convert to the character code, increment by 1, and then convert back to a string.
function nextChar(letter) {
return String.fromCharCode(letter.charCodeAt(0) + 1);
}
When I was originally building out my project, this was the thought process I followed. And I thought it would work until I realized this still has a couple bugs. I didn't realize at first, but javascript's string comparison works left to right. If the first left-most value is greater, then the comparison always returns true, and vice versa. For example:
'10' > '9' ==> false
'8' > '20' ==> true
'AA1' > 'B1' ==> false
And remember, column labels in xlsx increment X, Y, Z, AA, AB, AC, etc... Because of this logic, the maxCell function written before wouldn't always work correctly.
While I'm sure it's possible to modify maxCell() to factor in these constraints, to achieve a working function as desired, but at this point I felt like I was spending too much time on a small feature of my project. There had to be a less involved method to parse the data, or I had to place some constraints on my uploaded xlsx file.
By limiting the maxColumn to 'Z', I could avoid 'Z' > 'AA'. And then, since the column label is limited to one character, I can convert the row label to an integer and properly compare the row values.
function maxCell(cell1, cell2) {
var row1 = cell1.slice(1)
var column1 = parseInt(cell1.slice(0,1))
var row2 = cell2.slice(1)
var column2 = parseInt(cell2.slice(0,1))
var maxColumn = (column1 > column2) ? column1 : column 2
var maxRow = (row1 > row2) ? row1.toString() : row2.toString()
return `${maxColumn}${maxRow}`
}
Finally, circling back to the nested for loop I originally wrote, I now have the helper functions to iterate over the xlsx values like originally intended. From here it was easy to format into my database table object and send a POST request.
Top comments (1)
Awesome!