Managing Sheets

00:00 Quite a few workbooks that you’ll deal with will have more than one sheet. In fact, this is one of the things that makes Excel so business-friendly to use. When this is the case, you might not always be able to use sheet.active to grab the sheet you need.

00:13 You can actually select specific sheets in much the same way that you grabbed the cells earlier. Let’s go to the interpreter to see how this works. So, like before, start off with the regular imports, load a workbook, and grab the "hello_world.xlsx" workbook this time.

00:29 And instead of grabbing the active sheet, go ahead and say workbook.sheetnames. This property will show you a list of the sheet names in the workbook.

00:39 You can actually grab this sheet, kind of like the way you grabbed the cells. So you could say that sheet = workbook and then call the 'Sheet' index off of it. So now, if you take a look at what sheet is, you’ll see that you have the Worksheet object here.

00:53 Let’s give this a more descriptive name, so let’s say something like sheet.title and now set this equal to something like "Hello".

01:01 So now, if you take your workbook and you list the sheet names, you’ll see 'Hello'. If you want to add a sheet to a workbook you can call the .create_sheet() method with a sheet name.

01:13 So something like new_sheet is equal to a new workbook.create_sheet(), and call this something like "New sheet". So now, if you take your workbook and look at .sheetnames, you’ll see 'Hello' and 'New sheet'. .create_sheet() will also take an index, so if you say something like first_sheet = workbook.create_sheet() and then call this something like "First Sheet", and then passing in index 0. And now if you take a look at .sheetnames, you’ll see that 'First Sheet' is now in the first position.

01:48 Now, there’s no real need to have this 'First Sheet' here, so to get rid of it you can take your workbook and then call .remove(), and then pass in the sheet object, which you defined as first_sheet.

02:00 Take a look at .sheetnames, and it’s gone.

02:03 If you ever need to copy a worksheet, there’s a .copy_worksheet() method, as well. So take a Workbook and then call .copy_worksheet(), and then pass in the sheet that you would like to copy.

02:16 So, if we go back here, and pass in sheet and that returned this 'Hello Copy'.

02:23 Now, take a look at the workbook.sheetnames and you’ll see at the end, you now have this 'Hello Copy', which would be an exact copy of this first worksheet here.

02:32 Finally, sheets have the ability to freeze cells or hold them in view while the user scrolls through the rest of the spreadsheet. This is common to do on header rows, so the user can scroll through the worksheet and still see what each column represents. To do this, call .freeze_panes.

02:47 So, I’m going to make a new workbook called amzn (Amazon) and call load_workbook() and pass in the filename of "sample.xlsx", and then sheet is going to equal workbook.active, and then using sheet I’ll call .freeze_panes and set this equal to "C2". Now, take this new amzn workbook and save it as "sample_frozen.xlsx".

03:12 Let’s go take a look at what that workbook looks like. If you open up this sample_frozen.xlsx workbook and go to scroll—and, that didn’t actually freeze anything. And here it is.

03:26 So, we’ve loaded the new workbook as amzn, but the sheet was assigned off of the original workbook. So let’s rerun this and now say sheet = amzn.active and then call sheet.freeze_panes, set that equal to "C2", and then now go ahead and save that.

03:46 Going back over to the correct worksheet, if you try to scroll around, you’ll see that the column A and B in row 1 stay in view.

04:07 So, keep in mind that that .freeze_panes for a sheet acts just like freezing in Excel,

04:15 where the cell that you select will be the first one that will actually move and everything above or to the left will be frozen. And with that, you should have a solid understanding of some of the ways you can manage sheets in openpyxl. Thanks for watching.