MATLAB: How to create chart objects in Excel from MATLAB 7.8 (R2008a)

MATLAB

I want to create chart objects such as bar graphs in an excel sheet programmatically from inside MATLAB.

Best Answer

To be able to access or create chart objects in excel sheets, you have to communicate with Excel from MATLAB using COM. To do this, you will have to create an Automation server in MATLAB using the actxserver command and use COM API to talk to Excel. Once you establish this connection, you can use commands similar to the excel VBA editor to programmatically add or edit objects in excel sheet.

Here is an example on how you could access data in excel sheet and draw chart objects. The chart Book.xlsx that contains the data is attached.

excel = actxserver('excel.application');
excel.Visible = 1;
wkbk = excel.Workbooks.Open('<FullPath>\Book.xlsx');
wkbk.ActiveSheet;
wkbk.ActiveSheet.Shapes.AddChart.Select;
wkbk.ActiveChart.SetSourceData(wkbk.ActiveSheet.get('Range','A2:B4'))
wkbk.ActiveChart.ChartType = 'xlBarClustered';
wkbk.SaveAs('<FullPath>\Book2.xlsx'); % save the changes
wkbk.Close;
excel.Quit; % close excel 
excel.delete;

The above API commands is provided by Microsoft Excel and for more specific information on these, refer to the Microsoft Excel's documentation page.

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 programmatically set the elements of a chart object in Excel 2007 from MATLAB 7.8 (R2009a)

To access a chart object in Excel 2007 from MATLAB 7.8 (R2009a),

1) Start Excel as an ACTXSERVER application in MATLAB.

2) Open the Excel workbook.

3) Open the worksheet that contains the chart.

4) Get the Chart object to modify.

5) Implement the code that modifies the elements of the existing chart.

The following code provides an example on how to achieve this.

excel = actxserver('excel.application')
wkbk = excel.Workbooks.Open('Book1.xlsx') % open the excel file, full path need to be mentioned or else excel will pick it from most recently opened files.
wksheet = wkbk.Worksheets.Item('Sheet1') % Choose desired sheet
co = wksheet.ChartObjects.Item(1) % select the plot (this selects the first plot
co.Chart.ChartTitle.Text ='myplot1'  % Change the title
wkbk.Save % save the changes
excel.Quit % close excel

Also, see the link below for the way to do it using Microsoft Visual Studio with C# and Visual Basic:

 <http://msdn.microsoft.com/en-us/library/bb404904.aspx>

Best Answer

Related Solutions

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

MATLAB: How to programmatically set the elements of a chart object in Excel 2007 from MATLAB 7.8 (R2009a)

Related Question