Elixlsx

https://github.com/xou/elixlsx
Admin
Elixlsx is a writer for the MS Excel OpenXML format (.xlsx) that supports multiple sheets, various...
Tokens:5,403
Snippets:30
Trust Score:7.6
License:MIT
Update:1 year ago
Show doc for...
Tokens:
Raw
### Install Elixlsx from GitHub Repository

Source: https://github.com/xou/elixlsx/blob/master/README.md

This code snippet demonstrates how to add the Elixlsx library as a dependency in your Elixir project's `mix.exs` file by directly referencing the GitHub repository. This is useful for using the latest development version.

```elixir
defp deps do
  [{:elixlsx, github: "xou/elixlsx"}]
end
```

--------------------------------

### Install Elixlsx via Hex Package Manager

Source: https://github.com/xou/elixlsx/blob/master/README.md

This code snippet shows how to add the Elixlsx library as a dependency in your Elixir project's `mix.exs` file using the Hex package manager. Ensure you are using Elixir 1.12 or above.

```elixir
defp deps do
  [{:elixlsx, "~> 0.6.0"}]
end
```

--------------------------------

### Customize Column Widths and Row Heights (Elixir)

Source: https://context7.com/xou/elixlsx/llms.txt

Provides examples of how to set specific widths for columns using `set_col_width/3` and heights for rows using `set_row_height/3`.

```elixir
alias Elixlsx.Sheet

sheet = Sheet.with_name("Custom Sizes")
  |> Sheet.set_cell("A1", "Wide Column")
  |> Sheet.set_col_width("A", 25.0)
  |> Sheet.set_col_width("B", 15.5)
  |> Sheet.set_cell("A3", "Tall Row")
  |> Sheet.set_row_height(3, 40)
  |> Sheet.set_row_height(5, 60)
```

--------------------------------

### Complete Elixir Workbook Example with Formatting

Source: https://context7.com/xou/elixlsx/llms.txt

This comprehensive Elixir snippet creates a full-featured workbook with two sheets: 'Q4 Sales' and 'Summary'. It demonstrates setting cell values, applying formatting (bold, background color, number format), adding formulas, setting column widths, freezing panes, merging cells, and handling datetimes.

```elixir
alias Elixlsx.{Sheet, Workbook}

# Sales data sheet with formatting
sales = Sheet.with_name("Q4 Sales")
  |> Sheet.set_cell("A1", "Product", bold: true, bg_color: "#4472C4", color: "#FFFFFF")
  |> Sheet.set_cell("B1", "Units", bold: true, bg_color: "#4472C4", color: "#FFFFFF")
  |> Sheet.set_cell("C1", "Revenue", bold: true, bg_color: "#4472C4", color: "#FFFFFF")
  |> Sheet.set_cell("A2", "Widget A")
  |> Sheet.set_cell("B2", 150)
  |> Sheet.set_cell("C2", 1499.99, num_format: "$#,##0.00")
  |> Sheet.set_cell("A3", "Widget B")
  |> Sheet.set_cell("B3", 89)
  |> Sheet.set_cell("C3", 2199.99, num_format: "$#,##0.00")
  |> Sheet.set_cell("A4", "Total", bold: true)
  |> Sheet.set_cell("B4", {:formula, "SUM(B2:B3)"}, bold: true)
  |> Sheet.set_cell("C4", {:formula, "SUM(C2:C3)", value: 3699.98},
      bold: true, num_format: "$#,##0.00")
  |> Sheet.set_col_width("A", 15.0)
  |> Sheet.set_col_width("C", 12.0)
  |> Sheet.set_pane_freeze(1, 0)

# Summary sheet with merged cells
summary = %Sheet{
  name: "Summary",
  rows: [
    [["Q4 2023 Sales Report", bold: true, size: 16]],
    [],
    ["Generated:", {{2023, 12, 31}, {23, 59, 59}}]
  ],
  merge_cells: [{"A1", "C1"}]
}
|> Sheet.set_cell("A3", "Generated:")
|> Sheet.set_cell("B3", {{2023, 12, 31}, {23, 59, 59}}, datetime: true)
|> Sheet.set_col_width("B", 20.0)

# Create workbook
workbook = %Workbook{
  sheets: [summary, sales],
  datetime: "2023-12-31T23:59:59Z"
}

{:ok, _} = Elixlsx.write_to(workbook, "q4_report.xlsx")
```

--------------------------------

### Generate XLSX File in Memory (Elixir)

Source: https://context7.com/xou/elixlsx/llms.txt

This example shows how to create an Elixir workbook and its sheet, then write the XLSX content directly into memory as a binary. This is useful for scenarios where the Excel file needs to be sent over a network or stored in a database without intermediate file I/O. The `Elixlsx.write_to_memory` function is used for this purpose.

```elixir
alias Elixlsx.{Sheet, Workbook}

sheet = Sheet.with_name("Report")
  |> Sheet.set_cell("A1", "Data")

workbook = %Workbook{sheets: [sheet]}
{:ok, {"report.xlsx", binary}} = Elixlsx.write_to_memory(workbook, "report.xlsx")

# binary can now be sent over network, stored in database, etc.
```

--------------------------------

### Freeze Panes for Scrolling (Elixir)

Source: https://context7.com/xou/elixlsx/llms.txt

Illustrates how to freeze rows and/or columns using `set_pane_freeze/3` to keep them visible while scrolling. Shows examples of freezing rows and columns, freezing only rows, and removing freezing.

```elixir
alias Elixlsx.Sheet

sheet = Sheet.with_name("Frozen")
  |> Sheet.set_cell("A1", "Header")
  |> Sheet.set_pane_freeze(1, 1)  # Freeze first row and column

# Freeze first two rows and first column
sheet2 = sheet |> Sheet.set_pane_freeze(2, 1)

# Remove freezing
sheet3 = sheet2 |> Sheet.remove_pane_freeze()

# Use 0 for no freeze: {1, 0} freezes first row only
```

--------------------------------

### Insert Sheet at Specific Position in Elixir Workbook

Source: https://context7.com/xou/elixlsx/llms.txt

This Elixir example shows how to insert a sheet at a specific index within a workbook's sheets. The `Workbook.insert_sheet/3` function takes the workbook, the sheet to insert, and the zero-based index where it should be placed.

```elixir
alias Elixlsx.{Sheet, Workbook}

sheet1 = Sheet.with_name("First")
sheet2 = Sheet.with_name("Second")
sheet_middle = Sheet.with_name("Middle")

workbook = %Workbook{sheets: [sheet1, sheet2]}
  |> Workbook.insert_sheet(sheet_middle, 1)  # Insert at position 1

# Result: First, Middle, Second
```

--------------------------------

### Set Cell Colors (Text and Background) (Elixir)

Source: https://context7.com/xou/elixlsx/llms.txt

This example shows how to set the text color and background color for cells in an Elixir `Sheet` using hexadecimal color codes. The `color` option sets the font color, while `bg_color` sets the cell's background fill color. Both options can be used simultaneously to achieve custom cell appearances.

```elixir
alias Elixlsx.Sheet

sheet = Sheet.with_name("Colorful")
  |> Sheet.set_cell("A1", "Orange Text", color: "#FF8800")
  |> Sheet.set_cell("A2", "Yellow Background", bg_color: "#FFFF00")
  |> Sheet.set_cell("A3", "Red on Blue", color: "#FF0000", bg_color: "#0000FF")
```

--------------------------------

### Set Cells Using Zero-Based Indices (Elixir)

Source: https://context7.com/xou/elixlsx/llms.txt

This example demonstrates setting cell values in an Elixir `Sheet` using zero-based row and column indices instead of traditional Excel coordinates. The `Sheet.set_at/3` function takes the row index, column index, and the cell value. This method can be useful for programmatic cell manipulation based on array or list structures.

```elixir
alias Elixlsx.Sheet

sheet = Sheet.with_name("Indexed")
  |> Sheet.set_at(0, 0, "Top Left")
  |> Sheet.set_at(0, 2, "Top Right")
  |> Sheet.set_at(5, 5, "Middle")

# Results in cells A1, C1, and F6
```

--------------------------------

### Add Data Validations in Elixir Sheet

Source: https://context7.com/xou/elixlsx/llms.txt

This Elixir snippet demonstrates adding data validation rules to cells. It includes examples for creating dropdown lists with explicit values, referencing cells within the same sheet, and referencing cells in another sheet.

```elixir
alias Elixlsx.Sheet

sheet = Sheet.with_name("Validated")
  |> Sheet.set_cell("A1", "dog")
  |> Sheet.set_cell("A2", "cat")
  # Dropdown with explicit list
  |> Sheet.add_data_validations("B1", "B10", ["dog", "cat", "bird"])
  # Reference cells in same sheet
  |> Sheet.add_data_validations("C1", "C10", "=$A$1:$A$10")
  # Reference cells in another sheet
  |> Sheet.add_data_validations("D1", "D10", "=OtherSheet!$A$1:$A$20")
```

--------------------------------

### Basic Elixlsx Workbook Creation and Writing

Source: https://github.com/xou/elixlsx/blob/master/README.md

This is a 1-line tutorial demonstrating the basic usage of Elixlsx. It creates a new workbook, appends a sheet named 'Sheet 1', sets a bold string 'Hello' in cell A1, and writes the workbook to a file named 'hello.xlsx'.

```elixir
(alias Elixlsx.Workbook, alias Elixlsx.Sheet)
iex(1)> Workbook.append_sheet(%Workbook{}, Sheet.with_name("Sheet 1") |> Sheet.set_cell("A1", "Hello", bold: true)) |> Elixlsx.write_to("hello.xlsx")
```

--------------------------------

### Create Workbook and Write to File (Elixir)

Source: https://context7.com/xou/elixlsx/llms.txt

This snippet demonstrates how to create a basic Elixir workbook with a single sheet, populate it with data, and then write the workbook to an XLSX file on disk. It utilizes the `Sheet.with_name` and `Sheet.set_cell` functions to build the sheet, and the `Workbook` struct to hold the sheet, before finally calling `Elixlsx.write_to`.

```elixir
alias Elixlsx.{Sheet, Workbook}

# Create a simple workbook with one sheet
sheet = Sheet.with_name("Sales Data")
  |> Sheet.set_cell("A1", "Product")
  |> Sheet.set_cell("B1", "Revenue")
  |> Sheet.set_cell("A2", "Widget")
  |> Sheet.set_cell("B2", 1234.56)

workbook = %Workbook{sheets: [sheet]}
{:ok, _} = Elixlsx.write_to(workbook, "sales.xlsx")
```

--------------------------------

### Format Dates and Datetimes (Elixir)

Source: https://context7.com/xou/elixlsx/llms.txt

Shows how to format cells as dates and datetimes using Erlang calendar tuples or UNIX timestamps with the `datetime: true` option. Also demonstrates date-only and year-month formatting.

```elixir
alias Elixlsx.Sheet

sheet = Sheet.with_name("Dates")
  |> Sheet.set_cell("A1", {{2023, 12, 25}, {14, 30, 0}}, datetime: true)
  |> Sheet.set_cell("A2", 1703515800, datetime: true)
  |> Sheet.set_cell("A3", 1703515800, yyyymmdd: true)  # Date only
  |> Sheet.set_cell("A4", 1703515800, yyyymm: true)    # Year-month only
  |> Sheet.set_col_width("A", 20.0)  # Make room for date display
```

--------------------------------

### Apply Number Formatting with Format Codes (Elixir)

Source: https://context7.com/xou/elixlsx/llms.txt

Demonstrates using the `num_format` option to apply various Excel number format codes to cells, including decimals, percentages, and thousands separators.

```elixir
alias Elixlsx.Sheet

sheet = Sheet.with_name("Numbers")
  |> Sheet.set_cell("A1", 1234.567, num_format: "0.00")
  |> Sheet.set_cell("A2", 0.85, num_format: "0%")
  |> Sheet.set_cell("A3", 1234567, num_format: "#,##0")
  |> Sheet.set_cell("A4", 42.5, num_format: "0.000")
```

--------------------------------

### Format Empty Cells with Background and Borders (Elixir)

Source: https://context7.com/xou/elixlsx/llms.txt

Shows how to apply formatting, such as background color (`bg_color`) and borders, to cells that are explicitly set as empty using `:empty`.

```elixir
alias Elixlsx.Sheet

sheet = Sheet.with_name("Empty Formatted")
  |> Sheet.set_cell("A1", :empty, bg_color: "#FFFF00")
  |> Sheet.set_cell("A2", :empty, border: [bottom: [style: :double, color: "#000000"]])
```

--------------------------------

### Create Collapsible Row and Column Groups (Elixir)

Source: https://context7.com/xou/elixlsx/llms.txt

Demonstrates how to create collapsible groups for rows and columns using `group_rows/3` and `group_cols/2`. Shows grouping ranges and applying a collapsed state initially.

```elixir
alias Elixlsx.Sheet

# Using group_rows and group_cols functions
sheet = Sheet.with_name("Grouped")
  |> Sheet.group_rows(2, 5)                    # Group rows 2-5
  |> Sheet.group_rows(7, 10, collapsed: true)  # Group and collapse rows 7-10
  |> Sheet.group_cols("B", "E")                # Group columns B-E
  |> Sheet.group_cols("C", "D")                # Nested group C-D
```

--------------------------------

### Create Sheet by Setting Individual Cells (Elixir)

Source: https://context7.com/xou/elixlsx/llms.txt

This code illustrates creating an Elixir `Sheet` by incrementally adding cells using their Excel-style coordinates (e.g., 'A1', 'B2'). The `Sheet.with_name` function initializes the sheet, and `Sheet.set_cell` adds or updates cells with specified values. This method is suitable for building sheets programmatically when the cell layout is known.

```elixir
alias Elixlsx.Sheet

sheet = Sheet.with_name("Inventory")
  |> Sheet.set_cell("A1", "Item")
  |> Sheet.set_cell("B1", "Quantity")
  |> Sheet.set_cell("C1", "Price")
  |> Sheet.set_cell("A2", "Laptop")
  |> Sheet.set_cell("B2", 15)
  |> Sheet.set_cell("C2", 899.99)
```

--------------------------------

### Add Excel Formulas with Cached Values (Elixir)

Source: https://context7.com/xou/elixlsx/llms.txt

Explains how to insert Excel formulas into cells using the `{:formula, ...}` tuple. It also shows how to provide an optional cached `value` and apply number formatting to formula results.

```elixir
alias Elixlsx.Sheet

sheet = Sheet.with_name("Calculations")
  |> Sheet.set_cell("A1", 10)
  |> Sheet.set_cell("A2", 20)
  |> Sheet.set_cell("A3", 30)
  |> Sheet.set_cell("A4", {:formula, "SUM(A1:A3)"})
  |> Sheet.set_cell("B1", {:formula, "SUM(A1:A3)", value: 60}, num_format: "0.00")
  |> Sheet.set_cell("C1", {:formula, "NOW()"}, num_format: "yyyy-mm-dd hh:MM:ss")

# Note: Formulas with < or > must be escaped using :xmerl_lib.export_text/1
```

--------------------------------

### Create Sheet from Row Data List (Elixir)

Source: https://context7.com/xou/elixlsx/llms.txt

This snippet shows an alternative way to create an Elixir `Sheet` by providing all its data at once as a list of lists, where each inner list represents a row. This approach is convenient when data is already structured in a tabular format. The `Sheet` struct is initialized directly with the `rows` attribute.

```elixir
alias Elixlsx.Sheet

sheet = %Sheet{
  name: "Matrix Data",
  rows: [
    ["Name", "Age", "City"],
    ["Alice", 30, "NYC"],
    ["Bob", 25, "LA"],
    ["Carol", 35, "Chicago"]
  ]
}
```

--------------------------------

### Add Cell Borders with Styles and Colors (Elixir)

Source: https://context7.com/xou/elixlsx/llms.txt

Demonstrates how to add borders to cells with various styles (e.g., double, thin) and custom colors using the `border` option in `Sheet.set_cell/3`.

```elixir
alias Elixlsx.Sheet

sheet = Sheet.with_name("Borders")
  |> Sheet.set_cell("A1", "Double Bottom Border",
      border: [bottom: [style: :double, color: "#CC3311"]])
  |> Sheet.set_cell("A2", "All Borders",
      border: [
        top: [style: :thin, color: "#000000"],
        bottom: [style: :thin, color: "#000000"],
        left: [style: :thin, color: "#000000"],
        right: [style: :thin, color: "#000000"]
      ])
```

--------------------------------

### Store Boolean Values in Cells (Elixir)

Source: https://context7.com/xou/elixlsx/llms.txt

Demonstrates how to store standard boolean values (`true`, `false`) in cells, as well as how to use formulas to evaluate boolean expressions.

```elixir
alias Elixlsx.Sheet

sheet = Sheet.with_name("Booleans")
  |> Sheet.set_cell("A1", true)
  |> Sheet.set_cell("A2", false)
  |> Sheet.set_cell("A3", {:formula, "A1 AND A2"})
```

--------------------------------

### Apply Inline Cell Formatting in Row Data (Elixir)

Source: https://context7.com/xou/elixlsx/llms.txt

Illustrates how to apply formatting options like bold text (`bold: true`) and text color (`color: "#FF0000"`) directly within the row data structure using list notation.

```elixir
alias Elixlsx.Sheet

sheet = %Sheet{
  name: "Inline Styles",
  rows: [
    ["Normal", ["Bold", bold: true], ["Colored", color: "#FF0000"]],
    [123, ["Formatted Number", num_format: "#,##0.00"], "Text"]
  ]
}
```

--------------------------------

### Apply Font Styling to Cells (Elixir)

Source: https://context7.com/xou/elixlsx/llms.txt

This code snippet illustrates how to apply various font styles to cells within an Elixir `Sheet` using `Sheet.set_cell`. Options like `bold`, `italic`, `underline`, `strike` (strikethrough), `size`, and `font` (for custom font names) can be passed as keyword arguments. Combined styles are also supported.

```elixir
alias Elixlsx.Sheet

sheet = Sheet.with_name("Formatted")
  |> Sheet.set_cell("A1", "Bold Text", bold: true)
  |> Sheet.set_cell("A2", "Italic", italic: true)
  |> Sheet.set_cell("A3", "Underlined", underline: true)
  |> Sheet.set_cell("A4", "Strikethrough", strike: true)
  |> Sheet.set_cell("A5", "Large Font", size: 20)
  |> Sheet.set_cell("A6", "Custom Font", font: "Courier New")
  |> Sheet.set_cell("A7", "Combined", bold: true, italic: true, size: 16)
```

--------------------------------

### Append Sheets to Elixir Workbook

Source: https://context7.com/xou/elixlsx/llms.txt

This Elixir code illustrates how to create multiple sheets and append them sequentially to a workbook. The `Workbook.append_sheet/2` function adds sheets to the end of the existing list of sheets in the workbook.

```elixir
alias Elixlsx.{Sheet, Workbook}

sheet1 = Sheet.with_name("First")
sheet2 = Sheet.with_name("Second")
sheet3 = Sheet.with_name("Third")

workbook = %Workbook{sheets: [sheet1]}
  |> Workbook.append_sheet(sheet2)
  |> Workbook.append_sheet(sheet3)

Elixlsx.write_to(workbook, "multi_sheet.xlsx")
```

--------------------------------

### Control Grid Lines in Elixir Sheet

Source: https://context7.com/xou/elixlsx/llms.txt

This code shows how to create an Elixir sheet and disable the visibility of grid lines. Setting `show_grid_lines` to `false` results in a cleaner presentation without visible cell borders.

```elixir
alias Elixlsx.Sheet

sheet = %Sheet{
  name: "No Grid",
  show_grid_lines: false,
  rows: [["Clean", "Presentation"], ["No", "Lines"]]
}
```

--------------------------------

### Set Row Heights During Sheet Declaration (Elixir)

Source: https://context7.com/xou/elixlsx/llms.txt

Shows how to specify row heights when initializing a `Sheet` struct by providing a `row_heights` map where keys are row numbers and values are heights.

```elixir
alias Elixlsx.Sheet

sheet = %Sheet{
  name: "Sized Rows",
  rows: [
    ["Header"],
    ["Normal Row"],
    ["Tall Row"],
    ["Another Tall Row"]
  ],
  row_heights: %{1 => 30, 3 => 50, 4 => 50}
}
```

--------------------------------

### Declare Elixir Sheet with Row Grouping

Source: https://context7.com/xou/elixlsx/llms.txt

This snippet demonstrates how to declare a new Elixir sheet with specified rows, grouped rows, and grouped columns. It's useful for organizing data within a sheet, such as collapsing or expanding sections.

```elixir
sheet2 = %Sheet{
  name: "Groups",
  rows: List.duplicate([1, 2, 3, 4, 5, 6, 7, 8, 9, 10], 20),
  group_rows: [{2..5, collapsed: true}, 8..12],
  group_cols: [2..9, 3..6]
}
```

--------------------------------

### Apply Cell Alignment and Text Wrapping (Elixir)

Source: https://context7.com/xou/elixlsx/llms.txt

This snippet demonstrates how to control cell alignment and enable text wrapping in an Elixir `Sheet`. Horizontal alignment (`align_horizontal`) can be set to `:left`, `:center`, `:right`, or `:justify`. Vertical alignment (`align_vertical`) can be set to `:top`, `:center`, or `:bottom`. The `wrap_text` option allows long text to flow onto multiple lines within the cell.

```elixir
alias Elixlsx.Sheet

sheet = Sheet.with_name("Aligned")
  |> Sheet.set_cell("A1", "Left", align_horizontal: :left)
  |> Sheet.set_cell("B1", "Center", align_horizontal: :center)
  |> Sheet.set_cell("C1", "Right", align_horizontal: :right)
  |> Sheet.set_cell("D1", "Justify", align_horizontal: :justify)
  |> Sheet.set_cell("A2", "Top", align_vertical: :top)
  |> Sheet.set_cell("A3", "Middle", align_vertical: :center)
  |> Sheet.set_cell("A4", "Bottom", align_vertical: :bottom)
  |> Sheet.set_cell("A5", "Long text that should wrap within the cell", wrap_text: true)
```

--------------------------------

### Set Custom Workbook Creation Date (Elixir)

Source: https://context7.com/xou/elixlsx/llms.txt

This snippet demonstrates how to override the default creation timestamp for an Excel workbook generated by Elixlsx. By providing a `datetime` value in the `Workbook` struct, you can specify a custom date and time for when the workbook was created. The `Elixlsx.write_to` function then uses this custom date when writing the file.

```elixir
alias Elixlsx.{Sheet, Workbook}

sheet = Sheet.with_name("Historical")
workbook = %Workbook{
  sheets: [sheet],
  datetime: "2020-01-15T10:30:00Z"
}
Elixlsx.write_to(workbook, "historical.xlsx")
```

--------------------------------

### Merge Cells into Rectangular Ranges (Elixir)

Source: https://context7.com/xou/elixlsx/llms.txt

Demonstrates how to merge cells into various rectangular ranges (vertical, horizontal, block) using the `merge_cells` option when defining the `Sheet` struct. Notes that only the top-left cell's value is retained.

```elixir
alias Elixlsx.Sheet

sheet = %Sheet{
  name: "Merged",
  rows: [
    ["Merged Across", "B1", "C1", "D1"],
    ["A2", "Merged Down", "C2", "D2"],
    ["A3", "B3", "Large", "Merge"],
    ["A4", "B4", "Block", "Here"]
  ],
  merge_cells: [
    {"A1", "A3"},      # Vertical merge
    {"C1", "E1"},      # Horizontal merge
    {"C3", "E4"}
  ]
}

# Only the top-left cell value is retained in merged blocks
```
Tokens:
Raw