Video series: Reading Excel data into MATLAB with a GUI

August 13th, 2007

Video series: Reading Excel data into MATLAB with a GUI

Read the Excel data

Refactor to script into a function

Build the GUI

By Doug Hull

You can follow any responses to this entry through the RSS 2.0 feed. You can skip to the end and leave a response. Pinging is currently not allowed.

48 Responses to “Video series: Reading Excel data into MATLAB with a GUI”

Marcelo replied on August 14th, 2007 at 11:08 UTC :

Excellent videos. I never made any GUI, so this lesson is going to be a start.

Gerd Kunert replied on August 16th, 2007 at 01:29 UTC :

Hello,

the “Build a GUI” video is quite helpful.
For further videos, I propose to use a larger Matlab window (in particular width). Then I can see the whole line and do not have to switch to the source code.

Thanks

Doug replied on August 16th, 2007 at 07:58 UTC :

Gerd,

I completely agree that it would be nice to see the entire command line. The trade-off I am struggling with is I can only make the screen so wide if I want to embed the video in a web page. I could zoom out, but then the text is illegible.

I will try to use …
short commands or …
the commands can be …
continued like this …
so they all fit on …
screen at once.

If anyone has any other ideas, please let me know. I can make the videos taller and fit in the blog, but 500 pixels wide is all that fits nicely here (that is still 180 more across than I could get on YouTube)

Thanks,
Doug

Mike replied on August 20th, 2007 at 08:36 UTC :

Excellent Videos! This way form to learn MatLab it’s simply awesome. Great job!

Mauricio Argote-Cortes replied on September 25th, 2007 at 17:23 UTC :

Great videos Doug!

I got a problem when reproducing the Excel-plotting GUI… I guess I am missing something so the outcoming GUI stands by itself fine.

Here is what it happened:

While I was running the GUI from GUIDE or its M-file, everything was ok. When I opened MATLAB again (during a new session) and clicked the .FIG file on the Current Directory Window, the GUI appeared, but it started to produce the following errors:

—————–
handles =

fileName: ‘070924bgzFake.xls’

??? Reference to non-existent field ‘popupmenuX’.

Error in ==> PlotExcelGUI>pushbuttonLoadXLS_Callback at 140
setPopupmenuString(handles.popupmenuX, eventdata, handles)

Error in ==> gui_mainfcn at 95
feval(varargin{:});

Error in ==> PlotExcelGUI at 42
gui_mainfcn(gui_State, varargin{:});

??? Error using ==> PlotExcelGUI(’pushbuttonLoadXLS_Callback’,gcbo,[],guidata(gcbo))
Reference to non-existent field ‘popupmenuX’.

??? Error while evaluating uicontrol Callback

—————–

How can I make the .FIG file to run my GUI without getting into GUIDE each time?

Thanks. And again, great job for those videos!

Mauricio

Richard Lim replied on September 26th, 2007 at 18:01 UTC :

I tried downloading the mainGui.fig, mainGui.m and the excel file so I can run it, and the gui came up just fine, but once I selected the excel file, I got some callback errors:

?? Reference to non-existent field ‘popupmenuX’.
Error in ==> mainGui>pushbuttonLoadXLS_Callback at 130
setPopupmenuString(handles.popupmenuX, eventdata, handles)
Error in ==> gui_mainfcn at 75
feval(varargin{:});
Error in ==> mainGui at 42
gui_mainfcn(gui_State, varargin{:});
??? Error while evaluating uicontrol Callback.

Any ideas?

Doug replied on September 27th, 2007 at 08:54 UTC :

Mauricio and Richard,

To run this GUI, you should run the .m file, not the .fig file. The .m file contains a lot of important initialization code that should be run.

Doug

Richard Lim replied on September 27th, 2007 at 11:02 UTC :

Hi Doug,
Thanks for the prompt replay. I’m gonna ping you on something that I’m having a great deal of difficulty with. And that is, I have a text file pretty much the same format as the excel example you have. The first row is the header row (string) that specifies the names for the columns (numbers). How can I adapt your code to my needs? My goal is to come up with the gui that gives you the option to pick and choose which columns go to x and y, just like you have in your example. I think the key difference is knowing what ‘xlsread’ actually does. I’m using ‘textread’, but the real problem is the first row is a string, which ‘textread’ won’t read in. I can get ‘textread’ to read in the first column strings, but then I’ll need a separate ‘textread’ to read in the rest of the numbered data. Do you see how it complicates things?

Thanks,
Richard

FFA replied on October 16th, 2007 at 03:40 UTC :

Thanks a lot to you Doug Hull, I have learned a lot about MATLAB specially GUI by your video.

I am from very distance area but I am very interested in MATLAB.

In few words I am a big fan of yours

thanks again

FFA

[minor typos corrected, Doug]

Trevor replied on October 30th, 2007 at 14:17 UTC :

Hi
Thank you very much for your videos, I am a final year electrical engineering student and your videos have helped me produce MATLAB code that will be really useful in compleating my final year project.

many thanks
Trevor

Maryam replied on March 5th, 2008 at 20:21 UTC :

Hi,
I have a similar question about importing excel data into matlab, but about a more complicated type of file. My excel files has a lot of text near the top, and then many columns of data with x-y-z locations for 3-D points. After ALL of this data, there’s some more text, and then EMG data. I want to write a function to read through past the text, find the columns of data I’m interested in, and store them in their own variables. Do you know how I would go about doing this?

Doug replied on March 6th, 2008 at 11:51 UTC :

Maryam,

This is getting into reading custom formats inside of an Excel file. MATLAB gives you the ability to read an arbitrary range inside of an Excel file, and the ability to parse those results. These are the low level functions that are need to accomplish this goal. There is no built-in function that will do what you want since files can vary so much. However, you should be able to build your own.

Doug

farasha replied on March 13th, 2008 at 04:55 UTC :

Is this code will face problems if it used in matlab 6.5 ? because that happen to me
I mean the code used to read from excel files ..

Thanks

farasha replied on March 13th, 2008 at 09:50 UTC :

I want to know if it is possible to write data into an excel files using GUI Matlab program ??
Right now I am going to develop a big program that takes data from first users and make several kinds of calc as an output … excel file it is suppose to store these results (by Matlab supervision of course)… and for other users it is should to review latest results , makes same calc and then makes it is Owen results in different excel files …..
Is there any advice U can give me here?

Doug replied on March 13th, 2008 at 12:37 UTC :

Farasha,

I developed this code in 2007a. I just tried it in MATLAB 6.5, but it seems that XLSREAD was improved since then and this code does not work in 6.5.

For your second question, yes, you can write out Excel files. This functionality has been introduced since 6.5. You will likely want to upgrade to take advantage of these new features.

Doug

sivapriya replied on March 16th, 2008 at 03:45 UTC :

Hi,

My question is can i run an m file from excel sheet? i know i can read data from excel sheet and do calculations in matlab and then return the data to the excel sheet but this requires running of the m file. I am in the process of developing a userfriendly software and i feel combination of excel with VB will be more fit for the purpose. Please do reply.

regards
sivapriya

Doug replied on March 17th, 2008 at 07:39 UTC :

Sivapriya,

I think you want:

Spreadsheet link EX
http://www.mathworks.com/products/excellink/

MATLAB Builder EX
http://www.mathworks.com/products/matlabxl/index.html

Doug

Jason replied on March 31st, 2008 at 09:08 UTC :

I am trying to plot data in 3D. I have degrees for axis 1 across the top, degrees for axis 2 down the side, and the data (which will be the Z-axis) are what is in the matrix. How do I modify the mainGUI to incorporate x,y, and z and recognize that there isn’t a “heading” for the column 1 since that is actually one of the axis and not the data itself.

Thanks,
Jason

Doug replied on March 31st, 2008 at 10:56 UTC :

Jason,

This is quite a bit different from the GUI that I have here. I think you would be best served to start a new one with the knowledge you have gained in making this one.

I am not sure I understand the last part of your question. You might be better served by asking this of tech-support:

http://www.mathworks.com/support

Doug

izzri replied on March 31st, 2008 at 23:41 UTC :

Dear Doug,
I would like to merge data files from a few .xls files in one .mat file. Since in .xls file the max no. of column is 256 but I need 600 columns for my input data. That’s why I split data in few .xls files. I know that I can do it by cut and paste the data in .mat file but it will be time consuming. Is there any other way to do it? Please help. Thanks

regards,
Izzri

Doug replied on April 1st, 2008 at 08:49 UTC :

Izzri,

You can read in from several files and then concat the variables together:

a = **read file**
b = **read file**

c = [a b];

Enjoy,
Doug

Hamid replied on April 18th, 2008 at 15:05 UTC :

Dear Doug Hull,
Hi!
I have seen your tutorials and I have to admit that I have learned a lot thanks to your efforts.
While watching the tutorials I noticed that there aren’t any which covers the table component to be used as UI in order to collect and save editable data in a file in order to eliminate the dependency of Excel. I have tried to search for a hint but failed to find any which helps. I wonder if you are kind enough to show likes of me a hint on how to save editable data from a table to a file and then load it back to the table.
Respect,
Hamid

Doug replied on April 23rd, 2008 at 15:01 UTC :

I have not blogged it yet, but I have the video you seek on the file Exchange:

http://www.mathworks.com/matlabcentral/fileexchange/loadFile.do?objectId=19278&objectType=FILE

Enjoy,
Doug

Matt replied on June 2nd, 2008 at 09:32 UTC :

Doug,

I have no trouble reading in files, manipulating the data and plotting. My trouble is I want to do this with quite a few data files.

Is there a way to run through all the files in a folder or directory without inputting the file names manually? Can you help me automate this process?

A.K.A.
1) I want to open a file in a folder
2) Read the data into MATLAB*
3) Manipulate it *
4) Open the next file in the folder and repeat

Doug replied on June 2nd, 2008 at 09:50 UTC :

Matt,

I think you want this:

http://blogs.mathworks.com/pick/2008/02/26/matlab-basics-getting-a-directory-listing/

Doug

Antonio Macanita replied on July 4th, 2008 at 14:09 UTC :

Excellent. Thanks a lot.

Jon replied on July 22nd, 2008 at 17:09 UTC :

Hi Doug, thanks for your video about reading an Excel file from Matlab. I am pretty new to Matlab but I have been presented with a project to work on that I think Matlab will help with.

I have an Excel sheet with a lot of data that I need to make searchable for specific user-defined values. (I was thinking of putting in some user prompts). It seems like xlsread reads the entire Excel file, or a range of cells in the file. For my purposes I will only need to read one or a few cells at a time. Then I need to load a DLL for a data analysis program I have (I was thinking about using the Matlab Generic DLL Interface), and call specific functions to operate on the data, and then I was thinking about using xlswrite to print the results to a new worksheet or to the next page within the same Excel file.

For example, I run the program and I happen to be interested in Temperatures between 95 and 100. So I search for all of these values that might be in the Excel file, then let’s say I have a function in my DLL file called TEMP2. I would call that function and use it to process these temperatures.

Do you think I am on the right track? Could you possibly help me figure out how I can accomplish this?

Thank you.

Scott Hirsch replied on July 23rd, 2008 at 06:08 UTC :

Jon -

You definitely seem to be on the right track. I’ve been working with others here in MATLAB land to study the experience of new-ish MATLAB users, so I’d be happy to offer some help (in exchange for you telling us what’s easy and what’s hard for you). Drop me a line - my email is on my MATLAB Central page. (http://www.mathworks.com/matlabcentral/fileexchange/loadAuthor.do?objectType=author&objectId=919425)

-scott

Jon replied on July 23rd, 2008 at 17:28 UTC :

Thanks Scott, I will probably send you an email.

Basically what’s hard for me is wading through the syntax when first starting out, it’s not obvious to me what certains commands do unless I can see an example of where it is used.

I am pretty good at adapting examples to my specific problem.

The main concerns I have is that the DLLs I have won’t be compatible with the Matlab DLL Interface and I’ll end up using a Fortran script (which is originally what the data software people recommend, but what do they know about Matlab?)

Henry replied on August 13th, 2008 at 14:41 UTC :

Hi Doug,
Following your fantastic tutorial, I try to create a GUI bringing my Excel data and doing some calculations. My excel file has 20 worksheets(20 subjects), and each worksheet has data. These are part of my code:

setPopupmenuString(handles.popupmenu1, eventdata, handles)

function setPopupmenuString(hObject, eventdata, handles)
fileName = handles.fileName;
[tpe, xlSheets] = xlsfinfo(fileName);
set(hObject, ’string’, xlSheets);

It updates all sheet names on popup menu. Then I want it automatically updates data when I select different name from popup menu. So my question is how to get data from each sheet? I tried “get(handles.popupmenu1, ‘value’)” but it doesn’t work.
Any help will be highly appreciated! Thanks.

Doug replied on August 14th, 2008 at 10:51 UTC :

Henry,

It is not clear to me the specific question you need answered. I think you have a pop-up menu that is correctly populated with the various sheet names from your Excel file. From there, you want to figure out the name of the sheet that was selected in the pop-up.

Value will give you an ordinal number. You need to then use this number to index into the cell array that is stored in the ’string’ property to get the actual string that represents the sheet name.

Doug

maram replied on September 29th, 2008 at 06:41 UTC :

hi,
I have a question
I have a matlab code and i want to read a data from SQL server database to use it in that code

so i want to know if i can read this data? if yes, how can I do this ?

Best Regards,
Maram

Doug replied on September 29th, 2008 at 09:52 UTC :

Maram,

You might want to look at the Database toolbox.

http://www.mathworks.com/products/database/

Doug

MATLAB newbie replied on March 24th, 2009 at 08:40 UTC :

Look this is a great video but for a person totaly new to MATLAB there is a big problem in this tutorial. You need to identify what each code means. you say something like: setPopupmenuString(handles.PopupmenuX, eventdata, handles) what is setPopupmenuString? what are handles? setPopupmenuString (you never explain what is needed in here, what does it mean? Why you need it?)

MATLAB newbie replied on March 24th, 2009 at 08:48 UTC :

the first two videos are fairly easy to understand but the last one is abit confusing because I did not know what code to start with. I think you should explain in more detail why you should type this code? what does it does? one after every line. thanks

MATLAB newbie replied on March 24th, 2009 at 09:20 UTC :

btw how do you get filename if the file I wanted to get does not have any suffix? Can I do it this way?
–> fileName = uigetfile(’*')

MATLAB newbie replied on March 24th, 2009 at 09:51 UTC :

Hi Ive tried the same method used for unidentified files witout any suffix and I failed. What modifications to the codes you need to do for an unidentified file to get the same result as this tutorial?

dhull replied on March 24th, 2009 at 13:30 UTC :

Hello “MATALB Newbie”

There is always the difficult decision on how much detail I need to discuss in a video. This third video was already 13 minutes, nearly three times the average length. Sometimes, details need to be skipped.

There are plenty of other video that discuss the questions you have, like what is “handles”

http://blogs.mathworks.com/videos/2008/04/17/advanced-matlab-handles-and-other-inputs-to-guide-callbacks/

You can see all the GUI videos here:

http://blogs.mathworks.com/videos/category/gui-or-guide/

Doug

MATLAB newbie replied on March 25th, 2009 at 02:27 UTC :

My current version of MATLAB is 7.6.0 R2008a can it read Microsoft Excel 2007? i.e. .xlsx instead of .xls. If it is possible, what changes must I make to the xlsread code?

regards

MATLAB newbie

dhull replied on March 25th, 2009 at 18:17 UTC :

“Newbie”,

Have you tried this? It worked for me without issue, so I assume yours did not work. What was your code?

Doug

MATLAB newbie replied on March 25th, 2009 at 20:15 UTC :

fileName = ‘1.xlsx’

fileName =

1.xlsx

>> a = xlsxread(fileName)
??? Undefined function or method ‘xlsxread’ for input arguments of type ‘char’.

dhull replied on March 25th, 2009 at 20:30 UTC :

Check the spelling of the command, XLSREAD.

MATLAB newbie replied on May 6th, 2009 at 07:48 UTC :

Do you have a video that teaches you to write a program that automatically creates an excel file and writes data into that excel file once you execute the program?

Amine replied on May 15th, 2009 at 17:17 UTC :

Hi Doug,

I have recently built up a GUI which has multiple functionality of downloading Documents from an ftp server, listing dir and opening local files. what I want to do next is include a window on the GUI where I can load an excel sheet on it. i.e. have GUI like a remote desktop for that spreadsheet where I can modify/change/save…ect the data.

I have managed to add Microsoft office spreadsheet 11.0 activeX, but when implementing it within the GUI, it doesn’t ask me for which spreadsheet I want to open instead it loads a blanc one and everytime I export the data to Mic Off excel it saves it automaticaly under a different excel file.

I can open the file with the following matlab commands but I can’t really manage to link it to open within the GUI:

excel = actxserver(’Excel.Application’);
file = excel.Workbooks.Open(xlfile);

It would be great if anyone can help.

Thanks.

dhull replied on May 15th, 2009 at 17:41 UTC :

Amine,

The documentation on how to interact with actX controls is with the maker of the control, so there is not much I can do to help you here. I would check with the maker of the control to look for documentation on it.

Doug

dhull replied on July 6th, 2009 at 19:03 UTC :

@”MATLAB Newbie”

xlswrite will do what you are asking for.

a = magic(3)
xlswrite(’foo.xls’, a)

Lucy replied on October 5th, 2009 at 12:20 UTC :

Hi Doug,

I really like the style of your Matlab tutorials!!

A question: in the GUI you created I’d like to be able to select multiple values from the pop-up menu and plot them on the same graph. How can I alter your code to do this??

Many thanks

Lucy

dhull replied on October 8th, 2009 at 14:43 UTC :

@Lucy,

Thanks for the kind words. You can only select one value from a pop-up. Change it to a list box and change the MAAX property (weird, I know) so it is multi-select. The rest would be a matter of changing the plotting code to deal with all the selected values.

Doug

MATLAB Central Terms of Use

NOTICE: Any content you submit to MATLAB Central, including personal information, is not subject to the protections which may be afforded information collected under other sections of The MathWorks, Inc. Web site. You are entirely responsible for all content that you upload, post, e-mail, transmit or otherwise make available via MATLAB Central. The MathWorks does not control the content posted by visitors to MATLAB Central and, does not guarantee the accuracy, integrity, or quality of such content. Under no circumstances will The MathWorks be liable in any way for any content not authored by The MathWorks, or any loss or damage of any kind incurred as a result of the use of any content posted, e-mailed, transmitted or otherwise made available via MATLAB Central. Read the complete Terms prior to use.

Doug’s MATLAB Video Tutorials

August 13th, 2007