How to clear existing figure in excel using matlab?

5 次查看（过去 30 天）
Elizabeth
Elizabeth 2016-2-2
回答： Emanuel Arth 2017-5-3
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 个评论
显示 2更早的评论
Guillaume
Guillaume 2016-2-3
编辑：Guillaume 2016-2-3
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.

请先登录，再进行评论。

请先登录，再回答此问题。

采纳的回答

Guillaume
Guillaume 2016-2-3
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 个评论
Elizabeth
Elizabeth 2016-2-5
编辑：Elizabeth 2016-2-5
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.

请先登录，再进行评论。

更多回答（2 个）

Elizabeth
Elizabeth 2016-2-5
编辑：Guillaume 2016-2-5
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
thanks! Another reference: http://stackoverflow.com/questions/18958994/delete-excel-figures-from-matlab
  1 个评论
Guillaume
Guillaume 2016-2-5
编辑：Guillaume 2016-2-5
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

请先登录，再进行评论。

Emanuel Arth
Emanuel Arth 2017-5-3
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

类别

在 Help Center 和 File Exchange 中查找有关 Startup and Shutdown 的更多信息

标签

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by