[sheet_to_json] Range option: Support "end of column" selection #3008

New Issue

donnikitos · 2023-10-11T08:46:48Z

donnikitos commented

2023-10-11 08:46:48 +00:00

Description

XLSX.utils.sheet_to_json(ws, opts) gives you the option to select a range in A1-Style bounded range notation:

XLSX.utils.sheet_to_json(ws, {
	range: 'A5:A10',
});

Sadly there is not possibility to select a whole column.

Issue

In order to select a range from a specified row to the end of the worksheet you need to know forehand how many rows the worksheet has.
That's pretty cumbersome.

Suggestion

Consider adding the possibility to specify the end of columns with a letter, but without a number, e.g.:

XLSX.utils.sheet_to_json(ws, {
	range: 'A5:A',
});

⮤ This should ideally return you all rows between A5 and the end of the worksheet.

## Description `XLSX.utils.sheet_to_json(ws, opts)` gives you the option to select a range in A1-Style bounded range notation: ```JavaScript XLSX.utils.sheet_to_json(ws, { range: 'A5:A10', }); ``` Sadly there is not possibility to select a whole column. ## Issue In order to select a range from a specified row to the end of the worksheet you need to know forehand how many rows the worksheet has. That's pretty cumbersome. ## Suggestion Consider adding the possibility to specify the end of columns with a letter, but without a number, e.g.: ```JavaScript XLSX.utils.sheet_to_json(ws, { range: 'A5:A', }); ``` ⮤ This should ideally return you all rows between A5 and the end of the worksheet.

sheetjs commented

2023-10-11 19:35:06 +00:00

You don't need to know this in advance. Here's a programmatic solution:

var range = XLSX.utils.decode_range(ws["!ref"]);
range.s.r = 4; // start on Excel row 5 = SheetJS row 4
range.s.c = XLSX.utils.decode_col("A"); // start on Excel column A = SheetJS row 0
range.e.c = XLSX.utils.decode_col("A"); // end on Excel column A = SheetJS row 0
XLSX.utils.sheet_to_json(ws, { range: XLSX.utils.encode_range(range) });

You can also pass a number to start processing on the specified row (see https://docs.sheetjs.com/docs/api/utilities/array#array-output). Passing range: 3 is equivalent to:

var range = XLSX.utils.decode_range(ws["!ref"]);
range.s.r = 3; // start on Excel row 4 = SheetJS row 3
XLSX.utils.sheet_to_json(ws, { range: XLSX.utils.encode_range(range) });

The asymmetric range A5:A looks cool but the main issue is that it is not a valid range according to Excel.

You don't need to know this in advance. Here's a programmatic solution: ```js var range = XLSX.utils.decode_range(ws["!ref"]); range.s.r = 4; // start on Excel row 5 = SheetJS row 4 range.s.c = XLSX.utils.decode_col("A"); // start on Excel column A = SheetJS row 0 range.e.c = XLSX.utils.decode_col("A"); // end on Excel column A = SheetJS row 0 XLSX.utils.sheet_to_json(ws, { range: XLSX.utils.encode_range(range) }); ``` You can also pass a number to start processing on the specified row (see https://docs.sheetjs.com/docs/api/utilities/array#array-output). Passing `range: 3` is equivalent to: ```js var range = XLSX.utils.decode_range(ws["!ref"]); range.s.r = 3; // start on Excel row 4 = SheetJS row 3 XLSX.utils.sheet_to_json(ws, { range: XLSX.utils.encode_range(range) }); ``` The asymmetric range `A5:A` looks cool but the main issue is that it is not a valid range according to Excel.

Sign in to join this conversation.