Parse References

The spreadsheet/reference package turns a reference string into its parts. Anywhere you accept a range from a user, a config file or another workbook, this is what you use instead of splitting on ! and : yourself, because the interesting cases are not the ones you would think to write by hand.

from, to, err := reference.ParseRangeReference("Sheet1!A1:A3")
if err != nil {
    return err
}
fmt.Println(from.RowIdx, from.Column, from.ColumnIdx, from.SheetName)
fmt.Println(to.RowIdx, to.Column, to.ColumnIdx, to.SheetName)

Mind the two index conventions in the same struct. RowIdx is the row number as written, so A1 gives 1. ColumnIdx is a zero-based index, so A gives 0 and AA gives 26. Column keeps the letters. AbsoluteRow and AbsoluteColumn record where the dollar signs were, which is how the shared formula machinery knows which references to offset.

The sheet name is split off at the last ! in the string, not the first. That is what makes Shee!t1!A1:A3 parse as the sheet Shee!t1 rather than failing, and Excel does allow an exclamation mark in a sheet name. It also means Sheet1!!A1:A3 gives you the sheet Sheet1!, which is unlikely to be what the author meant but is the only reading consistent with the rule.

Quotes are not stripped. 'Sheet 1'!A1:A3 parses, and SheetName comes back as 'Sheet 1' with the quotes still attached, so strip them before comparing against Sheet.Name().

Limitations

A sheet-qualified reference whose column is absolute fails to parse. Sheet1!$A$1 and Sheet1!$A1 both fail, reporting no letter prefix in and the part after the sheet name, because the check for a leading dollar sign is made against the whole input rather than against the part after the sheet name. Sheet1!A$1 and the unqualified $A$1 are fine. This matters more than it sounds: Sheet.RangeReference produces exactly the form that fails, so a defined name’s content cannot be fed back through ParseRangeReference.

ParseRangeReference wants exactly two endpoints. A single cell such as Sheet1!A1 returns invalid range format, as does anything with a second colon. Use ParseCellReference for a single cell.

Column references are limited to two letters. ParseColumnReference, which ParseColumnRangeReference calls, rejects anything past ZZ with column reference must be between A and ZZ, so AAA:AAB does not parse even though Excel goes to XFD.

An empty sheet name is an error, so a string that begins with ! fails with Invalid sheet name. A string that merely contains a stray !, such as !Sheet1!A1:A3, parses with the sheet name !Sheet1.

Run the example

The example parses five range references through the same helper and prints the row, column and sheet name of each endpoint. Three of the five have an exclamation mark somewhere unusual, which is the point of it.

git clone https://github.com/unidoc/unioffice-examples.git
cd unioffice-examples/spreadsheet/references-with-sheet-name
go run main.go

If this is your first time using UniOffice, follow the getting started guide to create an API key and set up your development environment.

Sample output

1 A
3 A
1 A Sheet1
3 A Sheet1
1 A Sheet1!
3 A Sheet1!
1 A Shee!t1
3 A Shee!t1
1 A !Sheet1
3 A !Sheet1