Builder API Guide

⚠️ Experimental Feature: The Builder API is experimental and may 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.

The Builder API provides a high-level, simplified interface for creating Excel files without manually tracking cell positions. It's perfect for quickly generating reports and exporting data.

When to Use the Builder API

Use Builder when:

Quickly exporting data from lists or database queries
Generating simple reports with sequential data
You don't need advanced features like formulas, images, or merged cells
Speed of development is more important than fine-grained control
Working with large datasets in a straightforward layout

Use Low-Level API when:

Need precise control over cell positioning
Using advanced features (formulas, images, merged cells, freeze panes)
Creating complex layouts with scattered data
Building spreadsheet templates with specific structure

Quick Start

The simplest possible example:

alias XlsxWriter.Builder

Builder.create()
|> Builder.add_sheet("Report")
|> Builder.add_rows([
  ["Name", "Value"],
  ["Alice", 100],
  ["Bob", 200]
])
|> Builder.write_file("report.xlsx")

Core Concepts

1. Builder State

The Builder maintains state including:

List of sheets with their instructions
Current active sheet
Cursor position (row, col)
Column width mappings

All operations return the updated builder state for piping.

2. Automatic Cursor Tracking

The cursor automatically advances as you add data:

Builder.create()
|> Builder.add_sheet("Data")
|> Builder.add_rows([["Row 1"]])  # Cursor at (0,0) → (1,0)
|> Builder.add_rows([["Row 2"]])  # Cursor at (1,0) → (2,0)
|> Builder.add_rows([["Row 3"]])  # Cursor at (2,0) → (3,0)

3. Format Options

Cell formatting uses the same syntax as XlsxWriter.write/5:

# Cell with formatting
{"Bold Text", format: [:bold]}

# Multiple format options
{"Styled", format: [:bold, :italic, {:font_size, 14}, {:bg_color, "#FFFF00"}]}

# Column width (Builder-specific)
{"Wide", format: [:bold], width: 30}

API Reference

`create/0`

Creates a new builder instance.

builder = Builder.create()

Returns: A new builder state

`add_sheet/2`

Adds a new sheet and switches context to it. Resets cursor to (0, 0).

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

Parameters:

builder - The builder state
sheet_name - Name of the sheet (string)

Returns: Updated builder with new active sheet

`add_rows/3`

Adds multiple rows starting at the current cursor position.

# Simple rows
builder |> Builder.add_rows([
  ["A", "B", "C"],
  [1, 2, 3]
])

# With formatting
builder |> Builder.add_rows([
  [{"Header", format: [:bold]}, {"Value", format: [:bold]}],
  ["Data", 100]
])

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

Parameters:

builder - The builder state
rows - List of rows (list of lists)
opts - Options (optional):
- :start_row - Override cursor row (0-based)
- :start_col - Override cursor column (0-based)

Returns: Updated builder with cursor moved after last row

`skip_rows/2`

Moves cursor down by N rows for spacing.

builder
|> Builder.add_rows([["Section 1"]])
|> Builder.skip_rows(2)  # Add 2 blank rows
|> Builder.add_rows([["Section 2"]])

Parameters:

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

Returns: Updated builder with cursor moved down

`write_binary/1`

Generates the XLSX file as binary data.

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

Returns: {:ok, binary} or {:error, reason}

`write_file/2`

Generates and writes the XLSX file to disk.

builder |> Builder.write_file("report.xlsx")

Parameters:

builder - The builder state
path - Output file path (required)

Returns: :ok or {:error, reason}

Format Options

All format options from XlsxWriter.write/5 are supported:

Text Styles

format: [:bold]
format: [:italic]
format: [:strikethrough]
format: [:bold, :italic]  # Combine multiple

Fonts

format: [{:font_size, 14}]
format: [{:font_color, "#FF0000"}]  # Red
format: [{:font_name, "Arial"}]

Colors and Alignment

format: [{:bg_color, "#FFFF00"}]  # Yellow background
format: [{:align, :left}]
format: [{:align, :center}]
format: [{:align, :right}]

Number Formatting

format: [{:num_format, "$#,##0.00"}]  # Currency
format: [{:num_format, "0.00%"}]       # Percentage
format: [{:num_format, "#,##0"}]       # Thousands

Borders

format: [{:border, :thin}]
format: [{:border_top, :thick}]
format: [{:border_bottom, :double}]
format: [{:border_left, :dashed}]
format: [{:border_right, :dotted}]

Available border styles: :thin, :medium, :thick, :dashed, :dotted, :double, :hair, :medium_dashed, :dash_dot, :medium_dash_dot, :dash_dot_dot, :medium_dash_dot_dot, :slant_dash_dot

Column Width (Builder-Specific)

{"Text", width: 20}  # Set column width
{"Text", format: [:bold], width: 30}  # With formatting

Examples

Basic Report

alias XlsxWriter.Builder

Builder.create()
|> Builder.add_sheet("Sales Report")
|> Builder.add_rows([
  [{"Month", format: [:bold]}, {"Revenue", format: [:bold]}],
  ["January", {50000, format: [{:num_format, "$#,##0"}]}],
  ["February", {62000, format: [{:num_format, "$#,##0"}]}],
  ["March", {58000, format: [{:num_format, "$#,##0"}]}]
])
|> Builder.skip_rows(1)
|> Builder.add_rows([
  [{"Total", format: [:bold]}, {170000, format: [{:num_format, "$#,##0"}]}]
])
|> Builder.write_file("sales_report.xlsx")

Multi-Sheet Workbook

Builder.create()
|> Builder.add_sheet("Summary")
|> Builder.add_rows([
  [{"Quarterly Revenue", format: [:bold, {:font_size, 16}]}]
])
|> Builder.skip_rows(1)
|> Builder.add_rows([
  [{"Quarter", format: [:bold]}, {"Amount", format: [:bold]}],
  ["Q1", 170000],
  ["Q2", 185000],
  ["Q3", 195000],
  ["Q4", 210000]
])
|> Builder.add_sheet("Q1 Details")
|> Builder.add_rows([
  [
    {"Product", format: [:bold], width: 25},
    {"Units", format: [:bold], width: 12},
    {"Revenue", format: [:bold], width: 15}
  ],
  ["Widget A", 1200, 36000],
  ["Widget B", 850, 42500]
])
|> Builder.write_file("quarterly_report.xlsx")

Large Dataset Export

# Generate 10,000 rows from database query
data = MyApp.Repo.all(User)
|> Enum.map(fn user ->
  [user.name, user.email, user.created_at, user.status]
end)

Builder.create()
|> Builder.add_sheet("Users")
|> Builder.add_rows([
  [
    {"Name", format: [:bold], width: 20},
    {"Email", format: [:bold], width: 30},
    {"Created", format: [:bold], width: 15},
    {"Status", format: [:bold], width: 10}
  ]
])
|> Builder.add_rows(data)
|> Builder.write_file("users_export.xlsx")

Styled Report

Builder.create()
|> Builder.add_sheet("Status Report")
|> Builder.add_rows([
  [{"Project Status Dashboard", format: [:bold, {:font_size, 18}, {:font_color, "#0066CC"}]}]
])
|> Builder.skip_rows(2)
|> Builder.add_rows([
  [
    {"Status", format: [:bold, {:bg_color, "#EEEEEE"}]},
    {"Count", format: [:bold, {:bg_color, "#EEEEEE"}]},
    {"Percentage", format: [:bold, {:bg_color, "#EEEEEE"}]}
  ],
  [
    {"Completed", format: [{:font_color, "#00AA00"}]},
    42,
    {0.52, format: [{:num_format, "0.0%"}]}
  ],
  [
    {"In Progress", format: [{:font_color, "#FF9900"}]},
    28,
    {0.35, format: [{:num_format, "0.0%"}]}
  ],
  [
    {"Pending", format: [{:font_color, "#0066CC"}]},
    10,
    {0.13, format: [{:num_format, "0.0%"}]}
  ]
])
|> Builder.skip_rows(1)
|> Builder.add_rows([
  [
    {"Total", format: [:bold, {:bg_color, "#FFFF99"}]},
    {80, format: [:bold]},
    {1.0, format: [:bold, {:num_format, "0.0%"}]}
  ]
])
|> Builder.write_file("status_report.xlsx")

Positioned Data

# Create dashboard-style layout with data in specific positions
Builder.create()
|> Builder.add_sheet("Dashboard")
# Top-left section
|> Builder.add_rows([
  [{"Sales Metrics", format: [:bold, {:font_size, 14}]}]
], start_row: 0, start_col: 0)
|> Builder.add_rows([
  [{"Metric", format: [:bold]}, {"Value", format: [:bold]}],
  ["Daily Sales", 15000],
  ["Monthly Sales", 450000]
], start_row: 2, start_col: 0)
# Top-right section
|> Builder.add_rows([
  [{"Team Performance", format: [:bold, {:font_size, 14}]}]
], start_row: 0, start_col: 5)
|> Builder.add_rows([
  [{"Team", format: [:bold]}, {"Target", format: [:bold]}],
  ["East", {125, format: [{:num_format, "0%"}]}],
  ["West", {98, format: [{:num_format, "0%"}]}]
], start_row: 2, start_col: 5)
|> Builder.write_file("dashboard.xlsx")

Performance Tips

Batch your rows: Add multiple rows in one add_rows/3 call rather than multiple single-row calls
Use simple values: Plain values are faster than formatted tuples
Minimize format changes: Group cells with similar formatting together
Large datasets: The Builder handles 10,000+ rows efficiently

Common Patterns

Headers with Data

# Pattern: Bold headers + regular data
Builder.add_rows([
  [{"Col1", format: [:bold]}, {"Col2", format: [:bold]}],
  ["Data1", "Data2"],
  ["Data3", "Data4"]
])

Sections with Spacing

# Pattern: Section header + spacing + data + spacing
builder
|> Builder.add_rows([[{"Section 1", format: [:bold]}]])
|> Builder.skip_rows(1)
|> Builder.add_rows(section1_data)
|> Builder.skip_rows(2)
|> Builder.add_rows([[{"Section 2", format: [:bold]}]])
|> Builder.skip_rows(1)
|> Builder.add_rows(section2_data)

Totals Row

# Pattern: Data + blank row + totals
builder
|> Builder.add_rows(data_rows)
|> Builder.skip_rows(1)
|> Builder.add_rows([
  [{"Total", format: [:bold]}, {sum, format: [:bold, {:num_format, "#,##0"}]}]
])

Error Handling

# write_binary/1 returns result tuple
case Builder.write_binary(builder) do
  {:ok, content} ->
    File.write!("output.xlsx", content)
    :ok

  {:error, reason} ->
    Logger.error("Failed to generate XLSX: #{reason}")
    {:error, reason}
end

# write_file/2 also returns result
case Builder.write_file(builder, path) do
  :ok ->
    IO.puts("✓ File created: #{path}")

  {:error, reason} ->
    IO.puts("✗ Error: #{reason}")
end

Common errors:

"No sheets added" - Call add_sheet/2 before adding rows
"No active sheet" - Add at least one sheet with add_sheet/2

Demo Scripts

Run the included demo to see all features in action:

mix run examples/builder_demo.exs

This generates 5 example files demonstrating:

Simple reports with automatic positioning
Multi-sheet workbooks with formatting
Large dataset generation (1000 rows)
Complex formatting with colors and styles
Positioned data with explicit coordinates

Comparison: Builder vs Low-Level

Builder API

Builder.create()
|> Builder.add_sheet("Data")
|> Builder.add_rows([
  [{"Name", format: [:bold], width: 20}, {"Age", format: [:bold]}],
  ["Alice", 30],
  ["Bob", 25]
])
|> Builder.write_file("output.xlsx")

Low-Level API

sheet = XlsxWriter.new_sheet("Data")
  |> XlsxWriter.set_column_width(0, 20)
  |> XlsxWriter.write(0, 0, "Name", format: [:bold])
  |> XlsxWriter.write(0, 1, "Age", format: [:bold])
  |> XlsxWriter.write(1, 0, "Alice")
  |> XlsxWriter.write(1, 1, 30)
  |> XlsxWriter.write(2, 0, "Bob")
  |> XlsxWriter.write(2, 1, 25)

{:ok, content} = XlsxWriter.generate([sheet])
File.write!("output.xlsx", content)

The Builder API is more concise for sequential data!

Limitations

The Builder API currently does not support:

Formulas (use low-level API)
Images (use low-level API)
Merged cells (use low-level API)
Freeze panes (use low-level API)
Autofilter (use low-level API)
Hide rows/columns (use low-level API)
Row height control (use low-level API)

For these features, use the low-level XlsxWriter API or consider mixing both:

# Use Builder for bulk data
builder = Builder.create()
|> Builder.add_sheet("Data")
|> Builder.add_rows(lots_of_data)

# Get the generated sheets
{:ok, binary} = Builder.write_binary(builder)

# Or build manually with low-level API for advanced features
sheet = XlsxWriter.new_sheet("Advanced")
  |> XlsxWriter.write_formula(0, 0, "=SUM(A1:A10)")
  |> XlsxWriter.freeze_panes(1, 0)

{:ok, content} = XlsxWriter.generate([sheet])

Future Enhancements

Potential additions being considered:

skip_cols/2 - Horizontal cursor movement
move_to/2 - Absolute cursor positioning
Sheet-level options (freeze panes, autofilter)
add_row/2 - Single row variant
Template-based generation
Conditional formatting helpers

Feedback welcome! This API is experimental and we want your input.

FilesExpand file tree

builder_api.md

Latest commit

History

builder_api.md

File metadata and controls

Builder API Guide

When to Use the Builder API

Quick Start

Core Concepts

1. Builder State

2. Automatic Cursor Tracking

3. Format Options

API Reference

create/0

add_sheet/2

add_rows/3

skip_rows/2

write_binary/1

write_file/2

Format Options

Text Styles

Fonts

Colors and Alignment

Number Formatting

Borders

Column Width (Builder-Specific)

Examples

Basic Report

Multi-Sheet Workbook

Large Dataset Export

Styled Report

Positioned Data

Performance Tips

Common Patterns

Headers with Data

Sections with Spacing

Totals Row

Error Handling

Demo Scripts

Comparison: Builder vs Low-Level

Builder API

Low-Level API

Limitations

Future Enhancements

`create/0`

`add_sheet/2`

`add_rows/3`

`skip_rows/2`

`write_binary/1`

`write_file/2`