Take the 2-minute tour ×
Mathematica Stack Exchange is a question and answer site for users of Mathematica. It's 100% free, no registration required.

Has anyone had any success with importing an Excel Spreadsheet holding an array formula into Mathematica?

The Syntax I'm using is

Import["test.xlsx", {"Sheets", "SheetWithArrayFormula"}]

The error that is returned is: Import::fmterr: Cannot import data as XLSX format. >>

EDIT

A sample excel file can be found here: http://temp-share.com/show/gFHKQZS0Y

share|improve this question
    
Please provide a small sample Excel file for testing. –  Mr.Wizard Jul 19 '13 at 1:48
    
I've made an edit to contain a link with a sample excel file. –  Jonie Jul 19 '13 at 1:58

1 Answer 1

up vote 6 down vote accepted

It seems there are some troubles with the XLSX format, whereas using the old XLS it works fine. Look the following tests:

Import["c:\\tempmath\\test.xlsx", "Sheets"]

{"SheetWithArrayFormula"}

Import["c:\\tempmath\\test.xlsx", {"Sheets", "SheetWithArrayFormula"}]

Import::fmterr: Cannot import data as XLSX format. >>

$Failed

Import["c:\\tempmath\\test.xlsx", "Formulas"]

{{{"", "", ""}, {"", "", "A2:A4*B2:B4"}, {"", "", "A2:A4*B2:B4"}, {"", "", "A2:A4*B2:B4"}}}

Import["c:\\tempmath\\test.xlsx"]

Import::fmterr: Cannot import data as XLSX format. >>

$Failed

after converting the XLSX to XLS

Import["c:\\tempmath\\test.xls"]

{{{"X", "Y", "Formula"}, {1., 0.1, 0.1}, {2., 0.2, 0.4}, {3., 0.3, 0.9}}}

Import["c:\\tempmath\\test.xls", "Formulas"]

{{{"", "", ""}, {"", "", "A2:A4*B2:B4"}, {"", "", "A2:A4*B2:B4"}, {"", "", "A2:A4*B2:B4"}}}

Import["c:\\tempmath\\test.xls", {"Sheets", "SheetWithArrayFormula"}]

{{"X", "Y", "Formula"}, {1., 0.1, 0.1}, {2., 0.2, 0.4}, {3., 0.3, 0.9}}

generally when you want to look which elements are available into an external file, use the second Import argument "Elements"

Import["c:\\tempmath\\test.xlsx", "Elements"]

{"Data", "FormattedData", "Formulas", "Images", "Sheets"}

So, I guess the problem is not due to the Excel file contents (array formulas), rather to some wrong behavior of Import and XLSX.

share|improve this answer
    
I wonder if there is a workaround using "Data"? –  Verbeia Jul 19 '13 at 22:43
    
Unfortunately, even adding "Data" it doesn't work. However, you can convert the file to xls format and it works. –  bobknight Jul 20 '13 at 4:56
    
Yes, but that's going to be very unpopular with anyone hoping to use Mathematica for visualizing a lot of data stored in Excel. It is clearly a bug in Mathematica. –  Verbeia Jul 20 '13 at 7:27
    
@Verbeia that's right, I already sent a report to WRI technical support. –  bobknight Jul 20 '13 at 7:45

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.