readtable / readcell / readmatrix not reading xlsx calculated column

I am trying not to use xlsread and switching my code to readtable so it doesn't need Microsoft Office.

The column I'm trying to read is Column C in the attached file, which is a sumation of all other columns, however when I do readtable (and all other variants readcell / readmatrix / readvars / xlsread in basic mode ) I get 0s for that column instead of the sum. I've tried all Name Value pair options and spreadsheetImportOptions as well.

A possible solution would be to add the columns in Matlab, but these are dynamic files created by another entity and I'd rather take their calculation in case it changes.

I also tried xlsread in basic mode, but the function call just hangs.

Answer 1

[Ricardo de Azevedo]

From Mathworks Support:

Unfortunately, as of now, there is no functionality to read the values of cells that are computed using formulas using MATLAB on Linux.

I have informed the Development Team about the possibility of having this kind of feature for Linux users so that they will consider including this functionality in a future release.

I will close this case for now. However, if you have any follow-up questions, please let me know and I will be happy to assist you further.

Answer 2

[Ricardo de Azevedo]

Response from Mathworks (not yet resolved as I can't use Excel / MS Office):

It is my understanding that you would like to read the values in column 'C' obtained by the formula defined in the Excel spreadsheet. Below, I have listed two ways you can achieve that:

1) Using the function "readtable" or "readmatrix" with the flag 'UseExcel'-true (to read the results after applying the formulas). The code snippet below reads column 'C' from row 14 to row 2000.

>> T = readtable('022020.xlsx','Range','C14:C2000','UseExcel',true);

I recommend that you use the flag 'UseExcel'-true for the specific columns containing formulas, since there is an overhead associated with this flag (importing may be slower).

2) The second option is to use a COM Automation server to access another application from MATLAB. This creates a user interface to access the data in a Microsoft Excel file.

The code snippet below shows an example of how to achieve this using the Excel file you shared with us:

>> ex = actxserver('Excel.Application'); % Run Excel in Automation Server

>> wb = ex.Workbooks.Open('C:\Users\full_path_to_your_file\022020.xlsx');% Open file

>> ws = wb.Worksheets.Item(1); % Select Sheet 1

>> data = ws.usedRange.Value; % Obtain the Value of the cells in Sheet 1

>> wb.Close(); % Close file

>> ex.Quit(); % Quit application

>> clear ex wb ws % clear variables

Please note that for the code snippet above to work, you must replace the path passed to "wb = ex.Workbooks.Open(...)" with your full path to the file. The variable "data" contains the information of the Excel sheet as a cell array. After importing the file to the variable "data" you can process it using MATLAB.

For more information on the function "readtable" and the flag "UseExcel" please click on the link below:

https://www.mathworks.com/help/matlab/ref/readtable.html

For more information on Automation Server for Excel files, please click on the link below:

https://www.mathworks.com/help/matlab/matlab_external/example-reading-excel-spreadsheet-data.html