How to clear existing figure in excel using matlab?

Question

Elizabeth 2016-2-2

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/266211-how-to-clear-existing-figure-in-excel-using-matlab

回答： 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更早的评论隐藏 2更早的评论

Walter Roberson 2016-2-2

在 MATLAB Online 中打开

Speculation:

Sheets.Item('Graphs').Delete;

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.

请先登录，再进行评论。

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

Answer 1

Guillaume 2016-2-3

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/266211-how-to-clear-existing-figure-in-excel-using-matlab#answer_208332

在 MATLAB Online 中打开

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 个评论
显示 -1更早的评论隐藏 -1更早的评论

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.

请先登录，再进行评论。

Answer 2

Elizabeth 2016-2-5

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/266211-how-to-clear-existing-figure-in-excel-using-matlab#answer_208698

编辑：Guillaume 2016-2-5

在 MATLAB Online 中打开

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 个评论
显示 -1更早的评论隐藏 -1更早的评论

Guillaume 2016-2-5

编辑：Guillaume 2016-2-5

在 MATLAB Online 中打开

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

请先登录，再进行评论。

Answer 3

Emanuel Arth 2017-5-3

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/266211-how-to-clear-existing-figure-in-excel-using-matlab#answer_265464

在 MATLAB Online 中打开

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
  ...