#100DaysOfCode in Python Transcripts
Chapter: Days 70-72: Excel automation with openpyxl
Lecture: Understanding workbooks and worksheets in openpyxl
Login or
purchase this course
to watch this video and the rest of the course contents.
0:00
All right, we're going to start off pretty simple, because Excel automation can be a bit complex at times.
0:08
Let's open up the Python shell in our virtual environment. Okay, and we will import from openpyxl. We're going to import load_workbook, okay?
0:21
Now this is going to allow us to actually load the Excel workbook, the actual Excel file. So I've got the Excel file here.
0:32
This is a financial sample I pulled off the net just filled with lots of random data. I hope it's not actually real stuff. Now terminology, workbook.
0:43
Workbook is the name for this entire file, our Excel file, alright? That is the workbook. So when you hear the term workbook, envision that.
0:53
What you need to then remember if you're not versed with Excel is that these tabs down here, these are worksheets.
1:01
Okay, so the overall file, the parent file, is the workbook and these here are the worksheets. Okay, the different spreadsheets inside the workbook,
1:12
alright? So visualize that and then you won't get the two confused. Now if we want to open the workbook, we want to load it in,
1:23
we use workbook or wb = load_workbook, okay? And then we need the name of the file. So this is financial-sample.xlsx, okay.
1:38
Right and that loaded and then now we can actually start to interrogate the workbook. So we can go wb.sheetnames and that gives us the sheets,
1:51
or the spreadsheets down here so already you see we can with interrogating that file, we're talking to it, it's pretty cool, right?
1:59
Now one really cool thing that you'll probably see is you need to be able to drill down into these sheets.
2:10
So if we're going to import any data or pull any data, how are we going to know which sheet we're talking to? Well, that's the next step.
2:19
Alright and one of the default things that a lot of people go onto is saying okay, my worksheet, worksheet one,
2:26
is going to be the active, the active worksheet, alright, and the problem with this is and it's perfectly fine if you only have one worksheet
2:37
and you've got some file saves and tests involved here but you need to understand this catch. wb.active will put you on the first worksheet
2:51
or the last worksheet, I should say, that had any sort of data entered or edited, whatever, on it, any action on that, any activity, okay?
3:01
So you can see ws one, wb.active, is our finances 2017 worksheet. If we go in here and we enter in some bogus data, we save that.
3:16
Now we obviously need to reload the workbook so I'll do that very quickly. We reload the workbook. Now when we do, let's go ws two equals wb.active.
3:37
We get yearly totals. WS one is still pointing at finances 2017. So don't let that catch you out. If you always want to talk to the active sheet
3:52
or the last sheet that was edited, that's perfectly fine but if you want to talk to a specific sheet,
4:00
don't assume that workbook dot active is going to get you to the right worksheet.