MATLAB Answers

How to clear existing figure in excel using matlab?

9 views (last 30 days)
Elizabeth
Elizabeth on 2 Feb 2016
Answered: Emanuel Arth on 3 May 2017
I have an excel that is connected to matlab through active server. I am generating a graph via matlab and export it to excel. However, I need to clear the existing graphs first so it won't overwrite and make a stacked of graphs.
My idea is to find the existing graph first, and clear it. But, so far, I still don't know how to do it.
Anyone knows? I am open to any suggestions.
Thank you so much!
Elizabeth

  4 Comments

Show 1 older comment
Elizabeth
Elizabeth on 2 Feb 2016
This is how I code it.
Excel = actxserver ('Excel.Application');
filename = 'xxx'
invoke(Excel.Workbooks,'Open',filename);
Sheets = Excel.ActiveWorkBook.Sheets;
Sheets.Item('Graphs').Activate;
ActiveSheets = Excel.Activesheet;
plotpie(cost,init_stage)
Shapes = ActiveSheets.Shapes;
ActiveSheets.Shapes.AddPicture('location', 0, 1, 400, 325, 345, 230)
So, I send input to the plotpie. The plot pie is basically pie chart with some modifications I make for the input. Then, I save the figure, and export it to excel.
function plotpie(cost,init_stage)
% some loop for the input
q = figure;
set(q, 'visible', 'off');
pie(cost,Process);
print('D:\location.jpg', '-djpeg')
Guillaume
Guillaume on 3 Feb 2016
I don't think Elizabeth wants to delete the graph actually. That would break her code. She wants to delete the shape that's been added to the worksheet.

Sign in to comment.

Accepted Answer

Guillaume
Guillaume on 3 Feb 2016
Here is a more efficient version of your original code:
excel = actxserver('Excel.Application');
workbook = excel.Workbooks.Open(filename);
graph = Workbook.Sheets.Item('Graphs');
plotpie(cost, init_stage);
graph.Shapes.AddPicture('location', 0, 1, 400, 325, 345, 230);
To delete the picture you've added, and assuming that it is the only picture on the Graphs worksheet:
graph.Shapes.Item(1).Delete

  1 Comment

Elizabeth
Elizabeth on 5 Feb 2016
Hello, sorry for late reply. I will try it asap.
There are 4 graphs in the excel sheet. However, previous graphs needs to be removed before the new one is exported anyway so it won't stacked on each other. Thanks.

Sign in to comment.

More Answers (2)

Elizabeth
Elizabeth on 5 Feb 2016
Edited: Guillaume on 5 Feb 2016
The solution:
Sheets = Excel.ActiveWorkBook.Sheets;
Sheets.Item('Graphs').Activate;
ActiveSheets = Excel.Activesheet;
myshapes = ActiveSheets.Shapes;
for j = myshapes.Count:-1:2
myshapes.Item(j).Delete
end

  1 Comment

Guillaume
Guillaume on 5 Feb 2016
Yes, if you want to delete all the shapes (note: they're not what excel call graphs, it's a completely different concept) you simply iterate over the Shapes collection elements and delete them one by one.
Note that it's a bit pointless to activate the 'Graph' Sheet just to get a reference to it. Your above code can be simplified to:
Sheets = Excel.ActiveWorkBook.Sheets;
GraphSheet = Sheets.Item('Graph');
myshapes = GraphSheet.Shapes;
for j = myshapes.Count:-1:2 %why are you stopping at 2 and not 1?
myshapes.Item(j).Delete
end

Sign in to comment.


Emanuel  Arth
Emanuel Arth on 3 May 2017
Hi,
I have the same problem right now but I can't fix it with your code.
My working code (without the delete function):
sheet=Page %Indicates the Page on which I want to set the figure
Excel = actxserver ('Excel.Application');
File=Dateiname;
if ~exist(File,'file')
ExcelWorkbook = Excel.workbooks.Add;
ExcelWorkbook.SaveAs(File,1);
ExcelWorkbook.Close(false);
end
invoke(Excel.Workbooks,'Open',File);
Sheets = Excel.ActiveWorkBook.Sheets;
SheetNo = get(Sheets, 'Item', sheet);
SheetNo.Activate;
xls=Excel.ActiveSheet
print (figure1,'-dbitmap'); xls.Range('I85').PasteSpecial; %Prints my figure on the page
xls.Shapes.Item(1).Height=(2334/7);
invoke(Excel.ActiveWorkbook,'Save')
Excel.Quit
Excel.delete
clear Excel
I run this code 4 times. So I have 4 figures on my active Excel sheet. But when I run this code again I don't want to add new figures to the already existing - I want to delete the figures that are in this actual sheet and then add the new ones.
Can you help me? Which code do I need ? I tried following but it didn't work for me:
....
SheetNo.Activate;
Sheets.Item('Graphs').Activate;
ActiveSheets = Excel.Activesheet;
myshapes = ActiveSheets.Shapes;
for j = myshapes.Count:-1:1
myshapes.Item(j).Delete
end
xls=Excel.ActiveSheet
...
Thank you !
Emanuel

  0 Comments

Sign in to comment.

Sign in to answer this question.