XlsxWriter.Builder (xlsx_writer v0.7.5)

Copy Markdown

A high-level builder API for creating Excel files without manually tracking cell positions.

Experimental Feature

This API is experimental and subject to change in future releases. While functional and tested, the API design may evolve based on user feedback. Use with caution in production code and expect potential breaking changes.

This module provides a convenient way to generate Excel files by automatically tracking cursor positions as you add data. It's ideal for simple use cases where you're generating large files without complex layout requirements.

Quick Example

XlsxWriter.Builder.create()
|> XlsxWriter.Builder.add_sheet("Summary")
|> XlsxWriter.Builder.add_rows([
  [{"Q1", format: [:bold]}, {"Q2", format: [:bold]}, {"Q3", format: [:bold]}],
  [100, 200, 300]
])
|> XlsxWriter.Builder.skip_rows(1)
|> XlsxWriter.Builder.add_rows([["Total", {600, format: [:italic]}]])
|> XlsxWriter.Builder.write_file("output.xlsx")

Features

  • Automatic cursor position tracking
  • Per-cell formatting via tuples
  • Multi-sheet support with add_sheet/2
  • Simple cursor movement with skip_rows/2
  • Seamless conversion to final XLSX binary

Cell Format Options

Each cell can be either a plain value or a tuple {value, opts} where opts include:

  • width: number - Column width (applies to entire column) - Builder-specific
  • format: list - List of XlsxWriter format options (see below)

The format option accepts the same format list as XlsxWriter.write/5:

  • :bold, :italic, :strikethrough - Text styles
  • {:font_size, number} - Font size in points
  • {:font_color, "#RRGGBB"} - Font color (hex)
  • {:bg_color, "#RRGGBB"} - Background color (hex)

  • {:align, :left | :center | :right} - Text alignment

  • {:num_format, "format_string"} - Number format
  • {:border, style}, {:border_top, style}, etc. - Borders

See XlsxWriter.write/5 for complete format options.

Summary

Types

cell()
cell_value()
cell_with_format()
row()
t()

Functions

add_rows(builder, rows, opts \\ [])

Adds multiple rows starting at the current cursor position.

add_sheet(builder, sheet_name, opts \\ [])

Adds a new sheet to the workbook and switches context to it.

create()

Creates a new builder for generating an Excel file.

skip_rows(builder, n \\ 1)

Moves the cursor down by N rows.

write_binary(builder)

Generates the final Excel file and returns the binary content.

write_file(builder, path)

Generates the final Excel file and writes it to disk.

Types

cell()

@type cell() :: cell_value() | cell_with_format()

cell_value()

@type cell_value() :: any()

cell_with_format()

@type cell_with_format() :: {cell_value(), keyword()}

row()

@type row() :: [cell()]

t()

@type t() :: %XlsxWriter.Builder{
  column_widths: %{optional(non_neg_integer()) => number()},
  current_sheet: String.t() | nil,
  cursor_col: non_neg_integer(),
  cursor_row: non_neg_integer(),
  sheets: [{String.t(), list()}]
}

Functions

add_rows(builder, rows, opts \\ [])

Adds multiple rows starting at the current cursor position.

Each row is a list of cells. Cells can be plain values or tuples {value, opts} for formatting.

Parameters

  • builder - The builder state
  • rows - List of rows, where each row is a list of cells
  • opts - Optional keyword list:
    • :start_row - Override cursor row position (0-based)
    • :start_col - Override cursor column position (0-based)

Returns

Updated builder state with cursor moved after the last row.

Examples

# Simple rows
builder
|> XlsxWriter.Builder.add_rows([
  ["Name", "Age", "City"],
  ["Alice", 30, "NYC"],
  ["Bob", 25, "LA"]
])

# With formatting
builder
|> XlsxWriter.Builder.add_rows([
  [{"Name", format: [:bold]}, {"Age", format: [:bold]}],
  ["Alice", {30, format: [{:num_format, "0"}]}]
])

# With position override
builder
|> XlsxWriter.Builder.add_rows([["Data"]], start_row: 5, start_col: 2)

add_sheet(builder, sheet_name, opts \\ [])

Adds a new sheet to the workbook and switches context to it.

The cursor position resets to (0, 0) for the new sheet.

Parameters

  • builder - The builder state
  • sheet_name - Name of the new sheet
  • opts - Optional keyword list (reserved for future use)

Returns

Updated builder state with the new sheet active.

Examples

builder
|> XlsxWriter.Builder.add_sheet("Summary")
|> XlsxWriter.Builder.add_sheet("Details")

create()

Creates a new builder for generating an Excel file.

Returns

A new builder state.

Examples

iex> builder = XlsxWriter.Builder.create()
iex> is_struct(builder, XlsxWriter.Builder)
true

skip_rows(builder, n \\ 1)

Moves the cursor down by N rows.

Useful for adding spacing between sections of data.

Parameters

  • builder - The builder state
  • n - Number of rows to skip (default: 1)

Returns

Updated builder state with cursor moved down.

Examples

builder
|> XlsxWriter.Builder.add_rows([["Section 1"]])
|> XlsxWriter.Builder.skip_rows(2)
|> XlsxWriter.Builder.add_rows([["Section 2"]])

write_binary(builder)

Generates the final Excel file and returns the binary content.

This finalizes all sheets and generates the XLSX file binary.

Parameters

  • builder - The builder state

Returns

  • {:ok, xlsx_binary} on success
  • {:error, reason} on failure

Examples

{:ok, content} = builder |> XlsxWriter.Builder.write_binary()
File.write!("output.xlsx", content)

write_file(builder, path)

Generates the final Excel file and writes it to disk.

Convenience function that combines write_binary/1 and File.write!/2.

Parameters

  • builder - The builder state
  • path - The output file path (required)

Returns

  • :ok on success
  • {:error, reason} on failure

Examples

builder |> XlsxWriter.Builder.write_file("output.xlsx")
builder |> XlsxWriter.Builder.write_file("reports/sales.xlsx")