XlsxWriter (xlsx_writer v0.8.3)

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

autofit(arg)

Enables autofit for all columns in the sheet.

freeze_panes(arg, row, col)

Freezes panes at the specified row and column.

generate(sheets, opts \\ [])

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.

set_tab_color(arg, color)

Sets the color of the worksheet tab.

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, opts \\ [])

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

autofit(arg)

Enables autofit for all columns in the sheet.

Automatically adjusts column widths to fit the longest content in each column. This is applied after all data is written. Note that explicit column widths set via set_column_width/3 will take precedence.

Parameters

sheet - The sheet tuple {name, instructions}

Returns

Updated sheet tuple with the autofit instruction.

Examples

iex> sheet = XlsxWriter.new_sheet("Test")
iex> sheet = XlsxWriter.autofit(sheet)
iex> {"Test", [:set_autofit]} = sheet

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, opts \\ [])

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
opts - Optional keyword list:
- :properties - A %XlsxWriter.WorkbookProperties{} struct with document metadata

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

# With document properties
props = %XlsxWriter.WorkbookProperties{author: "John", title: "Report"}
{:ok, content} = XlsxWriter.generate([sheet], properties: props)

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

set_tab_color(arg, color)

Sets the color of the worksheet tab.

This changes the color of the sheet tab at the bottom of the Excel window, useful for visually organizing multi-sheet workbooks.

Parameters

sheet - The sheet tuple {name, instructions}
color - Hex color string (e.g., "#FF0000" for red)

Returns

Updated sheet tuple with the tab color instruction.

Examples

iex> sheet = XlsxWriter.new_sheet("Test")
iex> sheet = XlsxWriter.set_tab_color(sheet, "#FF0000")
iex> {"Test", [{:set_tab_color, "#FF0000"}]} = 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
- :text_wrap - Wrap text within the cell
- {:rotation, angle} - Rotate text (-90 to 90 degrees, or 270 for vertical stacked text)
- :shrink - Shrink text to fit within the cell width
- {:indent, level} - Indent text by the given level (integer)
- {:align, :left | :center | :right} - Horizontal text alignment
- {:valign, :top | :center | :bottom | :justify | :distributed} - Vertical 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, opts \\ [])

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 '=')
opts - Optional keyword list with formatting options

Options

:format - A list of format specifications

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

iex> sheet = XlsxWriter.new_sheet("Test")
iex> sheet = XlsxWriter.write_formula(sheet, 0, 2, "=A1+B1", format: [:bold])
iex> {"Test", [{:write, 0, 2, {:formula_with_format, "=A1+B1", [:bold]}}]} = 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