MATLAB: Readtable / readcell / readmatrix not reading xlsx calculated column

excelMATLABmicrosoftofficereadcellreadmatrix()readtablespreadsheetxlsread

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.

Best Answer

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.

Related Solutions

MATLAB: How to rename a sheet in Excel using the COM interface in MATLAB

You can rename an Excel sheet using the "Name" property of a sheet object. For example the following renames the first sheet of an Excel file:

filename = 'C:\SomeExcelFile.xls';
% Open Excel Automation server
Excel = actxserver('Excel.Application');
% Make Excel visible
Excel.Visible=1;
% Open Excel file
Workbook = Excel.Workbooks.Open(filename);
% Get the list of sheets in the workbook
Sheets = Excel.ActiveWorkbook.Sheets;
% Rename the first sheet
Sheets.Item(1).Name = 'This is sheet 1';

The following code then saves the Workbook, quits Excel and removes the COM server:

% Save the file
Workbook.Save();
% Quit Excel, remove the COM server and delete the related objects
Excel.Quit();
Excel.delete();
clear Excel;
clear Workbook;
clear Sheets;

MATLAB: How to open an existing Excel file and communicate with it using ActiveX

This bug has been fixed in Release 14 Service Pack 3 (R14SP3). For previous product releases, read below for any possible workarounds:

The following provides an example of opening an existing Excel file named example.xls and proceeding to write and read with it:

% Specify file name
file = 'D:\Applications\MATLAB701\work\example.xls'; % This must be full path name
% Open Excel Automation server
Excel = actxserver('Excel.Application');
Workbooks = Excel.Workbooks;
% Make Excel visible
Excel.Visible=1;
% Open Excel file
Workbook=Workbooks.Open(file);
% Specify sheet number, data, and range to write to 
sheetnum=1;
data=rand(4);  % use a cell array if you want both numeric and text data
range = 'F10:I13';
% Make the first sheet active
Sheets = Excel.ActiveWorkBook.Sheets;
sheet1 = get(Sheets, 'Item', sheetnum);
invoke(sheet1, 'Activate');
Activesheet = Excel.Activesheet;
% Put MATLAB data into Excel
ActivesheetRange = get(Activesheet,'Range',range);
set(ActivesheetRange, 'Value', data);
% Here you might manipulate the data in Excel
% Now read the data from the sheet; you could specify a new range here
Range = get(Activesheet,'Range',range);
out = Range.value;
% Save file
invoke(Workbook,'Save')
% Close Excel and clean up
invoke(Excel,'Quit');
delete(Excel);
clear Excel;

Best Answer

Related Solutions

MATLAB: How to rename a sheet in Excel using the COM interface in MATLAB

MATLAB: How to open an existing Excel file and communicate with it using ActiveX

Related Question