xlsx

The core package for reading and writing Microsoft Excel (XLSX) files. This package contains all the main types and functionality for manipulating Excel workbooks.

Core Types

Workbook

Workbook is the central container that owns all sheets and global state:

Sheets: sheets : Array[Worksheet], chart_sheets : Array[ChartSheet]
Styles: styles : Array[Style], conditional_styles : Array[Style]
Defined names: defined_names : Array[DefinedName]
Document properties: core_properties, app_properties, custom_properties
Protection: workbook_protection

Worksheet

Worksheet represents a single sheet and contains:

Cells: cells : Array[Cell] with row/column coordinates
Merged cells: merged_cells : Array[String]
Features: tables, charts, images, data validations, conditional formats, etc.
Layout: page margins, page layout, header/footer
Protection: sheet_protection

Cell

Cell stores cell data:

reference : String - Cell reference like "A1"
row : Int, col : Int - 1-indexed coordinates
value : String - Cell value as string
value_type : CellValueType - String, Number, Bool, or Error
formula : String? - Optional formula
style_id : Int - Index into workbook styles

Style

Style defines cell formatting:

font : Font? - Font styling
fill : Fill? - Background fill
border : Array[Border]? - Cell borders
alignment : Alignment? - Text alignment
number_format : NumberFormat? - Number formatting
protection : Protection? - Cell protection

Basic Usage

Creating Workbooks

///|
test "create workbook" {
  // Empty workbook
  let wb = @xlsx.Workbook::new()
  inspect(wb.sheets().length(), content="0")

  // Add a sheet
  ignore(wb.add_sheet("Data"))
  debug_inspect(wb.get_sheet_list(), content="[\"Data\"]")
}

Cell Operations

///|
test "cell operations" {
  let wb = @xlsx.Workbook::new()
  let sheet = wb.add_sheet("Test")

  // Set cell by reference
  sheet.set_cell("A1", "Hello")
  sheet.set_cell("B1", "World")

  // Set cell by row/column (1-indexed)
  sheet.set_cell_rc(2, 1, "Row 2, Col 1")

  // Read cells
  debug_inspect(sheet.get_cell("A1"), content="Some(\"Hello\")")
  debug_inspect(sheet.get_cell_rc(2, 1), content="Some(\"Row 2, Col 1\")")

  // Set typed values
  sheet.set_cell_value("C1", Numeric(42.5))
  sheet.set_cell_value("D1", Bool(true))
  debug_inspect(sheet.get_cell_value_raw("C1"), content="Some(Numeric(42.5))")
}

Row and Column Operations

///|
test "row and column operations" {
  let wb = @xlsx.Workbook::new()
  let sheet = wb.add_sheet("Grid")

  // Set entire row (1-indexed)
  sheet.set_row(1, ["A", "B", "C", "D"])

  // Set entire column (1-indexed)
  sheet.set_col(1, ["1", "2", "3"])

  // Read row/column
  debug_inspect(sheet.get_row(1), content="[\"1\", \"B\", \"C\", \"D\"]")
  debug_inspect(sheet.get_col(1), content="[\"1\", \"2\", \"3\"]")

  // Row/column dimensions
  inspect(sheet.max_row(), content="3")
  inspect(sheet.max_col(), content="4")
}

Formulas

///|
test "formulas" {
  let wb = @xlsx.Workbook::new()
  let sheet = wb.add_sheet("Calc")
  sheet.set_cell("A1", "10")
  sheet.set_cell("A2", "20")
  sheet.set_cell("A3", "30")

  // Formula with cached value
  sheet.set_cell_formula("A4", "SUM(A1:A3)", value="60")
  debug_inspect(sheet.get_cell_formula("A4"), content="Some(\"SUM(A1:A3)\")")

  // Calculate formula
  inspect(wb.calc_cell_value("Calc", "A4"), content="60")

  // Array formula
  let opts = @xlsx.FormulaOpts::array("B1:B3")
  sheet.set_cell_formula_opts("B1", "{A1:A3*2}", opts~, value="20")
}

Styling

Creating Styles

///|
test "creating styles" {
  let wb = @xlsx.Workbook::new()
  let sheet = wb.add_sheet("Styled")

  // Create style with font
  let bold_style = @xlsx.Style::font(
    @xlsx.Font::with_values(bold=true, size=14.0),
  )
  let bold_id = wb.add_style(bold_style)

  // Create style with fill
  let yellow_fill = @xlsx.Style::fill(@xlsx.Fill::solid("FFFF00"))
  let fill_id = wb.add_style(yellow_fill)

  // Combine multiple style elements
  let combined = @xlsx.Style::new()
    .with_font(@xlsx.Font::with_values(bold=true, color="FF0000"))
    .with_fill(@xlsx.Fill::solid("E0E0E0"))
    .with_alignment(
      @xlsx.Alignment::with_values(horizontal="center", wrap_text=true),
    )
  let combined_id = wb.add_style(combined)

  // Apply style to cell
  sheet.set_cell("A1", "Bold Text")
  sheet.set_cell_style("A1", bold_id)
  sheet.set_cell("A2", "Filled")
  sheet.set_cell_style("A2", fill_id)
  sheet.set_cell("A3", "Combined")
  sheet.set_cell_style("A3", combined_id)
}

Font Options

///|
test "font options" {
  let font = @xlsx.Font::with_values(
    bold=true,
    italic=true,
    size=12.0,
    color="0000FF", // Blue
    underline="single",
    strike=true,
  )
  debug_inspect(font.bold, content="Some(true)")
  debug_inspect(font.size, content="Some(12)")
}

Fill Options

///|
test "fill options" {
  // Solid fill
  let solid = @xlsx.Fill::solid("FF0000") // Red
  debug_inspect(solid.typ, content="Some(\"pattern\")")

  // Pattern fill
  ignore(@xlsx.Fill::pattern(pattern=17, color="00FF00"))

  // Gradient fill
  ignore(@xlsx.Fill::gradient("FF0000", "0000FF", shading=1))
}

Border Options

///|
test "border options" {
  // Create borders for all sides
  let borders = [
    @xlsx.Border::with_values("left", color="000000", style=1),
    @xlsx.Border::with_values("right", color="000000", style=1),
    @xlsx.Border::with_values("top", color="000000", style=1),
    @xlsx.Border::with_values("bottom", color="000000", style=2), // Thicker bottom
  ]
  let style = @xlsx.Style::border(borders)
  debug_inspect(style.border.map(fn(b) { b.length() }), content="Some(4)")
}

Number Formats

///|
test "number formats" {
  // Built-in number format (index 2 = "0.00")
  ignore(@xlsx.Style::builtin_number_format(2))

  // Custom number format
  ignore(@xlsx.Style::number_format("$#,##0.00"))

  // Percentage
  ignore(@xlsx.Style::builtin_number_format(10)) // "0.00%"
}

Data Validation

///|
test "data validation" {
  let wb = @xlsx.Workbook::new()
  let sheet = wb.add_sheet("Validation")

  // Dropdown list validation
  sheet.add_data_validation_list("A1:A10", ["Option 1", "Option 2", "Option 3"])

  // Range validation with DataValidation struct
  let dv = @xlsx.DataValidation::new(true) // allow_blank=true
  dv.set_sqref("B1:B10")
  dv.set_range(IntValue(1), IntValue(100), Whole, Between)
  dv.set_error(Stop, "Invalid", "Enter 1-100")
  dv.set_input("Hint", "Enter a number between 1 and 100")
  sheet.add_data_validation(dv)
}

Conditional Formatting

///|
test "conditional formatting" {
  let wb = @xlsx.Workbook::new()
  let sheet = wb.add_sheet("CF")

  // Create a style for conditional formatting
  let red_fill = @xlsx.Style::fill(@xlsx.Fill::solid("FF0000"))
  let cf_style_id = wb.new_conditional_style(red_fill)

  // Cell value condition
  let cf = @xlsx.ConditionalFormatOptions::new("cell")
  cf.set_criteria(">")
  cf.set_value("100")
  cf.set_format(Some(cf_style_id))
  sheet.set_conditional_format("A1:A100", [cf])
}

Charts

///|
test "charts" {
  let wb = @xlsx.Workbook::new()
  let sheet = wb.add_sheet("ChartData")

  // Add data
  sheet.set_row(1, ["Category", "Value"])
  sheet.set_row(2, ["A", "10"])
  sheet.set_row(3, ["B", "20"])
  sheet.set_row(4, ["C", "30"])

  // Create chart series
  let series = @xlsx.ChartSeries::new(
    "ChartData!$B$2:$B$4", // values
    "ChartData!$A$2:$A$4", // categories
    name="Sales",
  )

  // Create chart options
  let chart = @xlsx.ChartOptions::new(Bar)
  chart.series.push(series)
  chart.title = "Sales by Category"
  chart.dimension = @xlsx.ChartDimension::with_values(480, 300)

  // Add chart to sheet
  sheet.add_chart_with_options("E1", chart)
}

Tables

///|
test "tables" {
  let wb = @xlsx.Workbook::new()
  let sheet = wb.add_sheet("TableSheet")

  // Add data for table
  sheet.set_row(1, ["Name", "Age", "City"])
  sheet.set_row(2, ["Alice", "30", "NYC"])
  sheet.set_row(3, ["Bob", "25", "LA"])

  // Add table with range reference and column headers
  let table = sheet.add_table(
    "A1:C3", // range reference
    "Table1", // table name
    ["Name", "Age", "City"], // column headers
    display_name="People",
    style_name="TableStyleMedium2",
    show_row_stripes=true,
  )
  inspect(table.name, content="Table1")
}

Merged Cells

///|
test "merged cells" {
  let wb = @xlsx.Workbook::new()
  let sheet = wb.add_sheet("Merge")

  // Set value first
  sheet.set_cell("A1", "Merged Header")

  // Merge cells
  sheet.merge_cells("A1:D1")

  // Get merged ranges
  debug_inspect(sheet.merged_cells().to_owned(), content="[\"A1:D1\"]")

  // Unmerge
  sheet.unmerge_cells("A1:D1")
  debug_inspect(sheet.merged_cells().to_owned(), content="[]")
}

Hyperlinks

///|
test "hyperlinks" {
  let wb = @xlsx.Workbook::new()
  let sheet = wb.add_sheet("Links")

  // External hyperlink
  sheet.set_cell("A1", "Visit Example")
  sheet.set_cell_hyperlink(
    "A1",
    "https://example.com",
    External,
    display="Example Site",
    tooltip="Click to visit",
  )

  // Internal link to another cell
  sheet.set_cell("A2", "Go to Data")
  sheet.set_cell_hyperlink("A2", "Sheet2!A1", Location)

  // Enumerate full typed records, optionally filtered by hyperlink kind.
  inspect(sheet.get_hyperlinks().length(), content="2")
  inspect(sheet.get_hyperlinks(link_type=External).length(), content="1")
}

Mutation rejects empty targets, XML-illegal text, and unsafe or unknown absolute URI schemes. Absolute external targets allow http, https, mailto, ftp, ftps, sftp, news, tel, sms, file, about, and ppaction; forward-slash relative external paths remain supported. A hyperlink set on any cell in a merged range is addressed consistently through the range's top-left anchor for set, get, and remove.

Sheet Operations

///|
test "sheet operations" {
  let wb = @xlsx.Workbook::new()
  ignore(wb.add_sheet("Sheet1"))
  ignore(wb.add_sheet("Sheet2"))
  ignore(wb.add_sheet("Sheet3"))

  // Get sheet by name
  guard wb.sheet("Sheet1") is Some(sheet1) else { return }
  sheet1.set_cell("A1", "OK")

  // Rename sheet
  wb.set_sheet_name("Sheet1", "Data")
  debug_inspect(
    wb.get_sheet_list(),
    content="[\"Data\", \"Sheet2\", \"Sheet3\"]",
  )

  // Hide sheet
  wb.set_sheet_visible("Sheet3", false)
  inspect(wb.get_sheet_visible("Sheet3"), content="false")

  // Delete sheet
  wb.delete_sheet("Sheet2")
  debug_inspect(wb.get_sheet_list(), content="[\"Data\", \"Sheet3\"]")

  // Set active sheet
  wb.set_active_sheet(1)
  inspect(wb.active_sheet_index(), content="1")
}

Row/Column Visibility and Dimensions

///|
test "row column dimensions" {
  let wb = @xlsx.Workbook::new()
  let sheet = wb.add_sheet("Dims")

  // Set row height
  sheet.set_row_height(1, 30.0)
  debug_inspect(sheet.get_row_height(1), content="Some(30)")

  // Set column width
  sheet.set_col_width(1, 20.0)
  debug_inspect(sheet.get_col_width(1), content="Some(20)")

  // Hide row
  sheet.set_row_visible(2, false)
  inspect(sheet.row_visible(2), content="false")

  // Hide column
  sheet.set_col_visible(3, false)
  inspect(sheet.col_visible(3), content="false")
}

Sheet Protection

///|
test "sheet protection" {
  let wb = @xlsx.Workbook::new()
  let sheet = wb.add_sheet("Protected")

  // Protect sheet with options
  let opts = @xlsx.SheetProtectionOptions::with_values(
    password="secret",
    format_cells=false, // Prevent formatting
    insert_rows=false, // Prevent inserting rows
    delete_rows=false, // Prevent deleting rows
  )
  sheet.protect_sheet(opts)

  // Check protection
  debug_inspect(
    sheet.sheet_protection().map(fn(p) { p.sheet }),
    content="Some(true)",
  )

  // Unprotect
  sheet.unprotect_sheet(password="secret")
}

Page Layout

///|
test "page layout" {
  let wb = @xlsx.Workbook::new()
  let sheet = wb.add_sheet("Print")

  // Set page margins
  let margins = @xlsx.PageLayoutMarginsOptions::with_values(
    top=1.0,
    bottom=1.0,
    left=0.75,
    right=0.75,
    header=0.5,
    footer=0.5,
  )
  sheet.set_page_margins(Some(margins))

  // Set page layout
  let layout = @xlsx.PageLayoutOptions::with_values(
    orientation="landscape",
    size=9, // A4
    fit_to_width=1,
    fit_to_height=0, // Auto
  )
  sheet.set_page_layout(Some(layout))

  // Set header/footer
  let hf = @xlsx.HeaderFooterOptions::with_values(
    odd_header="&C&\"Arial,Bold\"Report Title",
    odd_footer="&LPage &P of &N&R&D",
  )
  sheet.set_header_footer(Some(hf))
}

Streaming API

For large files, use the streaming API to write rows in order:

///|
test "stream writer" {
  let wb = @xlsx.Workbook::new()
  ignore(wb.add_sheet("BigData"))

  // Create stream writer
  let sw = wb.new_stream_writer("BigData")

  // Set column widths (must be before writing rows)
  sw.set_col_width(1, 3, 15.0)

  // Write rows in order
  sw.set_row("A1", ["Header1", "Header2", "Header3"])
  sw.set_row("A2", ["Data1", "Data2", "Data3"])
  sw.set_row("A3", ["More", "Data", "Here"])

  // Merge cells
  sw.merge_cell("D1", "F1")

  // Must flush when done
  sw.flush()
}

Reading Workbooks

///|
test "read workbook" {
  // Create and write a workbook
  let wb = @xlsx.Workbook::new()
  let sheet = wb.add_sheet("Test")
  sheet.set_cell("A1", "Hello")
  sheet.set_cell("A2", "World")
  let bytes = @xlsx.write(wb)

  // Read it back
  let loaded = @xlsx.read(bytes)
  debug_inspect(loaded.get_sheet_list(), content="[\"Test\"]")
  debug_inspect(loaded.get_cell("Test", "A1"), content="Some(\"Hello\")")
  debug_inspect(loaded.get_cell("Test", "A2"), content="Some(\"World\")")
}

Embedded Cell Images

get_pictures returns the images anchored at a cell, including the modern embedded "in-cell" images that are not part of the drawing layer: WPS Office DISPIMG images, rich-value "Place in cell" images, and images inserted by the IMAGE() function. They are read from the xl/richData/* and xl/cellimages.xml parts on open.

///|
let workbook = @mbtexcel.open_file("with_images.xlsx")

///|
let pictures = workbook.get_pictures("Sheet1", "A1")

Dates and Times

Store a ZonedDateTime or Duration directly; the cell is written as an Excel serial number with an appropriate default number format.

///|
test "typed date and duration cells" {
  let wb = @xlsx.Workbook::new()
  ignore(wb.add_sheet("Sheet1"))

  // A date is stored as a whole-number serial (2024-07-03 -> 45476).
  wb.set_cell_time("Sheet1", "A1", @time.date_time(2024, 7, 3))
  debug_inspect(
    wb.get_cell("Sheet1", "A1"),
    content=(
      #|Some("45476")
    ),
  )

  // A duration is stored as a fraction of a day (90 minutes -> 0.0625).
  wb.set_cell_duration("Sheet1", "A2", @time.Duration::of(minutes=90))
  debug_inspect(
    wb.get_cell("Sheet1", "A2"),
    content=(
      #|Some("0.0625")
    ),
  )
}

Package Validation

validate_ooxml_package runs fast, dependency-free structural checks on serialized workbook bytes — content-type coverage for every part, relationship-target integrity, presence of the required core parts, and well-formed part names. These are the package-level problems that trigger Excel's "we found a problem" repair dialog. An empty result means the package is well-formed.

///|
test "validate package" {
  let wb = @xlsx.Workbook::new()
  ignore(wb.add_sheet("Sheet1"))
  wb.set_cell("Sheet1", "A1", "hello")
  debug_inspect(@xlsx.validate_ooxml_package(@xlsx.write(wb)), content="[]")
}

Error Handling

All operations that can fail raise XlsxError:

///|
test "error handling" {
  let wb = @xlsx.Workbook::new()

  // Try to get non-existent sheet
  let result : Result[Int, Error] = Ok(wb.get_sheet_index("Missing")) catch {
    e => Err(e)
  }
  debug_inspect(result, content="Ok(-1)")

  // Invalid cell reference
  let sheet = wb.add_sheet("Test")
  let bad_ref : Result[Unit, Error] = Ok(sheet.set_cell("123", "value")) catch {
    e => Err(e)
  }
  guard bad_ref is Err(_) else { return }
}

Cell Reference Utilities

///|
test "cell reference utilities" {
  // Split cell name
  debug_inspect(@xlsx.split_cell_name("AB123"), content="(\"AB\", 123)")

  // Join cell name
  inspect(@xlsx.join_cell_name("AB", 123), content="AB123")

  // Coordinates (1-indexed)
  debug_inspect(@xlsx.cell_name_to_coordinates("C5"), content="(3, 5)")
  inspect(@xlsx.coordinates_to_cell_name(3, 5), content="C5")

  // Absolute references
  inspect(@xlsx.coordinates_to_cell_name(3, 5, abs=true), content="$C$5")

  // Column conversion
  inspect(@xlsx.column_name_to_number("AA"), content="27")
  inspect(@xlsx.column_number_to_name(27), content="AA")
}

Color Utilities

///|
test "color utilities" {
  // RGB to HSL
  let (h, _, _) = @xlsx.rgb_to_hsl(255, 0, 0) // Red
  inspect(h < 1.0, content="true") // Hue near 0

  // HSL to RGB
  let (r, _, _) = @xlsx.hsl_to_rgb(0.0, 1.0, 0.5) // Red
  inspect(r, content="b'\\xFF'") // 255

  // Theme color with tint
  let tinted = @xlsx.theme_color("FF0000", 0.5)
  inspect(tinted.length(), content="8")
}

Defined Names

///|
test "defined names" {
  let wb = @xlsx.Workbook::new()
  ignore(wb.add_sheet("Data"))

  // Create defined name
  let dn = @xlsx.DefinedName::new(
    "SalesRange",
    "Data!$A$1:$D$100",
    scope="Data",
  )
  wb.set_defined_name(dn)

  // Get defined names
  let names = wb.get_defined_names()
  inspect(names.length(), content="1")
  inspect(names[0].name, content="SalesRange")
}

Document Properties

///|
test "document properties" {
  let wb = @xlsx.Workbook::new()

  // Set core properties
  let props = @xlsx.CoreProperties::with_values(
    title="Sales Report",
    creator="Finance Team",
    subject="Q4 2024 Sales",
    keywords="sales, quarterly, report",
    description="Quarterly sales report for Q4 2024",
  )
  wb.set_core_properties(props)

  // Read back
  let p = wb.core_properties()
  inspect(p.title, content="Sales Report")
}

API Summary

Workbook Methods

Category	Methods
Sheets	`add_sheet`, `delete_sheet`, `copy_sheet`, `sheet`, `sheets`, `get_sheet_list`, `set_sheet_name`, `set_sheet_visible`
Cells	`get_cell`, `set_cell`, `get_cell_formula`, `set_cell_formula`, `calc_cell_value`
Rows/Cols	`get_row`, `set_row`, `get_col`, `set_col`, `insert_rows`, `remove_row`, `insert_cols`, `remove_col`
Styles	`add_style`, `new_style`, `get_style`, `new_conditional_style`
Features	`add_chart`, `add_table`, `add_data_validation`, `add_pivot_table`, `add_sparkline`, `add_image`
Protection	`protect_workbook`, `unprotect_workbook`, `protect_sheet`, `unprotect_sheet`
Properties	`core_properties`, `app_properties`, `custom_properties`, `set_defined_name`
I/O	`save`, `save_as`, `write_to_buffer`

Worksheet Methods

Category	Methods
Cells	`get_cell`, `set_cell`, `get_cell_rc`, `set_cell_rc`, `set_cell_value`, `set_cell_formula`, `set_cell_style`
Rows/Cols	`get_row`, `set_row`, `get_col`, `set_col`, `set_row_height`, `set_col_width`, `set_row_visible`, `set_col_visible`
Merge	`merge_cells`, `unmerge_cells`, `merged_cells`
Features	`add_chart`, `add_table`, `add_data_validation`, `add_comment`, `add_hyperlink`, `add_image`
Layout	`set_page_margins`, `set_page_layout`, `set_header_footer`, `set_panes`
Navigation	`max_row`, `max_col`, `rows`, `cols`, `cells`

Uh oh!

FilesExpand file tree

README.mbt.md

Latest commit

History