API Reference

XLSXFile represents a reference to an Excel file.

It is created by using XLSX.readxlsx or XLSX.openxlsx.

From a XLSXFile you can navigate to a XLSX.Worksheet reference as shown in the example below.

Example

xf = XLSX.readxlsx("myfile.xlsx")
sh = xf["mysheet"] # get a reference to a Worksheet

readxlsx(source::Union{AbstractString, IO}) :: XLSXFile

Main function for reading an Excel file. This function will read the whole Excel file into memory and return a closed XLSXFile.

Consider using XLSX.openxlsx for lazy loading of Excel file contents.

openxlsx(f::F, source::Union{AbstractString, IO}; mode::AbstractString="r", enable_cache::Bool=true) where {F<:Function}

Open XLSX file for reading and/or writing. It returns an opened XLSXFile that will be automatically closed after applying f to the file.

Do syntax

This function should be used with do syntax, like in:

XLSX.openxlsx("myfile.xlsx") do xf
    # read data from `xf`
end

Filemodes

The mode argument controls how the file is opened. The following modes are allowed:

• r : read mode. The existing data in source will be accessible for reading. This is the default mode.

• w : write mode. Opens an empty file that will be written to source.

• rw : edit mode. Opens source for editing. The file will be saved to disk when the function ends.

Arguments

• source is IO or the complete path to the file.

• mode is the file mode, as explained in the last section.

• enable_cache:

If enable_cache=true, all read worksheet cells will be cached. If you read a worksheet cell twice it will use the cached value instead of reading from disk the second time.

If enable_cache=false, worksheet cells will always be read from disk. This is useful when you want to read a spreadsheet that doesn't fit into memory.

The default value is enable_cache=true.

Examples

Read from file

The following example shows how you would read worksheet cells, one row at a time, where myfile.xlsx is a spreadsheet that doesn't fit into memory.

julia> XLSX.openxlsx("myfile.xlsx", enable_cache=false) do xf
          for r in XLSX.eachrow(xf["mysheet"])
              # read something from row `r`
          end
       end

Write a new file

XLSX.openxlsx("new.xlsx", mode="w") do xf
    sheet = xf[1]
    sheet[1, :] = [1, Date(2018, 1, 1), "test"]
end

Edit an existing file

XLSX.openxlsx("edit.xlsx", mode="rw") do xf
    sheet = xf[1]
    sheet[2, :] = [2, Date(2019, 1, 1), "add new line"]
end

See also XLSX.readxlsx.

openxlsx(source::Union{AbstractString, IO}; mode="r", enable_cache=true) :: XLSXFile

Supports opening a XLSX file without using do-syntax. In this case, the user is responsible for closing the XLSXFile using close or writing it to file using XLSX.writexlsx.

See also XLSX.writexlsx.

writexlsx(output_source, xlsx_file; [overwrite=false])

Writes an Excel file given by xlsx_file::XLSXFile to IO or filepath output_source.

If overwrite=true, output_source (when a filepath) will be overwritten if it exists.

sheetnames(xl::XLSXFile)
sheetnames(wb::Workbook)

Returns a vector with Worksheet names for this Workbook.

sheetcount(xlsfile) :: Int

Counts the number of sheets in the Workbook.

hassheet(wb::Workbook, sheetname::AbstractString)
hassheet(xl::XLSXFile, sheetname::AbstractString)

Returns true if wb contains a sheet named sheetname.

A Worksheet represents a reference to an Excel Worksheet.

From a Worksheet you can query for Cells, cell values and ranges.

Example

xf = XLSX.readxlsx("myfile.xlsx")
sh = xf["mysheet"] # get a reference to a Worksheet
println( sh[2, 2] ) # access element "B2" (2nd row, 2nd column)
println( sh["B2"] ) # you can also use the cell name
println( sh["A2:B4"] ) # or a cell range
println( sh[:] ) # all data inside worksheet's dimension

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

Returns a scalar or matrix with values from a spreadsheet.

See also XLSX.getdata.

Examples

These function calls are equivalent.

julia> XLSX.readdata("myfile.xlsx", "mysheet", "A2:B4")
3×2 Array{Any,2}:
 1  "first"
 2  "second"
 3  "third"

julia> XLSX.readdata("myfile.xlsx", 1, "A2:B4")
3×2 Array{Any,2}:
 1  "first"
 2  "second"
 3  "third"

julia> XLSX.readdata("myfile.xlsx", "mysheet!A2:B4")
3×2 Array{Any,2}:
 1  "first"
 2  "second"
 3  "third"

getdata(sheet, ref)
getdata(sheet, row, column)

Returns a scalar or a matrix with values from a spreadsheet. ref can be a cell reference or a range.

Indexing in a Worksheet will dispatch to getdata method.

Example

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

julia> sheet = f["mysheet"]

julia> matrix = sheet["A1:B4"]

julia> single_value = sheet[2, 2] # B2

See also XLSX.readdata.

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.

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.

getcell(sheet, ref)

Returns an AbstractCell that represents a cell in the spreadsheet.

Example:

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

julia> sheet = xf["mysheet"]

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

getcellrange(sheet, rng)

Returns a matrix with cells as Array{AbstractCell, 2}. rng must be a valid cell range, as in "A1:B2".

row_number(c::CellRef) :: Int

Returns the row number of a given cell reference.

column_number(c::CellRef) :: Int

Returns the column number of a given cell reference.

eachrow(sheet)

Creates a row iterator for a worksheet.

Example: Query all cells from columns 1 to 4.

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

        # do something with cell
    end
end

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

Returns tabular data from a spreadsheet as a struct XLSX.DataTable. Use this function to create a 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 to specify names for the header of the table.

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

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, XLSX

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

See also: XLSX.gettable.

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

Returns tabular data from a spreadsheet as a struct XLSX.DataTable. Use this function to create a 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 infer_eltypes=true to get data as a Vector{Any} of typed vectors. The default value is infer_eltypes=false.

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, XLSX

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

See also: XLSX.readtable.

eachtablerow(sheet, [columns]; [first_row], [column_labels], [header], [stop_in_empty_row], [stop_in_row_function], [keep_empty_rows])

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.

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.

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

See also XLSX.gettable.

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

Write Tables.jl table to the specified filename.

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)

writetable(filename, data, columnnames; [overwrite], [sheetname])

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

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)

See also: XLSX.writetable!.

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

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.

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)

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

Write Tables.jl table to the specified sheet.

writetable!(
    sheet::Worksheet,
    data,
    columnnames;
    anchor_cell::CellRef=CellRef("A1"),
    write_columnnames::Bool=true,
)

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.

See also: XLSX.writetable.

rename!(ws::Worksheet, name::AbstractString)

Renames a Worksheet.

addsheet!(workbook, [name]) :: Worksheet

Create a new worksheet with named name. If name is not provided, a unique name is created.

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

Set the format used used by a single cell, a cell range, a column range or a named cell or named range in a worksheet or XLSXfile.

The function uses one keyword used to define a format:

• format::String = nothing : Defines a built-in or custom number format

The format keyword can define some built-in formats by name:

• General : specifies internal format ID 0 (General)
• Number : specifies internal format ID 2 (0.00)
• Currency : specifies internal format ID 7 ($#,##0.00_);($#,##0.00))
• Percentage : specifies internal format ID 9 (0%)
• ShortDate : specifies internal format ID 14 (m/d/yyyy)
• LongDate : specifies internal format ID 15 (d-mmm-yy)
• Time : specifies internal format ID 21 (h:mm:ss)
• Scientific : specifies internal format ID 48 (##0.0E+0)

If Currency is specified, Excel will use the appropriate local currency symbol.

Alternatively, format can be used to specify any custom format directly. Only weak checks are made of custom formats specified - they are otherwise added to the XLSXfile verbatim.

Formats may need characters that must to be escaped when specified.

Examples:

julia> XLSX.setFormat(sh, "D2"; format = "h:mm AM/PM")

julia> XLSX.setFormat(xf, "Sheet1!A2"; format = "# ??/??")

julia> XLSX.setFormat(sh, "F1:F5"; format = "Currency")

julia> XLSX.setFormat(sh, "A2"; format = "_-£* #,##0.00_-;-£* #,##0.00_-;_-£* \"-\"??_-;_-@_-")
 

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

Set the number format used by a cell range, a column range or a named range in a worksheet or XLSXfile to be to be uniformly the same format.

First, the number format of the first cell in the range (the top-left cell) is updated according to the given kw... (using setFormat()). The resultant format is then applied to each remaining cell in the range.

As a result, every cell in the range will have a uniform number format.

This is functionally equivalent to applying setFormat() to each cell in the range but may be very marginally more efficient.

The value returned is the numfmtId of the format uniformly applied to the cells. If all cells in the range are EmptyCells, the returned value is -1.

For keyword definitions see setFormat().

Examples:

julia> XLSX.setUniformFormat(xf, "Sheet1!A2:L6"; format = "# ??/??")

julia> XLSX.setUniformFormat(sh, "F1:F5"; format = "Currency")
 

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

Set the font used by a single cell, a cell range, a column range or a named cell or named range in a worksheet or XLSXfile.

Font attributes are specified using keyword arguments:

• bold::Bool = nothing : set to true to make the font bold.
• italic::Bool = nothing : set to true to make the font italic.
• under::String = nothing : set to single, double or none.
• strike::Bool = nothing : set to true to strike through the font.
• size::Int = nothing : set the font size (0 < size < 410).
• color::String = nothing : set the font color using an 8-digit hexadecimal RGB value.
• name::String = nothing : set the font name.

Only the attributes specified will be changed. If an attribute is not specified, the current value will be retained. These are the only attributes supported currently.

No validation of the font names specified is performed. Available fonts will depend on what your system has installed. If you specify, for example, name = "badFont", that value will be written to the XLSXfile.

As an expedient to get fonts to work, the scheme attribute is simply dropped from new font definitions.

The color attribute can only be defined as rgb values.

• The first two digits represent transparency (α). FF is fully opaque, while 00 is fully transparent.
• The next two digits give the red component.
• The next two digits give the green component.
• The next two digits give the blue component.

So, FF000000 means a fully opaque black color.

Font attributes cannot be set for EmptyCells. Set a cell value first. If a cell range or column range includes any EmptyCells, they will be quietly skipped and the font will be set for the remaining cells.

For single cells, the value returned is the fontId of the font applied to the cell. This can be used to apply the same font to other cells or ranges.

For cell ranges, column ranges and named ranges, the value returned is -1.

Examples:

julia> setFont(sh, "A1"; bold=true, italic=true, size=12, name="Arial")          # Single cell

julia> setFont(xf, "Sheet1!A1"; bold=false, size=14, color="FFB3081F")           # Single cell

julia> setFont(sh, "A1:B7"; name="Aptos", under="double", strike=true)           # Cell range

julia> setFont(xf, "Sheet1!A1:B7"; size=24, name="Berlin Sans FB Demi")          # Cell range

julia> setFont(sh, "A:B"; italic=true, color="FF8888FF", under="single")         # Column range

julia> setFont(xf, "Sheet1!A:B"; italic=true, color="FF8888FF", under="single")  # Column range

julia> setFont(sh, "bigred"; size=48, color="FF00FF00")                          # Named cell or range

julia> setFont(xf, "bigred"; size=48, color="FF00FF00")                          # Named cell or range
 

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

Set the font used by a cell range, a column range or a named range in a worksheet or XLSXfile to be uniformly the same font.

First, the font attributes of the first cell in the range (the top-left cell) are updated according to the given kw... (using setFont()). The resultant font is then applied to each remaining cell in the range.

As a result, every cell in the range will have a uniform font setting.

This differs from setFont() which merges the attributes defined by kw... into the font definition used by each cell individually. For example, if you set the font size to 12 for a range of cells, but these cells all use different fonts names or colors, etc, setFont() will change the font size but leave the font name and color unchanged for each cell individually.

In contrast, setUniformFont() will set the font size to 12 for the first cell, but will then apply all the font attributes from the updated first cell (ie. name, color, etc) to all the other cells in the range.

This can be more efficient when setting the same font for a large number of cells.

The value returned is the fontId of the font uniformly applied to the cells. If all cells in the range are EmptyCells the returned value is -1.

For keyword definitions see setFont().

Examples:

julia> setUniformFont(sh, "A1:B7"; bold=true, italic=true, size=12, name="Arial")       # Cell range

julia> setUniformFont(xf, "Sheet1!A1:B7"; size=24, name="Berlin Sans FB Demi")          # Cell range

julia> setUniformFont(sh, "A:B"; italic=true, color="FF8888FF", under="single")         # Column range

julia> setUniformFont(xf, "Sheet1!A:B"; italic=true, color="FF8888FF", under="single")  # Column range

julia> setUniformFont(sh, "bigred"; size=48, color="FF00FF00")                          # Named range

julia> setUniformFont(xf, "bigred"; size=48, color="FF00FF00")                          # Named range
 

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

Set the borders used used by a single cell, a cell range, a column range or a named cell or named range in a worksheet or XLSXfile.

Borders are independently defined for the keywords:

• left::Vector{Pair{String,String} = nothing
• right::Vector{Pair{String,String} = nothing
• top::Vector{Pair{String,String} = nothing
• bottom::Vector{Pair{String,String} = nothing
• diagonal::Vector{Pair{String,String} = nothing
• [allsides::Vector{Pair{String,String} = nothing]

These represent each of the sides of a cell . The keyword diagonal defines diagonal lines running across the cell. These lines must share the same style and color in any cell.

An additional keyword, allsides, is provided for convenience. It can be used in place of the four side keywords to apply the same border setting to all four sides at once. It cannot be used in conjunction with any of the side-specific keywords but it can be used together with diagonal.

The two attributes that can be set for each keyword are style and rgb. Additionally, for diagonal borders, a third keyword, direction can be used.

Allowed values for style are:

• none
• thin
• medium
• dashed
• dotted
• thick
• double
• hair
• mediumDashed
• dashDot
• mediumDashDot
• dashDotDot
• mediumDashDotDot
• slantDashDot

The color attribute is set by specifying an 8-digit hexadecimal value. No other color attributes can be applied.

Valid values for the direction keyword (for diagonal borders) are:

• up : diagonal border runs bottom-left to top-right
• down : diagonal border runs top-left to bottom-right
• both : diagonal borders run both ways

Both diagonal borders share the same style and color.

Setting only one of the attributes leaves the other attributes unchanged for that side's border. Omitting one of the keywords leaves the border definition for that side unchanged, only updating the other, specified sides.

Border attributes cannot be set for EmptyCells. Set a cell value first. If a cell range or column range includes any EmptyCells, they will be quietly skipped and the border will be set for the remaining cells.

For single cells, the value returned is the borderId of the borders applied to the cell. This can be used to apply the same borders to other cells or ranges.

For cell ranges, column ranges and named ranges, the value returned is -1.

Examples:

Julia> setBorder(sh, "D6"; allsides = ["style" => "thick"], diagonal = ["style" => "hair", "direction" => "up"])

Julia> setBorder(xf, "Sheet1!D4"; left     = ["style" => "dotted", "color" => "FF000FF0"],
                                  right    = ["style" => "medium", "color" => "FF765000"],
                                  top      = ["style" => "thick",  "color" => "FF230000"],
                                  bottom   = ["style" => "medium", "color" => "FF0000FF"],
                                  diagonal = ["style" => "dotted", "color" => "FF00D4D4"]
                                  )
 

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

Set the border used by a cell range, a column range or a named range in a worksheet or XLSXfile to be uniformly the same border.

First, the border attributes of the first cell in the range (the top-left cell) are updated according to the given kw... (using setBorder()). The resultant border is then applied to each remaining cell in the range.

As a result, every cell in the range will have a uniform border setting.

This differs from setBorder() which merges the attributes defined by kw... into the border definition used by each cell individually. For example, if you set the border style to thin for a range of cells, but these cells all use different border colors, setBorder() will change the border style but leave the border color unchanged for each cell individually.

In contrast, setUniformBorder() will set the border style to thin for the first cell, but will then apply all the border attributes from the updated first cell (ie. both style and color) to all the other cells in the range.

This can be more efficient when setting the same border for a large number of cells.

The value returned is the borderId of the border uniformly applied to the cells. If all cells in the range are EmptyCells the returned value is -1.

For keyword definitions see setBorder().

Examples:

Julia> setUniformBorder(sh, "B2:D6"; allsides = ["style" => "thick"], diagonal = ["style" => "hair"])

Julia> setUniformBorder(xf, "Sheet1!A1:F20"; left     = ["style" => "dotted", "color" => "FF000FF0"],
                                             right    = ["style" => "medium", "color" => "FF765000"],
                                             top      = ["style" => "thick",  "color" => "FF230000"],
                                             bottom   = ["style" => "medium", "color" => "FF0000FF"],
                                             diagonal = ["style" => "none"]
                                             )
 

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

Set the border around the outside of a cell range, a column range or a named range in a worksheet or XLSXfile.

Two key words can be defined:

• style::String = nothing : defines the style of the outside border
• color::String = nothing : defines the color of the outside border

Only the border definitions for the sides of boundary cells that are on the ouside edge of the range will be set to the specified style and color. The borders of internal edges and any diagonal will remain unchanged. Border settings for all internal cells in the range will remain unchanged.

The value returned is is -1.

For keyword definitions see setBorder().

Examples:

Julia> setOutsideBorder(sh, "B2:D6"; style = "thick")

Julia> setOutsideBorder(xf, "Sheet1!A1:F20"; style = "dotted", color = "FF000FF0")
 

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

Set the fill used used by a single cell, a cell range, a column range or a named cell or named range in a worksheet or XLSXfile.

The following keywords are used to define a fill:

• pattern::String = nothing : Sets the patternType for the fill.
• fgColor::String = nothing : Sets the foreground color for the fill.
• bgColor::String = nothing : Sets the background color for the fill.

Here is a list of the available pattern values (thanks to Copilot!):

• none
• solid
• mediumGray
• darkGray
• lightGray
• darkHorizontal
• darkVertical
• darkDown
• darkUp
• darkGrid
• darkTrellis
• lightHorizontal
• lightVertical
• lightDown
• lightUp
• lightGrid
• lightTrellis
• gray125
• gray0625

The two colors are set by specifying an 8-digit hexadecimal value for the fgColor and/or bgColor keywords. No other color attributes can be applied.

Setting only one or two of the attributes leaves the other attribute(s) unchanged for that cell's fill.

Fill attributes cannot be set for EmptyCells. Set a cell value first. If a cell range or column range includes any EmptyCells, they will be quietly skipped and the fill will be set for the remaining cells.

For single cells, the value returned is the fillId of the fill applied to the cell. This can be used to apply the same fill to other cells or ranges.

For cell ranges, column ranges and named ranges, the value returned is -1.

Examples:

Julia> setFill(sh, "B2"; pattern="gray125", bgColor = "FF000000")

Julia> setFill(xf, "Sheet1!A1:F20"; pattern="none", fgColor = "88FF8800")
 

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

Set the fill used by a cell range, a column range or a named range in a worksheet or XLSXfile to be uniformly the same fill.

First, the fill attributes of the first cell in the range (the top-left cell) are updated according to the given kw... (using setFill()). The resultant fill is then applied to each remaining cell in the range.

As a result, every cell in the range will have a uniform fill setting.

This differs from setFill() which merges the attributes defined by kw... into the fill definition used by each cell individually. For example, if you set the fill patern to darkGrid for a range of cells, but these cells all use different fill colors, setFill() will change the fill pattern but leave the fill color unchanged for each cell individually.

In contrast, setUniformFill() will set the fill pattern to darkGrid for the first cell, but will then apply all the fill attributes from the updated first cell (ie. pattern and both foreground and background colors) to all the other cells in the range.

This can be more efficient when setting the same fill for a large number of cells.

The value returned is the fillId of the fill uniformly applied to the cells. If all cells in the range are EmptyCells the returned value is -1.

For keyword definitions see setFill().

Examples:

Julia> setUniformFill(sh, "B2:D4"; pattern="gray125", bgColor = "FF000000")

Julia> setUniformFill(xf, "Sheet1!A1:F20"; pattern="none", fgColor = "88FF8800")
 

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

Set the alignment used used by a single cell, a cell range, a column range or a named cell or named range in a worksheet or XLSXfile.

The following keywords are used to define an alignment:

• horizontal::String = nothing : Sets the horizontal alignment.
• vertical::String = nothing : Sets the vertical alignment.
• wrapText::Bool = nothing : Determines whether the cell content wraps within the cell.
• shrink::Bool = nothing : Indicates whether the text should shrink to fit the cell.
• indent::Int = nothing : Specifies the number of spaces by which to indent the text (always from the left).
• rotation::Int = nothing : Specifies the rotation angle of the text in the range -90 to 90 (positive values rotate the text counterclockwise),

Here are the possible values for the horizontal alignment:

• left : Aligns the text to the left of the cell.
• center : Centers the text within the cell.
• right : Aligns the text to the right of the cell.
• fill : Repeats the text to fill the entire width of the cell.
• justify : Justifies the text, spacing it out so that it spans the entire width of the cell.
• centerContinuous : Centers the text across multiple cells (specifically the currrent cell and all empty cells to the right) as if the text were in a merged cell.
• distributed : Distributes the text evenly across the width of the cell.

Here are the possible values for the vertical alignment:

• top : Aligns the text to the top of the cell.
• center : Centers the text vertically within the cell.
• bottom : Aligns the text to the bottom of the cell.
• justify : Justifies the text vertically, spreading it out evenly within the cell.
• distributed : Distributes the text evenly from top to bottom in the cell.

For single cells, the value returned is the styleId of the cell.

For cell ranges, column ranges and named ranges, the value returned is -1.

Examples:

julia> setAlignment(sh, "D18"; horizontal="center", wrapText=true)

julia> setAlignment(xf, "sheet1!D18"; horizontal="right", vertical="top", wrapText=true)

julia> setAlignment(sh, "L6"; horizontal="center", rotation="90", shrink=true, indent="2")
 

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

Set the alignment used by a cell range, a column range or a named range in a worksheet or XLSXfile to be uniformly the same alignment.

First, the alignment attributes of the first cell in the range (the top-left cell) are updated according to the given kw... (using setAlignment()). The resultant alignment is then applied to each remaining cell in the range.

As a result, every cell in the range will have a uniform alignment setting.

This differs from setAlignment() which merges the attributes defined by kw... into the alignment definition used by each cell individually. For example, if you set the horizontal alignment to left for a range of cells, but these cells all use different vertical alignment or wrapText, setAlignment() will change the horizontal alignment but leave the vertical alignment and wrapText unchanged for each cell individually.

In contrast, setUniformAlignment() will set the horizontal alignment to left for the first cell, but will then apply all the alignment attributes from the updated first cell to all the other cells in the range.

This can be more efficient when setting the same alignment for a large number of cells.

The value returned is the styleId of the reference (top-left) cell, from which the alignment uniformly applied to the cells was taken. If all cells in the range are EmptyCells, the returned value is -1.

For keyword definitions see setAlignment().

Examples:

Julia> setUniformAlignment(sh, "B2:D4"; horizontal="center", wrap = true)

Julia> setUniformAlignment(xf, "Sheet1!A1:F20"; horizontal="center", vertical="top")
 

setUniformStyle(sh::Worksheet, cr::String) -> ::Int
setUniformStyle(xf::XLSXFile,  cr::String) -> ::Int

Set the cell style used by a cell range, a column range or a named range in a worksheet or XLSXfile to be the same as that of the first cell in the range that is not an EmptyCell.

As a result, every cell in the range will have a uniform style.

A cell style consists of the collection of format, alignment, border, font and fill.

If the first cell has no defined style (s=""), all cells will be given the same undefined style.

The value returned is the styleId of the style uniformly applied to the cells or nothing if the style is undefined. If all cells in the range are EmptyCells, the returned value is -1.

Examples:

julia> XLSX.setUniformStyle(xf, "Sheet1!A2:L6")

julia> XLSX.setUniformStyle(sh, "F1:F5")
 

setColumnWidth(sh::Worksheet, cr::String; kw...) -> ::Int
setColumnWidth(xf::XLSXFile,  cr::String, 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.

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.

NOTE: Unlike the other set and get XLSX functions, working with ColumnWidth requires a file to be open for writing as well as reading (mode="rw" or open as a template)

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)
 

setRowHeight(sh::Worksheet, cr::String; kw...) -> ::Int
setRowHeight(xf::XLSXFile,  cr::String, 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.

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.

You can set a row height to 0.

The function returns a value of 0.

Examples:

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

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

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