Tables

XLSX.DataTable — Type

XLSX.DataTable is a simple Tables.jl compatibledata structure to hold tabular data extracted from an Excel worksheet.

It is created by XLSX.gettable or, direct from a file, with XLSX.readtable.

Pass a DataTable to any Tables.jl compatible sink, e.g. DataFrame(dt).

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 has no effect on readtable and is kept for compatibility. readtable reads the values it needs straight from the worksheet, without filling the worksheet cache, which is faster and uses less memory. It decodes only the cells in the table's columns and rows (and the one row after the table that shows where it ends), so an invalid cell anywhere else in the sheet does not raise an error, as it does when the sheet is read through XLSX.readxlsx or XLSX.openxlsx.

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.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!(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

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).

ref must not overlap a merged range or another table on the sheet. Excel discards such a table when it opens the file, so an error is thrown instead.

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. Nor may a name be a cell address, in either reference style and in any case (T1, xfd1, R1C1, RC, or just R or C): Excel refuses a file with such a table.

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. With has_totals_row=true the last row of ref must be empty: if it already has content, an error is thrown and no table is added. (Excel refuses a file whose totals row holds plain values.)

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.appendtable! — Function

appendtable!(t::Table, data; kw...) -> Table
appendtable!(sheet::Worksheet, name::AbstractString, data; [check_empty]) -> Table
appendtable!(sheet::Worksheet, id::Integer, data; kw...) -> Table

Append rows to the existing Excel Table. Provide the Table t directly or specify the table in sheet by its name or workbook-scoped numeric id.

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.deletetable! — Function

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

Delete the given Excel Table, provided directly as t or by fron sheet by its name or 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!(t::Table, settings::Pair...)
settotals!(t::Table; kwargs...)
settotals!(sheet::Worksheet, name::AbstractString, settings::Pair...)
settotals!(sheet::Worksheet, name::AbstractString; kwargs...)
settotals!(sheet::Worksheet, id::Integer, settings::Pair...)
settotals!(sheet::Worksheet, id::Integer; kwargs...)

Add or update the totals row for the Excel Table t or the 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.gettablerange — Function

gettablerange(t::Table) -> SheetCellRange
gettablerange(t::Table, column; header=false) -> Union{SheetCellRange,SheetCellRef}
gettablerange(t::Table, first, last) -> SheetCellRange

The cells of a table's data rows as a sheet-qualified range, excluding the header row and any totals row: the whole table, one column, or the adjacent columns from first to last. A column is given by name or by its 1-based position in the table. With header = true, the one-column form returns that column's header cell.

The range is the table's extent when this is called; it does not follow the table if rows are added later. The table is looked up afresh, so t may be one obtained before the table was changed.

These are the ranges to pass to a chart:

t = table(ws, "Sales")
addSeries(c, gettablerange(t, "Amount"); categories = gettablerange(t, "Region"),
          name_ref = gettablerange(t, "Amount"; header = true))

source

XLSX.gettotals — Function

gettotals(t::Table) -> NamedTuple
gettotals(sheet::Worksheet, name::AbstractString)
gettotals(sheet::Worksheet, id::Integer)

Return the Excel Table's totals row as a NamedTuple keyed by column name. Provide the Table t directly or specify the table in sheet by its name or workbook-scoped numeric id. Each entry is itself a NamedTuple with fields:

setting: the column's totals setting — a Symbol naming a built-in function (:sum, :average, …), :custom for a custom formula, :label for a text label, or :none if the column has no totals setting.
formula: the totals cell's formula as a String, or nothing if the cell holds no formula.
value: the totals cell's current value. For a label column this is the label text. For a function or custom column this is missing unless the file was written by Excel (XLSX.jl does not evaluate formulas, so a totals cell it wrote holds no cached value until Excel recalculates and saves the file).

Returns an empty NamedTuple if the Table has no totals row.

Example

julia> tot = XLSX.gettotals(t)
(region = (setting = :label, formula = nothing, value = "Total"), revenue = (setting = :sum, formula = "=SUBTOTAL(109,Sales[revenue])", value = 3400), margin = (setting = :average, formula = "=SUBTOTAL(101,Sales[margin])", value = 243.33))

julia> tot.revenue.setting
:sum

julia> tot.revenue.value
3400

gettotals requires the XLSXFile to have enable_cache=true (the default). It throws otherwise.

source

XLSX.removetotals! — Function

removetotals!(t::Table) -> Table
removetotals!(sheet::Worksheet, name::AbstractString) -> Table
removetotals!(sheet::Worksheet, id::Integer) -> Table

Remove the totals row from the Excel Table given directly as t or by name or id on sheet, shrinking the Table by one row. Every column's totals function or label is cleared, and the cells that held the totals row are emptied.

Does nothing if the Table has no totals row.

source