#100DaysOfCode in Python Transcripts
Chapter: Days 70-72: Excel automation with openpyxl
Lecture: Inserting data into a worksheet
Login or
purchase this course
to watch this video and the rest of the course contents.
0:00
Alright, for this video, I thought I'd quickly show you what this could potentially look like in a script. So we'll do a standard import here,
0:11
from openpyxl import load_workbook, and we are going to load the same workbook. Now we're going to choose worksheet 1,
0:24
try and format this a little nicer, ws1 = wb, we're still specifying the same file, finances 2017, that same worksheet I should say.
0:38
Now what do we want to do? Let's create a quick function here. We are going to select our L column again, now where's that file going?
0:51
Let's bring this up here nice and quick. We want to take the same profit column, and this time we want to calculate the overall profit of whatever this
1:03
data sample happens to be of. And we want to dump it below here. So how are we going to do that? Well let's just pop back to the file quickly.
1:17
We're looking here, okay. Let's give ourselves some white space, let's throw in the default under there, let's create a function.
1:26
We're going to call this function insert_sum. Because what we're going to do is we're going to insert an actual function here, and insert one of those
1:40
standard Excel sum workers equals sum and so on. Now to do that, let's create the function, def insert_sum
1:52
Alright we don't actually need to pass any variables into this one, working at global level nice and simple. Okay so what do we need to do?
2:03
Well we need to figure out what cell, what row, what column we're working with, same with all the other videos we've seen so far.
2:14
Let's work with how about this. We're in row L, so why don't we go with L703. So how do we do that? We go ws1, still doing the same thing that we did
2:30
on the Python show before, ws1, and we're going to specify L703, now we're hide coding it. I'll show you to get around this in a minute.
2:43
Is, is being assigned, now this is where we throw in that function, so sum L2, 'cause we don't want the header,
2:56
2L, let's see, what was the last row, 701, so L701. Very simple Excel sum there. And then what do we need to do?
3:14
Well something we haven't covered yet, we actually need to save. So we go wb.save. And we're saving the workbook.
3:23
Now we don't necessarily have to put it in the function here, we can throw it down under here, so wb.save. Then we save it as the actual file name.
3:36
So financial-sample.xlsx. And that's it. So what this code will do is it's going to run this function here insert sum, and it's going to insert
3:49
this sum function here into this actual cell here. Let's run that. Python Excel Automation.
4:06
Now why didn't that work? Ah, why do you think? Permission denied, and that is because the file is still open.
4:13
So let's close it, let's not save it, and let's try again. Bang, there we go. Okay, let's open the file again. And let's see what we've got.
4:26
Alright, so there's our total there. We can format, you can see there's the sum that we put in our code, let's quickly format the cell's currency,
4:36
we get a 16.8 million dollar, 16.9 million dollars pretty much. Again we run into that problem with max_row. What if this spreadsheet grows?
4:48
Then we're kind of screwed, aren't we. So let's incorporate max_row in, let's get rid of this, delete that, save the file so there's nothing there,
5:00
and close it off so we don't have any issues. Now how can we change this? Well let's give ourselves a max_row variable, max_row equals ws1.max_row.
5:16
We've figured that out in the last video, now let's change this up. We don't necessarily need to know L703. We just need to know it's L.
5:26
So ws1['L'], let's just do some string work here, I'm going to keep it nice and simple. String max_row. So L max_row.
5:40
Remember the max_row could be 700, 800, 900, 10,000. It's always going to build that with this here. Now we're going to equal assign it, the sum.
5:53
But again the sum, we don't know what that end value is going to be, so let's build that. So we'll go sum L to L, and we'll do a little add here
6:05
of max_row, but think of it this way. If we do max_row, but we're trying to insert max_row into max_row, you're trying to insert the highest cell
6:19
into the same cell it's not going to work, because it's going to try and override itself. So we're going to do max_row minus one.
6:27
We want to go one row down from the max_row. And then we have to throw in that bracket at the end. Alright. That should be it.
6:40
Let's go back across here, get rid of some white space. Alright, let's save that and give it a go. So far so good, I was always confident.
6:58
Open the Excel, and there we go, it's down here. Now why is that? We know it's because there's some white space or something
7:06
in one of these cells here, and our max_row is 705. So what it's done is it's actually done the max_row
7:15
minus one from our code, max_row here, minus one, and that's made our last section here of the range to be L704.
7:29
So now if this spreadsheet grows, max_row let's say grows up to be 710, max_row will be 710 but our sum will point to L2 to L709.
7:43
And we have a nice little, oh let's cancel this, whatever. And I assume I've broken that now, look at that. I've absolutely broken it.
7:52
So let's pretend I didn't do that. And let's open that file again. And by doing this we now have this nice little figure there.
8:02
And that's pretty much it. That's how you add a sum, some sort of a function, or whatever you want.