XLSX (.xlsx)

  • Import and Export are fully compatible with the Excel 2007 standard.
  • Background

      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.
      XLSX is an acronym derived from Excel Spreadsheet.
      XML-based file bundle.
      Stores data, formulas, and graphics.

    Import and Export

    • Import["file.xlsx"] imports all sheets of an XLSX file, returning the result as a list of arrays.
    • Export["file.xlsx",expr] exports an array or a list of arrays to XLSX.
    • Import and Export also handle embedded images.
    • Import["file.xlsx"] returns the sheets of an XLSX file as an expression of the form {s1, s2,}, where the si are full arrays containing numerical and textual data, Boolean values, and date specifications.
    • Export["file.xlsx",expr] creates an XLSX file from array data.
    • The following expression types are supported by Export: Column, Grid, List, MatrixForm, Row, SparseArray, and TableForm.
    • Import["file.xlsx",elem] imports the specified element from an XLSX file.
    • Import["file.xlsx",{elem,suba,subb,}] imports a subelement.
    • Import["file.xlsx",{{elem1,elem2,}}] imports multiple elements.
    • The import format can be specified with Import["file","XLSX"] or Import["file",{"XLSX",elem,}].
    • 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",expr,opt1->val1,] exports expr with the specified option elements taken to have the specified values.
    • Export["file.xlsx",{elem1->expr1,elem2->expr2,},"Rules"] uses rules to specify the elements to be exported.
    • See the reference pages for full general information on Import and Export.
    • ImportString and ExportString support the XLSX format.

    Elements

    • General Import elements:
    • "Elements"list of elements and options available in this file
      "Rules"full list of rules for each element and option
      "Options"list of rules for options, properties, and settings
    • Data representation elements:
    • "Data"list of full arrays, representing all sheets
      "Formulas"all formulas as text
      "Images"embedded images as a list
    • Advanced data elements:
    • "FormattedData"data with some styling elements applied
    • Import and Export by default use the "Data" element.
    • XLSX data is represented in the Wolfram Language by real or integer numbers, strings, Boolean values True and False, and DateList specifications.
    • XLSX error cells are imported as $Failed.
    • 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.
    • Importing parts of a data sheet:
    • "Data",kthe k^(th) sheet, given as an array
      "Data",k,mrow m of the k^(th) sheet
      "Data",k,m,ncell {m,n} of the k^(th) sheet
    • Import["file.xlsx", {"Data",1}] returns the first sheet as an array.
    • The following can be used to select or specify individual sheets by name:
    • "Sheets"list of sheet names
      "Sheets",namesheet name as an array
      "Sheets",name,mrow m of sheet name
      "Sheets",name,m,ncell {m,n} in sheet name
    • 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 Wolfram Language
    • Export option:
    • "ImageFormat""JPEG"format used for exporting images

    Examples

    open allclose all

    Basic Examples  (4)

    Import the first sheet of an XLSX file:

    In[1]:=
    Click for copyable input
    Out[1]=

    Import an embedded image from an XLSX file:

    In[1]:=
    Click for copyable input
    Out[1]=

    Export the first 12 prime numbers to a spreadsheet:

    In[1]:=
    Click for copyable input
    Out[1]=

    Export data again with a header row:

    In[2]:=
    Click for copyable input
    Out[2]=

    Export two datasets to different sheets:

    In[1]:=
    Click for copyable input
    In[2]:=
    Click for copyable input
    Out[2]=

    Scope  (6)

    See Also

    "XLS"  "MDB"  "CSV"  "TSV"

    Introduced in 2010
    (8.0)
    | Updated in 2012
    (9.0)