#xlsx

#Core Types

#Workbook

• 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

• 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

• 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

• 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")
}

#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

///|
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

///|
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

///|
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

///|
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

///|
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

#Worksheet Methods