Best/Cleanest way to select a specific sheet from an excel file?

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');

Answer 1

[Ganesh]

Hi @Kealan,

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

Elapsed time is 0.084379 seconds.

tic
sheetnames("large.xlsx");
toc

Elapsed time is 0.013228 seconds.

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:

https://www.mathworks.com/matlabcentral/answers/97420-how-do-i-specify-the-location-of-a-listdlg

Using this, you can modify the size of the dialog box to improve your formatting too.

[Kealan]

Hey, that link does show how to make it better, but I cannot do that. I want to be able to share my script with people so they can use it, and everyone having to reconfigure their matlab files with a certain download in order to run the script properly is not something I can do.

Thank you for the input though. It seems that there really is not a way to highly customize the dialog boxes then.