In this video, we will see how to use MATLAB to delete entire rows or columns from an Excel worksheet as well as how to modify the contents of individual cells within a worksheet. First we will read in the resistor sheet from the components example. Looking at the contents of our data, we see that the fourth column has information about a 10k resistor. Let's simply delete that column. Note that our target variable here is all rows, the fourth column of our data.
That's the column containing the 10k information, and we want to replace that with an empty matrix. Now, if I replace that with an empty matrix, it's being replaced by nothing, so that entire column just disappears. Now, if we look in our data, we see that the column containing the 10K is indeed gone.
The other two columns to the right have, of course, moved over one column to the left to fill in that space. Next, let's change the power rating of the 220 ohm resistor to 1 watt. It's currently 1 half watt. Note that that power rating is in the third row.
the second column so that will be our target. We have rdata, third row, second column, and we want to replace that with a 1. Now do note that the 1 is in curly braces here. Since rdata is a cell array, we need to force that 1 to also be a cell array so that we won't have a conflict when we try to write it into rdata.
We'll see a slightly different way of doing this in just a moment. Now looking at our data, we see that indeed the power rating of the 220 ohm resistor has been changed to 1. Next, let's change the 5.1k resistor value to 7.5k. Now we will write that as 7k5 as we discussed in an earlier video.
That information is in row 2, column 3, so that will be our target this time. So we have the second row, third column of R data, and we want to replace that with the character array 7k5. Now note the 7k5 is in glitches to make it a character array, but our target is a cell array, so we also need to enclose that in curly braces. If we look at R data now, indeed it does contain a 7.5k. I want to show a slightly different method for doing this same thing, so I'm going to manually change this value back to 5k1.
There we go. In this second method, I'm going to use curly braces to index into our data instead of parentheses. This says I want to replace the contents. Then again, I've got second row, third column. But since now I'm replacing the contents, I don't want to replace the contents with a cell array.
I don't want a cell array within a cell array. So when I specify the value on the right, I don't use the curly braces. I just use the glitches to make it a character array.
When we enter that, we see that indeed it has been changed again to 7k5. Finally, we need to run the program. write this information back to our Excel spreadsheet.
Now we do have a little bit of a problem here because we deleted a column from our data. That means that our data has one fewer columns than its corresponding version back in the Excel worksheet. If I were to do just a simple write cell, it would overwrite the first five columns because that's how many columns are in our data. But since there was a sixth column of data in the spreadsheet itself, it would leave that untouched.
And so now we would have an extra column that we didn't really want in our spreadsheet. So somehow we need to tell write cell that we want to get rid of any extra data. The way we do that is by specifying a name value pair. The name is Write Mode and the value is Overwrite Sheet. What this says is when you do this right, first erase the entire sheet, then write the information.
Now if we look in the Excel worksheet, we see that indeed the new data has been written and that extra sixth column, which was sort of left over, is no longer there. Because the sheet was erased, before we did the write. In our next video, we will see how to acquire information from an Excel spreadsheet, perform some computations on the data contained therein while we're in MATLAB, and then update the Excel workbook to contain the results of those computations.