Supported features

This page lists the Excel features XLSX.jl works with, the functions for each, and where each is documented. The second table lists features XLSX.jl doesn't create or edit, so you needn't search for them.

Functions that add, change or remove something need a file opened with mode="rw", or one started with XLSX.newxlsx. Functions that only read work on any file.

What XLSX.jl can do

Data

Excel featureIn ExcelReadCreate or changeRemoveGuide
Cell valuestyping in cellssheet["A1"], XLSX.getdata, XLSX.readdatasheet["A1"] = value—XLSX Tutorial
Tabular data—XLSX.readtable, XLSX.gettable, XLSX.eachtablerowXLSX.writetable, XLSX.writetable!—XLSX Tutorial
Writing large files in constant memory——XLSX.writetable with streaming=true, XLSX.streamxlsx, XLSX.writerow!—Writing large files
Excel TablesInsert → TableXLSX.tables, XLSX.table, XLSX.gettotalsXLSX.addtable!, XLSX.appendtable!, XLSX.settotals!XLSX.deletetable!, XLSX.removetotals!Excel Tables
Formulas, with their cached valuesformula barXLSX.getFormula, sheet["A1"] for the valueXLSX.setFormula, with value—Using Formulas
Named rangesFormulas → Name ManagerXLSX.getDefinedNames, XLSX.getAllDefinedNamesXLSX.addDefinedNameXLSX.deleteDefinedName, XLSX.deleteAllDefinedNamesNamed ranges
Data validation and drop-downsData → Data ValidationXLSX.getDataValidations, XLSX.getDataValidationXLSX.addDataValidationXLSX.deleteDataValidationData validation
AutoFilter buttons, filter criteria and sortingData → Filter, Data → SortXLSX.getAutoFilterXLSX.setAutoFilter, with filters and sortXLSX.removeAutoFilterFiltering
Error values (#N/A, #DIV/0!, …)—XLSX.iserror, XLSX.geterror——Error Values (API)
Ignoring error-checking warningsgreen triangle → Ignore ErrorXLSX.getIgnoredErrorsXLSX.setIgnoredErrorsXLSX.removeIgnoredErrorsIgnoring error-checking warnings

Formatting

Excel featureIn ExcelReadCreate or changeRemoveGuide
Fonts, fills, borders, alignmentHome → Font / AlignmentXLSX.getFont, XLSX.getFill, XLSX.getBorder, XLSX.getAlignmentXLSX.setFont, XLSX.setFill, XLSX.setBorder, XLSX.setAlignment and their setUniform… forms, XLSX.setUniformStyle—Cell formats
Number formatsHome → NumberXLSX.getFormat, XLSX.getText (the text shown)XLSX.setFormat, XLSX.setUniformFormat—Number formats
Rich text (mixed formats in one cell)selecting part of the textXLSX.getRichTextStringXLSX.RichTextString, StyledStrings.jl—Rich text formats
Conditional formattingHome → Conditional FormattingXLSX.getConditionalFormatsXLSX.setConditionalFormat, XLSX.setColoredDataBars (experimental)XLSX.clearConditionalFormatsConditional formats
Checkboxes in cells (Excel 365)Insert → CheckboxXLSX.getCheckboxes, XLSX.hasCheckboxXLSX.setCheckboxXLSX.removeCheckboxCheckboxes
Merged cellsHome → Merge & CenterXLSX.getMergedCells, XLSX.isMergedCell, XLSX.getMergedBaseCellXLSX.mergeCellsXLSX.removeMergedCellsMerged cells
Column widths and row heightsHome → FormatXLSX.getColumnWidth, XLSX.getRowHeightXLSX.setColumnWidth, XLSX.setRowHeight, XLSX.autofitColumns—Column width and row height

Sheet layout and view

Excel featureIn ExcelReadCreate or changeRemoveGuide
Hidden rows and columnsHome → Format → Hide & UnhideXLSX.isRowHidden, XLSX.isColumnHiddenXLSX.setRowHidden, XLSX.setColumnHiddenthe same, with hidden=falseHiding and grouping
Grouping and outlinesData → GroupXLSX.getRowOutlineLevel, XLSX.getColumnOutlineLevelXLSX.groupRows, XLSX.groupColumnsXLSX.ungroupRows, XLSX.ungroupColumnsHiding and grouping
Freeze and split panesView → Freeze Panes / Split—XLSX.freezePanes, XLSX.splitPanes, XLSX.splitFreezeXLSX.removePanesFreeze and split panes
Zoom, gridlines, headings, zeros, right-to-left, scroll position and selectionViewXLSX.getSheetViewXLSX.setSheetView—Zoom, gridlines and the view
Inserting and deleting rows and columnsHome → Insert / Delete—XLSX.insertRows, XLSX.insertColumnsXLSX.deleteRows, XLSX.deleteColumnsInserting and deleting rows and columns
Excel featureIn ExcelReadCreate or changeRemoveGuide
Charts (experimental)Insert → ChartsgetCharts, getChartData and many moreaddChart, addChartEx, addSeries, makeCombo, the set… functionsdeleteChartCharts
ChartsheetsMove Chart → New sheetgetChartsaddChart given the fileXLSX.deletesheet!Sheets with charts
SparklinesInsert → SparklinesXLSX.getSparklinesXLSX.addSparklinesXLSX.deleteSparklinesSparklines
Images (PNG, JPEG, GIF)Insert → PicturesXLSX.getImagesXLSX.addImageXLSX.deleteImageImages
Pictures in cells (Excel 365)Insert → Pictures → Place in CellXLSX.getCellImage, XLSX.getCellImagesXLSX.CellImage written to a cell, XLSX.addImage with inCell=trueXLSX.deleteImagePictures in cells
Text boxes and shapesInsert → Text Box, Insert → ShapesXLSX.getShapesXLSX.addTextBox, XLSX.addShapeXLSX.deleteShapeText boxes and shapes
HyperlinksInsert → LinkXLSX.getHyperlink, XLSX.getHyperlinksXLSX.addHyperlinkXLSX.deleteHyperlinkHyperlinks
Comments (notes)Review → NotesXLSX.getComment, XLSX.getCommentsXLSX.addCommentXLSX.deleteCommentComments
Threaded commentsReview → New CommentXLSX.getThreadedComment, XLSX.getThreadedCommentsXLSX.addThreadedComment, XLSX.addThreadedReply, XLSX.setThreadedCommentResolvedXLSX.deleteThreadedCommentThreaded comments

Worksheets, protection and printing

Excel featureIn ExcelReadCreate or changeRemoveGuide
Document propertiesFile → Info → PropertiesXLSX.getDocumentProperties, XLSX.getCustomProperty, XLSX.getCustomPropertiesXLSX.setDocumentProperties, XLSX.setCustomPropertyXLSX.deleteCustomPropertyDocument properties
Worksheetssheet tabsXLSX.sheetnames, XLSX.sheetcount, XLSX.hassheetXLSX.addsheet!, XLSX.copysheet!, XLSX.renamesheet!, XLSX.movesheet!XLSX.deletesheet!Managing worksheets
Sheet visibilityright-click a tab → HideXLSX.getSheetVisibilityXLSX.setSheetVisibility—Hiding a sheet
Tab colourright-click a tab → Tab ColorXLSX.getTabColorXLSX.setTabColorXLSX.removeTabColorTab colour
Sheet protectionReview → Protect SheetXLSX.isSheetProtected, XLSX.getSheetProtection, XLSX.getCellProtectionXLSX.protectSheet, XLSX.setCellProtectionXLSX.unprotectSheetProtecting a sheet
Workbook protectionReview → Protect WorkbookXLSX.isWorkbookProtected, XLSX.getWorkbookProtectionXLSX.protectWorkbookXLSX.unprotectWorkbookProtecting the workbook
Page setup and printingPage LayoutXLSX.getPageSetup, XLSX.getPageMargins, XLSX.getPrintOptions, XLSX.getPrintArea, XLSX.getPrintTitlesXLSX.setPageSetup, XLSX.setPageMargins, XLSX.setPrintOptions, XLSX.setPrintArea, XLSX.setPrintTitlesXLSX.removePrintArea, XLSX.removePrintTitlesPrinting
Headers and footersPage Layout → Page Setup → Header/FooterXLSX.getHeaderFooterXLSX.setHeaderFooter, XLSX.headerFooterField, XLSX.headerFooterFontXLSX.removeHeaderFooterHeaders and footers

What XLSX.jl doesn't do

FeatureNotes
Calculating formulasXLSX.setFormula writes the formula, and Excel recalculates it when the file is opened. Its value reads as missing until then, unless you give the value yourself: see Cached values.
Filtering by colour or by iconXLSX.setAutoFilter filters by values, conditions, top or bottom items, the average and dates.
Creating region map charts, and some chart subtypesSee the charts Limitations.
Pivot tables, slicers and timelinesNo functions to create or edit them. A file holding them keeps them when it is edited and saved, but they aren't updated: refresh a pivot table in Excel.
Shapes filled with pictures, connectors, grouped shapes and SmartArtXLSX.addShape adds single preset shapes with text, fill and border.
Pictures in headers and footersXLSX.setHeaderFooter sets their text, fields and fonts only.
Password-encrypted filesTo read an encrypted file, decrypt it first with XLSXDecrypt.jl.
VBA macrosNot created or edited.

Opening a file that holds any of these in mode="rw" and saving it over the original deserves care: see the warning on XLSX.openxlsx.