Cells and data

Cell referencing

XLSX.CellRef — Type

CellRef(n::AbstractString)
CellRef(row::Int, col::Int)

A CellRef represents a cell location given by row and column identifiers.

CellRef("B6") indicates a cell located at column 2 and row 6.

These row and column integers can also be passed directly to the CellRef constructor: CellRef(6,2) == CellRef("B6").

Finally, a convenience macro @ref_str is provided: ref"B6" == CellRef("B6").

Examples

cn = XLSX.CellRef("AB1")
println( XLSX.row_number(cn) ) # will print 1
println( XLSX.column_number(cn) ) # will print 28
println( string(cn) ) # will print out AB1
println( cellname(cn) ) # will print out AB1

cn = XLSX.CellRef(1, 28)
println( XLSX.row_number(cn) ) # will print 1
println( XLSX.column_number(cn) ) # will print 28
println( string(cn) ) # will print out AB1
println( cellname(cn) ) # will print out AB1

cn = XLSX.ref"AB1"
println( XLSX.row_number(cn) ) # will print 1
println( XLSX.column_number(cn) ) # will print 28
println( string(cn) ) # will print out AB1
println( cellname(cn) ) # will print out AB1

source

XLSX.row_number — Function

row_number(c::CellRef) :: Int

Returns the row number of a given cell reference.

source

XLSX.column_number — Function

column_number(c::CellRef) :: Int

Returns the column number of a given cell reference.

source

XLSX.eachrow — Function

eachrow(sheet)

Creates a row iterator for a worksheet.

Base.eachrow(sheet::Worksheet) is defined as a synonym of XLSX.eachrow(sheet::Worksheet)

Example: Query all cells from columns 1 to 4.

left = 1  # 1st column
right = 4 # 4th column
for sheetrow in eachrow(sheet)
    for column in left:right
        cell = XLSX.getcell(sheetrow, column)

        # do something with cell
    end
end

Note

The eachrow row iterator will not return any row that consists entirely of EmptyCells. These empty rows are not represented in the .xlsx file and are therefore not seen by the iterator. The length(eachrow(sheet)) function returns the number of rows that are not entirely empty and will, in any case, only succeed if the worksheet cache is in use.

source

XLSX.eachtablerow — Function

eachtablerow(sheet, 
            [columns]; 
            [first_row], 
            [column_labels], 
            [header], 
            [stop_in_empty_row], 
            [stop_in_row_function], 
            [keep_empty_rows], 
            [normalizenames],
            [missing_strings]
) -> TableRowIterator

Constructs an iterator of table rows. Each element of the iterator is of type TableRow.

header is a boolean indicating whether the first row of the table is a table header.

If header == false and no column_labels were supplied, column names will be generated following the column names found in the Excel file.

The columns argument is a column range, as in "B:E". If columns is not supplied, the column range will be inferred by the non-empty contiguous cells in the first row of the table.

The user can replace column names by assigning the optional column_labels input variable with a Vector{Symbol}.

stop_in_empty_row is a boolean indicating whether an empty row marks the end of the table. If stop_in_empty_row=false, the iterator will continue to fetch rows until there's no more rows in the Worksheet. The default behavior is stop_in_empty_row=true. Empty rows may be returned by the iterator when stop_in_empty_row=false.

stop_in_row_function is a Function that receives a TableRow and returns a Bool indicating if the end of the table was reached. The row that satisfies stop_in_row_function is excluded from the table.

Example for stop_in_row_function:

function stop_function(r)
    v = r[:col_label]
    return !ismissing(v) && v == "unwanted value"
end

keep_empty_rows determines whether rows where all column values are equal to missing are kept (true) or skipped (false) by the row iterator. keep_empty_rows never affects the bounds of the iterator; the number of rows read from a sheet is only affected by first_row, stop_in_empty_row and stop_in_row_function (if specified). keep_empty_rows is only checked once the first and last row of the table have been determined, to see whether to keep or drop empty rows between the first and the last row.

normalizenames controls whether column names will be "normalized" to valid Julia identifiers. By default, this is false. If normalizenames=true, then column names with spaces or that start with numbers will be adjusted with underscores to become valid Julia identifiers. This is useful when you want to access columns via dot-access or getproperty, like file.col1. The identifier that comes after the . must be valid, so spaces or identifiers starting with numbers aren't allowed. (Based on CSV.jl's CSV.normalizename.)

missing_strings can be used to specify strings that should be interpreted as missing values in the resulting table. missing_strings can be a single string or a vector of strings. The default value is missing_strings=nothing.

Example code:

for r in XLSX.eachtablerow(sheet)
    # r is a `TableRow`. Values are read using column labels or numbers.
    rn = XLSX.row_number(r) # `TableRow` row number.
    v1 = r[1] # will read value at table column 1.
    v2 = r[:COL_LABEL2] # will read value at column labeled `:COL_LABEL2`.
end

Cell data

XLSX.readdata — Function

readdata(source, sheet, ref)
readdata(source, sheetref)

Return a scalar, vector or matrix with values from a spreadsheet file. 'ref' can be a defined name, a cell reference or a cell, column, row or non-contiguous range.

See also XLSX.readdata.

source

getdata(t::Table) -> Matrix{Any}

Return the data rows of Excel Table t (as returned by XLSX.table) as a row × column matrix. The header row and, if present, the totals row are excluded — only the table's data body is returned, in the same column order as t.columns.

Example

julia> t = XLSX.table(sheet, "Sales")

julia> XLSX.getdata(t)
3×2 Matrix{Any}:
 1000  "North"
 1500  "South"
  900  "East"

source

getdata(ws::Worksheet, cell::Cell) :: CellValue

Returns a Julia representation of a given cell value. The result data type is chosen based on the value of the cell as well as its style.

For example, date is stored as integers inside the spreadsheet, and the style is the information that is taken into account to chose Date as the result type.

For numbers, if the style implies that the number is visualized with decimals, the method will return a float, even if the underlying number is stored as an integer inside the spreadsheet XML.

If cell has empty value or empty String, this function will return missing.

source

XLSX.getcell — Function

getcell(xlsxfile, cell_reference_name) :: AbstractCell
getcell(worksheet, cell_reference_name) :: AbstractCell
getcell(sheetrow, column_name) :: AbstractCell
getcell(sheetrow, column_number) :: AbstractCell

Returns the internal representation of a worksheet cell.

Returns XLSX.EmptyCell if the cell has no data.

source

getcell(sheet, ref)
getcell(sheet, row, col)

Return an AbstractCell that represents a cell in the spreadsheet. Return a 2-D matrix as Array{AbstractCell, 2} if ref is a rectangular range. For row and column ranges, the extent of the range in the other dimension is determined by the worksheet's dimension. A non-contiguous range (which may not be rectangular) will return a vector of Array{AbstractCell, 2} with one element for each non-contiguous (comma separated) element in the range.

If ref is a range, getcell dispatches to getcellrange.

Example:

julia> xf = XLSX.readxlsx("myfile.xlsx")

julia> sheet = xf["mysheet"]

julia> cell = XLSX.getcell(sheet, "A1")

julia> cell = XLSX.getcell(sheet, 1:3, [2,4,6])

Other examples are as getdata().

source

XLSX.getcellrange — Function

getcellrange(sheet, rng)

Return a matrix with cells as Array{AbstractCell, 2}. rng must be a valid cell range, column range or row range, as in "A1:B2", "A:B" or "1:2", or a non-contiguous range. For row and column ranges, the extent of the range in the other dimension is determined by the worksheet's dimension. A non-contiguous range (which may not be rectangular) will return a vector of Array{AbstractCell, 2} with one element for each non-contiguous (comma separated) element in the range.

Example:

julia> ncr = "B3,A1,C2" # non-contiguous range, "out of order".
"B3,A1,C2"

julia>  XLSX.getcellrange(f[1], ncr)
3-element Vector{Matrix{XLSX.AbstractCell}}:
 [XLSX.Cell(B3, 0x0000000000000018, 0x00000000, 0x0000, XLSX.CT_INT, false);;]
 [XLSX.Cell(A1, 0x0000000000000018, 0x00000000, 0x0000, XLSX.CT_INT, false);;]
 [XLSX.Cell(C2, 0x0000000000000018, 0x00000000, 0x0000, XLSX.CT_INT, false);;]

For other examples, see getcell() and getdata().

source

XLSX.iserror — Function

iserror(s::Worksheet, ref::AbstractString)
iserror(s::Worksheet, rows, cols)

Returns true if the cell(s) at the given reference contain an error, false otherwise. An EmptyCell is not considered an error and returns false.

The return type depends on the type of ref and is the same shape as the return type of getcell for the same ref:

If ref or (row, col) refers to a single cell, returns a single Bool.
If ref or (rows, cols) refers to a range of cells, returns a matrix of Bools.
If ref or (rows, cols) refers to a non-contiguous range of cells, returns a vector of matrices of Bools.

Examples

julia> XLSX.iserror(sh, "A1") # Cell
true

julia> XLSX.iserror(sh, "I1") # EmptyCell
false

julia> XLSX.iserror(sh, "A1:I1") # CellRange - note that I1 is an EmptyCell, which is not an error
1×9 Matrix{Bool}:
 1  1  1  1  1  1  1  1  0

julia> XLSX.iserror(sh, "A1:B1,D1:E1") # non-contiguous range
2-element Vector{Matrix{Bool}}:
 [1 1]
 [1 1]

source

XLSX.geterror — Function

geterror(s::Worksheet, ref::AbstractString)
geterror(s::Worksheet, rows, cols)

Returns the error value (e.g. #DIV/0!) for the cell(s) at the given reference, if any. If there is no error, returns an empty string.

The return type depends on the type of ref and is the same shape as the return type of getcell for the same ref:

If ref or (row, col) refers to a single cell, returns a single Bool.
If ref or (rows, cols) refers to a range of cells, returns a matrix of Bools.
If ref or (rows, cols) refers to a non-contiguous range of cells, returns a vector of matrices of Bools.

Examples

julia> XLSX.geterror(sh, "A1") # Cell
"#NULL!"

julia> XLSX.geterror(sh, "I1") # EmptyCell
""

julia> XLSX.geterror(sh, "A1:I1") # CellRange - note that I1 is an EmptyCell, which returns an empty string
1×9 Matrix{String}:
 "#NULL!"  "#DIV/0!"  "#VALUE!"  "#REF!"  "#NAME?"  "#NUM!"  "#N/A"  "#VALUE!"  ""

julia> XLSX.geterror(sh, "A1:B1,D1:E1") # non-contiguous range
2-element Vector{Matrix{String}}:
 ["#NULL!" "#DIV/0!"]
 ["#REF!" "#NAME?"]

source

XLSX.gettable — Function

gettable(
    sheet,
    [columns];
    [first_row],
    [column_labels],
    [header],
    [infer_eltypes],
    [stop_in_empty_row],
    [stop_in_row_function],
    [keep_empty_rows],
    [normalizenames],
    [missing_strings]
) -> DataTable

Returns data from a spreadsheet as a struct XLSX.DataTable which can be passed directly to any function that accepts Tables.jl data. (e.g. DataFrame from package DataFrames.jl).

Use columns argument to specify which columns to get. For example, "B:D" will select columns B, C and D. If columns is not given, the algorithm will find the first sequence of consecutive non-empty cells.

Use first_row to indicate the first row from the table. first_row=5 will look for a table starting at sheet row 5. If first_row is not given, the algorithm will look for the first non-empty row in the spreadsheet.

header is a Bool indicating if the first row is a header. If header=true and column_labels is not specified, the column labels for the table will be read from the first row of the table. If header=false and column_labels is not specified, the algorithm will generate column labels. The default value is header=true.

Use column_labels as a vector of symbols to specify names for the header of the table.

Use normalizenames=true to normalize column names to valid Julia identifiers.

Use missing_strings to specify strings that should be interpreted as missing values in the resulting table. missing_strings can be a single string or a vector of strings. The default value is missing_strings=nothing.

Use infer_eltypes=true to get data as a Vector{Any} of typed vectors. The default value is infer_eltypes=true.

stop_in_empty_row is a boolean indicating whether an empty row marks the end of the table. If stop_in_empty_row=false, the TableRowIterator will continue to fetch rows until there's no more rows in the Worksheet. The default behavior is stop_in_empty_row=true.

stop_in_row_function is a Function that receives a TableRow and returns a Bool indicating if the end of the table was reached.

Example for stop_in_row_function

function stop_function(r)
    v = r[:col_label]
    return !ismissing(v) && v == "unwanted value"
end

keep_empty_rows determines whether rows where all column values are equal to missing are kept (true) or dropped (false) from the resulting table. keep_empty_rows never affects the bounds of the table; the number of rows read from a sheet is only affected by first_row, stop_in_empty_row and stop_in_row_function (if specified). keep_empty_rows is only checked once the first and last row of the table have been determined, to see whether to keep or drop empty rows between the first and the last row.

Example

julia> using DataFrames, PrettyTables, XLSX

julia> df = XLSX.openxlsx("myfile.xlsx") do xf
        DataFrame(XLSX.gettable(xf["mysheet"]))
    end

julia> PrettyTable(XLSX.gettable(xf["mysheet"], "A:C"))
┌─────────┬─────────┬─────────┐
│ Header1 │ Header2 │ Header3 │
├─────────┼─────────┼─────────┤
│       1 │       2 │       3 │
│       4 │       5 │       6 │
│       7 │       8 │       9 │
└─────────┴─────────┴─────────┘

See also: XLSX.readtable, XLSX.readto.

source

gettable(t::Table; [infer_eltypes], [normalizenames], [missing_strings]) -> DataTable

Returns data from an Excel Table t (as returned by XLSX.table) as a struct XLSX.DataTable, which can be passed directly to any function that accepts Tables.jl data (e.g. DataFrame from package DataFrames.jl).

Two `gettable` methods

Different from XLSX.gettable(sheet, ...), which infers a table's row/column bounds heuristically from cell content on a plain Worksheet. Here, t.ref is authoritative, so there is no columns/first_row/header/stop_in_empty_row/stop_in_row_function/ keep_empty_rows equivalent — the header row and totals row (if any) are always excluded, and any blank row within t.ref is returned as ordinary data.

Use normalizenames=true to normalize column names to valid Julia identifiers.

Use infer_eltypes=true (the default) to have each column narrowed to its own concrete type (e.g. Vector{Float64} rather than Vector{Any}), the same narrowing XLSX.eachtablerow/Tables.columns apply. Set infer_eltypes=false to skip narrowing and leave every column as Any.

Example

julia> using DataFrames

julia> t = XLSX.table(sheet, "Sales")

julia> df = DataFrame(XLSX.gettable(t))

source

XLSX.readtable — Function

readtable(
    source,
    [sheet,
    [columns]];
    [table_name],
    [first_row],
    [column_labels],
    [header],
    [infer_eltypes],
    [stop_in_empty_row],
    [stop_in_row_function],
    [enable_cache],
    [keep_empty_rows],
    [normalizenames],
    [missing_strings]
) -> DataTable

Returns tabular data from a spreadsheet as a struct XLSX.DataTable. Use this function to create a DataFrame from package DataFrames.jl (or other Tables.jl` compatible object).

If sheet is not given, the first sheet in the XLSXFile will be used.

readtable always loads only the requested sheet, regardless of how many other sheets exist in the workbook — other sheets are never read or cached, so reading from a single sheet of a large multi-sheet workbook is efficient.

Reading an Excel Table

Use table_name to read a named Excel Table (see XLSX.table) rather than a range of cells:

julia> XLSX.readtable("myfile.xlsx", "mysheet"; table_name="Sales")  # fast — sheet known

julia> XLSX.readtable("myfile.xlsx"; table_name="Sales")             # searches all sheets

When sheet is given, only that worksheet is decompressed, exactly as for a normal range read. When sheet is omitted, every worksheet's <tableParts> element is scanned to locate the table (cell data is still never materialized for the non-matching sheets, so the cost is modest), and the first sheet carrying a table of that name is used — table names are unique across a workbook.

An Excel Table's ref is authoritative, so when table_name is given, the columns, first_row, header, stop_in_empty_row, stop_in_row_function and keep_empty_rows arguments do not apply and are ignored: the table's header row and, if present, its totals row are always excluded, and any blank row within the table's range is returned as ordinary data. infer_eltypes, normalizenames, missing_strings and enable_cache apply as usual. Passing both table_name and columns throws an XLSXError.

Reading a cell range

Use first_row to indicate the first row of the table. first_row=5 will look for a table starting at sheet row 5. If first_row is not given, the algorithm will look for the first non-empty row in the spreadsheet.

Use column_labels to specify names for the header of the table.

Use normalizenames=true to normalize column names to valid Julia identifiers.

Use missing_strings to specify strings that should be interpreted as missing values in the resulting table. missing_strings can be a single string (e.g. "N/A") or a vector of strings (e.g. ["N/A", "NULL"]). The default value is missing_strings=nothing.

Use infer_eltypes=true to get data as a Vector{Any} of typed vectors. The default value is infer_eltypes=true.

stop_in_empty_row is a boolean indicating whether an empty row marks the end of the table. If stop_in_empty_row=false, the TableRowIterator will continue to fetch rows until there's no more rows in the Worksheet or range. The default behavior is stop_in_empty_row=true.

stop_in_row_function is a Function that receives a TableRow and returns a Bool indicating if the end of the table was reached.

Example for stop_in_row_function:

function stop_function(r)
    v = r[:col_label]
    return !ismissing(v) && v == "unwanted value"
end

enable_cache is a boolean that determines whether cell data are loaded into the worksheet cache on reading. Using readtable with enable_cache=true is faster than with enable_cache=false for large files, but uses more memory. The default behavior is enable_cache=true.

Example

julia> using DataFrames, XLSX

julia> df = DataFrame(XLSX.readtable("myfile.xlsx", "mysheet"))

julia> df = DataFrame(XLSX.readtable("myfile.xlsx", "mysheet"; table_name="Sales"))

source

XLSX.Table — Type

Table

A native Excel Table: a named, structured range within a worksheet, created in Excel with Insert → Table (or Ctrl+T), or in XLSX.jl with XLSX.addtable!.

Obtain one with XLSX.table or XLSX.tables. A Table conforms to the Tables.jl interface, so it can be passed directly to any compatible sink (e.g. DataFrame), or read with XLSX.gettable, XLSX.getdata or XLSX.eachtablerow. In every case only the Table's data rows are returned: the header row and, if present, the totals row are excluded.

Fields

id::Int: the Table's id, unique within the workbook.
name::String: the Table's name, unique within the workbook and sharing a namespace with defined names.
display_name::String: the name Excel displays. Usually identical to name.
ref::CellRange: the Table's full extent, including its header row and, if present, its totals row.
columns::Vector{String}: the column names, in order, taken from the header row.
has_totals_row::Bool: whether the last row of ref is a totals row.
style::Union{TableStyleInfo,Nothing}: the Table's style, or nothing if it has none.
sheet: the Worksheet the Table belongs to. Cell values are read from it on demand.

A Table is an immutable snapshot of the Table's structure at the time it was read. Functions that modify a Table (XLSX.settotals!, XLSX.appendtable!) return an updated Table; any earlier one goes stale and should be discarded.

source

XLSX.tables — Function

tables(ws::Worksheet) -> Vector{Table}
tables(xf::XLSXFile)  -> Vector{Table}

All Excel Tables defined on ws or on xf across every worksheet, in sheet order and then in document order within each sheet. Empty if the worksheet or workbook has none. Chartsheets are skipped.

Each Table knows the worksheet it belongs to (its sheet field), so the sheet is recoverable from the result.

# Examples

julia julia> XLSX.tables(sheet) 2-element Vector{XLSX.Table}: Table(id=1, "IOTable", A1:C8, 3 cols) Table(id=2, "Ageheight", E1:G6, 3 cols, +totals)

julia> for t in XLSX.tables(f) println(t.sheet.name, ": ", t.name, " ", t.ref) end Sheet1: IOTable A1:C8 Sheet1: Ageheight E1:G6 Sheet2: with_total A3:C11 ```

Note

XLSX.tables(xf::XLSXFile) must examine every worksheet, so on a large workbook opened lazily it will cause each sheet's Table metadata to be read (Cell data are not read in this process).

See also XLSX.table.

source

XLSX.table — Function

table(ws::Worksheet, name::AbstractString) -> Table
table(wb::Workbook, name::AbstractString) -> Table
table(xf::XLSXFile, name::AbstractString) -> Table
table(ws::Worksheet, id::Integer) -> Table
table(wb::Workbook, id::Integer) -> Table
table(xf::XLSXFile, id::Integer) -> Table

Look up a single table by name or workbook-scoped numeric id, searching a single worksheet or across every worksheet in the workbook.

Throws KeyError if not found.

Examples

julia> XLSX.table(sheet, "Age_height")
XLSX.Table: "Age_height"
  id      : 2
  range   : E1:G6
  columns : name, age, height
  style   : TableStyleMedium2 (row stripes)
  totals  : yes

julia> XLSX.table(sheet, 2)  # same table, looked up by id
XLSX.Table: "Age_height"
  id      : 2
  range   : E1:G6
  columns : name, age, height
  style   : TableStyleMedium2 (row stripes)
  totals  : yes

source

XLSX.addtable! — Function

addtable!(
    sheet::Worksheet,
    ref::Union{CellRange,AbstractString};
    name::AbstractString="",
    style::Union{AbstractString,Nothing}=nothing,
    has_totals_row::Bool=false,
) -> Table

Create a new Excel Table over ref on sheet, turning an existing range of cells into a Table object (banding, filter dropdowns, structured references, and the Table metadata itself) without changing any of the underlying data.

ref must already contain data before calling addtable!: specifically, the first row of ref is read as the table's header row, so every cell in that row must already contain the column name you want (write these first, e.g. with sheet[...] = ... or XLSX.writetable!). addtable! only wraps existing cells in a Table; it does not write any header, data, or totals values into the sheet itself.

ref must span at least two rows — a header row plus at least one data row. Excel does not support header-only tables (a single-row ref is rejected).

If name is not given, a unique name is generated ("Table1", "Table2", ...). Table names are workbook-scoped and must not collide with another table's name or with a defined name anywhere in the workbook.

style sets the table's visual style and should be one of Excel's built-in table style names, matching the "Table Styles" gallery in Excel's Table Design ribbon:

"TableStyleLightnn" where nn is between 1 and 21
"TableStyleMediumnn" where nn is between 1 and 28
"TableStyleDarknn" where nn is between 1 and 11
"None" for no style.

style is not validated and any string is passed straight through as the tableStyleInfo's name attribute; an unrecognized name will make Excel fall back to its default table appearance rather than causing an error. If omitted, Excel treats the table as having no explicit style (its own default appearance applies).

has_totals_row=true marks the last row of ref as the table's totals row. This only sets the flag that tells Excel to reserve and display that row as a totals row; it does not populate any totals formula or label into the cells themselves — write whatever content you want into that row's cells yourself (or leave them blank) before or after calling addtable!, or use XLSX.settotals! afterward to set per-column totals functions or labels. If the last row of ref already has content when has_totals_row=true is given, a warning is issued (not an error) — that row is still marked as the totals row regardless, since pre-existing content there may be intentional (e.g. a pre-authored totals formula or label).

Examples

julia> sheet[1, :] = ["id", "name", "score"]
julia> sheet[2, :] = [1, "alice", 10.5]
julia> sheet[3, :] = [2, "bob", 20.0]

julia> XLSX.addtable!(sheet, "A1:C3"; name="Results", style="TableStyleMedium2")

source

XLSX.deletetable! — Function

deletetable!(sheet::Worksheet, name::AbstractString)
deletetable!(sheet::Worksheet, id::Integer)

Delete the given Excel Table from sheet by name or by its workbook-scoped numeric id.

This removes the table object only: its xl/tables/tableN.xml part, its worksheet-level relationship, its <tablePart> entry, and its [Content_Types].xml override. It does not clear, delete, or modify any of the underlying cell data — the header row, data rows, and any totals row are left completely untouched, still holding whatever values they held while the table existed.

This mirrors what Excel itself does when you use Table Design → Convert to Range (or right-click → Table → Convert to Range): the table's structure (banding, filter dropdowns, structured references, and the Table object itself) is removed, but the cells and their values remain in place as an ordinary range. There is no single-step Excel operation that removes a table and its data together; if you want the data gone too, clear or delete those cells yourself as a separate step, e.g.:

julia> t = XLSX.table(sheet, "MyTable")

julia> XLSX.deletetable!(sheet, "MyTable")

julia> sheet[t.ref] = missing   # optional: also clear the data

Other tables on the same sheet, and tables on other sheets, are unaffected.

source

XLSX.settotals! — Function

settotals!(sheet::Worksheet, name::AbstractString, settings::Pair...)
settotals!(sheet::Worksheet, id::Integer, settings::Pair...)
settotals!(sheet::Worksheet, name::AbstractString; kwargs...)

Add or update the totals row for the Excel Table in sheet with the specified name or workbook-scoped numeric id.

Each element of settings is "ColumnName" => value (or, in the kwarg form, ColumnName=value for identifier-safe column names), where value is one of:

a Symbol naming a built-in totals function — :sum, :average, :count, :countnums, :max, :min, :stddev, :var or :none. Writes both the totalsRowFunction attribute and an actual SUBTOTAL(...) formula into the totals row cell for that column; Excel performs the calculation from this formula exactly as it would for any other SUBTOTAL formula.
a (:custom, formula::AbstractString) tuple, for a custom totals function — Excel's "More Functions..." option. formula is written verbatim into the cell (it need not be SUBTOTAL-based at all), and totalsRowFunction="custom" is set. XLSX.jl does not validate or evaluate the formula; Excel computes it on open, same as any other formula cell.
an AbstractString, written as a plain text label in that column's totals row cell (e.g. "Grand Total"), with no function or formula attached.

Columns not mentioned in settings are left untouched: if the table already has a totals row, their existing totals content (function, label, or blank) is preserved as-is. To remove a column's totals, pass :none explicitly:

julia> XLSX.settotals!(s, "Sales", "margin" => :none)   # margin's totals cell cleared

The totals row itself remains, even if every column's totals is cleared — an empty totals row is valid, and Excel displays it.

If the table does not already have a totals row, one is added by extending the table by one row — the row immediately following its current last row. That row must be completely empty; an XLSXError is thrown otherwise (clear it first, or use XLSX.addtable! with has_totals_row=true if that row was always meant to be part of the table).

As with every formula-writing path in XLSX.jl, no cached value is written alongside a totals-row formula — Excel recalculates it on open (update_workbook_xml! forces fullCalcOnLoad="1"). Formulas are written via XLSX.setFormula, which fully replaces the cell's formula/value while preserving its existing style, so calling settotals! again on a column that already has totals content (function, custom formula, or label) cleanly replaces it.

Examples

julia> XLSX.settotals!(sheet, "Sales",
           "Revenue" => :sum,
           "Notes"   => "Grand Total",
           "Margin"  => (:custom, "SUBTOTAL(109,Sales[Revenue])-SUBTOTAL(109,Sales[Cost])"),
       )

julia> XLSX.settotals!(sheet, "Sales"; Revenue=:sum, Margin=:average)

julia> XLSX.settotals!(sheet, tbl.id, "Revenue" => :sum)  # by workbook-scoped table id

julia> # Add a totals row to a table that doesn't have one yet — the row
       # immediately following the table's current last row must be empty.
       XLSX.settotals!(sheet, "Sales", "Revenue" => :sum)

julia> # Update just one column's totals function on a table that already
       # has a totals row — other columns' existing totals are untouched.
       XLSX.settotals!(sheet, "Sales", "Revenue" => :max)

julia> # A custom formula MUST aggregate each column reference itself
       # (e.g. via SUBTOTAL or SUM) — a bare `Sales[Revenue]` in a totals
       # cell is rewritten by Excel to a "this row" reference, which has
       # no valid row to intersect against in the totals row and raises
       # #VALUE!.
       XLSX.settotals!(sheet, "Sales",
           "Margin" => (:custom, "SUBTOTAL(109,Sales[Revenue])-SUBTOTAL(109,Sales[Cost])"),
       )

source

XLSX.appendtable! — Function

appendtable!(sheet::Worksheet, name::AbstractString, data; [check_empty]) -> Table

Append rows to the existing Excel Table name on sheet, extending the table's range.

data may be any Tables.jl-compatible source (e.g. an XLSX.DataTable or a DataFrame), an AbstractMatrix, or a vector of row vectors/tuples.

If data exposes column names, columns are matched by name and reordered to the table's own column order; a source missing any of the table's columns, or carrying any column the table doesn't have, is an error. Sources without column names (matrices, vectors of tuples/vectors) are matched positionally, so their column order must match the table's.

If the table has a totals row, it moves down to remain the last row of the table, and its content is regenerated from the table's own per-column totals settings. Functions, custom formulas and labels are all preserved but values are reset to missing.

The rows immediately below the table must be empty; an XLSXError is thrown otherwise. Pass check_empty=false to overwrite whatever is there.

source

XLSX.readto — Function

readto(
    source,
    [sheet,
    [columns]],
    sink;
    [table_name],
    [first_row],
    [column_labels],
    [header],
    [infer_eltypes],
    [stop_in_empty_row],
    [stop_in_row_function],
    [enable_cache],
    [keep_empty_rows],
    [normalizenames],
    [missing_strings]
) -> sink

Read and parse an Excel worksheet, materializing directly using the sink function, which can be any Tables.jl-compatible function (e.g. DataFrame, StructArray or TypedTable`).

Takes the same keyword arguments as XLSX.readtable, including table_name to read a named Excel Table (see XLSX.table) rather than a range of cells. Specifying sheet alongside table_name is faster, since only that worksheet is decompressed; omitting it searches every worksheet for the named table.

Example

julia> using DataFrames, StructArrays, TypedTables, XLSX

julia> df = XLSX.readto("myfile.xlsx", DataFrame)

julia> sa = XLSX.readto("myfile.xlsx", StructArray)

julia> tt = XLSX.readto("myfile.xlsx", Table) # from TypedTables.jl

julia> df = XLSX.readto("myfile.xlsx", "mysheet", DataFrame)

julia> df = XLSX.readto("myfile.xlsx", "mysheet", "A:C", DataFrame)

julia> df = XLSX.readto("myfile.xlsx", "mysheet", DataFrame; table_name="Sales")

julia> df = XLSX.readto("myfile.xlsx", DataFrame; table_name="Sales")

source

XLSX.gettransposedtable — Function

gettransposedtable(
    sheet,
    [rows];
    [first_column],
    [column_labels],
    [header],
    [normalizenames]
) -> DataTable

Read a transposed table from a worksheet in which data are arranged in rows rather than columns. For example:

Category    "A", "B", "C", "D"
variable 1  10,  20,  30,  40
variable 2  15,  25,  35,  40
variable 3  20,  30,  40,  50

Returns data from a worksheet as a struct XLSX.DataTable which can be passed directly to any function that accepts Tables.jl data. (e.g. DataFrame from package DataFrames.jl).

Use the rows argument to specify which worksheeet rows to include. For example, "2:7" will select rows 2 to 7 (inclusive). If rows is not given, the algorithm will find the first sequence of consecutive non-empty cells. If rows includes leading or trailing rows that are completely empty, these rows will be omitted from the returned table. In any case, the table will be truncated at the first and last non-empty rows, even if this range is smaller than rows. A valid sheet must be specified when specifying rows.

Use first_column to indicate the first column of the table. May be given as a column number or as a string, so that first_column="E" and first_column=5 will both look for a table starting at column 5 ("E"). Any leading completely empty columns will be ignored, including the first_column. If first_column is not given, the algorithm will look for the first non-empty column in the spreadsheet.

header is a Bool indicating if the first row is a header. If header=true and column_labels is not specified, the column labels for the table will be read from the first column of the table. If header=false and column_labels is not specified, the algorithm will generate column labels. The default value is header=true.

Use column_labels as a vector of symbols to specify names for the header of the table. If header=true and column_labels is also given, column_labels will be preferred and the first column of the table will be ignored.

Use normalizenames=true to normalize column names to valid Julia identifiers. The default is normalizenames=false.

Examples

julia> using DataFrames, PrettyTables, XLSX

julia> xf = XLSX.openxlsx("HTable.xlsx")
XLSXFile("HTable.xlsx") containing 4 Worksheets
            sheetname size          range
-------------------------------------------------
               Origin 6x10          B2:K7
               Offset 8x12          A1:L8
             Multiple 8x22          A1:V8
              Example 4x5           B2:F5
              
julia> DataFrame(XLSX.gettransposedtable(xf["Example"]))
4×4 DataFrame
 Row │ Category  Variable 1  Variable 2  Variable 3 
     │ String    Int64       Int64       Int64
─────┼──────────────────────────────────────────────
   1 │ A                 10          15          20
   2 │ B                 20          25          30
   3 │ C                 30          35          40
   4 │ D                 40          40          50

julia> PrettyTable(XLSX.gettransposedtable(xf["Example"]; normalizenames=true))
┌──────────┬────────────┬────────────┬────────────┐
│ Category │ Variable_1 │ Variable_2 │ Variable_3 │
├──────────┼────────────┼────────────┼────────────┤
│        A │         10 │         15 │         20 │
│        B │         20 │         25 │         30 │
│        C │         30 │         35 │         40 │
│        D │         40 │         40 │         50 │
└──────────┴────────────┴────────────┴────────────┘

julia> DataFrame(gettransposedtable(xf["Example"]; header=false))
5×4 DataFrame
 Row │ Col_1     Col_2       Col_3       Col_4      
     │ String    Any         Any         Any
─────┼──────────────────────────────────────────────
   1 │ Category  Variable 1  Variable 2  Variable 3
   2 │ A         10          15          20
   3 │ B         20          25          30
   4 │ C         30          35          40
   5 │ D         40          40          50

The worksheet Multiple contains two tables side by side, separated by an empty column. Only the first table is read by default. Read the second table by additionally specifying the first_column.

julia> DataFrame(XLSX.gettransposedtable(xf["Multiple"], "2:7"))
9×6 DataFrame
 Row │ Year   Col A  Col B  Col C  Col D    Col E      
     │ Int64  Int64  Int64  Int64  Float64  Any
─────┼─────────────────────────────────────────────────
   1 │  1940      1     10    100      0.1  Hello
   2 │  1950      2     20    200      0.2  2025-12-19
   3 │  1960      3     30    300      0.3  3
   4 │  1970      4     40    400      0.4  3.33
   5 │  1980      5     50    500      0.5  Hello
   6 │  1990      6     60    600      0.6  2025-12-19
   7 │  2000      7     70    700      0.7  3
   8 │  2010      8     80    800      0.8  3.33
   9 │  2020      9     90    900      0.9  true

julia> DataFrame(XLSX.gettransposedtable(xf["Multiple"], "2:7"; first_column="M"))
9×6 DataFrame
 Row │ date   name1    name2    name3  name4     name5      
     │ Int64  Float64  Float64  Bool   Time      Any
─────┼──────────────────────────────────────────────────────
   1 │  1840     12.4    0.045   true  10:22:00  Hello
   2 │  1841     12.6    0.046   true  10:23:00  2025-12-19
   3 │  1842     12.8    0.047  false  10:24:00  3
   4 │  1843     13.0    0.048   true  10:25:00  3.33
   5 │  1844     13.2    0.049  false  10:26:00  Hello
   6 │  1845     13.4    0.05    true  10:27:00  2025-12-19
   7 │  1846     13.6    0.051   true  10:28:00  3
   8 │  1847     13.8    0.052   true  10:29:00  3.33
   9 │  1848     14.0    0.053  false  10:30:00  true

source

XLSX.readtransposedtable — Function

readtransposedtable(
    source,
    [sheet,
    [rows]];
    [first_column],
    [column_labels],
    [header],
    [normalizenames]
) -> DataTable

Read a transposed table from an Excel file, source, in which data are arranged in rows rather than columns in a worksheet. For example:

Category      "A", "B", "C", "D"
"variable 1"  10,  20,  30,  40
"variable 2"  15,  25,  35,  40
"variable 3"  20,  30,  40,  50

Returns data from a worksheet as a struct XLSX.DataTable which can be passed directly to any function that accepts Tables.jl data. (e.g. DataFrame from package DataFrames.jl).

If sheet is not given, the first sheet in the XLSXFile will be used.

Use normalizenames=true to normalize column names to valid Julia identifiers. The default is normalizenames=false.

Examples

julia> using DataFrames, XLSX, PrettyTables

julia> DataFrame(readtransposedtable("HTable.xlsx", "Example"))
4×4 DataFrame
 Row │ Category  Variable 1  Variable 2  Variable 3 
     │ String    Int64       Int64       Int64
─────┼──────────────────────────────────────────────
   1 │ A                 10          15          20
   2 │ B                 20          25          30
   3 │ C                 30          35          40
   4 │ D                 40          40          50

julia> PrettyTable(readtransposedtable("HTable.xlsx", "Multiple", "2:7"; first_column=13))
┌──────┬───────┬───────┬───────┬──────────┬────────────┐
│ date │ name1 │ name2 │ name3 │    name4 │      name5 │
├──────┼───────┼───────┼───────┼──────────┼────────────┤
│ 1840 │  12.4 │ 0.045 │  true │ 10:22:00 │      Hello │
│ 1841 │  12.6 │ 0.046 │  true │ 10:23:00 │ 2025-12-19 │
│ 1842 │  12.8 │ 0.047 │ false │ 10:24:00 │          3 │
│ 1843 │  13.0 │ 0.048 │  true │ 10:25:00 │       3.33 │
│ 1844 │  13.2 │ 0.049 │ false │ 10:26:00 │      Hello │
│ 1845 │  13.4 │  0.05 │  true │ 10:27:00 │ 2025-12-19 │
│ 1846 │  13.6 │ 0.051 │  true │ 10:28:00 │          3 │
│ 1847 │  13.8 │ 0.052 │  true │ 10:29:00 │       3.33 │
│ 1848 │  14.0 │ 0.053 │ false │ 10:30:00 │       true │
└──────┴───────┴───────┴───────┴──────────┴────────────┘

source

XLSX.writetable — Function

writetable(filename, table; [overwrite], [sheetname])

Write a Tables.jl compatible table as an Excel file with the specified file name (and sheet name, if specified).

If a file with the given name already exists, writing will fail unless overwrite=true is specified, in which case the existing file will be overwritten.

source

writetable(filename::Union{AbstractString, IO}, tables::Vector{Pair{String, T}}; overwrite::Bool=false)
writetable(filename::Union{AbstractString, IO}, tables::Pair{String, Any}...; overwrite::Bool=false)

source

writetable(filename, data, columnnames; [overwrite], [sheetname], [anchor_cell], [as_table], [table_name], [table_style], [totals])

data is a vector of columns.
columnames is a vector of column labels.
overwrite is a Bool to control if filename should be overwritten if already exists.
sheetname is the name for the worksheet.
as_table is a Bool to turn the written range into an Excel Table if true.
table_name is the name of the Excel Table (if as_table=true)
table_style is the Ezxcel style to use for the Table (if as_table=true)
totals defines whether and how Table totals are defined.

For more details on table_name, table_style, totals, refer to XLSX.writetable!, XLSX.addtable! and XLSX.settotals!

Returns the filepath of the written file if a filename is supplied, or nothing if writing to an IO.

Example

import XLSX
columns = [ [1, 2, 3, 4], ["Hey", "You", "Out", "There"], [10.2, 20.3, 30.4, 40.5] ]
colnames = [ "integers", "strings", "floats" ]
XLSX.writetable("table.xlsx", columns, colnames)

julia> XLSX.writetable("table.xlsx", columns, colnames; as_table=true, table_name="MyData")

julia> XLSX.writetable("table.xlsx", columns, colnames;
           as_table=true, table_name="MyData",
           totals=["integers" => :sum, "strings" => "Total"])

source

writetable(filename::Union{AbstractString, IO}; overwrite::Bool=false, as_table::Bool=false, table_style=nothing, kw...)

Write multiple tables.

kw is a variable keyword argument list. Each element should be in this format: sheetname=( data, column_names ), where data is a vector of columns and column_names is a vector of column labels.

Set as_table=true to also turn each sheet's written range into an Excel Table. table_style (if given) is applied to every table the same way.

When as_table=true, each table's name defaults to its sheet's name, if that's a valid Excel Table name (no spaces, starts with a letter or underscore) and doesn't collide with an existing defined name. Otherwise, a warning is issued and an auto-generated name ("Table1", "Table2", ...) is used instead — sheet names and table names don't share the same character rules or, in the case of defined names, the same namespace guarantees.

Example:

julia> import DataFrames, XLSX

julia> df1 = DataFrames.DataFrame(COL1=[10,20,30], COL2=["Fist", "Sec", "Third"])

julia> df2 = DataFrames.DataFrame(AA=["aa", "bb"], AB=[10.1, 10.2])

julia> XLSX.writetable("report.xlsx", "REPORT_A" => df1, "REPORT_B" => df2)

julia> XLSX.writetable("report.xlsx", "REPORT_A" => df1, "REPORT_B" => df2;
           as_table=true, table_style="TableStyleMedium2")

source

writetable(filename::Union{AbstractString, IO}, tables::Vector{Tuple{String, Vector{Any}, Vector{String}}}; overwrite::Bool=false, as_table::Bool=false, table_style=nothing)

Write multiple tables.

Each element of tables is (sheetname, data, column_names), where data is a vector of columns and column_names is a vector of column labels.

Set as_table=true to also turn each sheet's written range into an Excel Table. table_style (if given) is applied to every table the same way.

Example

julia> XLSX.writetable("report.xlsx", [
           ("REPORT_A", columns_a, colnames_a),
           ("REPORT_B", columns_b, colnames_b),
       ]; as_table=true)

source

XLSX.writetable! — Function

writetable!(sheet::Worksheet, table; anchor_cell::CellRef=CellRef("A1")))

Write a Tables.jl compatible table to the specified sheet starting with the anchor cell (if given) in the top left.

source

writetable!(
    sheet::Worksheet,
    data,
    columnnames;
    anchor_cell::CellRef=CellRef("A1"),
    write_columnnames::Bool=true,
    as_table::Bool=false,
    table_name::AbstractString="",
    table_style::Union{AbstractString,Nothing}=nothing,
    totals::Union{Nothing,AbstractVector{<:Pair}}=nothing,
)

Write tabular data data with labels given by columnnames to sheet, starting at anchor_cell.

data must be a vector of columns. columnnames must be a vector of column labels.

Column labels that are not of type String will be converted to strings before writing. Any data columns that are not of type String, Float64, Int64, Bool, Date, Time, DateTime, Missing, or Nothing will be converted to strings before writing.

Set as_table=true to also turn the written range into an Excel Table (equivalent to calling XLSX.addtable! immediately afterward over exactly the range just written). Requires write_columnnames=true (a table needs a header row) and at least one data row. table_name and table_style are forwarded to addtable!'s name and style keywords — see its docstring for accepted values, including the list of Excel's built-in table style names.

Use totals to also set a totals row on the table in the same call — equivalent to calling XLSX.settotals! immediately afterward. totals is a vector of "ColumnName" => value pairs, using the same value forms settotals! accepts (a Symbol for a built-in function, a (:custom, formula) tuple, or a String label). Requires as_table=true.

Examples

julia> using DataFrames

julia> df = DataFrame(id=[1, 2], name=["alice", "bob"], score=[10.5, 20.0])

julia> XLSX.writetable!(sheet, collect(eachcol(df)), names(df);
           as_table=true, table_name="Results", table_style="TableStyleMedium2",
           totals=["id" => "Total", "score" => :sum])

source

Cell formulas

XLSX.setFormula — Function

setFormula(ws::Worksheet, RefOrRange::AbstractString, formula::AbstractString; raw=false, spill=false)
setFormula(xf::XLSXFile,  RefOrRange::AbstractString, formula::AbstractString; raw=false, spill=false)

setFormula(sh::Worksheet, row, col, formula::AbstractString; raw=false, spill=false)

Set the Excel formula to be used in the given cell or cell range.

Formulae must be valid Excel formulae and written in US english with comma separators. Cell references may be absolute or relative references in either the row or the column or both (e.g. $A$2). No validation of the specified formula is made by XLSX.jl and formulae are usually stored verbatim, as given.

Non-contiguous ranges are not supported by setFormula. Set the formula in each cell or contiguous range separately.

Use raw=true if entering a formula in xml-ready format to prevent any processing by setFormula.

Use spill=true to force the formula to be treated as an array formula that spills and spill=false to prevent it being treated as such. By default spill=nothing and setFormula will determine whether a formula should spill or not automatically.

Keyword options should be rarely needed - setFormula should handle most formulae.

Since XLSX.jl does not and cannot replicate all the functions built in to Excel, setting a formula in a cell does not permit the cell's value to be re-calculated within XLSX.jl. Instead, although the formula is properly added to the cell, the value is set to missing. However, the saved XLSXFile is set to force Excel to re-calculate on opening.

If a cell spills but any of the cells in the spill range already contains a value, Excel will show a #SPILL error.

More details can be found in the section Using Formulas.

Defined names

XLSX.addDefinedName — Function

addDefinedName(xf::XLSXFile,  name::AbstractString, value::Union{Int, Float64, String}; absolute=true)
addDefinedName(xf::XLSXFile,  name::AbstractString, value::AbstractString; absolute=true)
addDefinedName(sh::Worksheet, name::AbstractString, value::Union{Int, Float64, String}; absolute=true)
addDefinedName(sh::Worksheet, name::AbstractString, value::AbstractString; absolute=true)

Add a defined name to the Workbook or Worksheet. If an XLSXFile is passed, the defined name is added to the Workbook. If a Worksheet is passed, the defined name is added to the Worksheet.

When adding defined name referring to a cell or range to a workbook, value must include the sheet name (e.g. Sheet1!A1:B2).

If the new definedName is a cell reference or range, by default, it will be an absolute reference (e.g. $A$1:$C$6). If absolute=false is specified, the new definedName will be a relative reference (e.g. A1:C6). Any absolute argument specified is ignored if the definedName is not a cell reference or range.

In the context of XLSX.jl there is no difference between an absolute reference and a relative reference. However, Excel treats them differently. When definedNames are read in as part of an XLSXFile, we keep track of whether they are absolute or not. If the XLSXFile is subsequently written out again, the status of the definedNames is preserved.

Examples

julia> XLSX.addDefinedName(sh, "ID", "C21")

julia> XLSX.addDefinedName(sh, "NEW", "A1:B2")

julia> XLSX.addDefinedName(sh, "my_name", "A1,B2,C3")

julia> XLSX.addDefinedName(xf, "New", "'Mock-up'!A1:B2")

julia> XLSX.addDefinedName(xf, "Life_the_universe_and_everything", 42)

julia> XLSX.addDefinedName(xf, "first_name", "Hello World")

source