Make `simpleCellRangeToString` work with column ranges and row ranges (before: Convert or Parse strings "A1:B2", "A:A", and "1:2" to Range objects)

[impauloalves]

Description

Hello, I'm trying to convert (parse could also help) strings to ranges and vice-versa for the following range types:

• "A1:B2"
• "A:A"
• "1:2"

https://hyperformula.handsontable.com/guide/cell-references.html#range-references

I tried to use the tokenizeFormula function from the hyperformula lexer, but it doesn't seem to work for the "A:A" use case. Tried also to use the simpleCellRangeFromString function, but without success.

Steps to reproduce

            const lexer = doc._hfInstance._parser.lexer;
            const { tokens } = lexer.tokenizeFormula("A:A");
            for (const [index, token] of tokens.entries()) {
                if (token.tokenType.name === "ColumnRange") {
                    console.log("startOffset", token.startOffset);
                    console.log("endOffset", token.endOffset);
                }
            }

          // OR
          console.log("Range", doc._hfInstance.simpleCellRangeFromString("A:A", 1));

• HyperFormula version: 2.1.0
• Browser Name and version: Chrome Version 108.0.5359.124 (Official Build) (x86_64)
• Operating System: macOS

[sequba]

Hi @impauloalves, thanks for your feedback. The default way of converting strings to range objects is by using the simpleCellRangeFromString method. However - as you noticed - currently, it works only with cell ranges.

In my opinion, it makes sense to expand the scope of simpleCellRangeFromString method to handle row and column ranges as well. But at our current stage of development, we prioritize bug and interoperability fixes, so this feature is not on our immediate roadmap.

Also, bear in mind that HyperFormula is an open-source project. We are open to contributions from the community and we would be delighted to collaborate on that feature. Implementing it should be relatively straightforward. You can check out the implementations of:

• simpleCellRangeFromString
• columnAddressFromString
• rowAddressFromString

[brianjenkins94]

Here is my poor man's workaround:

function $$(cellRange: string, contextSheetId: number = sheetId) {
	let [start, end] = cellRange.split(":");

	const { height, width } = hfInstance.getSheetDimensions(sheetId);

	function columnToLetter(n) {
		return n > 26 ? columnToLetter(Math.floor((n - 1) / 26)) + columnToLetter(n % 26) : String.fromCharCode(65 + (n - 1 % 26));
	}

	switch (true) {
		// `start` ends with a letter
		// Example: A:A
		case /\D$/u.test(start):
			start += 1;
		// `end` ends with a letter
		// Example: A1:A
		case /\D$/u.test(end):
			end += height;
			break;
		// `start` starts with a digit
		// Example: 1:1
		case /^\d/u.test(start):
			start = "A" + start;
		// `end` starts with a digit
		// Example: A1:1
		case /^\d/u.test(end):
			end = /\d+$/u.exec(start)[0] === end ? /^\D+/u.exec(start)[0] + width : columnToLetter(width) + height;
			break;
		default:
	}

	return hfInstance.getRangeValues(hfInstance.simpleCellRangeFromString([start, end].join(":"), contextSheetId));
}

[sequba]

Hi @brianjenkins94, thank you for the workaround.

Since more people are interested in such a feature, I'm increasing the priority of this task.

Related issue: https://github.com/handsontable/hyperformula/issues/1375