XLS (.xls)

Background

    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 and Export

  • Import["file.xls"] imports all sheets of an XLS file, returning the result as a list of arrays.
  • Export["file.xls",expr] exports an array or a list of arrays to XLS.
  • Import and Export also handle embedded images.
  • Import["file.xls"] returns the sheets of an XLS 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.xls",expr] creates an XLS file from array data.
  • The following expression types are supported by Export: Column, Grid, List, MatrixForm, Row, SparseArray, and TableForm.
  • Import["file.xls",elem] imports the specified element from an XLS file.
  • 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, elem] creates an XLS file by treating expr as specifying element elem.
  • Export["file.xls",{expr1,expr2,},{{elem1,elem2,}}] treats each expri as specifying the corresponding elemi.
  • Export["file.xls",expr,opt1->val1,] exports expr with the specified option elements taken to have the specified values.
  • Export["file.xls",{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 XLS 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
  • Basic information element:
  • "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
  • 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
    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
    n;;mfrom n through m
    {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"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 options:
  • "ImageFormat""JPEG"format used for exporting images

Examples

open allclose all

Basic Examples  (4)

Import the first sheet of an XLS file:

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

Embed graphics in an XLS file:

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

Import an embedded image from an XLS file:

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

Export the first 12 prime numbers to a spreadsheet:

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

Export two datasets to different sheets:

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

Scope  (5)

Import Elements  (11)

Import Options  (6)

Possible Issues  (2)

See Also

"XLSX"  "MDB"  "CSV"  "TSV"

Introduced in 2004
(5.1)
| Updated in 2018
(11.3)