Xlsxir v1.5.1 Xlsxir

Extracts and parses data from a .xlsx file to an Erlang Term Storage (ETS) process and provides various functions for accessing the data.

Summary

Functions

close(table_id \\ :worksheet)

Deletes ETS process :worksheet and returns :ok if successful

extract(path, index, timer \\ false)

Extracts worksheet data contained in the specified .xlsx file to an ETS process named :worksheet which is accessed via the Xlsxir.Worksheet module. Successful extraction returns :ok with the timer argument set to false and returns a tuple of {:ok, time} where time is a list containing time elapsed during the extraction process (i.e. [hour, minute, second, microsecond]) when the timer argument is set to true

get_cell(cell_ref)

Accesses ETS process and returns value of specified cell

get_cell(table_id, cell_ref)

See get_cell/1 documentation

get_col(col)

Accesses :worksheet ETS process and returns values of specified column in a list

get_col(table_id, col)

See get_col/1 documentation

get_info(num_type \\ :all)

See get_multi_info2 documentation

get_list(table_id \\ :worksheet)

Accesses ETS process and returns data formatted as a list of row value lists

get_map(table_id \\ :worksheet)

Accesses ETS process and returns data formatted as a map of cell references and values

get_mda(table_id \\ :worksheet)

Accesses ETS process and returns an indexed map which functions like a multi-dimensional array in other languages

get_multi_info(table_id, num_type \\ :all)

Returns count data based on num_type specified:

:rows - Returns number of rows contained in worksheet
:cols - Returns number of columns contained in worksheet
:cells - Returns number of cells contained in worksheet
:all - Returns a keyword list containing all of the above

get_row(row)

Accesses :worksheet ETS process and returns values of specified row in a list

get_row(table_id, row)

See get_row/1 documentation

multi_extract(path, index \\ nil, timer \\ false)

Extracts worksheet data contained in the specified .xlsx file to an ETS process with a table identifier of table_id which is accessed via the Xlsxir.Worksheet module. Successful extraction returns {:ok, table_id} with the timer argument set to false and returns a tuple of {:ok, table_id, time} where time is a list containing time elapsed during the extraction process (i.e. [hour, minute, second, microsecond]) when the timer argument is set to true. The table_id is used to access data for that particular ETS process with the various access functions of the Xlsxir module

peek(path, index, rows)

Extracts the first n number of rows from the specified worksheet contained in the specified .xlsx file to an ETS process named :worksheet which is accessed via the Xlsxir.Worksheet module. Successful extraction returns :ok

Functions

close(table_id \\ :worksheet)

Deletes ETS process :worksheet and returns :ok if successful.

Example

Extract first worksheet in an example file named test.xlsx located in ./test/test_data:

iex> Xlsxir.extract("./test/test_data/test.xlsx", 0)
:ok
iex> Xlsxir.close
:ok

iex> {:ok, table_id} = Xlsxir.multi_extract("./test/test_data/test.xlsx", 0)
iex> Xlsxir.close(table_id)
:ok

extract(path, index, timer \\ false)

Cells containing formulas in the worksheet are extracted as either a string, integer or float depending on the resulting value of the cell. Cells containing an ISO 8601 date format are extracted and converted to Erlang :calendar.date() format (i.e. {year, month, day}).

Parameters

path - file path of a .xlsx file type in string format
index - index of worksheet from within the Excel workbook to be parsed (zero-based index)
timer - boolean flag that tracks extraction process time and returns it when set to true. Default value is false.

Example

Extract first worksheet in an example file named test.xlsx located in ./test/test_data:

iex> Xlsxir.extract("./test/test_data/test.xlsx", 0)
  :ok
  iex> Xlsxir.Worksheet.alive?
  true
  iex> Xlsxir.close
  :ok

get_cell(cell_ref)

Accesses ETS process and returns value of specified cell.

Parameters

table_id - table identifier of ETS process to be accessed, defaults to :worksheet
cell_ref - Reference name of cell to be returned in string format (i.e. "A1")

Example

An example file named test.xlsx located in ./test/test_data containing the following:

cell ‘A1’ -> “string one”
cell ‘B1’ -> “string two”
cell ‘C1’ -> integer of 10
cell ‘D1’ -> formula of “4 * 5”
cell ‘E1’ -> date of 1/1/2016 or Excel date serial of 42370

iex> Xlsxir.extract(“./test/test_data/test.xlsx”, 0) :ok iex> Xlsxir.get_cell(“A1”) “string one” iex> Xlsxir.close :ok

iex> {:ok, table_id} = Xlsxir.multi_extract(“./test/test_data/test.xlsx”, 0) iex> table_id |> Xlsxir.get_cell(“A1”) “string one” iex> table_id |> Xlsxir.close :ok

get_cell(table_id, cell_ref)

See get_cell/1 documentation.

get_col(col)

Accesses :worksheet ETS process and returns values of specified column in a list.

Parameters

table_id - table identifier of ETS process to be accessed, defaults to :worksheet
col - Reference name of column to be returned in string format (i.e. "A")

Example

An example file named test.xlsx located in ./test/test_data containing the following:

cell ‘A1’ -> “string one”
cell ‘B1’ -> “string two”
cell ‘C1’ -> integer of 10
cell ‘D1’ -> formula of “4 * 5”
cell ‘E1’ -> date of 1/1/2016 or Excel date serial of 42370

iex> Xlsxir.extract(“./test/test_data/test.xlsx”, 0) :ok iex> Xlsxir.get_col(“A”) [“string one”] iex> Xlsxir.close :ok

iex> {:ok, table_id} = Xlsxir.multi_extract(“./test/test_data/test.xlsx”, 0) iex> table_id |> Xlsxir.get_col(“A”) [“string one”] iex> table_id |> Xlsxir.close :ok

get_col(table_id, col)

See get_col/1 documentation.

get_info(num_type \\ :all)

See get_multi_info2 documentation.

get_list(table_id \\ :worksheet)

Accesses ETS process and returns data formatted as a list of row value lists.

Parameters

table_id - table identifier of ETS process to be accessed, defaults to :worksheet

Example

An example file named test.xlsx located in ./test/test_data containing the following:

cell ‘A1’ -> “string one”
cell ‘B1’ -> “string two”
cell ‘C1’ -> integer of 10
cell ‘D1’ -> formula of “4 * 5”
cell ‘E1’ -> date of 1/1/2016 or Excel date serial of 42370

iex> Xlsxir.extract(“./test/test_data/test.xlsx”, 0) :ok iex> Xlsxir.get_list [[“string one”, “string two”, 10, 20, {2016, 1, 1}]] iex> Xlsxir.close :ok

iex> {:ok, table_id} = Xlsxir.multi_extract(“./test/test_data/test.xlsx”, 0) iex> table_id |> Xlsxir.get_list [[“string one”, “string two”, 10, 20, {2016, 1, 1}]] iex> table_id |> Xlsxir.close :ok

get_map(table_id \\ :worksheet)

Accesses ETS process and returns data formatted as a map of cell references and values.

Parameters

table_id - table identifier of ETS process to be accessed, defaults to :worksheet

Example

An example file named test.xlsx located in ./test/test_data containing the following:

cell ‘A1’ -> “string one”
cell ‘B1’ -> “string two”
cell ‘C1’ -> integer of 10
cell ‘D1’ -> formula of “4 * 5”
cell ‘E1’ -> date of 1/1/2016 or Excel date serial of 42370

iex> Xlsxir.extract(“./test/test_data/test.xlsx”, 0) :ok iex> Xlsxir.get_map %{ “A1” => “string one”, “B1” => “string two”, “C1” => 10, “D1” => 20, “E1” => {2016,1,1}} iex> Xlsxir.close :ok

iex> {:ok, table_id} = Xlsxir.multi_extract(“./test/test_data/test.xlsx”, 0) iex> table_id |> Xlsxir.get_map %{ “A1” => “string one”, “B1” => “string two”, “C1” => 10, “D1” => 20, “E1” => {2016,1,1}} iex> table_id |> Xlsxir.close :ok

get_mda(table_id \\ :worksheet)

Accesses ETS process and returns an indexed map which functions like a multi-dimensional array in other languages.

Parameters

table_id - table identifier of ETS process to be accessed, defaults to :worksheet

Example

An example file named test.xlsx located in ./test/test_data containing the following:

cell ‘A1’ -> “string one”
cell ‘B1’ -> “string two”
cell ‘C1’ -> integer of 10
cell ‘D1’ -> formula of “4 * 5”
cell ‘E1’ -> date of 1/1/2016 or Excel date serial of 42370

iex> Xlsxir.extract(“./test/test_data/test.xlsx”, 0) :ok iex> mda = Xlsxir.get_mda %{0 => %{0 => “string one”, 1 => “string two”, 2 => 10, 3 => 20, 4 => {2016,1,1}}} iex> mda[0][0] “string one” iex> mda[0][2] 10 iex> Xlsxir.close :ok

iex> {:ok, table_id} = Xlsxir.multi_extract(“./test/test_data/test.xlsx”, 0) iex> mda = table_id |> Xlsxir.get_mda %{0 => %{0 => “string one”, 1 => “string two”, 2 => 10, 3 => 20, 4 => {2016,1,1}}} iex> mda[0][0] “string one” iex> mda[0][2] 10 iex> table_id |> Xlsxir.close :ok

get_multi_info(table_id, num_type \\ :all)

Returns count data based on num_type specified:

:rows - Returns number of rows contained in worksheet
:cols - Returns number of columns contained in worksheet
:cells - Returns number of cells contained in worksheet
:all - Returns a keyword list containing all of the above

Parameters

table_id - table identifier of ETS process to be accessed, defaults to :worksheet
num_type - type of count data to be returned (see above), defaults to :all

get_row(row)

Accesses :worksheet ETS process and returns values of specified row in a list.

Parameters

table_id - table identifier of ETS process to be accessed, defaults to :worksheet
row - Reference name of row to be returned in integer format (i.e. 1)

Example

An example file named test.xlsx located in ./test/test_data containing the following:

cell ‘A1’ -> “string one”
cell ‘B1’ -> “string two”
cell ‘C1’ -> integer of 10
cell ‘D1’ -> formula of “4 * 5”
cell ‘E1’ -> date of 1/1/2016 or Excel date serial of 42370

iex> Xlsxir.extract(“./test/test_data/test.xlsx”, 0) :ok iex> Xlsxir.get_row(1) [“string one”, “string two”, 10, 20, {2016, 1, 1}] iex> Xlsxir.close :ok

iex> {:ok, table_id} = Xlsxir.multi_extract(“./test/test_data/test.xlsx”, 0) iex> table_id |> Xlsxir.get_row(1) [“string one”, “string two”, 10, 20, {2016, 1, 1}] iex> table_id |> Xlsxir.close :ok

get_row(table_id, row)

See get_row/1 documentation.

multi_extract(path, index \\ nil, timer \\ false)

Parameters

path - file path of a .xlsx file type in string format
index - index of worksheet from within the Excel workbook to be parsed (zero-based index)
timer - boolean flag that tracts extraction process time and returns it when set to true. Defalut value is false.

Example

Extract first worksheet in an example file named test.xlsx located in ./test/test_data:

iex> {:ok, table_id} = Xlsxir.multi_extract("./test/test_data/test.xlsx", 0)
  iex> table_id |> Xlsxir.Worksheet.alive?
  true
  iex> table_id |> Xlsxir.close
  :ok

Example

Extract all worksheets in an example file named test.xlsx located in ./test/test_data:

iex> results = Xlsxir.multi_extract("./test/test_data/test.xlsx")
  iex> alive_ids = Enum.map(results, fn {:ok, table_id} -> table_id |> Xlsxir.Worksheet.alive? end)
  iex> Enum.all?(alive_ids)
  true
  iex> Enum.map(results, fn {:ok, id} -> Xlsxir.close(id) end) |> Enum.all?(fn result -> result == :ok end)
  true

Example

Extract all worksheets in an example file named test.xlsx located in ./test/test_data with timer:

iex> results = Xlsxir.multi_extract("./test/test_data/test.xlsx", nil, true)
  iex> alive_ids = Enum.map(results, fn {:ok, table_id, _timer} -> table_id |> Xlsxir.Worksheet.alive? end)
  iex> Enum.all?(alive_ids)
  true
  iex> Enum.map(results, fn {:ok, id, _timer} -> Xlsxir.close(id) end) |> Enum.all?(fn result -> result == :ok end)
  true

peek(path, index, rows)

Parameters

path - file path of a .xlsx file type in string format
index - index of worksheet from within the Excel workbook to be parsed (zero-based index)
rows - the number of rows to fetch from within the specified worksheet

Example

Peek at the first 10 rows of the 9th worksheet in an example file named test.xlsx located in ./test/test_data:

iex> Xlsxir.peek("./test/test_data/test.xlsx", 8, 10)
  :ok
  iex> Xlsxir.Worksheet.alive?
  true
  iex> Xlsxir.close
  :ok