Sheet layout

Column width and row height

Guide: Column width and row height.

XLSX.getColumnWidth — Function
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)
 
source
XLSX.setColumnWidth — Function
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.setColumnWidth(sh, "F1:F5"; width = 0)

julia> XLSX.setColumnWidth(sh, "I"; width = 24.37)
 
source
XLSX.autofitColumns — Function
autofitColumns(sh::Worksheet, [cols=:]; min_width=0, max_width=255) -> Dict{Int,Float64}

Set the width of columns to fit the text their cells show, as Excel's Format → AutoFit Column Width does. The text is what XLSX.getText gives: a cell's value in its number format, so a date or a number with separators is measured as it is shown. Each cell's font, size and boldness are taken into account.

cols is : (every column with something in it), a column number, a range or vector of numbers, or letters such as "B", "B:D" or "A,C:E". Widths are in characters, as XLSX.setColumnWidth takes them, and are kept between min_width and max_width (at most 255, Excel's widest). A column whose cells show nothing keeps its width. Returns the width set for each column, by column number.

The widths are approximate. Characters are measured with the widths Excel 365 uses for Calibri, Aptos, Aptos Narrow, Arial, Times New Roman and Courier New (other fonts as the nearest of these by name, or else Calibri), which were measured at one screen resolution; Excel's own autofit varies a little with the screen's. Cells in merged ranges are left out, as Excel leaves them out. A cell with several lines is measured by its longest. Rich text is measured in the cell's own font, and indents and rotated text are not allowed for.

Examples

julia> XLSX.autofitColumns(sh)                   # every column

julia> XLSX.autofitColumns(sh, "B:D"; max_width=40)

julia> XLSX.autofitColumns(sh, [1, 3]; min_width=8)
source
XLSX.getRowHeight — Function
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)
 
source
XLSX.setRowHeight — Function
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.

Rows needn't hold any data: an empty row is written with just its height.

You can set a row height 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.

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

Hiding and grouping rows and columns

Guide: Hiding and grouping rows and columns.

XLSX.setColumnHidden — Function
setColumnHidden(ws::Worksheet, cols; hidden=true)
setColumnHidden(xf::XLSXFile, "Sheet1!B:D"; hidden=true)

Hide the columns cols, or show them again with hidden=false.

cols may be a column number, a range or vector of column numbers, or a string naming columns ("B", "B:D"), cells ("B2:D5", whose columns are used) or a defined name, or several of these separated by commas ("B:C,F"). Columns needn't hold any data.

The file must be open for writing (mode="rw").

Examples

julia> XLSX.setColumnHidden(sheet, "C:D")

julia> XLSX.setColumnHidden(sheet, [2, 5, 6])

julia> XLSX.setColumnHidden(sheet, 3; hidden=false)

See also setRowHidden, isColumnHidden and groupColumns.

source
XLSX.setRowHidden — Function
setRowHidden(ws::Worksheet, rows; hidden=true)
setRowHidden(xf::XLSXFile, "Sheet1!3:5"; hidden=true)

Hide the rows rows, or show them again with hidden=false.

rows may be a row number, a range or vector of row numbers, or a string naming rows ("3", "3:5"), cells ("A3:D5", whose rows are used) or a defined name, or several of these separated by commas ("3:4,8"). Rows needn't hold any data.

The file must be open for writing (mode="rw").

Examples

julia> XLSX.setRowHidden(sheet, 2:4)

julia> XLSX.setRowHidden(sheet, "7"; hidden=false)

See also setColumnHidden, isRowHidden and groupRows.

source
XLSX.isColumnHidden — Function
isColumnHidden(ws::Worksheet, col) -> Bool
isColumnHidden(xf::XLSXFile, "Sheet1!C") -> Bool

Whether column col is hidden. col is a column number or a string naming a column ("C") or a cell in it ("C5").

See also setColumnHidden.

source
XLSX.isRowHidden — Function
isRowHidden(ws::Worksheet, row) -> Bool
isRowHidden(xf::XLSXFile, "Sheet1!5") -> Bool

Whether row row is hidden. row is a row number or a string naming a row ("5") or a cell in it ("C5").

Rows can be read from a file opened only for reading if the cell cache is enabled, as it is by default.

See also setRowHidden.

source
XLSX.groupColumns — Function
groupColumns(ws::Worksheet, cols; collapsed=false)
groupColumns(xf::XLSXFile, "Sheet1!B:D"; collapsed=false)

Group the columns cols, as Excel's Data → Group does, adding the outline control that hides and shows them. cols is given as for setColumnHidden; each run of adjacent columns in it becomes a group.

Grouping columns that are already grouped puts them one outline level deeper, making a group within a group. Excel allows up to 7 levels.

With collapsed=true the group is shown collapsed: its columns are hidden and the column after it, which carries the outline button, is marked collapsed.

The file must be open for writing (mode="rw").

Examples

julia> XLSX.groupColumns(sheet, "B:D")

julia> XLSX.groupColumns(sheet, "C:D"; collapsed=true)   # a collapsed group within B:D

See also ungroupColumns, getColumnOutlineLevel and groupRows.

source
XLSX.groupRows — Function
groupRows(ws::Worksheet, rows; collapsed=false)
groupRows(xf::XLSXFile, "Sheet1!3:5"; collapsed=false)

Group the rows rows, as Excel's Data → Group does, adding the outline control that hides and shows them. rows is given as for setRowHidden; each run of adjacent rows in it becomes a group.

Grouping rows that are already grouped puts them one outline level deeper, making a group within a group. Excel allows up to 7 levels.

With collapsed=true the group is shown collapsed: its rows are hidden and the row below it, which carries the outline button, is marked collapsed.

The file must be open for writing (mode="rw").

Examples

julia> XLSX.groupRows(sheet, 2:10)

julia> XLSX.groupRows(sheet, 3:5; collapsed=true)   # a collapsed group within 2:10

See also ungroupRows, getRowOutlineLevel and groupColumns.

source
XLSX.ungroupColumns — Function
ungroupColumns(ws::Worksheet, cols)
ungroupColumns(xf::XLSXFile, "Sheet1!B:D")

Move the columns cols out one outline level, undoing groupColumns. Columns not in a group are left alone. Columns hidden by a collapsed group stay hidden: show them with setColumnHidden(ws, cols; hidden=false).

The file must be open for writing (mode="rw").

source
XLSX.ungroupRows — Function
ungroupRows(ws::Worksheet, rows)
ungroupRows(xf::XLSXFile, "Sheet1!3:5")

Move the rows rows out one outline level, undoing groupRows. Rows not in a group are left alone. Rows hidden by a collapsed group stay hidden: show them with setRowHidden(ws, rows; hidden=false).

The file must be open for writing (mode="rw").

source
XLSX.getColumnOutlineLevel — Function
getColumnOutlineLevel(ws::Worksheet, col) -> Int
getColumnOutlineLevel(xf::XLSXFile, "Sheet1!C") -> Int

The outline level of column col: 0 if it isn't grouped, 1 in a group, 2 in a group within a group, and so on. col is given as for isColumnHidden.

See also groupColumns.

source
XLSX.getRowOutlineLevel — Function
getRowOutlineLevel(ws::Worksheet, row) -> Int
getRowOutlineLevel(xf::XLSXFile, "Sheet1!5") -> Int

The outline level of row row: 0 if it isn't grouped, 1 in a group, 2 in a group within a group, and so on. row is given as for isRowHidden.

See also groupRows.

source

Merged Cells

Guide: Working with merged cells.

XLSX.getMergedCells — Function
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 Worksheet: ["Sheet1"](A1:B2)

julia> XLSX.getMergedCells(s)
1-element Vector{XLSX.CellRange}:
 B1:B2
 
source
XLSX.isMergedCell — Function
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.

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)
 
source
XLSX.getMergedBaseCell — Function
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. 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)
source
XLSX.mergeCells — Function
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.
source
XLSX.removeMergedCells — Function
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.
source

Freeze/Split panes

Guide: Freeze and split panes.

XLSX.freezePanes — Function
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. The panes count from row 1 and column A, so a view scrolled elsewhere (see setSheetView) is scrolled back to the top.

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.

source
XLSX.splitFreeze — Function
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.

source
XLSX.splitPanes — Function
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.

Approximate divider placement

The requested boundary is converted to a twips position by summing actual column widths / row heights (falling back to sheetFormatPr defaults, then Excel's built-in defaults). The column-width-to-pixel step assumes a Maximum Digit Width of 7 (Calibri 11 / Arial 10 @ 96dpi). The divider will be very close to the requested cell boundary but not necessarily exact.

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

source
XLSX.removePanes — Function
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.

source

Sheet view

Guide: Zoom, gridlines and the view.

XLSX.setSheetView — Function
setSheetView(ws::Worksheet; zoom, view, showGridLines, showRowColHeaders, showZeros,
             rightToLeft, topLeftCell, activeCell, selection)

Set how a worksheet looks when Excel opens it, as Excel's View tab does. Only the keywords given are changed.

  • zoom: the zoom, in percent, from 10 to 400. Excel keeps a zoom for each view; this sets the zoom of the view the sheet is in.
  • view: :normal, :pageLayout or :pageBreakPreview. A view takes back the zoom it had before.
  • showGridLines, showRowColHeaders: show the gridlines, and the row and column headings (1, 2, 3... and A, B, C...), on screen. For printing them, see setPrintOptions.
  • showZeros: show a cell whose value is zero as 0, rather than empty.
  • rightToLeft: show column A at the right, for right-to-left languages.
  • topLeftCell: scroll so that this cell is at the top left of the window. With frozen panes, it is the top-left cell of the part that scrolls, so it must be below and to the right of the frozen rows and columns.
  • selection: the selected cells: a cell or range ("B2:D5"), several separated by commas ("B2:D5,F7"), or a defined name for them.
  • activeCell: the cell with the cursor, which must be in the selection. Given alone, it is selected on its own; a selection alone has its first cell active. With frozen or split panes, the selection is the active pane's, as in Excel.

Cells are given as a string ("C10") or a CellRef.

The file must be open for writing (mode="rw").

Examples

julia> XLSX.setSheetView(sheet; zoom=150, showGridLines=false)

julia> XLSX.setSheetView(sheet; selection="B2:D5", activeCell="C3", topLeftCell="A1")

See also getSheetView and freezePanes.

source
XLSX.getSheetView — Function
getSheetView(ws::Worksheet) -> NamedTuple

How a worksheet looks when Excel opens it: a NamedTuple with fields zoom, view, showGridLines, showRowColHeaders, showZeros, rightToLeft, topLeftCell, activeCell and selection, described in setSheetView. topLeftCell and activeCell are CellRefs, and selection is a string such as "B2:D5,F7". A setting the sheet doesn't set has Excel's default.

Works on files opened for reading only.

Examples

julia> XLSX.getSheetView(sheet).zoom
150
source

AutoFilter

Guide: Filtering.

XLSX.setAutoFilter — Function
setAutoFilter(ws::Worksheet, rng; filters=nothing, sort=nothing)
setAutoFilter(xf::XLSXFile, "Sheet1!A1:D20"; kw...)

Add filter buttons to the first row of rng, as Excel's Data → Filter does, so its columns can be filtered and sorted in Excel. rng is a cell range ("A1:D20"), a defined name, or : for the sheet's whole used range. Its first row is the header row.

A sheet has one filter, so this replaces any filter the sheet already has. The filter may not overlap an Excel Table, which has a filter of its own (see addtable!). Setting the filter again on the same range keeps its criteria and sort, unless new ones are given.

Filtering

filters gives criteria for some of the columns, as pairs of a column and a criterion. A column is given by its header text, its column letter or its position in the range (1 for the first). Rows a criterion doesn't show are hidden, and the others shown, so the file opens filtered, as Excel would have saved it. A row is shown if it meets the criteria of every filtered column. A criterion is one of:

  • a value, or a vector of values, to show: text (in any case, as the cell shows it), numbers, dates (by the day), true/false, and missing for empty cells.
  • a condition, op => value, where op is "==", "!=", ">", ">=", "<" or "<=" and value a number, a date or text. Text may use Excel's wildcards: * for any characters, ? for any one, and ~ before one of them for itself, so "==" => "A*" shows text beginning with A.
  • two conditions joined by :and or :or: (">=" => 10, :and, "<" => 20).
  • (:top, n) or (:bottom, n) for the n largest or smallest numbers, or (:top, n, :percent) for the top n percent.
  • :aboveAverage or :belowAverage.
  • a date relative to today: :today, :yesterday, :tomorrow, :thisWeek, :lastWeek, :nextWeek, :thisMonth, :lastMonth, :nextMonth, :thisQuarter, :lastQuarter, :nextQuarter, :thisYear, :lastYear, :nextYear or :yearToDate; or a quarter or month of any year, :Q1 to :Q4 or :M1 to :M12.

filters=[] removes the criteria and shows every row of the range.

Sorting

sort gives a column, column => :ascending or column => :descending, or a vector of these, the first sorting first. The rows below the header are put in that order, as Excel's sort puts them: numbers and dates, then text (in any case), then TRUE/FALSE, then errors, and empty cells last in either order. Each row's cells in the range move with their formats, notes and hyperlinks, and formulas move as Excel moves them, as if copied, so =B5*2 in row 5 becomes =B2*2 in row

  1. Rows a filter hides are sorted too, and stay hidden. Cells outside the range's

columns stay where they are. A range with merged cells, or with array formulas or shared formulas reaching outside it, can't be sorted. The sort is recorded, so Excel shows the sort arrow on the header.

The file must be open for writing (mode="rw").

Examples

julia> XLSX.setAutoFilter(sheet, "A1:F200")

julia> XLSX.setAutoFilter(sheet, :)

julia> XLSX.setAutoFilter(sheet, "A1:D50";
           filters = ["Colour" => ["Red", "Blue"], "Amount" => (">" => 100)],
           sort = "Amount" => :descending)

See also getAutoFilter and removeAutoFilter.

source
XLSX.getAutoFilter — Function
getAutoFilter(ws::Worksheet) -> Union{Nothing, CellRange}

The range of the sheet's filter (see setAutoFilter), or nothing if it has none. The filters of Excel Tables are not included.

source
XLSX.removeAutoFilter — Function
removeAutoFilter(ws::Worksheet) -> Bool

Remove the sheet's filter, its criteria and its sort. Returns false if it had none. Rows the filter hid stay hidden; show them with setRowHidden.

The file must be open for writing (mode="rw").

source

Inserting and deleting rows and columns

Guide: Inserting and deleting rows and columns.

XLSX.insertRows — Function
insertRows(ws::Worksheet, at; count=1)
insertRows(xf::XLSXFile, "Sheet1!5"; count=1)

Insert count empty rows before row at, as Excel's Insert Sheet Rows does. Rows from at down move down, and everything that refers to them follows: formulas anywhere in the workbook, merged cells, conditional formats, data validations, hyperlinks, the AutoFilter, panes, defined names (including the print area and titles), chart series, and the Excel Tables, images, charts and comments on the sheet. A range that spans row at grows to include the new rows.

at is a row number or a string naming one ("5"). The new rows are empty, and formatted like the row above as Excel formats them; see How new rows and columns are formatted. Inserting rows that would push data past the last row Excel allows is an error, as in Excel.

The file must be open for writing (mode="rw").

Examples

julia> XLSX.insertRows(sheet, 5)            # a new row 5

julia> XLSX.insertRows(sheet, 2; count=3)   # three new rows, 2 to 4

See also deleteRows, insertColumns and deleteColumns.

source
XLSX.deleteRows — Function
deleteRows(ws::Worksheet, rows)
deleteRows(xf::XLSXFile, "Sheet1!3:5")

Delete rows, as Excel's Delete Sheet Rows does. Rows below move up, and everything that refers to them follows, as for insertRows. A reference to a deleted cell becomes #REF!; a range that loses some of its rows shrinks, and one that loses all of them goes too (a merged range, a conditional format, a data validation, a hyperlink, the AutoFilter, an Excel Table), or becomes #REF! in a formula or defined name. An image or chart set to move and size with its cells (Excel's default for a chart) goes too when all its rows are deleted; one that only moves, or doesn't, is kept. Deleting a table's header row, its totals row or all its data rows, without the rest of it, is an error.

rows is a row number, a range or vector of row numbers, or a string naming rows ("3:5", "3:5,9"), cells (whose rows are used) or a defined name.

The file must be open for writing (mode="rw").

Examples

julia> XLSX.deleteRows(sheet, 5)

julia> XLSX.deleteRows(sheet, "3:5,9")

See also insertRows and deleteColumns.

source
XLSX.insertColumns — Function
insertColumns(ws::Worksheet, at; count=1)
insertColumns(xf::XLSXFile, "Sheet1!C"; count=1)

Insert count empty columns before column at, as Excel's Insert Sheet Columns does. at is a column number or letters ("C"). Everything that refers to the moved columns follows, as for insertRows.

Examples

julia> XLSX.insertColumns(sheet, "C")           # a new column C

julia> XLSX.insertColumns(sheet, 2; count=2)    # new columns B and C

See also deleteColumns and insertRows.

source
XLSX.deleteColumns — Function
deleteColumns(ws::Worksheet, cols)
deleteColumns(xf::XLSXFile, "Sheet1!B:C")

Delete columns, as Excel's Delete Sheet Columns does. Columns to the right move left, and everything that refers to them follows, as for deleteRows.

cols is a column number, a range or vector of column numbers, or a string naming columns ("B", "B:C", "B:C,F"), cells (whose columns are used) or a defined name.

Examples

julia> XLSX.deleteColumns(sheet, "B:C")

julia> XLSX.deleteColumns(sheet, [2, 5])

See also insertColumns and deleteRows.

source