#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,
0:03
because Excel automation can be a bit complex at times.
0:07
Let's open up the Python shell in our virtual environment.
0:11
Okay, and we will import from openpyxl.
0:16
We're going to import load_workbook, okay?
0:20
Now this is going to allow us to actually load
0:25
the Excel workbook, the actual Excel file.
0:29
So I've got the Excel file here.
0:31
This is a financial sample I pulled off the net
0:34
just filled with lots of random data.
0:36
I hope it's not actually real stuff.
0:38
Now terminology, workbook.
0:42
Workbook is the name for this entire file,
0:45
our Excel file, alright?
0:47
That is the workbook.
0:49
So when you hear the term workbook, envision that.
0:52
What you need to then remember if you're not versed
0:56
with Excel is that these tabs down here,
0:58
these are worksheets.
1:00
Okay, so the overall file, the parent file,
1:04
is the workbook and these here are the worksheets.
1:09
Okay, the different spreadsheets inside the workbook,
1:11
alright?
1:13
So visualize that and then you won't get the two confused.
1:16
Now if we want to open the workbook,
1:19
we want to load it in,
1:22
we use workbook or wb = load_workbook, okay?
1:28
And then we need the name of the file.
1:30
So this is financial-sample.xlsx, okay.
1:37
Right and that loaded and then now we can actually
1:41
start to interrogate the workbook.
1:43
So we can go wb.sheetnames
1:47
and that gives us the sheets,
1:50
or the spreadsheets down here so already you see we can
1:55
with interrogating that file,
1:56
we're talking to it, it's pretty cool, right?
1:58
Now one really cool thing that you'll probably see
2:02
is you need to be able to drill down into these sheets.
2:09
So if we're going to import any data or pull any data,
2:12
how are we going to know which sheet we're talking to?
2:16
Well, that's the next step.
2:18
Alright and one of the default things that a lot of people
2:21
go onto is saying okay, my worksheet, worksheet one,
2:25
is going to be the active, the active worksheet, alright,
2:31
and the problem with this is and it's perfectly fine
2:35
if you only have one worksheet
2:36
and you've got some file saves and tests involved here
2:40
but you need to understand this catch.
2:42
wb.active will put you on the first worksheet
2:50
or the last worksheet, I should say,
2:52
that had any sort of data entered or edited,
2:56
whatever, on it, any action on that, any activity, okay?
3:00
So you can see ws one, wb.active,
3:04
is our finances 2017 worksheet.
3:07
If we go in here and we enter in some bogus data,
3:13
we save that.
3:15
Now we obviously need to reload the workbook
3:20
so I'll do that very quickly.
3:23
We reload the workbook.
3:26
Now when we do, let's go ws two equals wb.active.
3:36
We get yearly totals.
3:40
WS one is still pointing at finances 2017.
3:46
So don't let that catch you out.
3:48
If you always want to talk to the active sheet
3:51
or the last sheet that was edited,
3:53
that's perfectly fine but if you want to talk
3:58
to a specific sheet,
3:59
don't assume that workbook dot active is going to get you
4:02
to the right worksheet.
