Search code examples
excelmatlabxlsread

Matlab - get formula from excel with xlsread?

In the documentation doc xlsread it is described that one can use [num,txt,raw] = xlsread('example.xls') to pull data from excel sheets into matlab.

I have a sheet containing formulas to be copied to other sheets, however xlread will take the interpreted formula value instead of the formula itself. For example one formula is =AVERAGE(B8:V8) which is what I would like to pull from the sheet programmatically, but instead excel returns the value 0.810 which is what the formula would return.

Is it possible to extract the formula in any way with matlab?

Solution

  • It is not possible with xlsread only.

    One example of using a COM Excel object:

    Let's use a simple excel sheet for example, containing text, values and formula :

    excel snippet

    Then the following code:

    xlfile  = 'test1.xlsx' ;
xlRange = 'B3:C6' ;

exl = actxserver('excel.application');                  %// Create a COM server
exlFile    = exl.Workbooks.Open( [pwd '\' xlfile] );    %'// Open the file
exlSheet1  = exlFile.Sheets.Item('Sheet1');             %// Choose the worksheet
strFormula = exlSheet1.Range(xlRange).Formula           %// Read the full range

    Yields a nice cell array :

    strFormula = 
    'This is text'       'hello'          
    'this is value'      '12.5'           
    'this is value'      '29'             
    'this is formula'    '=AVERAGE(C4:C5)'

    If you know directly the address of a specific cell, you return a simple string:

    cellFormula = exlSheet1.Range('C6').Formula             %// Read a single cell

cellFormula =
=AVERAGE(C4:C5)