WOLFRAM

Wolfram Language & System Documentation Center

XLS (.xls)

  • Import supports XLS files created by Excel 95, 97, 2000, XP, and 2003.
  • Export creates files compatible with the Excel 2000 standard.

Background & Context

    • Registered MIME type: application/vnd.ms-excel
    • Microsoft Excel spreadsheet file.
    • XLS is an acronym derived from Excel Spreadsheet.
    • Stores spreadsheet data and formulas.

Import & Export

  • Import["file.xls"] imports all sheets of an XLS 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.xls",elem] imports the specified element from an XLS file.
  • Import["file.xls",{elem,sub1,…}] imports subelements specifically useful for partial data import.
  • The import format can be specified with Import["file","XLS"] or Import["file",{"XLS",elem,…}].
  • Export["file.xls",expr] exports an array or a list of arrays to XLS.
  • Supported expressions expr include:
  • {v1,v2,…}a single column of data
    {{v11,v12,…},{v21,v22,…},…}lists of rows of data
    arrayan array such as SparseArray, QuantityArray, etc.
    tseriesa TimeSeries, EventSeries or a TemporalData object
    Dataset[…]a dataset
    Tabular[…]a tabular object
    {data1,…}a list of data to be exported as sheets
  • Export["file.xls",{expr1,expr2,…},{{elem1,elem2,…}}] treats each expri as specifying the corresponding elemi.
  • Export["file.xls",{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
    "EventSeries"table data as an EventSeries
    "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
    "Tabular"list of Tabular objects, representing all sheets
    "TabularAssociation"association of sheet names and Tabular objects
    "TimeSeries"table data as a TimeSeries
  • Import and Export by default use the "Data" element.
  • Any data representation element can specify {elem,sheets,rows,cols} to extract parts of an XLS 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
  • XLS data is represented in the Wolfram Language by real or integer numbers, strings, Boolean values True and False, and DateList specifications.
  • Import["file.xls","Formulas"] gives the Excel formulas for each cell in the form {f1,f2,…}, where the fi are arrays corresponding to the sheets of an XLS file.
  • Import yields spreadsheets as full rectangular arrays.
  • Export supports full and ragged arrays.

Options

  • Import and Export options:
  • "EmptyField"""how empty cells are represented in Wolfram Language
  • Import options:
  • "HeaderLines"Automaticnumber of lines to assume as headers
    "SkipLines"Automaticnumber of lines to skip at the beginning of the file
    "TimeColumn"Automaticcolumn to use for times in "EventSeries" and "TimeSeries" elements
  • 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 options:
  • "ImageFormat""JPEG"format used for exporting images

Examples

open all close all

Basic Examples  (5)

Import the first sheet of an XLS file:

Wolfram Language code: Import["ExampleData/elements.xls", {"Data", 1}]

Import as a Tabular object:

Wolfram Language code: Import["ExampleData/elements.xls", {"Tabular", 1}]

Import summary of the file:

Wolfram Language code: Import["ExampleData/elements.xls", "Summary"]

Embed graphics in an XLS file:

Wolfram Language code: Export["image.xls", {Range[5]}, "Images" -> [image]]

Import an embedded image from an XLS file:

Wolfram Language code: Import["image.xls", "Images"]

Export the first 12 prime numbers to a spreadsheet:

Wolfram Language code: primes = Table[{i, Prime[i]}, {i, 12}]
Wolfram Language code: Export["primes.xls", primes]

Export two datasets to different sheets:

Wolfram Language code: data1 = Table[{t, 9.8 * t ^ 2}, {t, 0, 3, .1}]; data2 = Table[{t, 9.8 * t ^ 2}, {t, 0, 5, .2}];
Wolfram Language code: Export["datasets.xls", "Sheets" -> {"Experiment 1" -> data1, "Experiment 2" -> data2}, "Rules"]

Scope  (8)

Import  (3)

Show the Import elements in a file:

Wolfram Language code: Import["ExampleData/elements.xls", "Elements"]

Import the first sheet as a Tabular object:

Wolfram Language code: Import["ExampleData/elements.xls", {"Tabular", 1}]

Import all sheets as an association of sheet names and Tabular objects:

Wolfram Language code: Import["ExampleData/elements.xls", "TabularAssociation"]

Export  (5)

Specify sheet names:

Wolfram Language code: Export["sheets.xls", {{"MySheet1" -> {Range[10]}, "MySheet2" -> {{"This is a string."}}}}, {{"Sheets"}}]

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

Wolfram Language code: Import["sheets.xls", {"Data", "MySheet1", 1, 4}]

Import the second sheet:

Wolfram Language code: Import["sheets.xls", {"Data", 2}]

Export date and date-time stamps:

Wolfram Language code: Export["dates.xls", {Transpose@{{"date + time", "date"}, {DateList[], {2010, 1, 1}}}}]

Import converts a date into a DateObject:

Wolfram Language code: Import["dates.xls"]

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

Wolfram Language code: Export["images.xls", {{"Multiple Images"}}, "Images" -> {[image], [image]}]

Import returns a list of images:

Wolfram Language code: Import["images.xls", "Images"]

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

Wolfram Language code: Export["images.xls", {{"Image"}}, "Images" -> {Import["ExampleData/coneflower.jpg", "String"]}]

Export a Dataset:

Wolfram Language code: dataset = Dataset[{ <|"a" -> 1, "b" -> "x"|>, <|"a" -> 2, "b" -> "y"|>, <|"a" -> 3, "b" -> "z"|>, <|"a" -> 4, "b" -> "x"|>, <|"a" -> 5, "b" -> "y"|>, <|"a" -> 6, "b" -> "z"|>}]
Wolfram Language code: input = Export["dataset.xls", dataset, "XLS"]

Use the "HeaderLines" option to import table headers:

Wolfram Language code: Import[input, {"XLS", "Dataset", 1}, "HeaderLines" -> 1]

Import Elements  (18)

"Data"  (6)

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

Wolfram Language code: Import["ExampleData/elements.xls", "Data"]

This is also the default element:

Wolfram Language code: Import["ExampleData/elements.xls"]

Import the first sheet of an XLS file:

Wolfram Language code: Import["ExampleData/elements.xls", {"Data", 1}]

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

Wolfram Language code: Import["ExampleData/elements.xls", {"Data", 1, 2}]

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

Wolfram Language code: Import["ExampleData/elements.xls", {"Data", 1, All, 2}]

Import the first 3 rows of a named sheet:

Wolfram Language code: Import["ExampleData/elements.xls", "Sheets"]
Wolfram Language code: Import["ExampleData/elements.xls", {"Data", "Spreadsheet1", 1 ;; 3}]

"Dataset"  (1)

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

Wolfram Language code: Import["ExampleData/elements.xls", {"Dataset", 1}]

Use the first row as header:

Wolfram Language code: Import["ExampleData/elements.xls", {"Dataset", 1}, "HeaderLines" -> 1]

"Dimensions"  (1)

Import the dimensions of the sheets in an XLS file:

Wolfram Language code: Import["ExampleData/wind.xls", "Dimensions"]

"EventSeries"  (1)

Import the first 10 rows from the first sheet of an XLS file as an EventSeries:

Wolfram Language code: Import["ExampleData/mrrogers.xls", {"EventSeries", 1, 1 ;; 10}]

"FormattedData"  (1)

Import XLS data, preserving basic formatting with Style:

Wolfram Language code: Import["ExampleData/wind.xls", {"FormattedData", 1, 1 ;; 10}]

"Formulas"  (1)

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

Wolfram Language code: Import["ExampleData/wind.xls", {"Formulas", 1}]

"Images"  (1)

Import an embedded image from an XLS file:

Wolfram Language code: Import["ExampleData/wind.xls", "Images"]

"NamedRanges"  (1)

Get named ranges in an XLS file:

Wolfram Language code: ranges = Import["ExampleData/mrrogers.xls", "NamedRanges"]

Import a specific range of data:

Wolfram Language code: Import["ExampleData/mrrogers.xls", {"FormattedData", "1984", Sequence@@ranges["1984", "Apr"]}]

"SheetCount"  (1)

Import the number of sheets in an XLS file:

Wolfram Language code: Import["ExampleData/wind.xls", "SheetCount"]

"Sheets"  (1)

Import the names of the sheets in an XLS file:

Wolfram Language code: Import["ExampleData/wind.xls", "Sheets"]

"Tabular"  (1)

Import the first sheet of an XLS file as a Tabular:

Wolfram Language code: Import["ExampleData/elements.xls", {"Tabular", 1}]

Use the first row as header:

Wolfram Language code: Import["ExampleData/elements.xls", {"Tabular", 1}, "HeaderLines" -> 1]

"TabularAssociation"  (1)

Import all sheet as an association of sheet names and Tabular objects:

Wolfram Language code: Import["ExampleData/elements.xls", "TabularAssociation"]

"TimeSeries"  (1)

Import the first 10 rows from the first sheet of an XLS file as a TimeSeries:

Wolfram Language code: Import["ExampleData/mrrogers.xls", {"TimeSeries", 1, 1 ;; 10}]

Import Options  (7)

"EmptyField"  (1)

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

Wolfram Language code: Export["file.xls", {{}, {"Name", "Age"}, {"Alice", 40}, {"Bob", 35}}]

By default, empty fields are imported as empty strings "":

Wolfram Language code: Import["file.xls"]

Specify the value of empty fields:

Wolfram Language code: Import["file.xls", "EmptyField" -> "XXXX"]

"HeaderLines"  (4)

By default, a header line is detected automatically:

Wolfram Language code: Import["ExampleData/elements.xls", {"XLS", "Dataset", 1}]

Specify column headers:

Wolfram Language code: Import["ExampleData/elements.xls", {"XLS", "Dataset", 1}, "HeaderLines" -> 1]

Specify row headers:

Wolfram Language code: Import["ExampleData/elements.xls", {"XLS", "Dataset", 1}, "HeaderLines" -> {0, 1}]

Specify row and column headers:

Wolfram Language code: Import["ExampleData/elements.xls", {"XLS", "Dataset", 1}, "HeaderLines" -> {1, 1}]

"SkipLines"  (1)

Some XLS sheets may include a comment line.

Skip the comment line:

Wolfram Language code: Import["ExampleData/wind.xls", {"XLS", "Dataset", 1}, "SkipLines" -> 1]

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

Wolfram Language code: Import["ExampleData/wind.xls", {"XLS", "Dataset", 1}, "SkipLines" -> 1, "HeaderLines" -> 1]

"TimeColumn"  (1)

By default, the time column is selected automatically for "TimeSeries" and "EventSeries" elements:

Wolfram Language code: Import["ExampleData/mrrogers.xls", {"TimeSeries", 1, 1 ;; 10}]

Use the "TimeColumn" option to specify the time column:

Wolfram Language code: Import["ExampleData/mrrogers.xls", {"TimeSeries", 1, 1 ;; 10}, "TimeColumn" -> "Episode Number"]

Possible Issues  (2)

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

Wolfram Language code: Import["ExampleData/elements.xls", {"XLS", "Dataset", 1}, "HeaderLines" -> {1, 1}]

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

Wolfram Language code: Export["test.xls", Partition[Take[Alphabet[], 9], 3]]
Wolfram Language code: Import["test.xls", {"XLS", "Dataset", 1}, "HeaderLines" -> {0, 1}]
Wolfram Language code: Dimensions[%]
Wolfram Language code: Export["test.xls", Partition[Take[Alphabet[], 24], 8]]
Wolfram Language code: Import["test.xls", {"XLS", "Dataset", 1}, "HeaderLines" -> {0, 1}]
Wolfram Language code: Dimensions[%]