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