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

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

I 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 DataFrameRead Tabular Data
create a new workbook, or edit and save an existing oneCreating and editing workbooks
write a DataFrame (or several) to a fileWriting Excel Files
read individual cells or a rangeGetting Started
edit an existing fileEdit Existing Files
read a file too big for memoryReading Large Excel Files and Caching
use FileIO's load and saveFileIO Tutorial

Data features

I want to…Where to look
create an Excel TableCreating Tables
add a totals row to a TableTotals rows
write a formulaUsing a simple formula
use SORT, UNIQUE and other spilling functionsSpill ranges
define or read a named rangeNamed ranges
make a drop-down listDrop-down lists
restrict a cell to numbers, dates or text lengthNumbers, dates, times and text length
add filter buttons to a header rowFiltering

Formatting

I want to…Where to look
make text bold, change font, fill or borderSetting format attributes of a cell
format numbers as currency, percentage, dates or a custom formatNumber formats
refer to cells, ranges, rows, columns or namesAddressing cells and ranges
format a whole range at onceFormatting multiple cells at once
choose a color, including theme colorsSpecifying colors
color part of the text in a cellRich text formats
highlight cells by value (conditional formatting)Dynamic conditional formats
list or remove conditional formatsReading and clearing conditional formats
add data bars, color scales or icon setsData Bar, Color Scale, Icon Set
merge cellsCreating merged cells
set column widths and row heightsColumn width and row height

Sheet layout and view

I want to…Where to look
freeze the header row or first columnFreeze Panes
insert or delete rows and columnsInserting and deleting rows and columns
hide rows or columnsHiding
group rows or columns (outline)Grouping
I want to…Where to look
create a chartA chart on a worksheet
create a waterfall, funnel, treemap, histogram or other new chart typeCreating chartEx charts
change a chart's colors, title, legend or axesFormatting charts
read the data behind an existing chartGetting the cached data
insert an image or logoImages
insert a plot made in JuliaAdding a plot image
add a hyperlinkHyperlinks
add a comment (note) to a cellComments

Worksheets, protection and printing

I want to…Where to look
add, rename, copy or delete a sheetManaging worksheets
reorder sheetsSheet order
hide a sheet or color its tabHiding a sheet, Tab colour
protect a sheet, leaving some cells editableProtecting a sheet, Unlocking cells
set orientation, paper size, margins or print areaPrinting

Where to find things

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.

References

Alternative Packages