8
$\begingroup$

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

$\endgroup$
  • $\begingroup$ Please provide a small sample Excel file for testing. $\endgroup$ – Mr.Wizard Jul 19 '13 at 1:48
  • $\begingroup$ I've made an edit to contain a link with a sample excel file. $\endgroup$ – Jonie Jul 19 '13 at 1:58
  • 2
    $\begingroup$ I have been informed by WRI that the reported issue has been resolved; a quick check done by me revealed no difficulties with array formulas in excel files being imported. I thus added an updated bug header. $\endgroup$ – gwr Sep 26 '16 at 17:46
9
$\begingroup$

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.

$\endgroup$
  • $\begingroup$ I wonder if there is a workaround using "Data"? $\endgroup$ – Verbeia Jul 19 '13 at 22:43
  • $\begingroup$ Unfortunately, even adding "Data" it doesn't work. However, you can convert the file to xls format and it works. $\endgroup$ – bobknight Jul 20 '13 at 4:56
  • $\begingroup$ 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. $\endgroup$ – Verbeia Jul 20 '13 at 7:27
  • $\begingroup$ @Verbeia that's right, I already sent a report to WRI technical support. $\endgroup$ – bobknight Jul 20 '13 at 7:45
  • 3
    $\begingroup$ I just received an answer from WRI technical support: The issue is known and they hope it will be fixed by a future release. Hope dies last as they say. $\endgroup$ – gwr Jun 30 '15 at 20:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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