Importing Excel Spreadsheet containing Array Formulas

Question

Bug introduced in ?.?.? and fixed in 11.0.0

---

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

Answer 1

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.