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.
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.
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.
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
.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.
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
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
So, I’m going to make a new workbook called
amzn (Amazon) and call
load_workbook() and pass in the
"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
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.
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.
Become a Member to join the conversation.