Contents

Read Excel Spreadsheet Data

This example creates a graphical interface to access the data in a Microsoft® Excel® file. To enable the communication between the MATLAB® software and the spreadsheet program, this example creates an Microsoft ActiveX® object in an Automation server running an Excel application. The MATLAB software then accesses the data in the spreadsheet through the interfaces provided by the Excel Automation server.

Techniques Demonstrated

This example shows how to use the following techniques:

  • Use of an Automation server to access another application from the MATLAB software.

  • Ways to manipulate Excel data into types used in the GUI and plotting.

  • Implementing a GUI that enables plotting of selected columns of the Excel spreadsheet.

  • Inserting a MATLAB figure into an Excel file.

Using the GUI

To use the GUI, select any items in the list box and click the Create Plot button. The sample data provided with this example contain three input and three associated response data sets, all of which are plotted versus the first column in the Excel file, which is the time data.

You can view the Excel data file by clicking the Show Excel Data File button, and you can save an image of the graph in a different Excel file by clicking Save Graph button. Note that the Save Graph option creates a temporary PNG file in the current folder, if you have write-access permission.

The following picture shows the GUI with an input/response pair selected in the list box and plotted in the axes.

Complete Code Listing

You can open the file used to implement this example in MATLAB Editor or run this example:

Excel Spreadsheet Format

This example assumes a particular organization of the Excel spreadsheet, as shown in the following picture.

The format of the Excel file is as follows:

  • The first element in each column is a text string that identifies the data contain in the column. These strings are extracted and used to populate the list box.

  • The first column (Time) is used for the x-axis of all plots of the remaining data.

  • All rows in each column are read into the MATLAB software.

Excel Automation Server

The first step in accessing the spreadsheet data from the MATLAB software is to run the Excel application in an Automation server process using the actxserver function and the program ID, excel.application.

exl = actxserver('excel.application');

The ActiveX object that is returned provides access to a number of interfaces supported by the Excel program. Use the workbook interface to open the Excel file containing the data.

exlWkbk = exl.Workbooks;
exlFile = exlWkbk.Open([docroot '/techdoc/matlab_external/examples/input_resp_data.xls']);

Use the workbook's sheet interface to access the data from a range object, which stores a reference to a range of data from the specified sheet. This example accesses all the data in column A, first cell to column G, last cell:

exlSheet1 = exlFile.Sheets.Item('Sheet1');
robj = exlSheet1.Columns.End(4);       % Find the end of the column
numrows = robj.row;                    % And determine what row it is
dat_range = ['A1:G' num2str(numrows)]; % Read to the last row
rngObj = exlSheet1.Range(dat_range);

At this point, the entire data set from the Excel file's sheet1 is accessed via the range object interface. This object returns the data in a MATLAB cell array, which can contain both numeric and character data:

exlData = rngObj.Value;

Manipulating the Data in the MATLAB Workspace

Now that the data is in a cell array, you can use MATLAB functions to extract and reshape parts of the data into the forms needed to use in the GUI and pass to the plot function.

The following code performs two operations:

  • Extracts numeric data from the cell array (indexing with curly braces), concatenates the individual doubles returned by the indexing operation (square brackets), and reshapes it into an array that arranges the data in columns.

  • Extracts the string in the first cell in each column of an Excel sheet and stores them in a cell array, which is used to generate the items in the list box.

for ii = 1:size(exlData,2)
   matData(:,ii) = reshape([exlData{2:end,ii}],size(exlData(2:end,ii)));
   lBoxList{ii} = [exlData{1,ii}];
end

The Plotter GUI

This example uses a GUI that enables you to select from a list of input and response data from a list box. All data is plotted as a function of time (which is, therefore, not a choice in the list box) and you can continue to add more data to the graph. Each data plot added to the graph causes the legend to expand.

Additional implementation details include:

  • A legend that updates as you add data to a graph

  • A clear button that enables you to clear all graphs from the axes

  • A save button that saves the graph as a PNG file and adds it to another Excel file

  • A toggle button that shows or hides the Excel file being accessed

  • The figure delete function (DeleteFcn property), which the MATLAB software calls when the figure is closed, is used to terminate the Automation server process.

Selecting and Plotting Data

When you click the Create Plot button, its callback function queries the list box to determine what items are selected and plots each data versus time. The legend is updated to display any new data while maintaining the legend for the existing data.

function plotButtonCallback(src,evnt)
iSelected = get(listBox,'Value');
grid(a,'on');hold all
for p = 1:length(iSelected)
   switch iSelected(p)
      case 1
         plot(a,tme,matData(:,2))
      case 2
         plot(a,tme,matData(:,3))
      case 3
         plot(a,tme,matData(:,4))
      case 4
         plot(a,tme,matData(:,5))
      case 5
         plot(a,tme,matData(:,6))
      case 6
         plot(a,tme,matData(:,7))
      otherwise
         disp('Select data to plot')
   end
end
[b,c,g,lbs] = legend([lbs lBoxList(iSelected+1)]);
end % plotButtonCallback

Clearing the Axes

The plotter is designed to continually add graphs as the user selects data from the list box. The Clear Graph button clears and resets the axes and clears the variable used to store the labels of the plot data (used by legend).

%% Callback for clear button
function clearButtonCallback(src,evt)
   cla(a,'reset')
   lbs = '';
end % clearButtonCallback

Display or Hide Excel File

The MATLAB program has access to the properties of the Excel application running in the Automation server. By setting the Visible property to 1 or 0, this callback controls the visibility of the Excel file.

%% Display or hide Excel file
function dispButtonCallback(src,evt)
   exl.visible = get(src,'Value');
end % dispButtonCallback

Close Figure and Terminate Excel Automation Process

Since the Excel Automation server runs in a separate process from the MATLAB software, you must terminate this process explicitly. There is no reason to keep this process running after the GUI has been closed, so this example uses the figure's delete function to terminate the Excel process with the Quit method. Also, terminate the second Excel process used for saving the graph. See Inserting MATLAB Graphs Into Excel Spreadsheets for information on this second process.

%% Terminate Excel processes
function deleteFig(src,evt)
   exlWkbk.Close
   exlWkbk2.Close
   exl.Quit
   exl2.Quit
end % deleteFig

Inserting MATLAB Graphs Into Excel Spreadsheets

You can save the graph created with this GUI in an Excel file. (This example uses a separate Excel Automation server process for this purpose.) The callback for the Save Graph push button creates the image and adds it to an Excel file:

  • Both the axes and legend are copied to an invisible figure configured to print the graph as you see it on the screen (figure PaperPositionMode property is set to auto).

  • The print command creates the PNG image.

  • Use the Shapes interface to insert the image in the Excel workbook.

The server and interfaces are instanced during GUI initialization phase:

exl2 = actxserver('excel.application');
exlWkbk2 = exl2.Workbooks;
wb = invoke(exlWkbk2,'Add');
graphSheet = invoke(wb.Sheets,'Add');
Shapes = graphSheet.Shapes;

The following code implements the Save Graph button callback:

function saveButtonCallback(src,evt)
   tempfig = figure('Visible','off','PaperPositionMode','auto');
   tempfigfile = [tempname '.png'];
   ah = findobj(f,'type','axes');
   copyobj(ah,tempfig) % Copy both graph axes and legend axes
   print(tempfig,'-dpng',tempfigfile);
   Shapes.AddPicture(tempfigfile,0,1,50,18,300,235);
   exl2.visible = 1;
end
Was this topic helpful?