XLSX (.xlsx, .xlsm)

Background & Context

    • Registered MIME type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
    • Microsoft Excel spreadsheet file.
    • Standard format of Excel 2007 and higher.
    • Also known as Office Open XML spreadsheet.
    • XML-based file bundle.
    • Stores data, formulas, and graphics.

Import & Export

  • Import["file.xlsx"] imports all sheets of an XLSX file, returning the result as a list of arrays.
  • Each sheet is imported as a full array containing numerical and textual data, Boolean values and date objects.
  • Import["file.xlsx",elem] imports the specified element from an XLSX file.
  • Import["file.xlsx",{elem,sub1,…}] imports subelements specifically useful for partial data import.
  • The import format can be specified with Import["file","XLSX"] or Import["file",{"XLSX",elem,…}].
  • Export["file.xlsx",expr] exports an array or a list of arrays to XLSX.
  • Export["file.xlsx",expr] also works for structured input such as Dataset, Grid and TimeSeries.
  • Export["file.xlsx",expr, elem] creates an XLSX file by treating expr as specifying element elem.
  • Export["file.xlsx",{expr1,expr2,…},{{elem1,elem2,…}}] treats each expri as specifying the corresponding elemi.
  • Export["file.xlsx",{elem1->expr1,elem2->expr2,…},"Rules"] uses rules to specify the elements to be exported.
  • See the following reference pages for full general information:
  • Import, Exportimport from or export to a file
    CloudImport, CloudExportimport from or export to a cloud object
    ImportString, ExportStringimport from or export to a string
    ImportByteArray, ExportByteArrayimport from or export to a byte array

Import Elements

  • General Import elements:
  • "Elements" list of elements and options available in this file
    "Summary"summary of the file
    "Rules"list of rules for all available elements
  • Basic information element:
  • "Dimensions"association of sheet names and sheet dimensions
    "SheetCount"number of sheets
    "Sheets"list of sheet names
  • Data representation elements:
  • "Data"list of full arrays, representing all sheets
    "Dataset"list of Dataset objects, representing all sheets
    "FormattedData"data with some styling elements applied
    "Formulas"all formulas as text
    "Images"embedded images as a list
    "NamedRanges"associations of named ranges as spans
  • Import and Export by default use the "Data" element.
  • Any data representation element can specify {elem,sheets,rows,cols} to extract parts of an XLSX file.
  • Part specification sheet can be any of the following:
  • nnth sheet
    -ncounts from the end
    namesheet name
    n;;msheets n through m
    n;;m;;ssheets n through m with steps of s
    {n1,name,…}specific name or sheet index ni
  • Part specifications row and col can be any of the following:
  • nnth row or column
    -ncounts from the end
    n;;mfrom n through m
    n;;m;;sfrom n through m with steps of s
    {n1,n2,…}specific sheet index ni or name
  • XLSX data is represented in the Wolfram Language by real or integer numbers, strings, Boolean values True and False, and DateList specifications.
  • Import["file.xlsx","Formulas"] gives the Excel formulas for each cell in the form {f1,f2,…}, where the fi are arrays corresponding to the sheets of an XLSX file.
  • Import yields spreadsheets as full rectangular arrays.
  • Export supports full and ragged arrays.

Options

  • Import and Export option:
  • "EmptyField"""how empty cells are represented in the Wolfram Language
  • Import options:
  • "HeaderLines"0number of lines to assume as headers
    "SkipLines"0number of lines to skip at the beginning of the file
  • Possible settings for "HeaderLines" and "SkipLines" are:
  • nn rows to skip or to use as Dataset headers
    {rows,cols}rows and columns to skip or to use as headers
  • Export option:
  • "ImageFormat""JPEG"format used for exporting images

Examples

open allclose all

Basic Examples  (4)

Import the first sheet of an XLSX file:

Import an embedded image from an XLSX file:

Export the first 12 prime numbers to a spreadsheet:

Export two datasets to different sheets:

Scope  (6)

Show the Import elements in a file:

Specify sheet names:

Import the cell at row 1, column 4, for a given sheet:

Import the second sheet:

Export date and date-time stamps:

Import converts a date into a DateObject:

Export raster images to XLSX (automatically rasterizing vector graphics):

Import returns a list of images:

Raw bytes representing common image file formats can also be exported as images to XLSX:

Export a Dataset:

Use the "HeaderLines" option to import table headers:

Import Elements  (14)

"Data"  (6)

Import an XLSX file as a 3D list of values:

This is also the default element:

Import the first sheet of an XLSX file:

Import a single row from a sheet in an XLSX file:

Import a single column from a sheet in an XLSX file:

Import the first 3 rows of a named sheet:

"Dataset"  (1)

Import the first sheet of an XLSX file as a Dataset:

Use the first row as header:

"Dimensions"  (1)

Import the dimensions of the sheets in an XLSX file:

"FormattedData"  (1)

Import XLSX data, preserving basic formatting with Style:

"Formulas"  (1)

Import the raw data of all formula cells in an XLSX file:

"Images"  (1)

Import an embedded image from an XLSX file:

"NamedRanges"  (1)

Get named ranges in an XLSX file:

Import a specific range of data:

"SheetCount"  (1)

Import the number of sheets in an XLSX file:

"Sheets"  (1)

Import the names of the sheets in an XLSX file:

Import Options  (6)

"EmptyField"  (1)

Some XLSX files may contain empty cells. Create a file with empty fields:

By default, empty fields are imported as "":

Specify a different value for empty fields:

"HeaderLines"  (4)

By default, no row or column is assumed to be a header line:

Use the first row as a header line:

Specify row headers:

Specify row and column headers:

"SkipLines"  (1)

Some XLSX sheets may include a comment line.

Create a file with a comment line:

Skip the comment line:

Skip the comment line, and use the next line as a Dataset header:

Possible Issues  (2)

The top-left corner of data is lost when importing a Dataset with row and column headers:

Dataset may look different depending on the dimensions of the data: