0
424
Viewsjs-xlsx - get particular rows of excel sheet
I have an Excel sheet which has 500 rows. I am trying to get the rows from 101 to 200.
I have searched in google, and I have found an example to get starting rows, e.g. 1 to 100 or 1 to 1000, or even 1 to any number.
But I haven't found any code to get rows from mth row to nth row (Here 'm' and 'n' can be any number eg m=101 to n=200)
Below is the code which I have found to get first 100 rows:
let workbook = XLSX.readFile('./files/uploaded_files/testfile.xlsx', {sheetRows: 100})
const wsname = workbook.SheetNames[0];
const ws = workbook.Sheets[wsname];
var exceldata = XLSX.utils.sheet_to_json(ws);
Even if there is any other module to get the rows in between, I would like to know if there is any ?
1 answers
Answer question0
Using a smaller example of:
There are several options:
You can use your current approach and slice the array returned from sheet_to_json e.g.
// option 1
const maxRow = 6;
const minRow = 3;
const wb = XLSX.readFile("./Book1.xlsx", {sheetRows: maxRow});
const ws = wb.Sheets[wb.SheetNames[0]];
let data = XLSX.utils.sheet_to_json(ws);
data = data.slice(minRow <= 2 ? 0 : minRow - 2);
console.log(data);
It's minRow - 2 to account for 1 row being headers and that the other 1 is to include row 3, not exclude it. This produces:
[
{ a: 4, b: 5, c: 6 },
{ a: 7, b: 8, c: 9 },
{ a: 10, b: 11, c: 12 },
{ a: 13, b: 14, c: 15 }
]
Another option is to combine use of the range and header (see here) options. range allows you to control what range is considered by sheet_to_json and header is used to define the keys used in the output array of objects.
You can use this after importing the whole file or continue to use the sheetRows option as well e.g.:
// option 2
const importRange = "A3:F6";
const headers = ["a", "b", "c"];
const wb = XLSX.readFile("./Book1.xlsx"); // not using sheetRows
const ws = wb.Sheets[wb.SheetNames[0]];
const data = XLSX.utils.sheet_to_json(ws, {range: importRange, header: headers});
console.log(data);
Which produces:
[
{ a: 4, b: 5, c: 6 },
{ a: 7, b: 8, c: 9 },
{ a: 10, b: 11, c: 12 },
{ a: 13, b: 14, c: 15 }
]
Noting that if you omit the headers option then the output is:
[
{ '4': 7, '5': 8, '6': 9 },
{ '4': 10, '5': 11, '6': 12 },
{ '4': 13, '5': 14, '6': 15 }
]
Because the values in row 3 become the new default headers (which I think you probably don't want).
Finally, if you don't know the headers in advance you can just get an array of arrays and figure the headers out later:
// option 3
const importRange = "A3:F6";
const headers = 1;
const wb = XLSX.readFile("./Book1.xlsx"); // not using sheetRows
const ws = wb.Sheets[wb.SheetNames[0]];
const data = XLSX.utils.sheet_to_json(ws, {range: importRange, header: headers});
console.log(data);
Which produces:
[
[ 4, 5, 6 ],
[ 7, 8, 9 ],
[ 10, 11, 12 ],
[ 13, 14, 15 ]
]