Locked lesson.
About this lesson
To work with workbooks and worksheets in excel, we need to import some things from openpyxl.
Exercise files
Download this lesson’s related exercise files.
13 - Creating Workbooks and Worksheets.docx57.2 KB 13 - Creating Workbooks and Worksheets SOLUTION.docx
57.6 KB
Quick reference
Creating Workbooks and Worksheets
To work with workbooks and worksheets in excel, we need to import some things from openpyxl.
When to use
Do this anytime you want to work with an Excel spreadsheet in Python.
Instructions
from openpyxl.workbook import Workbook
# Create a workbook instance
wb = Workbook()
# Set an active worksheet
ws = ws.active
# Create another worksheet
work_sheet1 = wb.create_sheet('New Sheet 1')
# Set a title to active sheet
ws.title = "Our Active Worksheet"
# Print out sheet names
print(wb.sheetnames)
Hints & tips
- from openpyxl.workbook import Workbook
- wb = Workbook()
- ws = ws.active
- work_sheet1 = wb.create_sheet('New Sheet 1')
- ws.title = "Our Active Worksheet"
- print(wb.sheetnames)
- 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.