GitHub

Files and worksheets

Files

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

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

Open an 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-only 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. If source already exists it will be overwritten.

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

Warning

The rw mode is known occasionally to produce some data loss. See #159. (Now fixed!)

Simple data should work fine. Users are advised to use this feature with caution when working with charts.

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 and the file is opened in read-only mode, all worksheet cells will be cached as they are read the first time. When you read a worksheet cell for the second (or subsequent) time it will use the cached value instead of reading from disk. If enable_cache=true and the file is opened in write mode, all cells are eagerly read into the cache as the file is opened (they will be needed at write anyway). For very large files, this can take a few seconds.

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

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

Supports opening a XLSX file without using do-syntax.

If opened with mode="rw" then use savexlsx to save the XLSX back to source, overwriting the original file. Alternatively, use writexlsx to save to a different filename.

These two invocations of openxlsx are functionally equivalent:

XLSX.openxlsx("myfile.xlsx", mode="rw") do xf
    # Do some processing on the content
end

xf = openxlsx("myfile.xlsx", mode="rw")
# Do some processing on the content
XLSX.savexlsx(xf)
source
XLSX.opentemplate — Function
opentemplate(source::Union{AbstractString, IO}) :: XLSXFile

Read an existing Excel (.xlsx) file as a template and return as a writable XLSXFile for editing and saving to another file with XLSX.writexlsx.

A convenience function equivalent to openxlsx(source; mode="rw", enable_cache=true)

Note

XLSX.jl only works with .xlsx files and cannot work with Excel .xltx template files. Reading as a template in this package merely means opening a .xlsx file to edit, update and then write as an updated .xlsx file (e.g. using XLSX.writexlsx()). Doing so retains the formatting and layout of the opened file, but this is not the same as using a .xltx file.

Examples

julia> xf = opentemplate("myExcelFile.xlsx")
source
XLSX.newxlsx — Function
newxlsx([sheetname::AbstractString]) :: XLSXFile

Return an empty, writable XLSXFile with 1 worksheet for editing and subsequent saving to a file with XLSX.writexlsx. By default, the worksheet is Sheet1. Specify sheetname to give the worksheet a different name.

Examples

julia> xf = XLSX.newxlsx()

julia> xf = XLSX.newxlsx("MySheet")
source
XLSX.writexlsx — Function
writexlsx(output_source::Union{AbstractString,IO}, xf::XLSXFile; [overwrite=false])

Write an XLSXFile given by xf to the IO or filepath output_source.

The source attribute of the XLSXFile will be updated to the output_source if it is a filepath.

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

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

See also savexlsx.

source
XLSX.savexlsx — Function
savexlsx(f::XLSXFile)

Save an XLSXFile instance back to the file from which it was opened (given in f.source), overwriting original content.

A new XLSXFile created with XLSX.newxlsx (or using openxlsx without specifying a filename) will have source set to "blank.xlsx" and cannot be saved with this function. Use writexlsx instead to specify a file name for the saved file.

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

source

Worksheets

XLSX.Worksheet — Type

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
source
XLSX.sheetnames — Function
sheetnames(xl::XLSXFile)
sheetnames(wb::Workbook)

Return a vector with Worksheet names for this Workbook.

source
XLSX.hassheet — Function
hassheet(wb::Workbook, sheetname::AbstractString)
hassheet(xl::XLSXFile, sheetname::AbstractString)

Return true if wb contains a sheet named sheetname.

source
XLSX.rename! — Function
rename!(ws::Worksheet, name::AbstractString)

Rename a Worksheet to name.

source
XLSX.addsheet! — Function
addsheet!(wb::Workbook, [name::AbstractString=""]) --> ::Worksheet
addsheet!(xf::XLSXFile, [name::AbstractString=""]) --> ::Worksheet

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

See also copysheet!, deletesheet!

source
XLSX.copysheet! — Function
copysheet!(ws::Worksheet, [name::AbstractString=""]) --> ::Worksheet

Create a copy of the worksheet ws and add it to the end of the workbook with the specified worksheet name. Return the new worksheet object. If name is not provided, a new name is generated by appending " (copy)" to the original worksheet name, with a further numerical suffix to guarantee uniqueness if necessary. To copy worksheets, the XLSXFile must be writable (opened with mode="rw" or as a template). See also XLSX.openxlsx and XLSX.opentemplate.

Experimental

This function is experimental is not guaranteed to work with all XLSX files, especially those with complex features. However, cell formats, conditional formats and worksheet defined names should all copy OK. Please report any issues.

See also addsheet!, deletesheet!

Examples

julia> f=XLSX.openxlsx("general.xlsx", mode="rw")
XLSXFile("C:\...\general.xlsx") containing 13 Worksheets
            sheetname size          range
-------------------------------------------------
              general 10x6          A1:F10
               table3 5x6           A2:F6
               table4 4x3           E12:G15
                table 12x8          A2:H13
               table2 5x3           A1:C5
                empty 1x1           A1:A1
               table5 6x1           C3:C8
               table6 8x2           B1:C8
               table7 7x2           B2:C8
               lookup 4x9           B2:J5
         header_error 3x4           B2:E4
       named_ranges_2 4x5           A1:E4
         named_ranges 14x6          A2:F15

julia> XLSX.copysheet!(f[4])
12×8 XLSX.Worksheet: ["table (copy)"](A2:H13)

julia> f
XLSXFile("C:\...\general.xlsx") containing 14 Worksheets
            sheetname size          range
-------------------------------------------------
              general 10x6          A1:F10
               table3 5x6           A2:F6
               table4 4x3           E12:G15
                table 12x8          A2:H13
               table2 5x3           A1:C5
                empty 1x1           A1:A1
               table5 6x1           C3:C8
               table6 8x2           B1:C8
               table7 7x2           B2:C8
               lookup 4x9           B2:J5
         header_error 3x4           B2:E4
       named_ranges_2 4x5           A1:E4
         named_ranges 14x6          A2:F15
         table (copy) 12x8          A2:H13
source
XLSX.deletesheet! — Function
deletesheet!(ws::Worksheet) -> ::XLSXFile
deletesheet!(wb::Workbook, name::AbstractString) -> ::XLSXFile
deletesheet!(xf::XLSXFile, name::AbstractString) -> ::XLSXFile
deletesheet!(xf::XLSXFile, sheetId::Integer) -> ::XLSXFile

Delete the given worksheet, the worksheet with the given name or the worksheet with the given sheetId from its XLSXFile (sheetId is a 1-based integer representing the order in which worksheet tabs are displayed in Excel).

note "Caution"

Cells in the other sheets that have references to the deleted sheet will fail when the sheet is deleted.
The formulae are updated to contain a `#Ref!` error in place of each sheetcell reference.

See also addsheet!, copysheet!

Examples

julia> f = XLSX.opentemplate("general.xlsx")
XLSXFile("C:\...\general.xlsx") containing 13 Worksheets
            sheetname size          range
-------------------------------------------------
              general 10x6          A1:F10
               table3 5x6           A2:F6
               table4 4x3           E12:G15
                table 12x8          A2:H13
               table2 5x3           A1:C5
                empty 1x1           A1:A1        
               table5 6x1           C3:C8
               table6 8x2           B1:C8
               table7 7x2           B2:C8
               lookup 4x9           B2:J5
         header_error 3x4           B2:E4
       named_ranges_2 4x5           A1:E4
         named_ranges 14x6          A2:F15


julia> XLSX.deletesheet!(f[4])
XLSXFile("C:\...\general.xlsx") containing 12 Worksheets
            sheetname size          range
-------------------------------------------------
              general 10x6          A1:F10
               table3 5x6           A2:F6
               table4 4x3           E12:G15
               table2 5x3           A1:C5
                empty 1x1           A1:A1
               table5 6x1           C3:C8
               table6 8x2           B1:C8
               table7 7x2           B2:C8
               lookup 4x9           B2:J5
         header_error 3x4           B2:E4
       named_ranges_2 4x5           A1:E4
         named_ranges 14x6          A2:F15


julia> XLSX.deletesheet!(f, "table5")
XLSXFile("C:\...\general.xlsx") containing 11 Worksheets
            sheetname size          range
-------------------------------------------------
              general 10x6          A1:F10
               table3 5x6           A2:F6
               table4 4x3           E12:G15
               table2 5x3           A1:C5
                empty 1x1           A1:A1
               table6 8x2           B1:C8
               table7 7x2           B2:C8
               lookup 4x9           B2:J5
         header_error 3x4           B2:E4
       named_ranges_2 4x5           A1:E4
         named_ranges 14x6          A2:F15


julia> XLSX.deletesheet!(f, 1)
XLSXFile("C:\...\general.xlsx") containing 10 Worksheets
            sheetname size          range
-------------------------------------------------
               table3 5x6           A2:F6
               table4 4x3           E12:G15
               table2 5x3           A1:C5
                empty 1x1           A1:A1
               table6 8x2           B1:C8
               table7 7x2           B2:C8
               lookup 4x9           B2:J5
         header_error 3x4           B2:E4
       named_ranges_2 4x5           A1:E4
         named_ranges 14x6          A2:F15
source