Elixlsx

You can create an .xlsx file by composing Workbook and Sheet operations. Use Workbook.append_sheet/2 to add sheets to a workbook and Elixlsx.write_to/2 to save the file to disk. Sheets can be named using Sheet.with_name/1 and cells can be populated using Sheet.set_cell/4 with various formatting options like bold: true.

(alias Elixlsx.Workbook, alias Elixlsx.Sheet)

Workbook.append_sheet(%Workbook{}, Sheet.with_name("Sheet 1") |> Sheet.set_cell("A1", "Hello", bold: true)) 
|> Elixlsx.write_to("hello.xlsx")

Add elixlsx to your mix.exs dependencies. Note that version 0.6 and above requires Elixir 1.12 or higher.

# Via Hex
defp deps do
  [{:elixlsx, "~> 0.6.0"}]
end

# Via GitHub
defp deps do
  [{:elixlsx, github: "xou/elixlsx"}]
end

Add data validation rules to a range of cells.

Parameters:

• start_cell: The starting Excel coordinate (e.g., "A1").
• end_cell: The ending Excel coordinate (e.g., "B10").
• values: A single String.t() or a list(String.t()) representing the allowed values.

# Validates that cells in A1:A10 contain one of the specified values
Elixlsx.Sheet.add_data_validations(sheet, "A1", "A10", ["Option1", "Option2"])

The Elixlsx.Style.NumFmt module is used to define how numbers and dates are formatted in an Excel spreadsheet. You can create format structs using from_props/1 by passing a map of properties, or by using the provided helper functions for common date formats.

Supported Properties for from_props/1:

• :yyyymmdd: Returns a yyyy-mm-dd format.
• :yyyymm: Returns a yyyy-mm format.
• :datetime: Returns a yyyy-mm-dd h:mm:ss format.
• :num_format: Accepts a custom string representing a valid Excel format code. This will raise an ArgumentError if the string is not a valid XML string.

# Using predefined date formats
fmt_dd = Elixlsx.Style.NumFmt.from_props(%{yyyymmdd: true})
fmt_mm = Elixlsx.Style.NumFmt.from_props(%{yyyym: true})

# Using a custom format code
custom_fmt = Elixlsx.Style.NumFmt.from_props(%%{num_format: "#,##0.00"})

Use Elixlsx.Sheet.with_name/1 to initialize a new sheet. The sheet name can be up to 31 characters long.

Elixlsx.Sheet.with_name("My Sheet")

The Elixlsx.Style.Border.get_border_entry/1 function converts a %Elixlsx.Style.Border{} struct into its corresponding XML string representation used in XLSX files.

Example

# Input
Elixlsx.Style.Border.get_border_entry %Elixlsx.Style.Border{type: :top, style: :dotted}

# Output
"<top style=\"dotted\"></top>"

Elixlsx.Style.Border.get_border_entry %Elixlsx.Style.Border{type: :top, style: :dotted}
# => "<top style=\"dotted\"></top>"

Use Elixlsx.write_to/2 to save an Elixlsx.Workbook object to a specific file on disk. This function handles the compilation of the workbook (including shared strings and styles) and packages it into the .xlsx (OpenXML) format using the :zip application.

Elixlsx.write_to(workbook, "output.xlsx")

Freeze panes at a specific row and column intersection. This keeps certain rows or columns visible while scrolling.

• Rows and columns are indexed starting from 1.
• Use 0 to indicate no freezing for that dimension (e.g., {1, 0} freezes the first row but no columns).
• Use remove_pane_freeze/1 to clear any existing freeze settings.

# Freeze the first row and first column
Elixlsx.Sheet.set_pane_freeze(sheet, 1, 1)

# Remove freezing
Elixlsx.Sheet.remove_pane_freeze(sheet)

Use Elixlsx.write_to_memory/2 to generate the .xlsx binary in memory instead of writing directly to a file. This is useful for web applications where you want to serve the file as a download or pass it to another process without disk I/O.

It returns a tuple containing the filename (as a charlist) and the actual binary data.

{:ok, {filename, binary}} = Elixlsx.write_to_memory(workbook, "output.xlsx")

Increase the outline level for a range of columns. Columns are indexed by name (e.g., "A", "C").

Options:

• collapsed: If true, the group will be collapsed by default.

# Groups columns from A to C and collapses them
Elixlsx.Sheet.group_cols(sheet, "A", "C", collapsed: true)