Why can't i change an excel file sheets during a loop in matlab

Question

Ali 2022-10-4

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1817360-why-can-t-i-change-an-excel-file-sheets-during-a-loop-in-matlab

编辑： dpb 2022-10-5

Hi,

I want this function which is an exemple store the first second and thrid value of ne in an excel sheet each but i also want to it to rename the excel sheets while writing but what i don't understand is when it's done only the first sheet will be renamed and it won't be renamed A but C

function excel(init)
Varlist = ['A' 'B' 'C']
ne = [1 2 3]
a = 1
path_save_csv=uigetdir;
cd(path_save_csv);
while a <= 3
    xlswrite('data.xlsx',ne(a),a,'A1');
    e = actxserver('Excel.Application');
    ewb = e.Workbooks.Open('C:\Users\223080038\Desktop\data.xlsx');
    ewb.Worksheets.Item(1).Name =[Varlist(1,a)] ;
    ewb.Save ;
    ewb.Close(false);
    e.Quit ;
    a=a+1
end

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

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

Answer 1

dpb 2022-10-4

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1817360-why-can-t-i-change-an-excel-file-sheets-during-a-loop-in-matlab#answer_1066580

Because in

ewb.Worksheets.Item(1).Name =[Varlist(1,a)] ;

that's exactly what you told it to do -- rename Item(1) each time.

Use writematrix instead of the deprecated xlswrite and set the sheet name on write and you won't need any of the above gyrations and double-opening/closing the file anyway...

Something more like

function excel_init
  Varlist = {'A','B','C'};
  ne = 1:3;
  path_save_csv=uigetdir;
  for i=1:numel(ne)
    writematrix(ne(i),fullfile(path_save,'data.xlsx'),'Sheet',Varlist(i),'Range','A1');
  end
end

I'd probably pull the call to uigetdir from inside this function to the caller and pass the result as it's likely you'll want it elsewhere in the application.

3 个评论
显示 1更早的评论隐藏 1更早的评论

dpb 2022-10-5

编辑：dpb 2022-10-5

That's getting to be quite a while back -- writetable wasn't introduced until R2013b so if you can't upgrade to a more recent version (and the table itself was also in R2013b and alone is worthy of serious consideration without other new features), then the venerable xlswrite is the tool for spreadsheets.

It also will let you specify the sheet, although it doesn't use name-value pairs to do so...

xlswrite(fullfile(path_save,'data.xlsx'),ne(i),Varlist{i},'A1')

Ali 2022-10-5