Suppose I have xlsx file with multiple sheets. I want to create table A from the first sheet and create table B from the second sheet. Please advise.
MATLAB: Create table from excel spreadsheet with multiple sheets
data importMATLAB
Related Solutions
See my demo:
% Demo macro to write numerical arrays and cell arrays
% to two different worksheets in an Excel workbook file.
% Uses xlswrite1, available from the File Exchange
% http://www.mathworks.com/matlabcentral/fileexchange/10465-xlswrite1
function ExcelDemoclc;close all;clear all;fullFileName = GetXLFileName();if isempty(fullFileName) % User clicked Cancel.
return;endExcel = actxserver('Excel.Application'); % Prepare proper filename extension.
% Get the Excel version because if it's version 11 (Excel 2003) the file extension should be .xls,
% but if it's 12.0 (Excel 2007) then we'll need to use an extension of .xlsx to avoid nag messages.
excelVersion = str2double(Excel.Version);if excelVersion < 12 excelExtension = '.xls';else excelExtension = '.xlsx';end% Determine the proper format to save the files in. It depends on the extension (Excel version).
switch excelExtension case '.xls' %xlExcel8 or xlWorkbookNormal
xlFormat = -4143; case '.xlsb' %xlExcel12
xlFormat = 50; case '.xlsx' %xlOpenXMLWorkbook
xlFormat = 51; case '.xlsm' %xlOpenXMLWorkbookMacroEnabled
xlFormat = 52; otherwise xlFormat = -4143;endif ~exist(fullFileName, 'file') message = sprintf('I am going to create Excel workbook:\n\n%s\n\nClick OK to continue.\nClick Exit to exit this function', fullFileName); button = questdlg(message, 'Creating new workbook', 'OK', 'Exit', 'OK'); drawnow; % Refresh screen to get rid of dialog box remnants.
if strcmpi(button, 'Exit') return; end % Add a new workbook.
ExcelWorkbook = Excel.workbooks.Add; % Save this workbook we just created.
ExcelWorkbook.SaveAs(fullFileName, xlFormat); ExcelWorkbook.Close(false);else % Delete the existing file.
delete(fullFileName);end% Open up the workbook named in the variable fullFileName.
invoke(Excel.Workbooks,'Open', fullFileName);Excel.visible = true;% Create some sample data.
myData = magic(20);myOtherData = rand(10);% Then run the new xlswrite1 function as many times as needed or in a loop
% (for example xlswrite1(fullFileName, yourArrayName, XL_CellLocation).
% IMPORTANT NOTE: the Excel object variable MUST exist in the routine that calls xlswrite1()
% and it MUST be named "Excel" EXACTLY because xlswrite1() has this line it it:
% Excel = evalin('caller', 'Excel');
xlswrite1(fullFileName, myData, 'mySheetName', 'B2');ca = {'Column Header 1', 'Column Header 2'};xlswrite1(fullFileName, ca, 'mySheetName', 'B1');ca = {'Row Header 1'; 'Row Header 2'};xlswrite1(fullFileName, ca, 'mySheetName', 'A2');xlswrite1(fullFileName, myOtherData, 'myOtherSheetName', 'B2');% Delete all empty sheets in the active workbook.
DeleteEmptyExcelSheets(Excel);% For fun, add comments to cells A1:A12 on sheet #1.
worksheets = Excel.sheets;thisSheet = get(worksheets, 'Item', 1); for k = 1 : 12 myComment = sprintf('Comment for cell A%d', k); cellReference = sprintf('A%d', k); theCell = thisSheet.Range(cellReference); theCell.AddComment(myComment);end% Then run the following code to close the activex server:
invoke(Excel.ActiveWorkbook,'Save');Excel.Quit;Excel.delete;clear Excel;message = sprintf('Done!\nThis Excel workbook has been created:\n%s', fullFileName);msgbox(message);% End of main function: ExcelDemo.m -----------------------------
%--------------------------------------------------------------------
% Gets the name of the workbook from the user.
function fullExcelFileName = GetXLFileName() fullExcelFileName = []; % Default.
% Ask user for a filename.
FilterSpec = {'*.xls', 'Excel workbooks (*.xls)'; '*.*', 'All Files (*.*)'}; DialogTitle = 'Save workbook file name'; % Get the default filename. Make sure it's in the folder where this m-file lives.
% (If they run this file but the cd is another folder then pwd will show that folder, not this one.
thisFile = mfilename('fullpath'); [thisFolder, baseFileName, ext] = fileparts(thisFile); DefaultName = sprintf('%s/%s.xls', thisFolder, baseFileName); [fileName, specifiedFolder] = uiputfile(FilterSpec, DialogTitle, DefaultName); if fileName == 0 % User clicked Cancel. return; end % Parse what they actually specified.
[folder, baseFileName, ext] = fileparts(fileName); % Create the full filename, making sure it has a xls filename.
fullExcelFileName = fullfile(specifiedFolder, [baseFileName '.xls']);% --------------------------------------------------------------------
% DeleteEmptyExcelSheets: deletes all empty sheets in the active workbook.
% This function looped 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)% excelObject = actxserver('Excel.Application');
% excelWorkbook = excelObject.workbooks.Open(fileName);
worksheets = excelObject.sheets; sheetIdx = 1; sheetIdx2 = 1; numSheets = worksheets.Count; % Prevent beeps from sounding if we try to delete a non-empty worksheet.
excelObject.EnableSound = false; % Loop over all sheets
while sheetIdx2 <= numSheets % Saves the current number of sheets in the workbook
temp = 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(sheetIdx>1,numSheets-sheetIdx2>0) % worksheets.Item(sheetIdx).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(sheetIdx).UsedRange.Count == 1 worksheets.Item(sheetIdx).Delete; end end % Check whether the number of sheets has changed. If this is not the
% case the counter "sheetIdx" is increased by one.
if temp == worksheets.count; sheetIdx = sheetIdx + 1; end sheetIdx2 = sheetIdx2 + 1; % prevent endless loop...
end excelObject.EnableSound = true; return;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.
endendexcelObject.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);endreturn; % from DeleteEmptyExcelSheets
end % of DeleteEmptyExcelSheets
Related Question
- How to rename a sheet in Excel using the COM interface in MATLAB
- Opening excel only once at the beginning of the optimization proces in Matlab and not each time Matlab sends a new set of decision values to excel
- Looping through different sheets/sheet names in each loop to access data
- Changing the name of the sheet in excel
Best Answer