I've been tasked with importing and parsing a huge XLSX file and displaying as a dashboard (I'm aware it would be a task for the backend, but I need to do it on frontend).
I need the last 100 rows, can anyone help me out? Here's my code so far:
const handleFile = async(e) => {
const file = e.target.files[0];
const data = await file.arrayBuffer();
const workbook = XLSX.readFile(data, {sheetRows: 100});
const worksheet = workbook.Sheets[workbook.SheetNames[0]];
const jsonData = XLSX.utils.sheet_to_json(worksheet, { raw: false, range: 1048476});
setChartData(jsonData)
}
I've tried the Range parameter, but it brings me more than one objects and crashes my dashboards.
The one issue with range is that you need to set the header column, otherwise it's going to interpret your first row as the header. You can do that by just reading the first two rows and getting the keys from that. Then, you can get the number of rows by using XLSX.utils.decode_range(worksheet['!ref'])
Altogether, it should look like something like:
var csv = "a,b,c\n1,2,3\n4,5,6\n7,8,9\n10,11,12\n13,14,15\n16,17,18\n19,20,21\n22,23,24\n25,26,27\n28,29,30\n1,2,3\n4,5,6\n7,8,9\n10,11,12\n13,14,15\n16,17,18\n19,20,21\n22,23,24\n25,26,27\n67,68,69\n70,71,72\n73,74,75\n76,77,78\n79,80,81\n82,83,84\n85,86,87\n88,89,90\n91,92,93\n94,95,96\n97,98,99"
var wb = XLSX.read(csv, {type:"binary"});
const worksheet = wb.Sheets[wb.SheetNames[0]];
var range = XLSX.utils.decode_range(worksheet['!ref']);
var numRows = range.e.r - range.s.r + 1
const header = Object.keys(XLSX.utils.sheet_to_json(wb.Sheets.Sheet1, {raw:false, range: `A1:ZZ2`})[0]);
const numCols = header.length - 1;
const lastCol = String.fromCharCode('A'.charCodeAt(0) + numCols);
var data = XLSX.utils.sheet_to_json(wb.Sheets.Sheet1, {raw:false, defval:null, range: numRows-10, header});
console.log(data)<script src="https://unpkg.com/xlsx@0.18.5/dist/xlsx.full.min.js"></script>
<pre id="out"></pre>