Changing the name of the sheet in excel

Question

Gopalakrishnan venkatesan 2015-5-8

1
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/215703-changing-the-name-of-the-sheet-in-excel

评论： qing sheng 2022-1-27

采纳的回答： Thomas Koelen

I want to change the names of sheets in excel and then i need to perform the write operation on those sheets?

Is it possible to do with xlswrite?

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

请先登录，再进行评论。

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

Answer 1

Thomas Koelen 2015-5-8

13
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/215703-changing-the-name-of-the-sheet-in-excel#answer_178357

You can use ActiveX directly from MATLAB:

xlswrite('test.xls',1) % # create test file
e = actxserver('Excel.Application'); % # open Activex server
ewb = e.Workbooks.Open('c:\test\test.xls'); % # open file (enter full path!)
ewb.Worksheets.Item(1).Name = 'new name'; % # rename 1st sheet
ewb.Save % # save to the same file
ewb.Close(false)
e.Quit

Be careful while testing, it overwrite the original file. Make a backup.

6 个评论
显示 4更早的评论隐藏 4更早的评论

Enri 2019-3-8

As far as I tried, Matlab function "writetable"behaves like this:

if you don't indicate other parameters for the property 'sheet', it creates just the sheet in which you want to write your table:

    % Example: write four tables in a specific file
    fileName = 'dunno.xlsx';
    folderName = 'someFolder';
    filePath = [folderName, '/', fileName];
    writetable(tableName_1, filePath, 'sheet', 1);
    writetable(tableName_2, filePath, 'sheet', 2);
    writeTable(tableName_3, filePath, 'sheet', 3);
    writeTable(tableName_4, filePath, 'sheet', 4);

it creates just four sheets by default, with one of the tables for each one.

2. if you indicate another sheets, with a specific name, it creates three sheets by default, and then it creates the sheets you've asked for:

        % Example: write four tables in a specific file
    fileName = 'dunno.xlsx';
    folderName = 'someFolder';
    filePath = [folderName, '/', fileName];
    % Initialize tables
    tables.tableName_1 = tableName_1;
    tables.tableName_2 = tableName_2;
    tables.tableName_3 = tableName_3;
    tables.tableName_4 = tableName_4;
    tabNames = fieldnames(tables);
    % Initialize the 
    sheets.sheetName_1 = 'sheetName_1';
    sheets.sheetName_2 = 'sheetName_2';
    sheets.sheetName_3 = 'sheetName_3';
    sheets.sheetName_4 = 'sheetName_4';
    sNames = fieldnames(sheets);
    
    for i = 1:4
        writetable(tables.(tabNames{i}), filePath ...
                   'sheets', sheets.(sNames{i}) );          
    end 
    

Funny thing, I have an Italian excel, so with the second code, it creates:

"Foglio 1", "Sheet 2", "Sheet 3" and of course sheetNames_(1,2,3,4)...

Laura Stewart 2020-9-10

Be warned if using @Enri solution it will populate the data to the Excel File as text.

This was problematic as I was exporting a matrix of cells with Excel functions (so the Excel user could open the file and easily track and modify the cell values if needed) and once the data was in Excel the cells had to be modified to a number type, then clicked & modified (space added then deleted), and entered one-by-one to show the function value.

qing sheng 2022-1-27