Now we will begin considering the problem of using MATLAB to modify an Excel workbook. We will continue with our parts list example from the earlier videos on write cell. Assuming that we have already created the Excel file components.xlsx, we will read the sheet of results.
resistor data into a MATLAB variable called rdata. Let's take a look at what's in rdata now. Some of you may have already noticed that the layout of this information is not particularly user-friendly unless you just happen to already know what all of this information means it could be very difficult to guess so we're going to add a column on the left of all of this data containing information about what the data in each row represents first we will create a three element cell array containing information about each of the rows the first row contains the component values in ohms, the second row contains the minimum power rating in watts, and the third row contains the number of each component that is required. Note that the three individual text strings have been separated by semicolons so that this comes out as a single column cell array rather than a single row. And there are our our three labels in rowhead.
Next, we need to add the contents of rowhead to the left of the contents of our data. For this, I will use the concatenation operator, square brackets. Note that when you are concatenating two or more items, the dimensions must be consistent.
Now, what that means is that if you are sticking things together horizontally, horizontally, they all need to have the same number of rows. In this case, R data has three rows. Row head has three rows. So we can successfully concatenate those horizontally.
Note that the separator here is a comma so that they will be put side by side, not on top of one another. Now, if you look at R data, you note that it is a three by six. It was originally a three by And if we look at the contents, we see that those labels have indeed been added to the left of the data. It would also be helpful to the user to add some information to the top to indicate in general what this information was all about. So I will place a text string with a short description of this file in the variable top head and we will end up adding that.
to the top of our data. Here I will just add the text string list of resistors for gizmo. Now remember that when we concatenate two things, the dimensions have to be consistent. Since we're going to concatenate vertically in this case, we want this new thing labeled top head to be on top of our data.
The two items need to have the same number of columns. Now, top head is only a single item, so it's only a single column, whereas R data has six columns. So somehow we need to artificially add five columns to top head so that we can indeed successfully concatenate it with R data.
Now, we happen to know that R data has six columns. But in general, if we were writing this as a program, we might not know how many columns we have. columns that are in R data, so it is sensible to use the size function to get the dimensions of R data and then use the results of that to determine how many columns we need to add to top head.
There's our size function and I will put the two dimensions of that into R and C. Next we need to add columns to through whatever is in C. see the total number of columns here i'm just going to add a space character in a later video we will discuss why i'm using a space character rather than an empty text string or an empty matrix finally we need to do the concatenation again we will use square brackets the concatenation operator and here the two items are separated by a semicolon so that indeed top head goes on the top of our data.
Note that our data is now a four by six. And if we look at the contents, there is that information at the top. Now we are ready to write this back to our Excel file. Now note that I have specified the sheet to be resistors, although that is not necessary in this case since resistors is the first sheet.
The file has been has now been updated, and if you look at it, we see that perhaps the display of this information is not as attractive as you might like. You may want to format some of this information in Excel to improve its appearance a little bit. For example, you might merge the top row of cells and center that overall header, but that has to be done in Excel.
Next, Next we will consider adding data into the middle of the data read in from the Excel file, not just on one of the edges.