XlsxWriter (xlsx_writer v0.7.5)

A high-performance library for creating Excel xlsx files in Elixir.

Built with the powerful rust_xlsxwriter crate via Rustler NIF for excellent speed and memory efficiency. Supports rich formatting, formulas, images, and advanced layout features.

Quick Start

sheet = XlsxWriter.new_sheet("My Sheet")
  |> XlsxWriter.write(0, 0, "Hello", format: [:bold])
  |> XlsxWriter.write(0, 1, "World")

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

Key Features

Data Types: Strings, numbers, dates, booleans, URLs, formulas, images
Rich Formatting: Fonts, colors, borders, alignment, number formats
Layout Control: Freeze panes, merged cells, autofilters, hide rows/columns
High Performance: Rust-powered NIF for fast generation of large spreadsheets

Guides

Getting Started - Basic usage and data types
Builder API - High-level API for quick data export (⚠️ Experimental)
Advanced Formatting - Fonts, colors, borders, and number formats
Layout Features - Freeze panes, merged cells, autofilters, and more

API Overview

Core Functions

generate/1 - Generate XLSX binary from sheets
new_sheet/1 - Create a new worksheet

Writing Data

write/5 - Write any value to a cell
write_formula/4 - Write Excel formula
write_boolean/5 - Write boolean value
write_url/5 - Write clickable URL
write_image/4 - Embed image
write_comment/5 - Add comment/note to cell
write_blank/4 - Write formatted blank cell

Layout & Structure

set_column_width/3, set_row_height/3 - Size columns and rows
set_column_range_width/4, set_row_range_height/4 - Size multiple columns/rows at once
freeze_panes/3 - Lock rows/columns when scrolling
merge_range/7 - Combine multiple cells
hide_row/2, hide_column/2 - Hide rows/columns
set_autofilter/5 - Add dropdown filters to headers

See the full documentation for detailed function references.

Summary

Functions

freeze_panes(arg, row, col)

Freezes panes at the specified row and column.

generate(sheets)

Generates an Excel xlsx file from a list of sheets.

hide_column(arg, col)

Hides a specific column in the sheet.

hide_row(arg, row)

Hides a specific row in the sheet.

merge_range(arg, first_row, first_col, last_row, last_col, val, opts \\ [])

Merges a range of cells into a single cell.

new_sheet(name)

Creates a new empty sheet with the given name.

set_autofilter(arg, first_row, first_col, last_row, last_col)

Sets an autofilter on a range of cells.

set_column_range_width(arg, first_col, last_col, width)

Sets the width for a range of columns in the sheet.

set_column_width(arg, col, width)

Sets the width of a specific column in the sheet.

set_row_height(arg, row, height)

Sets the height of a specific row in the sheet.

set_row_range_height(arg, first_row, last_row, height)

Sets the height for a range of rows in the sheet.

write(arg, row, col, val, opts \\ [])

Writes a value to a specific cell in the sheet.

write_blank(arg, row, col, opts \\ [])

Writes a blank cell with formatting to the sheet.

write_boolean(arg, row, col, val, opts \\ [])

Writes a boolean value to a specific cell in the sheet.

write_comment(arg, row, col, text, opts \\ [])

Writes a comment/note to a specific cell in the sheet.

write_formula(arg, row, col, val)

Writes an Excel formula to a specific cell in the sheet.

write_image(arg, row, col, image_binary)

Writes an image to a specific cell in the sheet.

write_rich_string(arg, row, col, segments, opts \\ [])

Writes a rich text string to a specific cell in the sheet.

write_url(arg, row, col, url, opts \\ [])

Writes a URL/hyperlink to a specific cell in the sheet.

Functions

freeze_panes(arg, row, col)

Freezes panes at the specified row and column.

This locks rows and/or columns so they remain visible when scrolling. Very useful for keeping headers visible.

Parameters

sheet - The sheet tuple {name, instructions}
row - The row to freeze at (0-based). Rows above this remain visible.
col - The column to freeze at (0-based). Columns left of this remain visible.

Returns

Updated sheet tuple with the freeze panes instruction.

Examples

# Freeze the first row (header row)
iex> sheet = XlsxWriter.new_sheet("Test")
iex> sheet = XlsxWriter.freeze_panes(sheet, 1, 0)
iex> {"Test", [{:set_freeze_panes, 1, 0}]} = sheet

# Freeze first column
iex> sheet = XlsxWriter.new_sheet("Test")
iex> sheet = XlsxWriter.freeze_panes(sheet, 0, 1)
iex> {"Test", [{:set_freeze_panes, 0, 1}]} = sheet

# Freeze first row and first column
iex> sheet = XlsxWriter.new_sheet("Test")
iex> sheet = XlsxWriter.freeze_panes(sheet, 1, 1)
iex> {"Test", [{:set_freeze_panes, 1, 1}]} = sheet

generate(sheets)

Generates an Excel xlsx file from a list of sheets.

Takes a list of sheet tuples where each tuple contains a sheet name and a list of instructions for that sheet.

Parameters

sheets - A list of {sheet_name, instructions} tuples

Returns

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

Examples

iex> sheet = XlsxWriter.new_sheet("Test")
...>   |> XlsxWriter.write(0, 0, "Hello")
iex> {:ok, xlsx_content} = XlsxWriter.generate([sheet])
iex> is_binary(xlsx_content)
true

hide_column(arg, col)

Hides a specific column in the sheet.

Parameters

sheet - The sheet tuple {name, instructions}
col - The column index to hide (0-based)

Returns

Updated sheet tuple with the hide column instruction.

Examples

iex> sheet = XlsxWriter.new_sheet("Test")
iex> sheet = XlsxWriter.hide_column(sheet, 2)
iex> {"Test", [{:set_column_hidden, 2}]} = sheet

hide_row(arg, row)

Hides a specific row in the sheet.

Parameters

sheet - The sheet tuple {name, instructions}
row - The row index to hide (0-based)

Returns

Updated sheet tuple with the hide row instruction.

Examples

iex> sheet = XlsxWriter.new_sheet("Test")
iex> sheet = XlsxWriter.hide_row(sheet, 5)
iex> {"Test", [{:set_row_hidden, 5}]} = sheet

merge_range(arg, first_row, first_col, last_row, last_col, val, opts \\ [])

Merges a range of cells into a single cell.

The merged cell will contain the specified value and formatting. All merged cells will appear as one cell in Excel.

Parameters

sheet - The sheet tuple {name, instructions}
first_row - The first row of the merge range (0-based)
first_col - The first column of the merge range (0-based)
last_row - The last row of the merge range (0-based)
last_col - The last column of the merge range (0-based)
val - The value to write in the merged cell
opts - Optional keyword list with formatting options

Returns

Updated sheet tuple with the merge range instruction.

Examples

# Merge cells A1:D1 with centered title
iex> sheet = XlsxWriter.new_sheet("Test")
iex> sheet = XlsxWriter.merge_range(sheet, 0, 0, 0, 3, "Title", format: [:bold, {:align, :center}])
iex> {"Test", [{:merge_range, 0, 0, 0, 3, {:string_with_format, "Title", [:bold, {:align, :center}]}}]} = sheet

# Merge cells for a number
iex> sheet = XlsxWriter.new_sheet("Test")
iex> sheet = XlsxWriter.merge_range(sheet, 1, 1, 3, 1, 100)
iex> {"Test", [{:merge_range, 1, 1, 3, 1, {:float, 100}}]} = sheet

new_sheet(name)

Creates a new empty sheet with the given name.

Parameters

name - The name of the sheet (must be a string)

Returns

A sheet tuple {name, []} ready for writing data.

Examples

iex> XlsxWriter.new_sheet("My Sheet")
{"My Sheet", []}

set_autofilter(arg, first_row, first_col, last_row, last_col)

Sets an autofilter on a range of cells.

Adds dropdown filter buttons to the specified range, typically used on header rows.

Parameters

sheet - The sheet tuple {name, instructions}
first_row - The first row of the filter range (0-based)
first_col - The first column of the filter range (0-based)
last_row - The last row of the filter range (0-based)
last_col - The last column of the filter range (0-based)

Returns

Updated sheet tuple with the autofilter instruction.

Examples

# Set autofilter on header row (row 0, columns A-E)
iex> sheet = XlsxWriter.new_sheet("Test")
iex> sheet = XlsxWriter.set_autofilter(sheet, 0, 0, 0, 4)
iex> {"Test", [{:set_autofilter, 0, 0, 0, 4}]} = sheet

set_column_range_width(arg, first_col, last_col, width)

Sets the width for a range of columns in the sheet.

This is a convenience function to set the same width for multiple consecutive columns.

Parameters

sheet - The sheet tuple {name, instructions}
first_col - The first column index (0-based)
last_col - The last column index (0-based, inclusive)
width - The width value in pixels

Returns

Updated sheet tuple with the new column range width instruction.

Examples

iex> sheet = XlsxWriter.new_sheet("Test")
iex> sheet = XlsxWriter.set_column_range_width(sheet, 0, 4, 20)
iex> {"Test", [{:set_column_range_width, 0, 4, 20}]} = sheet

set_column_width(arg, col, width)

Sets the width of a specific column in the sheet.

Parameters

sheet - The sheet tuple {name, instructions}
col - The column index (0-based)
width - The width value (typically a float)

Returns

Updated sheet tuple with the new column width instruction.

Examples

iex> sheet = XlsxWriter.new_sheet("Test")
iex> sheet = XlsxWriter.set_column_width(sheet, 0, 25)
iex> {"Test", [{:set_column_width, 0, 25}]} = sheet

set_row_height(arg, row, height)

Sets the height of a specific row in the sheet.

Parameters

sheet - The sheet tuple {name, instructions}
row - The row index (0-based)
height - The height value (typically a float)

Returns

Updated sheet tuple with the new row height instruction.

Examples

iex> sheet = XlsxWriter.new_sheet("Test")
iex> sheet = XlsxWriter.set_row_height(sheet, 0, 30.0)
iex> {"Test", [{:set_row_height, 0, 30.0}]} = sheet

set_row_range_height(arg, first_row, last_row, height)

Sets the height for a range of rows in the sheet.

This is a convenience function to set the same height for multiple consecutive rows.

Parameters

sheet - The sheet tuple {name, instructions}
first_row - The first row index (0-based)
last_row - The last row index (0-based, inclusive)
height - The height value in pixels

Returns

Updated sheet tuple with the new row range height instruction.

Examples

iex> sheet = XlsxWriter.new_sheet("Test")
iex> sheet = XlsxWriter.set_row_range_height(sheet, 0, 9, 25)
iex> {"Test", [{:set_row_range_height, 0, 9, 25}]} = sheet

write(arg, row, col, val, opts \\ [])

Writes a value to a specific cell in the sheet.

Supports various data types including strings, numbers, dates, and Decimal values. Can also apply formatting options to the cell.

Parameters

sheet - The sheet tuple {name, instructions}
row - The row index (0-based)
col - The column index (0-based)
val - The value to write
opts - Optional keyword list with formatting options

Formatting Options

:format - A list of format specifications:
- :bold - Make text bold
- :italic - Make text italic
- :strikethrough - Strike through text
- :superscript - Superscript text
- :subscript - Subscript text
- {:align, :left | :center | :right} - Text alignment
- {:num_format, format_string} - Custom number format
- {:bg_color, hex_color} - Background color (e.g., "#FFFF00" for yellow)
- {:font_color, hex_color} - Font color (e.g., "#FF0000" for red)
- {:font_size, size} - Font size in points (e.g., 12, 14, 16)
- {:font_name, name} - Font family (e.g., "Arial", "Times New Roman")
- {:underline, :single | :double | :single_accounting | :double_accounting} - Underline style
- {:pattern, :solid | :none | :gray125 | :gray0625} - Fill pattern
- {:border, style} - Apply border to all sides (see border styles below)
- {:border_top, style} - Top border
- {:border_bottom, style} - Bottom border
- {:border_left, style} - Left border
- {:border_right, style} - Right border
- {:border_color, hex_color} - Color for all borders
- {:border_top_color, hex_color} - Top border color
- {:border_bottom_color, hex_color} - Bottom border color
- {:border_left_color, hex_color} - Left border color
- {:border_right_color, hex_color} - Right border color

Border Styles

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

Returns

Updated sheet tuple with the new write instruction.

Examples

iex> sheet = XlsxWriter.new_sheet("Test")
iex> sheet = XlsxWriter.write(sheet, 0, 0, "Hello")
iex> {"Test", [{:write, 0, 0, {:string, "Hello"}}]} = sheet

iex> sheet = XlsxWriter.new_sheet("Test")
iex> sheet = XlsxWriter.write(sheet, 0, 0, "Bold", format: [:bold])
iex> {"Test", [{:write, 0, 0, {:string_with_format, "Bold", [:bold]}}]} = sheet

iex> sheet = XlsxWriter.new_sheet("Test")
iex> sheet = XlsxWriter.write(sheet, 0, 0, "Yellow", format: [{:bg_color, "#FFFF00"}])
iex> {"Test", [{:write, 0, 0, {:string_with_format, "Yellow", [{:bg_color, "#FFFF00"}]}}]} = sheet

iex> sheet = XlsxWriter.new_sheet("Test")
iex> sheet = XlsxWriter.write(sheet, 0, 0, "Red Italic", format: [:italic, {:font_color, "#FF0000"}])
iex> {"Test", [{:write, 0, 0, {:string_with_format, "Red Italic", [:italic, {:font_color, "#FF0000"}]}}]} = sheet

iex> sheet = XlsxWriter.new_sheet("Test")
iex> sheet = XlsxWriter.write(sheet, 0, 0, "Bordered", format: [{:border, :thin}])
iex> {"Test", [{:write, 0, 0, {:string_with_format, "Bordered", [{:border, :thin}]}}]} = sheet

write_blank(arg, row, col, opts \\ [])

Writes a blank cell with formatting to the sheet.

A blank cell differs from an empty cell - it has no data but can have formatting. This is useful for pre-formatting cells before data is added.

Parameters

sheet - The sheet tuple {name, instructions}
row - The row index (0-based)
col - The column index (0-based)
opts - Keyword list with :format specifications

Returns

Updated sheet tuple with the new blank cell instruction.

Examples

iex> sheet = XlsxWriter.new_sheet("Test")
iex> sheet = XlsxWriter.write_blank(sheet, 0, 0, format: [:bold, {:bg_color, "#FFFF00"}])
iex> {"Test", [{:write, 0, 0, {:blank, [:bold, {:bg_color, "#FFFF00"}]}}]} = sheet

write_boolean(arg, row, col, val, opts \\ [])

Writes a boolean value to a specific cell in the sheet.

Parameters

sheet - The sheet tuple {name, instructions}
row - The row index (0-based)
col - The column index (0-based)
val - The boolean value (true or false)
opts - Optional keyword list with formatting options

Returns

Updated sheet tuple with the new boolean instruction.

Examples

iex> sheet = XlsxWriter.new_sheet("Test")
iex> sheet = XlsxWriter.write_boolean(sheet, 0, 0, true)
iex> {"Test", [{:write, 0, 0, {:boolean, true}}]} = sheet

iex> sheet = XlsxWriter.new_sheet("Test")
iex> sheet = XlsxWriter.write_boolean(sheet, 0, 0, false, format: [:bold])
iex> {"Test", [{:write, 0, 0, {:boolean_with_format, false, [:bold]}}]} = sheet

write_comment(arg, row, col, text, opts \\ [])

Writes a comment/note to a specific cell in the sheet.

Comments appear when hovering over a cell and are useful for documentation, instructions, or additional context about cell values.

Parameters

sheet - The sheet tuple {name, instructions}
row - The row index (0-based)
col - The column index (0-based)
text - The comment text content
opts - Optional keyword list:
- :author - Author name (string, max 52 characters)
- :visible - Whether to show the comment by default (boolean, default: false)
- :width - Comment box width in pixels (integer, default: 128)
- :height - Comment box height in pixels (integer, default: 74)

Returns

Updated sheet tuple with the new comment instruction.

Examples

# Simple comment
iex> sheet = XlsxWriter.new_sheet("Test")
iex> sheet = XlsxWriter.write_comment(sheet, 0, 0, "This is a note")
iex> {"Test", [{:insert_note, 0, 0, "This is a note", _}]} = sheet

# Comment with author
iex> sheet = XlsxWriter.new_sheet("Test")
iex> sheet = XlsxWriter.write_comment(sheet, 0, 0, "Review this", author: "John Doe")
iex> {"Test", [{:insert_note, 0, 0, "Review this", %XlsxWriter.NoteOptions{author: "John Doe"}}]} = sheet

# Visible comment with custom size
iex> sheet = XlsxWriter.new_sheet("Test")
iex> sheet = XlsxWriter.write_comment(sheet, 0, 0, "Important!",
...>   visible: true, width: 300, height: 200)
iex> {"Test", [{:insert_note, 0, 0, "Important!", options}]} = sheet
iex> options.visible
true
iex> options.width
300

write_formula(arg, row, col, val)

Writes an Excel formula to a specific cell in the sheet.

Parameters

sheet - The sheet tuple {name, instructions}
row - The row index (0-based)
col - The column index (0-based)
val - The Excel formula string (should start with '=')

Returns

Updated sheet tuple with the new formula instruction.

Examples

iex> sheet = XlsxWriter.new_sheet("Test")
iex> sheet = XlsxWriter.write_formula(sheet, 0, 2, "=A1+B1")
iex> {"Test", [{:write, 0, 2, {:formula, "=A1+B1"}}]} = sheet

write_image(arg, row, col, image_binary)

Writes an image to a specific cell in the sheet.

Parameters

sheet - The sheet tuple {name, instructions}
row - The row index (0-based)
col - The column index (0-based)
image_binary - The binary content of the image file

Returns

Updated sheet tuple with the new image instruction.

Examples

iex> image_data = <<137, 80, 78, 71>>  # Mock PNG header
iex> sheet = XlsxWriter.new_sheet("Test")
iex> sheet = XlsxWriter.write_image(sheet, 0, 0, image_data)
iex> {"Test", [{:write, 0, 0, {:image, ^image_data}}]} = sheet

write_rich_string(arg, row, col, segments, opts \\ [])

Writes a rich text string to a specific cell in the sheet.

A rich string allows different formatting for different parts of the text within a single cell. Each segment consists of text and optional formatting.

Parameters

sheet - The sheet tuple {name, instructions}
row - The row index (0-based)
col - The column index (0-based)
segments - A list of {text, formats} tuples, where:
- text is a string
- formats is a list of format options (can be empty [] for default formatting)
opts - Optional keyword list with:
- :format - Cell-level formatting (alignment, borders, background, etc.)

Segment Format Options

Each segment can have text formatting options:

:bold - Make text bold
:italic - Make text italic
:strikethrough - Strike through text
:superscript - Superscript text
:subscript - Subscript text
{:font_color, hex_color} - Font color (e.g., "#FF0000" for red)
{:font_size, size} - Font size in points
{:font_name, name} - Font family
{:underline, style} - Underline style

Returns

Updated sheet tuple with the new rich string instruction.

Examples

# Simple bold and normal text
iex> sheet = XlsxWriter.new_sheet("Test")
iex> sheet = XlsxWriter.write_rich_string(sheet, 0, 0, [
...>   {"Bold ", [:bold]},
...>   {"Normal", []}
...> ])
iex> {"Test", [{:write, 0, 0, {:rich_string, [{"Bold ", [:bold]}, {"Normal", []}]}}]} = sheet

# Colored text segments
iex> sheet = XlsxWriter.new_sheet("Test")
iex> sheet = XlsxWriter.write_rich_string(sheet, 0, 0, [
...>   {"Red ", [{:font_color, "#FF0000"}]},
...>   {"Blue", [{:font_color, "#0000FF"}]}
...> ])
iex> {"Test", [{:write, 0, 0, {:rich_string, [{"Red ", [{:font_color, "#FF0000"}]}, {"Blue", [{:font_color, "#0000FF"}]}]}}]} = sheet

# With cell-level formatting (centered)
iex> sheet = XlsxWriter.new_sheet("Test")
iex> sheet = XlsxWriter.write_rich_string(sheet, 0, 0, [
...>   {"Bold ", [:bold]},
...>   {"Italic", [:italic]}
...> ], format: [{:align, :center}])
iex> {"Test", [{:write, 0, 0, {:rich_string_with_format, [{"Bold ", [:bold]}, {"Italic", [:italic]}], [{:align, :center}]}}]} = sheet

write_url(arg, row, col, url, opts \\ [])

Writes a URL/hyperlink to a specific cell in the sheet.

Parameters

sheet - The sheet tuple {name, instructions}
row - The row index (0-based)
col - The column index (0-based)
url - The URL string
opts - Optional keyword list with:
- :text - Display text (different from URL)
- :format - Format specifications

Returns

Updated sheet tuple with the new URL instruction.

Examples

iex> sheet = XlsxWriter.new_sheet("Test")
iex> sheet = XlsxWriter.write_url(sheet, 0, 0, "https://example.com")
iex> {"Test", [{:write, 0, 0, {:url, "https://example.com"}}]} = sheet

iex> sheet = XlsxWriter.new_sheet("Test")
iex> sheet = XlsxWriter.write_url(sheet, 0, 0, "https://example.com", text: "Click here")
iex> {"Test", [{:write, 0, 0, {:url_with_text, "https://example.com", "Click here"}}]} = sheet