DOCSEARCHbyAlgolia
Submit Docs

XLSX.jl

repository·master·Indexed 43 days ago

https://github.com/juliadata/xlsx.jl

A Julia package for reading and writing Excel (.xlsx) spreadsheet files implementing the ECMA-376 standard. It provides comprehensive tools for managing worksheets, cell formatting, formulas, and Excel Tables (ListObjects), as well as support for charts, images, and defined names. The package integrates with FileIO.jl for generic loading and saving and requires Julia v1.10 (LTS) or more recent.

Tokens
21.6K
Snippets
76
Records
96
Agent score
54%

What's inside XLSX.jl

  2. How Excel cell formatting hierarchy works

    master

    In XLSX.jl, formatting follows the Excel style hierarchy. A Cell refers to a Style, which defines Alignment and can point to shared definitions for font, fill, border, and number format. Multiple styles can share the same font or fill definition to optimize the file structure.

                    `Cell`
                  │
               `Style` => `Alignment`
                  │
  ┌──────────┬────┴─────┬─────────┐
  │          │          │         │
`font`     `fill`    `border`  `format`

  3. Use ReferencedFormula for ranges

    master

    When you provide a contiguous range to setFormula (e.g., setFormula(s, "B2:B4", "=B1+A2")), XLSX.jl creates a ReferencedFormula.

    This behaves like Excel's 'drag fill' feature: the first cell in the range contains the actual formula, and subsequent cells contain a reference to that first cell with appropriate offsets. When calling XLSX.getFormula on any cell within that range, the returned string will be the adjusted formula as it would appear in the Excel formula bar.

    s[1:4, 1] = 1:4
setFormula(s, "B1", "=A1")
setFormula(s, "B2:B4", "=B1+A2") # creates a running total

XLSX.getFormula(s, "B2") # returns "=B1+A2"
XLSX.getFormula(s, "B4") # returns "=B3+A4" (adjusted for offset)

  4. Handle relative and absolute cell references in formulas and values

    master

    When specifying value, value2, or formula (for :expression types), you can use relative or absolute references.

    Important: In Julia, the $ character used for absolute references in Excel must be escaped with a backslash (\$).

    • Relative Reference: "B2" — The cell used in comparison is adjusted for each cell in the target range (e.g., if the range starts at A2, A2 compares to B2, A3 compares to B3, etc.).
    • Absolute Reference: "\$B\$2" — Every cell in the target range is compared against the exact same cell (B2).

    Limitations:

    • Relative cell references cannot be used with :dataBar, :colorScale, or :iconSet types.
    • References to cells in other sheets (e.g., "OtherSheet!A1") are not yet universally implemented in XLSX.jl.
    # Relative reference
value = "B2"

# Escaped absolute reference
value = "\$B\$2"

  5. Overlay multiple conditional formats

    master

    You can apply multiple conditional formats to the same cell or overlapping ranges. Excel applies these in priority order, which corresponds to the order in which they were defined using setConditionalFormat.

    To check the current formats applied to a worksheet, use XLSX.getConditionalFormats(s).

    # Applying multiple formats sequentially
XLSX.setConditionalFormat(s, "A2:C5", :colorScale; colorscale="greenyellowred")

XLSX.setConditionalFormat(s, "A2:C5", :top10; 
                    operator ="topN", 
                    value="3",
                    font=["color"=>"magenta3", "strike"=>"true"],
                    fill=["pattern"=>"lightVertical", "fgColor"=>"lawngreen", "bgColor"=>"cornsilk"],
                    border=["style"=>"double", "color"=>"cyan"])

XLSX.setConditionalFormat(s, "A2:A5", :cellIs; 
                   operator="lessThan", 
                   value="2",
                   font=["color"=>"coral", "bold"=>"true"],
                   fill=["pattern"=>"lightHorizontal", "fgColor"=>"cornsilk"],
                   border=["style"=>"dashed", "color"=>"orangered4"])

  6. How Tables.jl integration works in XLSX.jl

    master

    XLSX.jl is built on the Tables.jl interface, allowing seamless interoperability with other Julia data packages.

    Key Concepts:

    • Writing: XLSX.writetable and XLSX.XLSXFile accept any object conforming to the Tables.jl interface (e.g., DataFrames.DataFrame).
    • Reading: You can convert an Excel worksheet back into a Tables.jl compatible format using XLSX.eachtablerow(sheet). This returns an XLSX.TableRowIterator.
    • Sinks: XLSXFile can act as a sink for functions like CSV.read, allowing you to pipe CSV data directly into an Excel file.

    Example: Reading a worksheet into a DataFrame

    using XLSX, DataFrames

f = XLSX.readxlsx("output_table.xlsx")
s = f["report"]
df2 = XLSX.eachtablerow(s) |> DataFrames.DataFrame
    using CSV, XLSX

# Direct pipe from CSV to XLSX
xf = CSV.read("iris.csv", XLSXFile)

  7. Update rich text cells with setFont, setUniformFont, or setUniformStyle

    master

    Functions like setFont, setUniformFont, and setUniformStyle operate at the cell level. When applied to a cell containing rich text:

    1. Partial Overrides: If you use setFont to change an attribute (e.g., under="none"), it will remove that specific attribute from the rich text runs, but other heterogeneous formatting (like color or size) will remain.
    2. Uniformity: setUniformFont and setUniformStyle will convert rich text attributes into uniform cell-level attributes if they match the new setting. Any remaining heterogeneity that doesn't match the new uniform setting will be preserved.
    3. Limitations: These functions cannot target specific substrings. To edit individual runs, you must overwrite the cell with a completely new RichTextString or AnnotatedString.

    Note: An XLSXFile must be opened in write mode to edit rich text formatting.

    # Removing underlining from a rich text cell in column B
setFont(s, "B"; under="none")

# Applying a uniform color to a range; existing rich text heterogeneity remains
setUniformFont(s, "A1:F2"; color="orange")

# Applying the style of A1 to a range; rich text heterogeneity (like bold/italic) is preserved
setUniformStyle(s, "A1,D1:F2")

  8. Distinguish between tabular data and native Excel Tables

    master

    In XLSX.jl, the term 'table' refers to two different concepts:

    1. Tabular data in a cell range: A rectangular block of cells that looks like a table. Its extent is inferred heuristically from cell content using functions like XLSX.gettable, XLSX.readtable, or XLSX.eachtablerow(sheet, ...). This is not a formal object in the Excel file.
    2. A native Excel Table: A formal object created in Excel (via Insert → Table or Ctrl+T). It is represented by the XLSX.Table type. It has a unique name, a recorded extent (range), named columns, and may include styles, autofilters, or a totals row. Because its extent is recorded in the file, XLSX.jl does not need to guess its boundaries.

    When using ambiguous function names, the argument type determines the behavior: gettable(sheet) reads an inferred range, while gettable(t::Table) reads a native Excel Table object.

  9. Difference between static and dynamic conditional formats

    master

    XLSX.jl supports two ways to handle conditional formatting, which behave differently when cell values change:

    1. Static conditional formats: These are applied based on the cell values at the exact moment the formatting function is run. If the cell values change later, the formatting will not update automatically. These are created using setAttribute() functions (like XLSX.setFont, XLSX.setFill, or XLSX.setBorder).

    2. Dynamic conditional formats: These use Excel's native conditional formatting engine. If the cell values change, the formatting will update automatically to reflect the new values. These are created using the setConditionalFormat() function.

  10. How splitFreeze panes work

    master

    The splitFreeze() function behaves identically to freezePanes() in terms of visual result. However, it marks the panes internally with state="frozenSplit". This mimics Excel's behavior when a user freezes panes that were originally created via a draggable split. This distinction only affects how Excel handles the panes if the user later interacts with them (e.g., unfreezing them) within Excel.

    using XLSX
f = XLSX.newxlsx()
sheet = f[1]

# Freezes panes and marks them as originating from a split
XLSX.splitFreeze(sheet; nrows=3, ncols=2)

  11. Apply uniform attributes vs uniform styles

    master

    XLSX.jl provides different ways to apply formatting to ranges, depending on whether you want to merge attributes or overwrite them entirely.

    1. setAttribute (e.g., setFont, setBorder)

    Behavior: Merges new attributes with existing ones on a cell-by-cell basis. Use case: When you want to add a border to cells without losing their existing background colors or fonts.

    2. setUniformAttribute (e.g., setUniformBorder, setUniformFill)

    Behavior: Updates the first cell in the range with the new attribute, then applies that specific attribute ID to all other cells in the range. This overwrites only that specific category (e.g., only the border) for the rest of the cells. Use case: When you want all cells in a range to have the same border, but keep their existing fonts/fills.

    3. setUniformStyle

    Behavior: Takes the styleId of the first cell in the range and applies it uniformly to every cell in the range. This overwrites all formatting (font, fill, border, format, and alignment) to ensure perfect consistency. Use case: When you need a range of cells to be completely identical in appearance. This is the most efficient method for large ranges.

    # 1. Merge (cell-by-cell)
XLSX.setBorder(s, "B2,D2,F2"; top=["style"=>"thick", "color"=>"red"])

# 2. Uniform category (overwrites only borders)
XLSX.setUniformBorder(s, "B2,D2,F2"; allsides=["color"=>"green"])

# 3. Uniform everything (overwrites all formatting)
XLSX.setUniformStyle(s, "B2,D2,F2")

  12. Manage Defined Names and Scopes

    master

    Defined names in Excel have two scopes: Workbook scope (visible throughout the entire file) and Worksheet scope (visible only within a specific sheet).

    Functions in XLSX.jl determine their scope based on the first argument provided:

    • Passing an XLSXFile object targets workbook scope.
    • Passing a Worksheet object targets that specific worksheet scope.

    Key behaviors:

    • XLSX.getDefinedNames(ws) returns only names scoped to that worksheet. To see all names in the file regardless of scope, use XLSX.getAllDefinedNames(file).
    • Names are matched case-insensitively.
    • Defined names share a namespace with Excel Table names.

    API Operations:

    • XLSX.addDefinedName(...) to create a name.
    • XLSX.getDefinedNames(...) to list names in a specific scope.
    • XLSX.getAllDefinedNames(file) to list all names in the workbook.
    • XLSX.deleteDefinedName(...) to remove a name.
    • XLSX.deleteAllDefinedNames(...) to clear names.