UmyaSpreadsheet

CIHex.pmHex DocsLicense

UmyaSpreadsheetEx Logo

Everything you ever needed (and probably more) to work with Excel files in Elixir, powered by Rust!

An Elixir NIF wrapper for the amazing umya-spreadsheet Rust library, providing comprehensive Excel file (.xlsx, .xlsm) manipulation capabilities with the performance benefits of Rust.

Features

Version Information

This package is built on:

Documentation

UmyaSpreadsheet has comprehensive guides for all major features:

Online Documentation

Reference Documentation

Installation

You can install using igniter for the most comfortable experience:

# install igniter if you haven't already
mix archive.install hex igniter_new
# then install umya_spreadsheet_ex
mix igniter.install umya_spreadsheet_ex

Or add umya_spreadsheet_ex to your list of dependencies in mix.exs:

def deps do
  [
    {:umya_spreadsheet_ex, "~> 0.7.0"}
  ]
end

The package includes precompiled NIF files for common platforms, but will compile from source if needed.

To force NIF compilation, set the UMYA_SPREADSHEET_BUILD environment variable to true:

export UMYA_SPREADSHEET_BUILD=true

mix clean;
mix compile;

Quick Start Guide

# Create a new spreadsheet
{:ok, spreadsheet} = UmyaSpreadsheet.new()

# Write data to cells
:ok = UmyaSpreadsheet.set_cell_value(spreadsheet, "Sheet1", "A1", "Hello")
:ok = UmyaSpreadsheet.set_cell_value(spreadsheet, "Sheet1", "B1", "World")
:ok = UmyaSpreadsheet.set_cell_value(spreadsheet, "Sheet1", "C1", "42")

# Format your data
:ok = UmyaSpreadsheet.set_background_color(spreadsheet, "Sheet1", "A1", "FF0000") # Red
:ok = UmyaSpreadsheet.set_font_bold(spreadsheet, "Sheet1", "A1", true)
:ok = UmyaSpreadsheet.set_number_format(spreadsheet, "Sheet1", "C1", "#,##0.00")

# Save your spreadsheet
:ok = UmyaSpreadsheet.write(spreadsheet, "hello_world.xlsx")

# Read it back
{:ok, loaded_spreadsheet} = UmyaSpreadsheet.read("hello_world.xlsx")
{:ok, value} = UmyaSpreadsheet.get_cell_value(loaded_spreadsheet, "Sheet1", "A1")
# => {:ok, "Hello"}

Documentation and Guides

For more detailed examples and complete API documentation, visit: https://hexdocs.pm/umya_spreadsheet_ex

We provide detailed guides for specific features:

You can find all guides in our Guide Index.

Complete Usage Examples

Styling and Formatting

# Create a styled spreadsheet
{:ok, spreadsheet} = UmyaSpreadsheet.new()

# Apply various styling options
:ok = UmyaSpreadsheet.set_cell_value(spreadsheet, "Sheet1", "A1", "Styled Title")
:ok = UmyaSpreadsheet.set_font_size(spreadsheet, "Sheet1", "A1", 16)
:ok = UmyaSpreadsheet.set_font_bold(spreadsheet, "Sheet1", "A1", true)
:ok = UmyaSpreadsheet.set_background_color(spreadsheet, "Sheet1", "A1", "CCFFCC") # Light green

# Add numbers with specific formats
:ok = UmyaSpreadsheet.set_cell_value(spreadsheet, "Sheet1", "B3", "1234.56")
:ok = UmyaSpreadsheet.set_number_format(spreadsheet, "Sheet1", "B3", "#,##0.00")

:ok = UmyaSpreadsheet.set_cell_value(spreadsheet, "Sheet1", "B4", "0.42")
:ok = UmyaSpreadsheet.set_number_format(spreadsheet, "Sheet1", "B4", "0.00%")

# Currency formatting
:ok = UmyaSpreadsheet.set_cell_value(spreadsheet, "Sheet1", "B5", "9999.99")
:ok = UmyaSpreadsheet.set_number_format(spreadsheet, "Sheet1", "B5", "$#,##0.00")

# Date formatting
:ok = UmyaSpreadsheet.set_cell_value(spreadsheet, "Sheet1", "B6", "44500") # Excel date serial
:ok = UmyaSpreadsheet.set_number_format(spreadsheet, "Sheet1", "B6", "yyyy-mm-dd")

# Save the spreadsheet
:ok = UmyaSpreadsheet.write(spreadsheet, "formatted_spreadsheet.xlsx")

Rich Text Formatting

{:ok, spreadsheet} = UmyaSpreadsheet.new()

# Create a rich text object
rich_text = UmyaSpreadsheet.RichText.create()

# Add formatted text with different styles
:ok = UmyaSpreadsheet.RichText.add_formatted_text(rich_text, "Bold text", %{bold: true})
:ok = UmyaSpreadsheet.RichText.add_formatted_text(rich_text, " and ", %{})
:ok = UmyaSpreadsheet.RichText.add_formatted_text(rich_text, "italic text", %{italic: true})
:ok = UmyaSpreadsheet.RichText.add_formatted_text(rich_text, " with ", %{})
:ok = UmyaSpreadsheet.RichText.add_formatted_text(rich_text, "colored text", %{color: "#FF0000"})

# Set the rich text to a cell
:ok = UmyaSpreadsheet.RichText.set_cell_rich_text(spreadsheet, "Sheet1", "A1", rich_text)

# Alternative: Create rich text from HTML
html_rich_text = UmyaSpreadsheet.RichText.create_from_html("<b>Bold</b> and <i>italic</i> text")
:ok = UmyaSpreadsheet.RichText.set_cell_rich_text(spreadsheet, "Sheet1", "A2", html_rich_text)

# Create individual text elements for more control
element1 = UmyaSpreadsheet.RichText.create_text_element("Large text", %{size: 18, bold: true})
element2 = UmyaSpreadsheet.RichText.create_text_element(" and small text", %{size: 10})

# Add elements to rich text
rich_text2 = UmyaSpreadsheet.RichText.create()
:ok = UmyaSpreadsheet.RichText.add_text_element(rich_text2, element1)
:ok = UmyaSpreadsheet.RichText.add_text_element(rich_text2, element2)
:ok = UmyaSpreadsheet.RichText.set_cell_rich_text(spreadsheet, "Sheet1", "A3", rich_text2)

# Generate HTML from rich text
html_output = UmyaSpreadsheet.RichText.to_html(rich_text)
# => "<b>Bold text</b> and <i>italic text</i> with <span style=\"color:#FF0000\">colored text</span>"

# Get font properties from text elements
{:ok, props} = UmyaSpreadsheet.RichText.get_element_font_properties(element1)
# props[:bold] => "true"
# props[:size] => "18"

:ok = UmyaSpreadsheet.write(spreadsheet, "rich_text_example.xlsx")

Sheet Operations

{:ok, spreadsheet} = UmyaSpreadsheet.new()

# Add a new sheet
:ok = UmyaSpreadsheet.add_sheet(spreadsheet, "Data")

# Clone a sheet
:ok = UmyaSpreadsheet.clone_sheet(spreadsheet, "Sheet1", "Sheet1 Copy")

# Get all sheet names
sheet_names = UmyaSpreadsheet.get_sheet_names(spreadsheet)
# => ["Sheet1", "Data", "Sheet1 Copy"]

# Hide a sheet
:ok = UmyaSpreadsheet.set_sheet_state(spreadsheet, "Data", "hidden")

# Remove a sheet
:ok = UmyaSpreadsheet.remove_sheet(spreadsheet, "Sheet1 Copy")

# Turn off grid lines
:ok = UmyaSpreadsheet.set_show_grid_lines(spreadsheet, "Sheet1", false)

Working with Charts

{:ok, spreadsheet} = UmyaSpreadsheet.new()

# Prepare some data for the chart
:ok = UmyaSpreadsheet.set_cell_value(spreadsheet, "Sheet1", "A1", "Month")
:ok = UmyaSpreadsheet.set_cell_value(spreadsheet, "Sheet1", "B1", "Sales")
:ok = UmyaSpreadsheet.set_cell_value(spreadsheet, "Sheet1", "A2", "January")
:ok = UmyaSpreadsheet.set_cell_value(spreadsheet, "Sheet1", "B2", "1200")
:ok = UmyaSpreadsheet.set_cell_value(spreadsheet, "Sheet1", "A3", "February")
:ok = UmyaSpreadsheet.set_cell_value(spreadsheet, "Sheet1", "B3", "1500")
:ok = UmyaSpreadsheet.set_cell_value(spreadsheet, "Sheet1", "A4", "March")
:ok = UmyaSpreadsheet.set_cell_value(spreadsheet, "Sheet1", "B4", "2200")

# Define data series for the chart
data_series = ["Sheet1!$B$2:$B$4"]
categories = "Sheet1!$A$2:$A$4"
title = "Quarterly Sales"

# Add a column chart
:ok = UmyaSpreadsheet.add_chart(
  spreadsheet,
  "Sheet1",
  "ColumnChart",  # Other options: LineChart, PieChart, BarChart
  "D2",           # Top-left position of chart
  "J10",          # Bottom-right position of chart
  title,          # Chart title
  data_series,    # Data series
  categories      # Categories (optional)
)

Export to CSV

# Read an existing spreadsheet
{:ok, spreadsheet} = UmyaSpreadsheet.read("sales_report.xlsx")

# Export just one sheet to CSV
:ok = UmyaSpreadsheet.write_csv(spreadsheet, "Sheet1", "sheet1_data.csv")

Data Validation

{:ok, spreadsheet} = UmyaSpreadsheet.new()

# Add a dropdown list to cells
:ok = UmyaSpreadsheet.add_list_validation(
  spreadsheet,
  "Sheet1",
  "A1:A10",
  ["Option 1", "Option 2", "Option 3"],
  true,  # Allow blank values
  "Invalid Selection",  # Error title
  "Please select from the list",  # Error message
  "Selection Required",  # Prompt title
  "Choose an option from the dropdown"  # Prompt message
)

# Add number validation (between 1 and 100)
:ok = UmyaSpreadsheet.add_number_validation(
  spreadsheet,
  "Sheet1",
  "B1:B10",
  "between",  # Operator: between, greaterThan, lessThan, etc.
  1.0,  # Minimum value
  100.0,  # Maximum value
  true,  # Allow blank values
  "Invalid Number",  # Error title
  "Please enter a number between 1 and 100"  # Error message
)

# Add date validation (future dates only)
# Option 1: Using Date struct directly
:ok = UmyaSpreadsheet.add_date_validation(
  spreadsheet,
  "Sheet1",
  "C1:C10",
  "greaterThan",  # Operator
  Date.utc_today(),  # Compare date as Date struct
  nil,  # Second date (for between operator)
  true,  # Allow blank values
  "Invalid Date",  # Error title
  "Please enter a future date"  # Error message
)

# Option 2: Using ISO string format
today_string = Date.utc_today() |> Date.to_iso8601()
:ok = UmyaSpreadsheet.add_date_validation(
  spreadsheet,
  "Sheet1",
  "D1:D10",
  "greaterThan",
  today_string,  # Compare date as string
  nil,
  true,
  "Invalid Date",
  "Please enter a future date"
)

# Add text length validation (max 10 characters)
:ok = UmyaSpreadsheet.add_text_length_validation(
  spreadsheet,
  "Sheet1",
  "D1:D10",
  "lessThanOrEqual",  # Operator
  10,  # Character limit
  nil,  # Second value (for between operator)
  true  # Allow blank values
)

# Remove validation from a range
:ok = UmyaSpreadsheet.remove_data_validation(
  spreadsheet,
  "Sheet1",
  "A5:A10"
)

Improved Memory Usage with Light Writers

# Create a large spreadsheet with many sheets
{:ok, spreadsheet} = UmyaSpreadsheet.new()

# [... add lots of data ...]

# Use the light writer for better memory efficiency
:ok = UmyaSpreadsheet.write_light(spreadsheet, "large_file.xlsx")

# Or with password protection
:ok = UmyaSpreadsheet.write_with_password_light(spreadsheet, "secure_large_file.xlsx", "password123")

Working with Pivot Tables

{:ok, spreadsheet} = UmyaSpreadsheet.new()

# Create some sample data for the pivot table
:ok = UmyaSpreadsheet.set_cell_value(spreadsheet, "Sheet1", "A1", "Region")
:ok = UmyaSpreadsheet.set_cell_value(spreadsheet, "Sheet1", "B1", "Product")
:ok = UmyaSpreadsheet.set_cell_value(spreadsheet, "Sheet1", "C1", "Sales")

:ok = UmyaSpreadsheet.set_cell_value(spreadsheet, "Sheet1", "A2", "North")
:ok = UmyaSpreadsheet.set_cell_value(spreadsheet, "Sheet1", "B2", "Apples")
:ok = UmyaSpreadsheet.set_cell_value(spreadsheet, "Sheet1", "C2", "10000")

:ok = UmyaSpreadsheet.set_cell_value(spreadsheet, "Sheet1", "A3", "North")
:ok = UmyaSpreadsheet.set_cell_value(spreadsheet, "Sheet1", "B3", "Oranges")
:ok = UmyaSpreadsheet.set_cell_value(spreadsheet, "Sheet1", "C3", "8000")

:ok = UmyaSpreadsheet.set_cell_value(spreadsheet, "Sheet1", "A4", "South")
:ok = UmyaSpreadsheet.set_cell_value(spreadsheet, "Sheet1", "B4", "Apples")
:ok = UmyaSpreadsheet.set_cell_value(spreadsheet, "Sheet1", "C4", "12000")

# Add a sheet for the pivot table
:ok = UmyaSpreadsheet.add_sheet(spreadsheet, "Pivot")

# Create a pivot table
:ok = UmyaSpreadsheet.add_pivot_table(
  spreadsheet,                      # Spreadsheet object
  "Pivot",                          # Destination sheet
  "Sales Analysis",                 # Pivot table name
  "Sheet1",                         # Source sheet
  "A1:C4",                          # Source data range
  "A3",                             # Pivot table top-left position
  [0],                              # Row fields (Region - column index 0)
  [1],                              # Column fields (Product - column index 1)
  [{2, "sum", "Total Sales"}]       # Data fields (Sum of Sales)
)

# Check if a sheet has pivot tables
has_pivots = UmyaSpreadsheet.has_pivot_tables?(spreadsheet, "Pivot")
# => true

# Count pivot tables on a sheet
count = UmyaSpreadsheet.count_pivot_tables(spreadsheet, "Pivot")
# => 1

# Refresh all pivot tables
:ok = UmyaSpreadsheet.refresh_all_pivot_tables(spreadsheet)

# Remove a pivot table
:ok = UmyaSpreadsheet.remove_pivot_table(spreadsheet, "Pivot", "Sales Analysis")

Working with Excel Tables

{:ok, spreadsheet} = UmyaSpreadsheet.new()

# Add some sample data for the table
UmyaSpreadsheet.set_cell_value(spreadsheet, "Sheet1", "A1", "Product")
UmyaSpreadsheet.set_cell_value(spreadsheet, "Sheet1", "B1", "Category")
UmyaSpreadsheet.set_cell_value(spreadsheet, "Sheet1", "C1", "Price")
UmyaSpreadsheet.set_cell_value(spreadsheet, "Sheet1", "D1", "Stock")

UmyaSpreadsheet.set_cell_value(spreadsheet, "Sheet1", "A2", "Laptop")
UmyaSpreadsheet.set_cell_value(spreadsheet, "Sheet1", "B2", "Electronics")
UmyaSpreadsheet.set_cell_value(spreadsheet, "Sheet1", "C2", 999.99)
UmyaSpreadsheet.set_cell_value(spreadsheet, "Sheet1", "D2", 50)

UmyaSpreadsheet.set_cell_value(spreadsheet, "Sheet1", "A3", "Mouse")
UmyaSpreadsheet.set_cell_value(spreadsheet, "Sheet1", "B3", "Electronics")
UmyaSpreadsheet.set_cell_value(spreadsheet, "Sheet1", "C3", 29.99)
UmyaSpreadsheet.set_cell_value(spreadsheet, "Sheet1", "D3", 100)

# Create a table with the data
{:ok, :ok} = UmyaSpreadsheet.add_table(
  spreadsheet,
  "Sheet1",
  "ProductTable",
  "Product Inventory",
  "A1",
  "D3",
  ["Product", "Category", "Price", "Stock"],
  true  # Show totals row
)

# Apply a table style
{:ok, :ok} = UmyaSpreadsheet.set_table_style(
  spreadsheet,
  "Sheet1",
  "ProductTable",
  "TableStyleMedium9",
  true,   # Show first column
  false,  # Show last column
  true,   # Show banded rows
  false   # Show banded columns
)

# Add a new column to the table
{:ok, :ok} = UmyaSpreadsheet.add_table_column(
  spreadsheet,
  "Sheet1",
  "ProductTable",
  "Total Value",
  "sum",
  "Grand Total"
)

# Check if sheet has tables
{:ok, true} = UmyaSpreadsheet.has_tables?(spreadsheet, "Sheet1")

# Get all tables from the sheet
{:ok, tables} = UmyaSpreadsheet.get_tables(spreadsheet, "Sheet1")
[table | _] = tables
# table["name"] => "ProductTable"
# table["display_name"] => "Product Inventory"

# Remove the table
{:ok, :ok} = UmyaSpreadsheet.remove_table(spreadsheet, "Sheet1", "ProductTable")

Advanced Features

Row and Column Operations

{:ok, spreadsheet} = UmyaSpreadsheet.new()

# Insert 2 new rows at index 3
:ok = UmyaSpreadsheet.insert_new_row(spreadsheet, "Sheet1", 3, 2)

# Insert 1 new column at column C
:ok = UmyaSpreadsheet.insert_new_column(spreadsheet, "Sheet1", "C", 1)

# Remove row at index 5
:ok = UmyaSpreadsheet.remove_row(spreadsheet, "Sheet1", 5, 1)

# Set column width
:ok = UmyaSpreadsheet.set_column_width(spreadsheet, "Sheet1", "A", 15.5)

# Auto-fit column width based on content
:ok = UmyaSpreadsheet.set_column_auto_width(spreadsheet, "Sheet1", "B", true)

# Set row height
:ok = UmyaSpreadsheet.set_row_height(spreadsheet, "Sheet1", 1, 30.0)

# Apply styling to entire row
:ok = UmyaSpreadsheet.set_row_style(spreadsheet, "Sheet1", 1, "EEEEEE", "000000")

Security and Protection

{:ok, spreadsheet} = UmyaSpreadsheet.new()

# Add data to the spreadsheet
:ok = UmyaSpreadsheet.set_cell_value(spreadsheet, "Sheet1", "A1", "Confidential Data")
:ok = UmyaSpreadsheet.set_cell_value(spreadsheet, "Sheet1", "A2", "Employee Salaries")

# Protect the entire workbook
:ok = UmyaSpreadsheet.set_workbook_protection(spreadsheet, true)

# Protect a specific worksheet
:ok = UmyaSpreadsheet.set_sheet_protection(
  spreadsheet,
  "Sheet1",
  true,
  "This sheet is protected"  # Optional message
)

# Save with password protection
:ok = UmyaSpreadsheet.write_with_password(spreadsheet, "confidential.xlsx", "secret123")

# Apply password to an existing file
:ok = UmyaSpreadsheet.set_password("original.xlsx", "protected_copy.xlsx", "secret123")

Print Settings and Page Setup

{:ok, spreadsheet} = UmyaSpreadsheet.new()

# Add some content to the spreadsheet
:ok = UmyaSpreadsheet.set_cell_value(spreadsheet, "Sheet1", "A1", "Report Title")
:ok = UmyaSpreadsheet.set_font_size(spreadsheet, "Sheet1", "A1", 16)
:ok = UmyaSpreadsheet.set_font_bold(spreadsheet, "Sheet1", "A1", true)

# Configure print settings
# Set to landscape orientation
:ok = UmyaSpreadsheet.set_page_orientation(spreadsheet, "Sheet1", "landscape")

# Set to A4 paper size
:ok = UmyaSpreadsheet.set_paper_size(spreadsheet, "Sheet1", 9)

# Set page margins (in inches)
:ok = UmyaSpreadsheet.set_page_margins(spreadsheet, "Sheet1", 1.0, 0.75, 1.0, 0.75)

# Set header and footer margins
:ok = UmyaSpreadsheet.set_header_footer_margins(spreadsheet, "Sheet1", 0.5, 0.5)

# Add a custom header
:ok = UmyaSpreadsheet.set_header(spreadsheet, "Sheet1", "&C&\"Arial,Bold\"Confidential Report")

# Add a footer with page numbers
:ok = UmyaSpreadsheet.set_footer(spreadsheet, "Sheet1", "&RPage &P of &N")

# Define a specific print area
:ok = UmyaSpreadsheet.set_print_area(spreadsheet, "Sheet1", "A1:H20")

# Set rows 1-2 to repeat at the top of each printed page
:ok = UmyaSpreadsheet.set_print_titles(spreadsheet, "Sheet1", "1:2", "")

# Center the printout horizontally on the page
:ok = UmyaSpreadsheet.set_print_centered(spreadsheet, "Sheet1", true, false)

# Save the spreadsheet
:ok = UmyaSpreadsheet.write(spreadsheet, "print_ready_report.xlsx")

Working with Images

{:ok, spreadsheet} = UmyaSpreadsheet.new()

# Add an image to cell A1
:ok = UmyaSpreadsheet.add_image(spreadsheet, "Sheet1", "A1", "path/to/logo.png")

# When reading a spreadsheet with images, you can download them
{:ok, spreadsheet} = UmyaSpreadsheet.read("report_with_images.xlsx")
:ok = UmyaSpreadsheet.download_image(spreadsheet, "Sheet1", "A1", "downloaded_image.png")

# Replace an existing image
:ok = UmyaSpreadsheet.change_image(spreadsheet, "Sheet1", "A1", "path/to/new_logo.png")

Working with Drawing Objects

{:ok, spreadsheet} = UmyaSpreadsheet.new()

# Add a shape
:ok = UmyaSpreadsheet.add_shape(spreadsheet, "Sheet1", "D3", "rectangle", 200, 100, "blue", "black", 1.0)

# Add a text box
:ok = UmyaSpreadsheet.add_text_box(spreadsheet, "Sheet1", "D5", "Important Note", 200, 100, "yellow", "black", "gray", 1.0)

# Add a connector between cells
:ok = UmyaSpreadsheet.add_connector(spreadsheet, "Sheet1", "A1", "D3", "green", 1.5)

Testing and Development

The library includes comprehensive test cases. To run them:

mix test

Test files are created in the test/result_files directory and are automatically ignored by git.

For more details on development, check out the DEVELOPMENT.md file.

Advanced File Format Options

{:ok, spreadsheet} = UmyaSpreadsheet.new()
# Add data to the spreadsheet...

# Control the compression level (0-9)
:ok = UmyaSpreadsheet.write_with_compression(spreadsheet, "optimized.xlsx", 8)

# Enhanced encryption with AES256
:ok = UmyaSpreadsheet.write_with_encryption_options(
  spreadsheet,
  "secure.xlsx",
  "myPassword",
  "AES256",        # Algorithm
  "customSaltValue", # Optional salt value
  100000           # Optional spin count
)

# Generate binary XLSX for web responses
xlsx_binary = UmyaSpreadsheet.to_binary_xlsx(spreadsheet)

# In a Phoenix controller:
conn
|> put_resp_content_type("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
|> put_resp_header("content-disposition", ~s[attachment; filename="report.xlsx"])
|> send_resp(200, xlsx_binary)

Performance Considerations

License

UmyaSpreadsheetEx is available under the MIT License. See the LICENSE file for more info.

Acknowledgements

This library is a wrapper around the excellent umya-spreadsheet Rust library.