XLSX.jl
Introduction
XLSX.jl is a Julia package to read and write Excel spreadsheet files.
Internally, an Excel XLSX file is just a Zip file with a set of XML files inside. The formats for these XML files are described in the Standard ECMA-376.
This package follows ECMA-376 to parse and generate XLSX files.
Features
- Read and write cell values, ranges and whole tables through the Tables.jl interface, so data goes straight to and from a
DataFrame(see the XLSX Tutorial) - Stream the rows of large files with
eachrowandeachtablerow, without loading the whole sheet - Cell formatting and conditional formats
- Formulas and named ranges
- Insert and delete rows and columns, with formulas and everything else that refers to them updated
- Data validation, including drop-down lists
- AutoFilter buttons on plain ranges
- Sheet visibility and tab colours, protection and page setup
- Native Excel Tables
- Merged cells, column widths and row heights, hidden and grouped rows and columns and freeze/split panes
- Adding, copying, renaming, moving and deleting sheets
- Hyperlinks, images and cell comments
- Native Excel charts: read, format and create (experimental)
- FileIO.jl integration (see the FileIO Tutorial)
Supported features lists every Excel feature, what XLSX.jl can do with it, and what it can't do.
Quick Start
using XLSX, DataFrames
df = DataFrame(name = ["a", "b", "c"], value = [1.5, 2.5, 3.5])
XLSX.writetable("data.xlsx", df; sheetname = "Data", overwrite = true)
df2 = DataFrame(XLSX.readtable("data.xlsx", "Data"))
XLSX.openxlsx("data.xlsx", mode = "rw") do xf
sheet = xf["Data"]
sheet["C1"] = "Total"
XLSX.setFormula(sheet, "C2", "=SUM(B2:B4)")
XLSX.setFont(sheet, "C1"; bold = true)
endI want to…
The Cookbook answers the most common of these with a few lines of code each.
Read and write data
| I want to… | Where to look |
|---|---|
read a sheet into a DataFrame | Read Tabular Data |
| create a new workbook, or edit and save an existing one | Creating and editing workbooks |
write a DataFrame (or several) to a file | Writing Excel Files |
| read individual cells or a range | Getting Started |
| edit an existing file | Edit Existing Files |
| read a file too big for memory | Reading Large Excel Files and Caching |
use FileIO's load and save | FileIO Tutorial |
Data features
| I want to… | Where to look |
|---|---|
| create an Excel Table | Creating Tables |
| add a totals row to a Table | Totals rows |
| write a formula | Using a simple formula |
use SORT, UNIQUE and other spilling functions | Spill ranges |
| define or read a named range | Named ranges |
| make a drop-down list | Drop-down lists |
| restrict a cell to numbers, dates or text length | Numbers, dates, times and text length |
| add filter buttons to a header row | Filtering |
Formatting
| I want to… | Where to look |
|---|---|
| make text bold, change font, fill or border | Setting format attributes of a cell |
| format numbers as currency, percentage, dates or a custom format | Number formats |
| refer to cells, ranges, rows, columns or names | Addressing cells and ranges |
| format a whole range at once | Formatting multiple cells at once |
| choose a color, including theme colors | Specifying colors |
| color part of the text in a cell | Rich text formats |
| highlight cells by value (conditional formatting) | Dynamic conditional formats |
| list or remove conditional formats | Reading and clearing conditional formats |
| add data bars, color scales or icon sets | Data Bar, Color Scale, Icon Set |
| merge cells | Creating merged cells |
| set column widths and row heights | Column width and row height |
Sheet layout and view
| I want to… | Where to look |
|---|---|
| freeze the header row or first column | Freeze Panes |
| insert or delete rows and columns | Inserting and deleting rows and columns |
| hide rows or columns | Hiding |
| group rows or columns (outline) | Grouping |
Charts, images, links and comments
| I want to… | Where to look |
|---|---|
| create a chart | A chart on a worksheet |
| create a waterfall, funnel, treemap, histogram or other new chart type | Creating chartEx charts |
| change a chart's colors, title, legend or axes | Formatting charts |
| read the data behind an existing chart | Getting the cached data |
| insert an image or logo | Images |
| insert a plot made in Julia | Adding a plot image |
| add a hyperlink | Hyperlinks |
| add a comment (note) to a cell | Comments |
Worksheets, protection and printing
| I want to… | Where to look |
|---|---|
| add, rename, copy or delete a sheet | Managing worksheets |
| reorder sheets | Sheet order |
| hide a sheet or color its tab | Hiding a sheet, Tab colour |
| protect a sheet, leaving some cells editable | Protecting a sheet, Unlocking cells |
| set orientation, paper size, margins or print area | Printing |
Where to find things
- Tutorials for a first walk-through: XLSX Tutorial, Creating and editing workbooks and FileIO Tutorial.
- Cookbook: short recipes for common tasks, from writing a
DataFrameto protecting a sheet. - Guides for each feature, in the sidebar.
- Examples: longer worked examples, from a formatted table to a native chart.
- Supported features: what XLSX.jl can and can't do with each Excel feature.
- API Reference: every function, with an alphabetical index.
- FAQ and troubleshooting: error messages and surprises, and what to do about them.
- Migration Guide: changes between versions.
Requirements
Julia v1.10 (LTS) or more recent
Linux, macOS or Windows.
Installation
From a Julia session, run:
julia> using Pkg
julia> Pkg.add("XLSX")Source Code
The source code for this package is hosted at https://github.com/juliadata/XLSX.jl.
License
The source code for the package XLSX.jl is licensed under the MIT License.
Getting Help
If you're having any trouble, have any questions about this package or want to ask for a new feature, just open a new issue.
Contributing
Contributions are always welcome!
To contribute, fork the project on GitHub and send a Pull Request.