How can i move excel sheets in the same file
22 次查看(过去 30 天)
显示 更早的评论
Hello everyone!
I have an Excel file that contains many sheets.
the names of the sheets are something like this:
1_1,1 1_1,2 1_1,3 2_1,3 2_1,2 2_1,1 3_1,1 3_1,2 3_1,3 4_1,3 4_1,2 4_1,1 ....100_1,3 100_1,2 100_1,1
Is there any way to rearrange the sheets so they are in asceding order?
1_1,1 1_1,2 1_1,3 2_1,1 2_1,2 2_1,3 ...... 100_1,1 100_1,2 100_1,3
I have read this answer by Image Analyst https://www.mathworks.com/matlabcentral/answers/84997-moving-excel-sheets-within-excel-file
but if i try to copy all this sheets one by one, create new one ,paste it and then delete it and then move to the next, this would take a long time i believe.
Is there any other solution?
Thank you very much!
P.S: I have the option to rename the sheets like this 1 2 3 6 5 4 7 8 9 12 11 10 .... and then of course i would like them to rearranged as this 1 2 3 4 5 6 7 8 9 10 11 12...
4 个评论
Guillaume
2019-9-11
I have not found any great resource for how to use the actxserver
Matlab documentation on using COM objects is here and Excel's object model documentation is there. The code you'd write in matlab would be more or less identical to the one you'd write in VBA. The main difference is that matlab does not support default properties, so the VBA code:
' wb is an excel workbook
set ws = wb.Worksheets('SomeSheetName') 'Index the worksheets collection. Don't have to use Item as it's the default property
would translate in matlab to:
%wb is an excel workbook
ws = wb.Worksheets.Item('SomeSheetName'); %Item is the default property of the worksheets collection. Has to be called explicitly in matlab
If you're familiar with excel VBA, it's trivial to write the equivalent in matlab. If you're not, you've got a steep learning curve ahead of you.
采纳的回答
Guillaume
2019-9-11
This should do the job:
function reordersheets(excelfile)
%excelfile: full path of excel file whose sheet are to be reordered
%sheet names must ALL follow the EXACT pattern number_number,number
excel = actxserver('Excel.Application'); %start excel
clearobj = onCleanup(@() excel.Quit); %Quit excel whenever the function exits
workbook = excel.Workbooks.Open(excelfile); %open excel file
sheetnames = arrayfun(@(idx) workbook.Worksheets.Item(idx).Name, 1:workbook.Worksheets.Count, 'UniformOutput', false); %get names of all worksheets
tokens = regexp(sheetnames, '^(\d+)_(\d+),(\d+)$', 'tokens', 'once'); %extract numbers from names
assert(all(~cellfun(@isempty, tokens)), 'At least one sheet name doesn''t conform to pattern');
tokens = str2double(vertcat(tokens{:})); %convert number strings to actual numbers
[~, neworder] = sortrows(tokens, 'descend'); %get new order
ordered = sheetnames(neworder); %and reorder the names accordingly
lastsheet = workbook.Worksheets.Item(ordered{1}); %sheet before which to move current sheet
for sheetname = ordered(2:end)
currentsheet = workbook.Sheets.Item(sheetname{1});
currentsheet.Move(lastsheet); %move sheet before previous sheet
lastsheet = currentsheet;
end
workbook.Save; %save workbook
end
7 个评论
Guillaume
2019-9-12
编辑:Guillaume
2019-9-12
Well, obviously the fix replaces the original line that caused the error. So you replace
[~, neworder] = sortrows(tokens, 'descend'); %get new order
by
[~, neworder] = sortrows(tokens, -(1:3)); %get new order
in my original answer.
"Actually i have R2014a". Yes, I see now that the 'descend' option was introduced in R2013b but only fo table inputs. It's only in R2017a that it graduated to a full blown option.
更多回答(1 个)
Image Analyst
2020-9-10
For what it's worth, I have a static method in my Excel_utils class that lets you move a worksheet to be the first one. It's probably easy to modify it to be any index in the workbook:
%--------------------------------------------------------------------------------------------------------------------------------------------------------------------
% Moves the worksheet named "sheetName" so that it is the very first worksheet in the workbook.
% Example call:
% Excel_utils.MoveToSheet1(Excel, 'Summary'); % Make 'Summary' worksheet be the first worksheet in the workbook.
function MoveToSheet1(Excel, sheetName)
try
firstSheet = Excel.Worksheets.Item(1); % Get object/handle of the first worksheet in the workbook.
currentSheet = Excel.Worksheets.Item(sheetName); % Get object/handle of the user-specified, named worksheet.
currentSheet.Move(firstSheet); % Move the specified worksheet to be before the first worksheet.
catch ME
errorMessage = sprintf('Error in function MoveToSheet1.\nThe Error Message:\n%s', ME.message);
fprintf(errorMessage);
end
return; % from MoveToSheet1
end % of the MoveToSheet1() method.
1 个评论
hxen
2023-6-2
awesome! was exactly what I was looking for a work around with writetable. very helpful. :)
另请参阅
类别
在 Help Center 和 File Exchange 中查找有关 Use COM Objects in MATLAB 的更多信息
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!