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.
| Excel feature | In Excel | Read | Create or change | Remove | Guide |
|---|
| Cell values | typing in cells | sheet["A1"], XLSX.getdata, XLSX.readdata | sheet["A1"] = value | — | XLSX Tutorial |
| Tabular data | — | XLSX.readtable, XLSX.gettable, XLSX.eachtablerow | XLSX.writetable, XLSX.writetable! | — | XLSX Tutorial |
| Writing large files in constant memory | — | — | XLSX.writetable with streaming=true, XLSX.streamxlsx, XLSX.writerow! | — | Writing large files |
| Excel Tables | Insert → Table | XLSX.tables, XLSX.table, XLSX.gettotals | XLSX.addtable!, XLSX.appendtable!, XLSX.settotals! | XLSX.deletetable!, XLSX.removetotals! | Excel Tables |
| Formulas, with their cached values | formula bar | XLSX.getFormula, sheet["A1"] for the value | XLSX.setFormula, with value | — | Using Formulas |
| Named ranges | Formulas → Name Manager | XLSX.getDefinedNames, XLSX.getAllDefinedNames | XLSX.addDefinedName | XLSX.deleteDefinedName, XLSX.deleteAllDefinedNames | Named ranges |
| Data validation and drop-downs | Data → Data Validation | XLSX.getDataValidations, XLSX.getDataValidation | XLSX.addDataValidation | XLSX.deleteDataValidation | Data validation |
| AutoFilter buttons, filter criteria and sorting | Data → Filter, Data → Sort | XLSX.getAutoFilter | XLSX.setAutoFilter, with filters and sort | XLSX.removeAutoFilter | Filtering |
Error values (#N/A, #DIV/0!, …) | — | XLSX.iserror, XLSX.geterror | — | — | Error Values (API) |
| Ignoring error-checking warnings | green triangle → Ignore Error | XLSX.getIgnoredErrors | XLSX.setIgnoredErrors | XLSX.removeIgnoredErrors | Ignoring error-checking warnings |
| Excel feature | In Excel | Read | Create or change | Remove | Guide |
|---|
| Fonts, fills, borders, alignment | Home → Font / Alignment | XLSX.getFont, XLSX.getFill, XLSX.getBorder, XLSX.getAlignment | XLSX.setFont, XLSX.setFill, XLSX.setBorder, XLSX.setAlignment and their setUniform… forms, XLSX.setUniformStyle | — | Cell formats |
| Number formats | Home → Number | XLSX.getFormat, XLSX.getText (the text shown) | XLSX.setFormat, XLSX.setUniformFormat | — | Number formats |
| Rich text (mixed formats in one cell) | selecting part of the text | XLSX.getRichTextString | XLSX.RichTextString, StyledStrings.jl | — | Rich text formats |
| Conditional formatting | Home → Conditional Formatting | XLSX.getConditionalFormats | XLSX.setConditionalFormat, XLSX.setColoredDataBars (experimental) | XLSX.clearConditionalFormats | Conditional formats |
| Checkboxes in cells (Excel 365) | Insert → Checkbox | XLSX.getCheckboxes, XLSX.hasCheckbox | XLSX.setCheckbox | XLSX.removeCheckbox | Checkboxes |
| Merged cells | Home → Merge & Center | XLSX.getMergedCells, XLSX.isMergedCell, XLSX.getMergedBaseCell | XLSX.mergeCells | XLSX.removeMergedCells | Merged cells |
| Column widths and row heights | Home → Format | XLSX.getColumnWidth, XLSX.getRowHeight | XLSX.setColumnWidth, XLSX.setRowHeight, XLSX.autofitColumns | — | Column width and row height |
| Excel feature | In Excel | Read | Create or change | Remove | Guide |
|---|
| Hidden rows and columns | Home → Format → Hide & Unhide | XLSX.isRowHidden, XLSX.isColumnHidden | XLSX.setRowHidden, XLSX.setColumnHidden | the same, with hidden=false | Hiding and grouping |
| Grouping and outlines | Data → Group | XLSX.getRowOutlineLevel, XLSX.getColumnOutlineLevel | XLSX.groupRows, XLSX.groupColumns | XLSX.ungroupRows, XLSX.ungroupColumns | Hiding and grouping |
| Freeze and split panes | View → Freeze Panes / Split | — | XLSX.freezePanes, XLSX.splitPanes, XLSX.splitFreeze | XLSX.removePanes | Freeze and split panes |
| Zoom, gridlines, headings, zeros, right-to-left, scroll position and selection | View | XLSX.getSheetView | XLSX.setSheetView | — | Zoom, gridlines and the view |
| Inserting and deleting rows and columns | Home → Insert / Delete | — | XLSX.insertRows, XLSX.insertColumns | XLSX.deleteRows, XLSX.deleteColumns | Inserting and deleting rows and columns |
| Excel feature | In Excel | Read | Create or change | Remove | Guide |
|---|
| Charts (experimental) | Insert → Charts | getCharts, getChartData and many more | addChart, addChartEx, addSeries, makeCombo, the set… functions | deleteChart | Charts |
| Chartsheets | Move Chart → New sheet | getCharts | addChart given the file | XLSX.deletesheet! | Sheets with charts |
| Sparklines | Insert → Sparklines | XLSX.getSparklines | XLSX.addSparklines | XLSX.deleteSparklines | Sparklines |
| Images (PNG, JPEG, GIF) | Insert → Pictures | XLSX.getImages | XLSX.addImage | XLSX.deleteImage | Images |
| Pictures in cells (Excel 365) | Insert → Pictures → Place in Cell | XLSX.getCellImage, XLSX.getCellImages | XLSX.CellImage written to a cell, XLSX.addImage with inCell=true | XLSX.deleteImage | Pictures in cells |
| Text boxes and shapes | Insert → Text Box, Insert → Shapes | XLSX.getShapes | XLSX.addTextBox, XLSX.addShape | XLSX.deleteShape | Text boxes and shapes |
| Hyperlinks | Insert → Link | XLSX.getHyperlink, XLSX.getHyperlinks | XLSX.addHyperlink | XLSX.deleteHyperlink | Hyperlinks |
| Comments (notes) | Review → Notes | XLSX.getComment, XLSX.getComments | XLSX.addComment | XLSX.deleteComment | Comments |
| Threaded comments | Review → New Comment | XLSX.getThreadedComment, XLSX.getThreadedComments | XLSX.addThreadedComment, XLSX.addThreadedReply, XLSX.setThreadedCommentResolved | XLSX.deleteThreadedComment | Threaded comments |
| Excel feature | In Excel | Read | Create or change | Remove | Guide |
|---|
| Document properties | File → Info → Properties | XLSX.getDocumentProperties, XLSX.getCustomProperty, XLSX.getCustomProperties | XLSX.setDocumentProperties, XLSX.setCustomProperty | XLSX.deleteCustomProperty | Document properties |
| Worksheets | sheet tabs | XLSX.sheetnames, XLSX.sheetcount, XLSX.hassheet | XLSX.addsheet!, XLSX.copysheet!, XLSX.renamesheet!, XLSX.movesheet! | XLSX.deletesheet! | Managing worksheets |
| Sheet visibility | right-click a tab → Hide | XLSX.getSheetVisibility | XLSX.setSheetVisibility | — | Hiding a sheet |
| Tab colour | right-click a tab → Tab Color | XLSX.getTabColor | XLSX.setTabColor | XLSX.removeTabColor | Tab colour |
| Sheet protection | Review → Protect Sheet | XLSX.isSheetProtected, XLSX.getSheetProtection, XLSX.getCellProtection | XLSX.protectSheet, XLSX.setCellProtection | XLSX.unprotectSheet | Protecting a sheet |
| Workbook protection | Review → Protect Workbook | XLSX.isWorkbookProtected, XLSX.getWorkbookProtection | XLSX.protectWorkbook | XLSX.unprotectWorkbook | Protecting the workbook |
| Page setup and printing | Page Layout | XLSX.getPageSetup, XLSX.getPageMargins, XLSX.getPrintOptions, XLSX.getPrintArea, XLSX.getPrintTitles | XLSX.setPageSetup, XLSX.setPageMargins, XLSX.setPrintOptions, XLSX.setPrintArea, XLSX.setPrintTitles | XLSX.removePrintArea, XLSX.removePrintTitles | Printing |
| Headers and footers | Page Layout → Page Setup → Header/Footer | XLSX.getHeaderFooter | XLSX.setHeaderFooter, XLSX.headerFooterField, XLSX.headerFooterFont | XLSX.removeHeaderFooter | Headers and footers |
| Feature | Notes |
|---|
| Calculating formulas | XLSX.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 icon | XLSX.setAutoFilter filters by values, conditions, top or bottom items, the average and dates. |
| Creating region map charts, and some chart subtypes | See the charts Limitations. |
| Pivot tables, slicers and timelines | No 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 SmartArt | XLSX.addShape adds single preset shapes with text, fill and border. |
| Pictures in headers and footers | XLSX.setHeaderFooter sets their text, fields and fonts only. |
| Password-encrypted files | To read an encrypted file, decrypt it first with XLSXDecrypt.jl. |
| VBA macros | Not 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.