A1 notation: the 'Q1 costs'!B4:C9 strings Google Sheets speaks.
Sheetshow rows and columns are 0-indexed. A1 rows are 1-indexed and A1
columns are bijective base-26 letters (A, Z, AA). This module holds the
conversions; Sheetshow.Coord and Sheetshow.Range parse and print with them.
A sheet name is quoted when it could be mistaken for a cell reference or
contains anything but letters, digits and underscores, with ' doubled
inside quotes, as Sheets does.
Summary
Functions
0-indexed column to letters.
Letters to 0-indexed column. Case-insensitive. Raises ArgumentError on
anything but letters.
Parses one endpoint of a reference into {col, row}, 0-indexed, where a
missing part is nil. $ anchors are accepted and dropped.
Quotes a sheet name when A1 notation needs it.
Whether a bare string reads as a cell or range reference rather than a sheet name. Sheet names for which this is true need quoting, as in Sheets itself.
Splits an A1 string into {sheet, rest}. The sheet is nil when there is
no sheet part; the rest is nil when a quoted name stands alone.
Functions
@spec col_to_letters(non_neg_integer()) :: String.t()
0-indexed column to letters.
iex> Sheetshow.A1.col_to_letters(0)
"A"
iex> Sheetshow.A1.col_to_letters(27)
"AB"@spec letters_to_col(String.t()) :: non_neg_integer()
Letters to 0-indexed column. Case-insensitive. Raises ArgumentError on
anything but letters.
iex> Sheetshow.A1.letters_to_col("ab")
27@spec parse_ref(String.t()) :: {:ok, {non_neg_integer() | nil, non_neg_integer() | nil}} | :error
Parses one endpoint of a reference into {col, row}, 0-indexed, where a
missing part is nil. $ anchors are accepted and dropped.
iex> Sheetshow.A1.parse_ref("B4")
{:ok, {1, 3}}
iex> Sheetshow.A1.parse_ref("$C")
{:ok, {2, nil}}
iex> Sheetshow.A1.parse_ref("10")
{:ok, {nil, 9}}
iex> Sheetshow.A1.parse_ref("A0")
:errorQuotes a sheet name when A1 notation needs it.
iex> Sheetshow.A1.quote_sheet("Costs")
"Costs"
iex> Sheetshow.A1.quote_sheet("Q1 costs")
"'Q1 costs'"
iex> Sheetshow.A1.quote_sheet("Q1's")
"'Q1''s'"
iex> Sheetshow.A1.quote_sheet("Tab1")
"'Tab1'"Whether a bare string reads as a cell or range reference rather than a sheet name. Sheet names for which this is true need quoting, as in Sheets itself.
iex> Sheetshow.A1.ref_like?("Tab1")
true
iex> Sheetshow.A1.ref_like?("A:A")
true
iex> Sheetshow.A1.ref_like?("log")
false@spec split_sheet(String.t()) :: {:ok, {String.t() | nil, String.t() | nil}} | {:error, Sheetshow.Error.t()}
Splits an A1 string into {sheet, rest}. The sheet is nil when there is
no sheet part; the rest is nil when a quoted name stands alone.
iex> Sheetshow.A1.split_sheet("'Q1 costs'!A1:B2")
{:ok, {"Q1 costs", "A1:B2"}}
iex> Sheetshow.A1.split_sheet("Costs!A1")
{:ok, {"Costs", "A1"}}
iex> Sheetshow.A1.split_sheet("A1")
{:ok, {nil, "A1"}}
iex> Sheetshow.A1.split_sheet("'Costs'")
{:ok, {"Costs", nil}}