how to paste figure to excel file and then save and exit excel?

39 次查看（过去 30 天）
Stephen
Stephen 2020-8-29
回答： Stephen 2020-9-8
I generated an excel file in my MatLab script, added data to it, and then using PlotInExcel (below), I have added a figure:
function PlotInExcel
x= {1:10};
a= cell2mat(x);
y= {1:10};
b= cell2mat(y);
%............plotting..............................................................................................
plot(a,b);
xlabel('X Values');
ylabel('Y Values');
print -dmeta; %.................Copying to clipboard
FILE = 'C:DATA.xlsx';
Range='OL14';
%.............excel COM object............................................................................
Excel = actxserver ('Excel.Application');
Excel.Visible = 1;
if ~exist(FILE,'file')
ExcelWorkbook=Excel.Workbooks.Add;
ExcelWorkbook.SaveAs(FILE);
ExcelWorkbook.Close(false);
end
invoke(Excel.Workbooks,'Open',FILE); %Open the file
ActiveSheet = Excel.ActiveSheet;
ActiveSheetRange = get(ActiveSheet,'Range',Range);
ActiveSheetRange.Select;
ActiveSheetRange.PasteSpecial; %.................Pasting the figure to the selected location
%-----------------------------------end of function"PlotInExcel--------------------------------------
Now I want to add a second figure to another Range (so it doesn't overwrite the first figure). But if I don't manually Save and Exit the Excel file before re-running PlotInExcel (with a new Range, of course), PlotInExcel merely erases the first figure and adds the second one. Yet, if I manually save/exit my Excel file, PlotInExcel with the new range works fine.
How can I either (1) edit the above PlotInExcel functon to Save/Exit my excel file after adding a figure, or (2) edit my script to Save/Exit the excel file after running PlotInExcel?
I should add that the following, which worked well before I included PlotInExcel, worked fine to save my file:
invoke(Workbook,'Save') % Save file
invoke(Excel,'Quit'); % Close Excel and clean up
delete(Excel);
clear Excel;
..but no longer does the trick.

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

采纳的回答

Stephen
Stephen 2020-9-8
With enough bashing around, I was finally able to answer my own question. I'm posting the resolution here in the event anyone else has this problem. I removed the lines at the beginning because they just produced a test figure. The function below assumes you have your own figure you want to paste into an excel spreadsheet. BTW, you must have your figure saved to the clipboard before running this function.
function PlotInExcel
FILE = 'C:\DATA.xlsx'; %identify the name and location of your excel spreadsheet
Range='R1'; %select where you want your figure pasted in the speadsheet (cell ID)
%.............excel COM object............................................................................
Excel = actxserver ('Excel.Application');
Excel.Visible = 1;
if ~exist(FILE,'file')
ExcelWorkbook=Excel.Workbooks.Add;
ExcelWorkbook.SaveAs(FILE);
ExcelWorkbook.Close(false);
end
invoke(Excel.Workbooks,'Open',FILE); %Open the file
ActiveSheet = Excel.ActiveSheet;
ActiveSheetRange = get(ActiveSheet,'Range',Range);
ActiveSheetRange.Select;
ActiveSheetRange.PasteSpecial; %.................Pasting the figure to the selected location
Excel.ActiveWorkbook.Save % Now save the workbook
if eq(Excel.ActiveWorkbook.Saved,1)
Excel.ActiveWorkbook.Close;
else
Excel.ActiveWorkbook.Save;
end
invoke(Excel, 'Quit'); % Quit Excel
delete(Excel); % End process
end

更多回答（1 个）

Ayush Gupta
Ayush Gupta 2020-9-1
The problem is arising because at the end of the function, once the graph is pasted to the excel file, it is not saved and directly exit from the program which is the main issue behind this. Refer to the following code for a work-around.
xls = xl.Workbooks.Open('myexcelfile.xlsx'); % Open specific document
% Perform functions like attach graphs to it or do anything
xls.Save; % Save the document
xl.Quit; % Close the document
xl.delete; % Remove Excel reference to allow the document to be opened independently
Also to see some example on how to use to use actxserver with Excel use the following link.
  1 个评论
Stephen
Stephen 2020-9-2
Thanks, Ayush! However, I'm sufficiently unfamiliar with Matlab-to-Excel that I can't see where your suggested code (or the suggestions on the link you kindly included) should be used. Specifically, the PlotInExcel function uses "invoke" to open the file:
invoke(Excel.Workbooks,'Open',FILE); %Open the file
ActiveSheet = Excel.ActiveSheet;
ActiveSheetRange = get(ActiveSheet,'Range',Range);
ActiveSheetRange.Select;
ActiveSheetRange.PasteSpecial; %.................Pasting the figure to the selected location
If I (naively) substitute
xls = xl.Workbooks.Open('myexcelfile.xlsx'); % Open specific document
for the "invoke" line to open the file, I get an error
Can you instruct me how to change the PlotInExcel fnx to get the sheet to save/close/delete so I can re-open to add another figure later in my script.
BTW, I am aware that there is a typo in the PlotINExcel I included needs a backslash:
FILE = 'C:\DATA.xlsx';

请先登录，再进行评论。

类别

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

标签

产品


版本

R2020a

Community Treasure Hunt

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

Start Hunting!

Translated by