#100DaysOfCode in Python Transcripts
Chapter: Days 70-72: Excel automation with openpyxl
Lecture: Concepts: what did we learn
Login or
purchase this course
to watch this video and the rest of the course contents.
0:00
And that's pretty much the basics of openpyxl.
0:04
So it's really cool,
0:05
it's really interesting and you can see how
0:08
with a little bit of work
0:09
you can start to automate
0:11
adding data to specific cells
0:14
or specific columns.
0:16
Really it's all just about knowing the column numbers,
0:19
isn't it?
0:20
So let's go over everything we've learned
0:23
for the past couple of days.
0:25
Alright, so first and foremost,
0:27
there's the Excel workbook
0:28
that we're playing with.
0:30
By now you probably hate it
0:32
as do I.
0:33
I don't blame you.
0:34
So we pretty much dealt with the L column here
0:38
where we were looking at the profit.
0:40
Okay, we did touch on the country column
0:42
just to show how to list out everything
0:44
in a specific column.
0:46
Alright, so how do we start?
0:49
Well, we import it, openpyxl,
0:51
load_workbook.
0:54
Okay, that's pretty much all we needed really.
0:56
And we use load workbook to load in
0:59
our workbook.
1:00
Nice and easy, so far and
1:04
we assign that to the variable WB
1:07
and we can then call .sheetnames.
1:11
And that allows us to print
1:13
all of the worksheets in the workbook
1:15
remembering that your worksheets
1:17
they're sort of tabbed spreadsheets
1:20
that at along the bottom
1:21
of your Excel workbook.
1:24
Okay.
1:26
Now, the little gotcha.
1:28
Workbook.active,
1:30
the active assigns the last active worksheets.
1:34
So the last worksheet that had an edit
1:37
saved to it.
1:39
Okay, that is what active does.
1:41
So that is something that can catch you out
1:43
if you're not careful.
1:44
The safer bet is to just assign
1:48
the actual spreadsheet name.
1:51
The actual worksheet name.
1:53
So, we used Finances 2017.
1:56
We assign that to the other actual worksheet
1:59
Two Variable, ws2.
2:01
Okay?
2:03
Same sort of thing.
2:05
This time we're specifying a specific cell.
2:10
Okay? So we chose C9, ws1, C9,
2:15
and then we wanted the value of that cell.
2:18
Okay, if we got rid of value
2:19
and we just click that
2:20
we'd get just the object, okay?
2:25
So this is how we got value.
2:28
And finally, on that day
2:30
we did go through putting all of this together
2:33
into a four loop, okay?
2:35
And what this did was it went over that list L,
2:38
we took the column L, and we made it a list
2:41
and we iterated over it
2:43
with every row that we had
2:46
and we went all the way to 101.
2:48
So, pretty much, 100 cells, we looked at
2:52
and then we built the cell number here,
2:55
and then took that cell number
2:57
and added the value,
2:59
so it was a dollar value.
3:00
We added all of that together
3:02
and threw it in the profit total variable.
3:05
Okay?
3:06
And then printed that out.
3:07
So that's a nice little use case
3:09
of openpyxl.
3:14
Then we talked about how to actually
3:16
specify the maximum row because we don't always
3:19
want to hard code a cell in there
3:22
as the higher end of our range.
3:25
So that's where max_row comes in handy.
3:28
Okay?
3:29
So it gets the number of the maximum row
3:31
that is used even if you have blank rows,
3:35
some of them maybe active
3:37
because they have a space in there
3:39
or they had data,
3:40
or that was selected when it was saved.
3:42
And that will result in max_row being
3:46
whatever that cell was.
3:47
Okay?
3:50
Now we put that in place
3:52
in the range, okay.
3:55
As a range argument and therefore,
3:58
we were able to go from cell two
4:01
all the way to the last row,
4:03
and then print that out
4:04
and this column B was our country column
4:07
and that allowed us to printout
4:09
all of the countries that were used in that specific column,
4:13
all 701 of them or something like that.
4:18
Alright, now, moving on.
4:19
We then did something similar
4:22
but we were dealing with the actual sum,
4:26
the actual Excel function that we can put in there,
4:29
the formula.
4:30
Okay?
4:31
So we wanted to hard code
4:34
in cell L703,
4:37
The Excel sum formula or function.
4:40
Okay?
4:41
And we specified L2 to L701,
4:44
sum it up, throw it into that cell there.
4:49
And then we saved it. Super important.
4:50
Have to save it.
4:51
And just remember, the issue that we had
4:54
was that we tried to save it
4:55
while the document was open,
4:56
in Excel itself.
4:57
And it won't do that, okay?
4:58
It won't save,
4:59
you'll get an error code.
5:01
Now to implement max_row into this sum,
5:05
we then did something similar, okay?
5:08
We just rebuilt this entire line here,
5:11
but substituted the actual cells,
5:14
with the max_row command.
5:16
Alright.
5:17
So we put in max_row here,
5:19
to get our cell that we wanted to put
5:21
the actual data into at the end of it.
5:26
Then, in order for the calculation to work
5:30
so there would be no sort of conflicts,
5:32
we then took max_row
5:34
minus one, so we wanted the max_row
5:38
but this, the cell above it
5:41
and that should get us the last dollar value
5:45
and then there would be no overrides
5:49
and no sort of conflict, okay?
5:51
So there we go at the minus one
5:53
to prevent clashes with calculations
5:56
and then we saved it again.
5:57
Now obviously, this is not fool proof.
6:00
This is just working for this specific spreadsheet.
6:02
You'd obviously have to do those checks
6:04
in detail for yours.
6:07
Okay, now it's your turn.
6:09
So what can you do with this?
6:11
Well, I think and I pretty well
6:14
used use case,
6:15
something that's obvious,
6:17
would be to monitor a spreadsheet.
6:20
So a lot of places might use Spreadsheets
6:22
for things like say, rosters
6:24
or financial tracking or your budget,
6:27
so you could potentially use a script
6:29
to monitor a certain cell
6:33
or to add data in as you go,
6:36
just to come up with with something intuitive,
6:38
something interesting to do.
6:41
I think a budget is a really good example
6:43
or some sort of a rostering system.
6:45
So have a play with openpyxl,
6:47
see what you can insert and add and edit and whatever
6:52
to an Excel spreadsheet
6:54
and do that for Day 3.
6:56
Just how.
