Best/Cleanest way to select a specific sheet from an excel file?
16 views (last 30 days)
Show older comments
I am importing an excel file that has multiple sheets. I am using the readtable command to import the file. For the sheet choice, I am giving the read table command the 'Sheet' modifier with the sheet name.
My question is about getting the sheet name. Currently, I have the "sheetnames" command get a string array of the sheet names, then I use "listdlg" to display them and return a selection. the listdlg command is just not the best though. The formatting does not look very good, and I have multiple monitors. It does not pop up on the same monitor or the previously used monitor. It always pops up on the first, which if you need to run the program many times quickly, means you have to move across all monitors to select an option. Is there a better way to format it or an alternative to the "listdlg"?
Is there a different way of doing all of this that is better than sheetnames and listdlg? My program I've written pretty heavily relies on "readtable", so I cannot change that aspect.
% get the file name and file path of the selected excel file
[fileName,pathName] = uigetfile('*xlsx;*.xls','Select the Excel file');
% check if user canceled file selection and act accordingly
if fileName == 0
return;
end
% combine the file name and file path to get the complete file path of the
% selected excel file
completeTableFilePath = fullfile(pathName,fileName);
% gather the names of all the sheets of the excel file
sheetNamesArray = sheetnames(completeTableFilePath);
% create a dialog box that lists all the sheet names in the file and
% prompts the user to pick one
sheetChoice = listdlg('PromptString','Select the sheet:','SelectionMode','single','ListString',sheetNamesArray);
% check if user canceled file selection
if isempty(sheetChoice)
return;
end
% remembers the index of the selected excel sheet
sheetChoiceFileName = sheetNamesArray(sheetChoice);
% intake the specific sheet of the selected excel file as strings
inputTable = readtable(completeTableFilePath,'Sheet',sheetChoiceFileName,'TextType','string');
0 Comments
Answers (1)
Ganesh
on 15 Jun 2024
Edited: Ganesh
on 15 Jun 2024
Excuse me for this elaborate answer, but I will be answering two parts of your question.
To say the "best" way in your case, we need to ensure that while calling "sheetnames()", MATLAB is not opening the complete ".xlsx" file. If it is doing so, it would mean that we are opening the sheet twice. I have uploaded two files, "small.xlsx" and "large.xlsx", both of which have the same Sheet Names but they differ in size due to the amount of data in each sheet. Taking a look at the time taken to access sheet names for each of them:
clear all
tic
sheetnames("small.xlsx");
toc
tic
sheetnames("large.xlsx");
toc
The difference is minimal, implying the sheetnames are not being affected by the size of the files. Thus, your implementation seems fine, and straight forward.
The issue with listdlg can be resolved by modifying the listdlg function to allow you to display it where it is needed to be displayed. The issue has been addressed in the following MATLAB Answer provided by MathWorks Technical Support:
Using this, you can modify the size of the dialog box to improve your formatting too.
See Also
Categories
Find more on Data Import from MATLAB in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!