OpenersPrimaryExamplesSection(Spacer1)Examples  (28)

OpenersExampleSection0(Spacer1)Basic Examples  (4)

Import the first sheet of an XLS file:

Import["ExampleData/elements.xls",{"Data",1}]

Embed graphics in an XLS file:

PieChart[Range[5],ChartElementFunction->"GlassSector",ChartStyle->"Pastel"]

Export["image.xls",{Range[5]}, "Images"->%]

Import an embedded image from an XLS file:

Import["image.xls", "Images"]

Export the first 12 prime numbers to a spreadsheet:

primes= {{1,2},{2,3},{3,5},{4,7},{5,11},{6,13},{7,17},{8,19},{9,23},{10,29},{11,31},{12,37}};
Export["primes.xls", primes]

Export two datasets to different sheets:

data1 = Table[{t, 9.8*t^2}, {t, 0, 3, .1}];
data2 = Table[{t, 9.8*t^2}, {t, 0, 5, .2}];

Export["datasets.xls","Sheets"->{"Experiment 1"->data1,"Experiment 2"-> data2},"Rules"]

OpenersExampleSection1(Spacer1)Scope  (5)

Show the (RefLink[Import, paclet:ref/Import]) elements in a file:

Import["ExampleData/elements.xls", "Elements"]

Specify sheet names:

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

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

Import["sheets.xls",{"Data","MySheet1",1,4}]

Import the second sheet:

Import["sheets.xls",{"Data",2}]

Export date and date-time stamps:

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

(RefLink[Import, paclet:ref/Import]) converts a date into a (RefLink[DateObject, paclet:ref/DateObject]):

Import["dates.xls"]

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

Export["images.xls",{{"Multiple Images"}},"Images"-> {,}]

(RefLink[Import, paclet:ref/Import]) returns a list of images:

Import["images.xls","Images"]

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

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

OpenersExampleSection2(Spacer1)Import Elements  (11)

OpenersExampleSubsection0(Spacer1)"Data"  (6)

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

Import["ExampleData/elements.xls", "Data"]

This is also the default element:

Import["ExampleData/elements.xls"]

Import the first sheet of an XLS file:

Import["ExampleData/elements.xls",{"Data",1}]

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

Import["ExampleData/elements.xls",{"Data",1,2}]

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

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

Import the first 3 rows of a named sheet:

Import["ExampleData/elements.xls","Sheets"]

Import["ExampleData/elements.xls",{"Data","Spreadsheet1",1;;3}]

OpenersExampleSubsection1(Spacer1)"Dataset"  (1)

Import the first sheet of an XLS file as a (RefLink[Dataset, paclet:ref/Dataset]):

Import["ExampleData/elements.xls",{"Dataset",1}]

Use the first row as header:

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

OpenersExampleSubsection2(Spacer1)"FormattedData"  (1)

Import XLS data, preserving basic formatting with (RefLink[Style, paclet:ref/Style]):

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

OpenersExampleSubsection3(Spacer1)"Formulas"  (1)

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

Import["ExampleData/wind.xls",{"Formulas",1}]

OpenersExampleSubsection4(Spacer1)"Images"  (1)

Import an embedded image from an XLS file:

Import["ExampleData/wind.xls", "Images"]

OpenersExampleSubsection5(Spacer1)"Sheets"  (1)

Import the names of the sheets in an XLS file:

Import["ExampleData/wind.xls","Sheets"]

OpenersExampleSection4(Spacer1)Import Options  (6)

OpenersExampleSubsection6(Spacer1)"EmptyField"  (1)

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

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

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

Import["file.xls"]

Specify the value of empty fields:

Import["file.xls","EmptyField"-> "XXXX"]

OpenersExampleSubsection7(Spacer1)"HeaderLines"  (4)

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

Import["ExampleData/elements.xls",{"XLS","Dataset",1}]

Specify column headers:

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

Specify row headers:

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

Specify row and column headers:

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

OpenersExampleSubsection8(Spacer1)"SkipLines"  (1)

Some XLS sheets may include a comment line.

Skip the comment line:

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

Skip the comment line, and use the next line as a (RefLink[Dataset, paclet:ref/Dataset]) header:

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

OpenersExampleSection6(Spacer1)Possible Issues  (2)

The top-left corner of data is lost when importing a (RefLink[Dataset, paclet:ref/Dataset]) with row and column headers:

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

(RefLink[Dataset, paclet:ref/Dataset]) may look different depending on the dimensions of the data:

Export["test.xls",Partition[Take[Alphabet[],9],3]]

Import["test.xls",{"XLS","Dataset",1}, "HeaderLines" ->{0,1}]

Dimensions[%]

Export["test.xls",Partition[Take[Alphabet[],24],8]]

Import["test.xls",{"XLS","Dataset",1}, "HeaderLines" ->{0,1}]

Dimensions[%]

({{DocumentationFooterIconsRelatedFunction.png, ({{See Also}, {Related Formats:  (RefLink[XLSX, paclet:ref/format/XLSX])  ▪  (RefLink[MDB, paclet:ref/format/MDB])  ▪  (RefLink[CSV, paclet:ref/format/CSV])  ▪  (RefLink[TSV, paclet:ref/format/TSV])}})}})

({{DocumentationFooterIconsRelatedGuide.png, ({{Related Guides}, {▪(RefLinkPlain[Basic Formats, paclet:guide/BasicFormats])}, {▪(RefLinkPlain[Date & Time, paclet:guide/DateAndTime])}, {▪(RefLinkPlain[Importing & Exporting, paclet:guide/ImportingAndExporting])}, {▪(RefLinkPlain[Importing & Exporting Database Formats, paclet:guide/ImportingAndExportingDatabaseFormats])}, {▪(RefLinkPlain[Listing of All Formats, paclet:guide/ListingOfAllFormats])}, {▪(RefLinkPlain[Numerical Data, paclet:guide/NumericalData])}, {▪(RefLinkPlain[Statistics, paclet:guide/Statistics])}, {▪(RefLinkPlain[Tabular & Spreadsheet Formats, paclet:guide/TabularAndSpreadsheetFormats])}})}})

({{(Spacer1), ({{(Spacer1)Related Links}, {(Spacer1)▪(RefLinkPlain[(Spacer1)How to: Export a Spreadsheet, paclet:howto/ExportASpreadsheet])}})}})