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