#100DaysOfCode in Python Transcripts
Chapter: Days 70-72: Excel automation with openpyxl
Lecture: Understanding workbooks and worksheets in openpyxl

Learn more about this course
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.