#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,
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:11
open.
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:35
Alright.
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.
