Stuart McGarrity, MathWorks
(Originally posted on Stuart's MATLAB Videos blog.)
I often need to export data from MATLAB to Excel. As the files are used by others, I like to format them nicely for easy viewing and browsing. To do this, I simply create a spreadsheet with all the required formatting as a template and copy it each time.
Play the video in full screen mode for a better viewing experience.
I often need to write data to Excel to be viewed by others. And in those reports, it's nice for the data to be as formatted as clearly as possible. Normally, when you write data into Excel from MATLAB, the columns all have the same width and they're all the same font.
So, what I usually like to do is to match the width of the columns to the data. Also, perhaps make the header bold, freeze the top row, and put on auto-filtering.
So, I use a very simple technique of creating an Excel template. And then just copying that template each time I want to create a new spreadsheet.
So, first of all, I want to grab a table to work with. And I think I know of one in the documentation under Table Properties. Yeah, here, Load Patients.
There we are. So, here is a table with a number of columns in it. So, now if you wanted to write that to Excel, you would just-- well, let's first of all specify a file name. We'll call it Results.XLSX, like that. And then we would write Table T file name.
And then if we opened it up, take a look at it. It will look like this. So, this looks fine. All the data is here. But if someone is looking at this, analyzing this, it's nice for it to be formatted better. For example, it'd be nice if the column headers were bolded.
It would be nice if they had the proper widths. It could be that some of this data actually is pretty wide, and therefore is going to wrap. Sometimes color coding might be nice, too. Freezing the top row is also nice with this.
So, what I do is I make a template of the very first spreadsheet I've made. So, I am going to-- let me see if this is the correct width for some of these. I'm going to meet the top row bold. I'm going to freeze the top row like that. The other thing I like to do is have the filtering on by default. So, I want to filter those. And I'm going to delete the contents like that.
Now, to make sure these headers get removed when I write out to it. So, I'm going to just erase them to make it obvious that they're not being used. And finally, maybe I'll add some color coding to these, which may be appropriate for the data. Like that. And then I would save it to perhaps the file name underscore template. Like that.
So, here's the template I have. And there's the original spreadsheet. So, any time I want to create a formatted spreadsheet, I would do a copy file, file name underscore template. Oh, I need to define file name. File name underscore template, first of all. So, let's do-- file name underscore template equals that.
Then if I want to use the template, I'll do a copy file from the file name template to file name. And that puts the template into this file. And if I want to look at it, you can see that it now has the formatted layout. And then I can just do my write again, and write into the table. And then we'll take a look at it.
And you see, we have the color coding, we have the formatting. We have the auto-filter, and the frozen top row.
Now, if you want to write into multiple tabs, that can be useful too. And I recommend specifying the names of those. So, if I open up the template again, I can give it a name like can Results1. And then I'm actually going to copy it. And call it Results2. Like that.
And if you-- so, we'll make that. And you copy again-- we'll copy the template again. Now, if I write in and I specify the sheet-- and just remember, if you specify the wrong sheet, just call "results," like that. You'll get a warning because that sheet is not known.
So, if you open up the Results, you'll see it's created a new tab over here with the name Results. So you don't want to do that. So, let's copy the template again. And then write in-- and this time I'm going to write into Results1 and Results2. And we'll take a look. And there we have the results nicely formatted.
So, there is a very simple way to write data to Excel and have the Excel file formatted appropriately for others, for easier reading. Now, we could have used the com interface to talk to the application, Excel, itself, and programmatically control the formatting of the different cells.
Now, that might be useful or might be necessary in some situations to give you ultimate control over the formatting. But that is much more complicated than just this very simple process here, which works for me for the most of my use cases.
Recorded: 28 Mar 2016