# 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

```mbt check
///|
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

```mbt check
///|
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

```mbt check
///|
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

```mbt check
///|
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

```mbt check
///|
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

```mbt check
///|
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

```mbt check
///|
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

```mbt check
///|
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

```mbt check
///|
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

```mbt check
///|
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

```mbt check
///|
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

```mbt check
///|
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

```mbt check
///|
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

```mbt check
///|
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

```mbt check
///|
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

```mbt check
///|
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

```mbt check
///|
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

```mbt check
///|
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

```mbt check
///|
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:

```mbt check
///|
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

```mbt check
///|
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.

```mbt nocheck
///|
async test "read the images anchored at a cell" {
  let workbook = @mbtexcel.open_file("with_images.xlsx")
  let pictures = workbook.get_pictures("Sheet1", "A1")
  for picture in pictures {
    println("\{picture.extension} \{picture.data.length()} bytes")
  }
}
```

## Dates and Times

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

```mbt check
///|
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.

```mbt check
///|
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`:

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

  // Try to get non-existent sheet
  let result : Result[Int32, 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

```mbt check
///|
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

```mbt check
///|
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

```mbt check
///|
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

```mbt check
///|
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` |

### Multi-value AutoFilters

`set_auto_filter` accepts any number of equality comparisons joined by `or`
(or `||`), for example `x == EMEA or x == APAC or x == AMER`. Other comparisons,
including wildcard expressions, remain limited to one or two custom conditions.
Only `and`/`&&` and `or`/`||` are accepted as conjunctions.

Use `set_auto_filter_values` when values already exist as data, without quoting
or expression parsing:

```mbt check
///|
test "literal multi-value auto filter" {
  let wb = @xlsx.Workbook::new()
  ignore(wb.add_sheet("Regions"))
  wb.set_auto_filter_values("Regions", "B1:D50", [
    { column: "B", values: ["EMEA", "APAC", "North America", "or"], },
  ])
}
```

The worksheet method takes the same range and options without the sheet name.
Both methods replace the entire filter. Column names are absolute worksheet
columns, must lie inside the range, and repeated columns use the last option.
Value lists must be nonempty and are copied; an empty options array sets only
filter arrows. Literal values retain spaces, quotes, `*`, `?`, and `blanks`
without interpreting them as expression syntax or special filter keywords.
These methods store filter criteria; they do not automatically hide rows.