📂Julia

How to Read Excel XLSX Files in Julia

Overview

Introducing how to read *.XLSX files, which are Excel workbook extensions, in Julia. Although it is somewhat more complex than reading CSV files, in scenarios where you must use *.XLSX files due to the nature of the data or the sheer number of such files, it’s inevitable to use them¹.

Code

For instance, assume that the example.xlsx file contains alpha, beta sheets as follows. Specifically, beta has a detached cell called note.

XLSX.readxlsx

julia> xf = XLSX.readxlsx("example.xlsx")
XLSXFile("example.xlsx") containing 2 Worksheets
            sheetname size          range
-------------------------------------------------
                alpha 7x2           A1:B7
                 beta 4x4           A1:D4

The XLSX.readxlsx function reads the file as an XLSX.XLSXFile type. The XLSXFile acts similarly to a dictionary that maps strings String to matrices Matrix{Any}.

XLSX.sheetnames

julia> XLSX.sheetnames(xf)
2-element Vector{String}:
 "alpha"
 "beta"

The XLSX.sheetnames function returns an array of sheet names, which are considered keys of the file.

Accessing Sheets and Cells

julia> sh = xf[XLSX.sheetnames(xf)[1]]
7×2 XLSX.Worksheet: ["alpha"](A1:B7)

julia> sh[:]
7×2 Matrix{Any}:
  "t"    "x"
 0     10
 1     15
 2     19
 3     22
 4     24
 5     25

julia> sh = xf[XLSX.sheetnames(xf)[2]]
4×4 XLSX.Worksheet: ["beta"](A1:D4)

julia> sh[:]
4×4 Matrix{Any}:
 "name"    "vol"  missing  missing
 "alice"  1.2     missing  "note"
 "bob"    1.7     missing  missing
 "eve"    1       missing  missing

Sheets can be accessed in the XLSX.XLSXFile by their string names, and cells can be accessed in the XLSX.Worksheet by their indices. Notably, the empty spaces in the original beta sheet are filled with missing.

Full Code

using XLSX

xf = XLSX.readxlsx("example.xlsx")
XLSX.sheetnames(xf)

sh = xf[XLSX.sheetnames(xf)[1]]
sh[:]

sh = xf[XLSX.sheetnames(xf)[2]]
sh[:]

Environment

• OS: Windows
• julia: v1.10.0
• XLSX v0.10.1