Creating Workbooks and Worksheets

Quick reference

• 00:04 Okay, in this video we want to start working with openpyxl.
• 00:07 So when you think of spreadsheets, you think of workbooks and worksheets, right?
• 00:11 The workbook is sort of like Excel itself.
• 00:14 It's the file that you open.
• 00:15 The worksheet is the individual sheet that comes in the workbook, and
• 00:19 you can have many different worksheets in a workbook.
• 00:22 So in this video, we're going to look at creating worksheets using Python.
• 00:25 So first things first, we need to import some things from openpyxl.
• 00:29 So we can go, from openpyxl.workbook.
• 00:36 We want to import Workbook.
• 00:39 And you'll notice the W is capitalized here, that's important.
• 00:43 So we also want to load workbooks that already exist.
• 00:47 And we're not going to do that in this video, but
• 00:49 we might as well import this while we're importing things.
• 00:51 So let's go, from openpyxl import load_workbook.
• 00:58 So there are many different things in openpyxl, and this is how we sort of
• 01:01 import different things that we're going to be using in our code.
• 01:05 So, all right, first things first, let's create a workbook object.
• 01:12 So we're going to call this wb, stands for workbook.
• 01:15 You can call this anything you want, but that makes sense to me.
• 01:17 And we want to set this equal to a Workbook object, and
• 01:21 that's how we do that.
• 01:22 So once we've created our workbook, we need to sort of create some worksheets and
• 01:27 designate which ones are active, right?
• 01:30 Whenever you open an Excel file,
• 01:31 there's always one worksheet that is opened by default.
• 01:34 We can set that by making an active worksheet.
• 01:37 So let's go, set active worksheet.
• 01:42 And we're going to call this ws for worksheet,
• 01:46 and we can set that equal to a wb.active.
• 01:49 And that's this wb is this workbook, right?
• 01:52 We're referencing this workbook we just created.
• 01:55 We're saying, hey, make this worksheet the active workbook sheet, right?
• 02:00 Okay, so that's one sheet.
• 02:02 Now, what if we want more than one sheet?
• 02:03 Let's create a couple of new ones, a couple of other ones.
• 02:06 So let's go, create two new worksheets.
• 02:09 And obviously, these are empty for now, but we just want to create them.
• 02:13 So let's call this one work_sheet1.
• 02:15 And that will be a wb object, dot.
• 02:20 What do we want to do, we want to create a sheet, right?
• 02:24 And then now, let's name this, and let's name this New Sheet 1.
• 02:27 And I want this one to be listed first.
• 02:30 And remember when we talked about lists, the first item of a list is always 0.
• 02:35 Same thing here, the first item in our workbook will be 0.
• 02:39 So let's create another one.
• 02:40 Let's go, work_sheet2.
• 02:42 And that's going to be, actually, we can just copy this whole thing and
• 02:46 paste it in.
• 02:47 And let's call this one New Sheet 2.
• 02:49 And we don't need an index number, it'll just be the next one.
• 02:53 So we've got these two new sheets, and we've named them New Sheet 1 and
• 02:57 New Sheet 2.
• 02:58 We've also got this this sheet that we created, the first one, and
• 03:01 we don't have a name for it.
• 03:02 So let's add a name, so add title to active sheet.
• 03:08 So we can just go, ws.title, and then send it to whatever we want.
• 03:13 So let's call this, and you can use single quotes or double quotes.
• 03:17 Let's call this New Sheet 3.
• 03:21 So we've got these three sheets.
• 03:23 They're in this workbook, and what can we do?
• 03:26 Well, before we get into actually putting data in them,
• 03:29 let's just run a command from our terminal to see if this worked,
• 03:32 if we've actually created these things and what their names are.
• 03:36 Let's go, print out sheet names.
• 03:39 And we can do that by calling the print function like we've learned before,
• 03:44 and then just passing in wb.sheetnames, because that's what we want.
• 03:49 We want the sheet names.
• 03:50 So okay, let's save this, head over to our terminal.
• 03:53 And let's run this file in Python, hello.py.
• 03:55 And when we do, we get this Python list.
• 03:58 Hey, remember lists, because of our square brackets here,
• 04:02 with each of the sheet names.
• 04:03 Now, notice how they're numbered, New Sheet 1, New Sheet 3, and New Sheet 2.
• 04:08 Why is that?
• 04:09 Well, if we head back over to our code, remember,
• 04:13 right here we set New Sheet 1 to be the first one, which it is.
• 04:18 But New Sheet 3 is the next one listed, why is that?
• 04:21 Well, that's because we defined it up here,
• 04:24 above worksheet 2 or New Sheet 2, right?
• 04:28 Even though we named it down here, we defined it up here.
• 04:30 So it's the active one, it's listed before New Sheet 2.
• 04:35 So okay, that's cool.
• 04:36 Now, one quick thing, notice I mentioned these are in lists.
• 04:40 So we can reference each of these individually if we want,
• 04:43 using the things we've learned from lists already.
• 04:46 So we can go wb.sheetnames, and then pass in a list item.
• 04:50 So if we want the first one, we can just pass in 0.
• 04:53 So let's save this and run it.
• 04:57 And boom, New Sheet 1, very cool.
• 05:00 So we can also loop through here with lists.
• 05:02 So we can go, let's say, for sheet in.
• 05:06 And then we could just reference this whole thing, right?
• 05:10 And then let's just print out, sheet.
• 05:13 And let's comment this one out.
• 05:16 Okay, so let's save this and run it.
• 05:20 And we get New Sheet 1, New Sheet 3, and New Sheet 2 in nice, readable format.
• 05:24 So very cool and very easy, and just like that,
• 05:27 we've created a workbook with three new sheets.
• 05:29 We can reference the names if we want programmatically, and
• 05:32 we're moving right along.
• 05:33 So that's all for this video.
• 05:34 In the next video, we'll look at loading an existing Excel sheet.

Lesson notes are only available for subscribers.