Sheet layout

getColumnWidth(sh::Worksheet, cr::String) -> ::Union{Nothing, Real}
getColumnWidth(xf::XLSXFile,  cr::String) -> ::Union{Nothing, Real}

getColumnWidth(sh::Worksheet,  row::Int, col::Int) -> ::Union{Nothing, Real}

Get the width of a column defined by a cell reference or named cell. The specified cell must be within the sheet dimension.

A standard cell reference or defined name may be used to define the column. The function will use the column number and ignore the row.

The function returns the value of the column width or nothing if the column does not have an explicitly defined width.

Examples:

julia> XLSX.getColumnWidth(xf, "Sheet1!A2")

julia> XLSX.getColumnWidth(sh, "F1")

julia> XLSX.getColumnWidth(sh, 1, 6)
 

setColumnWidth(sh::Worksheet, cr::String; kw...) -> ::Int
setColumnWidth(xf::XLSXFile,  cr::String, kw...) -> ::Int

setColumnWidth(sh::Worksheet, row, col; kw...) -> ::Int
setColumnWidth(sh::Worksheet, col; kw...) -> ::Int

Set the width of a column or column range.

A standard cell reference or cell range can be used to define the column range. The function will use the columns and ignore the rows. Named cells and named ranges can similarly be used. Alternatively, specify the row and column using any combination of Integer, UnitRange, Vector{Integer} or :, but only the columns will be used. The row can be omitted and column specified alone, using an integer or colon.

The function uses one keyword used to define a column width:

• width::Real = nothing : Defines width in Excel's own (internal) units

When you set a column widths interactively in Excel you can see the width in "internal" units and in pixels. The width stored in the xlsx file is slightly larger than the width shown intertactively because Excel adds some cell padding. The method Excel uses to calculate the padding is obscure and complex. This function does not attempt to replicate it, but simply adds 0.71 internal units to the value specified. The value set is unlikely to match the value seen interactivley in the resultant spreadsheet, but will be close.

You can set a column width to 0.

The function returns a value of 0. The return value is meaningless but required to comply with the range dispatch infrastructure, which expects an Int return from all set* functions.

setColumnWidth requires a file to be open for writing as well as reading (mode="rw" or open as a template) but it can work on empty cells.

Examples:

julia> XLSX.setColumnWidth(xf, "Sheet1!A2"; width = 50)

julia> XLSX.seColumnWidth(sh, "F1:F5"; width = 0)

julia> XLSX.setColumnWidth(sh, "I"; width = 24.37)
 

getRowHeight(sh::Worksheet, cr::String) -> ::Union{Nothing, Real}
getRowHeight(xf::XLSXFile,  cr::String) -> ::Union{Nothing, Real}

getRowHeight(sh::Worksheet,  row::Int, col::Int) -> ::Union{Nothing, Real}

Get the height of a row defined by a cell reference or named cell. The specified cell must be within the sheet dimension.

A standard cell reference or defined name must be used to define the row. The function will use the row number and ignore the column.

The function returns the value of the row height or nothing if the row does not have an explicitly defined height.

If the row is not found (an empty row), returns -1.

Examples:

julia> XLSX.getRowHeight(xf, "Sheet1!A2")

julia> XLSX.getRowHeight(sh, "F1")

julia> XLSX.getRowHeight(sh, 1, 6)
 

setRowHeight(sh::Worksheet, cr::String; kw...) -> ::Int
setRowHeight(xf::XLSXFile,  cr::String, kw...) -> ::Int

setRowHeight(sh::Worksheet, row, col; kw...) -> ::Int

Set the height of a row or row range.

A standard cell reference or cell range must be used to define the row range. The function will use the rows and ignore the columns. Named cells and named ranges can similarly be used. Alternatively, specify the row and column using any combination of Integer, UnitRange, Vector{Integer} or :, but only the rows will be used.

The function uses one keyword used to define a row height:

• height::Real = nothing : Defines height in Excel's own (internal) units.

When you set row heights interactively in Excel you can see the height in "internal" units and in pixels. The height stored in the xlsx file is slightly larger than the height shown interactively because Excel adds some cell padding. The method Excel uses to calculate the padding is obscure and complex. This function does not attempt to replicate it, but simply adds 0.21 internal units to the value specified. The value set is unlikely to match the value seen interactivley in the resultant spreadsheet, but it will be close.

Row height cannot be set for empty rows, which will quietly be skipped. A row must have at least one cell containing a value before its height can be set.

You can set a row height to 0.

The function returns a value of 0 unless all rows are empty, in which case it returns a value of -1.

Examples:

julia> XLSX.setRowHeight(xf, "Sheet1!A2"; height = 50)

julia> XLSX.setRowHeight(sh, "F1:F5"; height = 0)

julia> XLSX.setRowHeight(sh, "I"; height = 24.56)

getMergedCells(ws::Worksheet) -> Union{Vector{CellRange}, Nothing}

Return a vector of the CellRange of all merged cells in the specified worksheet. Return nothing if the worksheet contains no merged cells.

The Excel file must be opened in write mode to work with merged cells.

Examples:

julia> f = XLSX.readxlsx("test.xlsx")
XLSXFile("C:\Users\...\test.xlsx") containing 1 Worksheet
            sheetname size          range
-------------------------------------------------
               Sheet1 2x2           A1:B2

julia> s = f["Sheet1"]
2×2 XLSX.Worksheet: ["Sheet1"](A1:B2)

julia> XLSX.getMergedCells(s)
1-element Vector{XLSX.CellRange}:
 B1:B2
 

isMergedCell(ws::Worksheet,  cr::String) -> Bool
isMergedCell(xf::XLSXFile,   cr::String) -> Bool

isMergedCell(ws::Worksheet,  row::Int, col::Int) -> Bool

Return true if a cell is part of a merged cell range and false if not. The specified cell must be within the sheet dimension.

Alternatively, if you have already obtained the merged cells for the worksheet, you can avoid repeated determinations and pass them as a keyword argument to the function:

isMergedCell(ws::Worksheet, cr::String; mergedCells::Union{Vector{CellRange}, Nothing, Missing}=missing) -> Bool
isMergedCell(xf::XLSXFile,  cr::String; mergedCells::Union{Vector{CellRange}, Nothing, Missing}=missing) -> Bool

isMergedCell(ws::Worksheet,  row:Int, col::Int; mergedCells::Union{Vector{CellRange}, Nothing, Missing}=missing) -> Bool

The Excel file must be opened in write mode to work with merged cells.

Examples:

julia> XLSX.isMergedCell(xf, "Sheet1!A1")

julia> XLSX.isMergedCell(sh, "A1")

julia> XLSX.isMergedCell(sh, 2, 4) # cell D2

julia> mc = XLSX.getMergedCells(sh)

julia> XLSX.isMergedCell(sh, XLSX.CellRef("A1"), mc)
 

getMergedBaseCell(ws::Worksheet, cr::String) -> Union{Nothing, NamedTuple{CellRef, Any}}
getMergedBaseCell(xf::XLSXFile,  cr::String) -> Union{Nothing, NamedTuple{CellRef, Any}}

getMergedBaseCell(ws::Worksheet, row::Int, col::Int) -> Union{Nothing, NamedTuple{CellRef, Any}}

Return the cell reference and cell value of the base cell of a merged cell range in a worksheet as a named tuple. The specified cell must be within the sheet dimension. If the specified cell is not part of a merged cell range, return nothing.

The base cell is the top-left cell of the merged cell range and is the reference cell for the range.

The tuple returned contains:

• baseCell : the reference (CellRef) of the base cell
• baseValue : the value of the base cell

Additionally, if you have already obtained the merged cells for the worksheet, you can avoid repeated determinations and pass them as a keyword argument to the function:

getMergedBaseCell(ws::Worksheet, cr::String; mergedCells::Union{Vector{CellRange}, Nothing, Missing}=missing) -> Union{Nothing, NamedTuple{CellRef, Any}}
getMergedBaseCell(xf::XLSXFile,  cr::String; mergedCells::Union{Vector{CellRange}, Nothing, Missing}=missing) -> Union{Nothing, NamedTuple{CellRef, Any}}

getMergedBaseCell(ws::Worksheet, row::Int, col::Int; mergedCells::Union{Vector{CellRange}, Nothing, Missing}=missing) -> Union{Nothing, NamedTuple{CellRef, Any}}

The Excel file must be opened in write mode to work with merged cells.

Examples:

julia> XLSX.getMergedBaseCell(xf, "Sheet1!B2")
(baseCell = B1, baseValue = 3)

julia> XLSX.getMergedBaseCell(sh, "B2")
(baseCell = B1, baseValue = 3)

julia> XLSX.getMergedBaseCell(sh, 2, 2)
(baseCell = B1, baseValue = 3)

mergeCells(ws::Worksheet, cr::String) -> 0
mergeCells(xf::XLSXFile,  cr::String) -> 0

mergeCells(ws::Worksheet, row::Int, col::Int) -> 0

Merge the cells in the range given by cr. The value of the merged cell will be the value of the first cell in the range (the base cell) prior to the merge. All other cells in the range will be set to missing, reflecting the behaviour of Excel itself.

Merging is limited to the extent of the worksheet dimension.

The specified range must not overlap with any previously merged cells, nor with an Excel Table (Excel discards a table that overlaps a merged range).

It is not possible to merge a single cell!

A non-contiguous range composed of multiple cell ranges will be processed as a list of separate ranges. Each range will be merged separately. No range within a non-contiguous range may be a single cell.

The Excel file must be opened in write mode to work with merged cells.

Examples:

julia> XLSX.mergeCells(xf, "Sheet1!B2:D3")  # Merge a cell range.

julia> XLSX.mergeCells(sh, 1:3, :)          # Merge rows to the extent of the dimension.

julia> XLSX.mergeCells(sh, "A:D")           # Merge columns to the extent of the dimension.

removeMergedCells(ws::Worksheet, cr::String) -> 0
removeMergedCells(xf::XLSXFile,  cr::String) -> 0

removeMergedCells(ws::Worksheet, row::Int, col::Int) -> 0

removeMergedCells(ws::Worksheet)             -> 0
removeMergedCells(ws::Worksheet, ::Colon)    -> 0

Unmerge merged cells in a worksheet.

A merged range is unmerged if it fully encloses the range given by cr. Naming any single cell of a merged range is therefore enough to unmerge the whole of it, without needing to know its extent. A range larger than the merge encloses nothing and is a no-op: removeMergedCells(sh, "D47:J47") leaves a merged D47:H47 intact.

Because merged ranges cannot overlap one another, a contiguous cr unmerges at most one range. Use the non-contiguous form, or the no-argument form, to unmerge several at once.

Called with no cell reference at all (or with a single Colon), every merged range in the worksheet is unmerged.

Unmerging does not change any cell values, reflecting the behaviour of Excel itself.

If nothing in the worksheet encloses cr, the worksheet is left untouched. Specifying a range that is not merged is not an error it is a no-op.

A non-contiguous range composed of multiple cell ranges will be processed as a list of separate ranges. Each range will be unmerged separately.

The Excel file must be opened in write mode to work with merged cells.

Examples:

julia> XLSX.removeMergedCells(xf, "Sheet1!B2")     # Unmerge the range containing B2.

julia> XLSX.removeMergedCells(sh, "B2:C3")         # Unmerge a range enclosing B2:C3.

julia> XLSX.removeMergedCells(sh, 4, 4)            # Unmerge the range containing D4.

julia> XLSX.removeMergedCells(sh, :, 10)           # Unmerge a whole-column merge in J.

julia> XLSX.removeMergedCells(sh)                  # Unmerge everything in the worksheet.

freezePanes(ws::Worksheet; nrows::Int=1, ncols::Int=0)
freezePanes(ws::Worksheet, anchor_cell::AbstractString)

Freeze the first nrows rows and/or ncols columns of ws so they stay visible while scrolling, or equivalently freeze everything above/left of anchor_cell (the first cell of the scrolling region). Calling this again replaces any existing frozen/split panes.

Examples

julia> XLSX.freezePanes(sh)                       # freeze row 1 (the default)
julia> XLSX.freezePanes(sh; nrows=2, ncols=1)     # freeze first 2 rows and column A
julia> XLSX.freezePanes(sh, "B2")                 # equivalent to nrows=1, ncols=1

The Excel file must be opened in write mode to work with panes.

splitFreeze(ws::Worksheet; nrows::Int=1, ncols::Int=0)
splitFreeze(ws::Worksheet, anchor_cell::AbstractString)

Like freezePanes, but marks the pane as originating from a split (state="frozenSplit") rather than a plain freeze (state="frozen"). This mirrors what Excel writes when a user freezes panes while a draggable split already exists. Functionally very similar to freezePanes for a freshly created pane; the distinction mainly affects how Excel behaves if the pane is later unfrozen interactively.

The Excel file must be opened in write mode to work with panes.

splitPanes(ws::Worksheet; nrows::Int=1, ncols::Int=0)
splitPanes(ws::Worksheet, anchor_cell::AbstractString)

Split ws into a draggable (non-frozen) pane layout at the given row/column boundary.

The Excel file must be opened in write mode to work with panes.

removePanes(ws::Worksheet)

Remove any frozen or split panes from ws, restoring a plain single-pane view. Equivalent to freezePanes(ws; nrows=0, ncols=0).

The Excel file must be opened in write mode to work with panes.