How to control a variable number of sheets in excel files?
1 visualizzazione (ultimi 30 giorni)
Mostra commenti meno recenti
Yasmin Tamimi
il 28 Ott 2012
Modificato: Mohamed Farooq
il 11 Ott 2018
Hey all,
I have like 10 excel files in a folder, but the number of sheets in each file is not the same. I want to read the .xlsx files through a loop but I don't know how to write it?
for i = 1: (#number of sheets in the excel file)
Any help is really appreciated!
2 Commenti
Azzi Abdelmalek
il 28 Ott 2012
Modificato: Azzi Abdelmalek
il 28 Ott 2012
do you know how many sheet you have for each file
Image Analyst
il 28 Ott 2012
You don't need to know that in advance. You can get the number of worksheets in the workbook from code like this:
worksheets = excelObject.sheets;
numberOfSheets = worksheets.Count;
Risposta accettata
Image Analyst
il 28 Ott 2012
Modificato: Image Analyst
il 28 Ott 2012
Yasmine, it can be done with ActiveX. While more complicated, this will be much, much faster than using xlsread() if you have more than 1 file since you will need to launch and shutdown Excel only once, not 10 or more times (once for every single file that you have).
See the FAQ for how to loop over the workbook files, and then inside that loop, with a single specific workbook, you can use ActiveX to loop over the worksheets in that workbook. You just do something like this:
excelObject = actxserver('Excel.Application');
filePattern = fullfile(myFolder, '*.xls');
xlsFiles = dir(filePattern);
for k = 1:length(xlsFiles)
baseFileName = xlsFiles(k).name;
fullFileName = fullfile(myFolder, baseFileName);
fprintf(1, 'Now reading %s\n', fullFileName);
excelWorkbook = excelObject.workbooks.Open(fullFileName);
worksheets = excelObject.sheets;
numberOfSheets = worksheets.Count;
for sheetIndex = 1 : numberOfSheets
% Do whatever you want to do.
end
end
excelObject.Quit;
Here's a utility where I use ActiveX to loop over worksheets, deleting empty ones:
% --------------------------------------------------------------------
% DeleteEmptyExcelSheets: deletes all empty sheets in the active workbook.
% This function loops through all sheets and deletes those sheets that are empty.
% Can be used to clean a newly created xls-file after all results have been saved in it.
function DeleteEmptyExcelSheets(excelObject)
try
% excelObject = actxserver('Excel.Application');
% excelWorkbook = excelObject.workbooks.Open(fileName);
% Run Yair's program http://www.mathworks.com/matlabcentral/fileexchange/17935-uiinspect-display-methods-properties-callbacks-of-an-object
% to see what methods and properties the Excel object has.
% uiinspect(excelObject);
worksheets = excelObject.sheets;
sheetIndex = 1;
sheetIndex2 = 1;
initialNumberOfSheets = worksheets.Count;
% Prevent beeps from sounding if we try to delete a non-empty worksheet.
excelObject.EnableSound = false;
% Tell it to not ask you for confirmation to delete the sheet(s).
excelObject.DisplayAlerts = false;
% Loop over all sheets
while sheetIndex2 <= initialNumberOfSheets
% Saves the current number of sheets in the workbook.
preDeleteSheetCount = worksheets.count;
% Check whether the current worksheet is the last one. As there always
% need to be at least one worksheet in an xls-file the last sheet must
% not be deleted.
if or(sheetIndex>1,initialNumberOfSheets-sheetIndex2>0)
% worksheets.Item(sheetIndex).UsedRange.Count is the number of used cells.
% This will be 1 for an empty sheet. It may also be one for certain other
% cases but in those cases, it will beep and not actually delete the sheet.
if worksheets.Item(sheetIndex).UsedRange.Count == 1
worksheets.Item(sheetIndex).Delete;
end
end
% Check whether the number of sheets has changed. If this is not the
% case the counter "sheetIndex" is increased by one.
postDeleteSheetCount = worksheets.count;
if preDeleteSheetCount == postDeleteSheetCount;
% If this sheet was not empty, and wasn't deleted, move on to the next sheet.
sheetIndex = sheetIndex + 1;
else
% sheetIndex stays the same. It's not incremented because the current sheet got deleted,
% and all the other sheets shift down in their sheet number. So now sheetIndex will
% point to the same number which is the next sheet in line for checking.
end
sheetIndex2 = sheetIndex2 + 1; % prevent endless loop...
end
excelObject.EnableSound = true;
catch ME
errorMessage = sprintf('Error in function DeleteEmptyExcelSheets.\n\nError Message:\n%s', ME.message);
fprintf('%s\n', errorMessage);
WarnUser(errorMessage);
end
return; % from DeleteEmptyExcelSheets
end % of DeleteEmptyExcelSheets
Più risposte (1)
Mohamed Farooq
il 11 Ott 2018
Modificato: Mohamed Farooq
il 11 Ott 2018
filename = 'spreadsheet.xlsx';
[~,sheets,~] = xlsfinfo(filename);
for i=1:size(sheets,2)
0 Commenti
Vedere anche
Categorie
Scopri di più su Spreadsheets in Help Center e File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!