#100DaysOfCode in Python Transcripts
Chapter: Days 70-72: Excel automation with openpyxl
Lecture: Concepts: what did we learn
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: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:26 Now, the little gotcha.
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: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: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: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: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:31 So we wanted to hard code
4:34 in cell L703,
4:37 The Excel sum formula or function.
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: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.