This is machine translation

Translated by Microsoft
Mouse over text to see original. Click the button below to return to the English verison of the page.

Read Spreadsheet Data Using Excel as Automation Server

For alternatives to importing Excel® spreadsheet data into MATLAB®, see the functions and examples in Spreadsheets.

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

Techniques Demonstrated

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

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

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

  • Inserting a MATLAB figure into an Excel file.

Using the UI

To use the UI, 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 UI 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 text that identifies the data contain in the column. These valuess 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 MATLAB.

Excel Automation Server

The first step in accessing the spreadsheet data from MATLAB 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 UI 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 text 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}];

The Plotter UI

This example uses a UI 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 MATLAB 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
      case 2
      case 3
      case 4
      case 5
      case 6
         disp('Select data to plot')
[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)
   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 MATLAB, you must terminate this process explicitly. There is no reason to keep this process running after the UI 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)
end % deleteFig

Inserting MATLAB Graphs Into Excel Spreadsheets

You can save the graph created with this UI 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 UI 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
   exl2.visible = 1;

See Also

More About

Was this topic helpful?