ExVEx (ExVEx v0.1.1)

Pure-Elixir reader and editor for .xlsx / .xlsm workbooks.

Quick start

{:ok, book} = ExVEx.open("path/to/file.xlsx")
ExVEx.sheet_names(book)            #=> ["Sheet1", "Sheet2"]
{:ok, "hello"} = ExVEx.get_cell(book, "Sheet1", "A1")
:ok = ExVEx.save(book, "path/to/output.xlsx")

Design

ExVEx opens a workbook lazily: raw ZIP part bytes are kept in memory and untouched parts are written back verbatim on save/2. This preserves unknown content (custom XML, VBA macros, extension schemas) on round-trip without the caller needing to opt in.

Summary

Types

cell_ref()

cell_value()

path()

range_ref()

sheet_name()

Functions

cells(book, sheet)

Returns every populated cell on a sheet as a map of A1 refs to resolved values. Empty cells are omitted.

each_cell(book, sheet)

Streams every populated cell on a sheet as {a1_ref, value} pairs in row-major order (row 1 before row 2; column A before column B within a row).

get_cell(book, sheet, ref)

get_formula(book, sheet, ref)

get_style(book, sheet, ref)

merge_cells(book, sheet, ref, opts \\ [])

Merges a rectangular range of cells on a sheet.

merged_ranges(book, sheet)

Returns the list of merged ranges on a sheet as A1-style range refs.

open(path)

put_cell(book, sheet, ref, value)

save(book, path)

sheet_names(workbook)

sheet_path(book, name)

unmerge_cells(book, sheet, ref, opts \\ [])

Removes a merged range from a sheet.

Types

cell_ref()

@type cell_ref() :: String.t() | {pos_integer(), pos_integer()}

cell_value()

@type cell_value() ::
  binary()
  | number()
  | boolean()
  | nil
  | {:formula, String.t()}
  | {:formula, String.t(), binary() | number() | boolean()}

path()

@type path() :: Path.t()

range_ref()

@type range_ref() :: String.t()

sheet_name()

@type sheet_name() :: String.t()

Functions

cells(book, sheet)

@spec cells(ExVEx.Workbook.t(), sheet_name()) ::
  {:ok, %{required(cell_ref()) => term()}} | {:error, term()}

Returns every populated cell on a sheet as a map of A1 refs to resolved values. Empty cells are omitted.

each_cell(book, sheet)

@spec each_cell(ExVEx.Workbook.t(), sheet_name()) ::
  {:ok, Enumerable.t({cell_ref(), term()})} | {:error, term()}

Streams every populated cell on a sheet as {a1_ref, value} pairs in row-major order (row 1 before row 2; column A before column B within a row).

get_cell(book, sheet, ref)

@spec get_cell(ExVEx.Workbook.t(), sheet_name(), cell_ref()) ::
  {:ok, cell_value() | Date.t() | NaiveDateTime.t()} | {:error, term()}

get_formula(book, sheet, ref)

@spec get_formula(ExVEx.Workbook.t(), sheet_name(), cell_ref()) ::
  {:ok, String.t() | nil} | {:error, term()}

get_style(book, sheet, ref)

@spec get_style(ExVEx.Workbook.t(), sheet_name(), cell_ref()) ::
  {:ok, ExVEx.Style.t()} | {:error, term()}

merge_cells(book, sheet, ref, opts \\ [])

@spec merge_cells(ExVEx.Workbook.t(), sheet_name(), range_ref(), keyword()) ::
  {:ok, ExVEx.Workbook.t()} | {:error, term()}

Merges a rectangular range of cells on a sheet.

Options

:preserve_values — false (default) to clear every non-anchor cell in the range (Excel's convention); true to leave underlying cells untouched. Excel will still only display the anchor cell's value, but get_cell/3 on a non-anchor cell will keep returning whatever was there.
:on_overlap — :error (default) to refuse a range that overlaps an existing merge and return {:error, {:overlaps, existing_ref}}; :replace to remove the overlapping range(s) first; :allow to permit overlapping ranges (matches openpyxl's lenient behaviour).

merged_ranges(book, sheet)

@spec merged_ranges(ExVEx.Workbook.t(), sheet_name()) ::
  {:ok, [range_ref()]} | {:error, term()}

Returns the list of merged ranges on a sheet as A1-style range refs.

open(path)

@spec open(path()) :: {:ok, ExVEx.Workbook.t()} | {:error, term()}

put_cell(book, sheet, ref, value)

@spec put_cell(ExVEx.Workbook.t(), sheet_name(), cell_ref(), cell_value()) ::
  {:ok, ExVEx.Workbook.t()} | {:error, term()}

save(book, path)

@spec save(ExVEx.Workbook.t(), path()) :: :ok | {:error, term()}

sheet_names(workbook)

@spec sheet_names(ExVEx.Workbook.t()) :: [sheet_name()]

sheet_path(book, name)

@spec sheet_path(ExVEx.Workbook.t(), sheet_name()) :: {:ok, String.t()} | :error

unmerge_cells(book, sheet, ref, opts \\ [])

@spec unmerge_cells(ExVEx.Workbook.t(), sheet_name(), range_ref(), keyword()) ::
  {:ok, ExVEx.Workbook.t()} | {:error, term()}

Removes a merged range from a sheet.

Options

:on_missing — :error (default) returns {:error, :not_merged} when the exact range is not currently merged; :ignore makes the call a no-op in that case.