Locked lesson.
About this lesson
In this lesson, we'll build a Table.
Exercise files
Download this lesson’s related exercise files.
39 - Tables Part 2.docx57.4 KB 39 - Tables Part 2 SOLUTION.docx
58.1 KB
Quick reference
Tables - Part 2
In this video we'll build a Table.
When to use
Use this whenever you want to use a Table in your spreadsheet.
Instructions
# Import libraries:
from openpyxl.worksheet.table import Table, TableStyleInfo
# Create a Table instance
tab = Table(displayName="Our Table", ref='A1:B6')
# Define the Table Style
style = TableStyleInfo(
name = "TableStyleMedium9",
showFirstColumn = False,
showLastColumn = False,
showRowStripes = True,
showColumnStripes = True,
)
# Set defined Style
tab.tableStyleInfo = style
# Add the table to the worksheet
ws.add_table(tab)
Hints & tips
- from openpyxl.worksheet.table import Table, TableStyleInfo
- tab = Table(displayName="Our Table", ref='A1:B6')
- style = TableStyleInfo(name = "TableStyleMedium9", showFirstColumn = False, showLastColumn = False, showRowStripes = True, showColumnStripes = True,)
- tab.tableStyleInfo = style
- ws.add_table(tab)
- 00:04 Okay, in this video, we want to continue on and build out our table.
- 00:07 So in the last video we set up all of our starter code, we imported this worksheet,
- 00:13 that table, table, and table style info thing, and now we want to use those.
- 00:18 So let's start out by creating a variable and I'm going to call it tab and
- 00:21 we're going to set that = Table() instance, and then inside of here,
- 00:25 we need to give this a name.
- 00:26 So let's go displayName =, and I'm just going to call this Table1,
- 00:31 call it really anything.
- 00:33 And now we need to define what our stuff is going to be in this table.
- 00:37 So let's create a reference to and I just want it from A1 to B6.
- 00:44 And if we look really quickly at our company salaries file,
- 00:48 we see it starts at A1, and goes down to B6, right?
- 00:52 So we just give it a range of those things, so, okay, we can close that.
- 00:57 Now, we need to define the style, so let's go style,
- 01:01 and let's set that equal to a TableStyleInfo() instance.
- 01:06 And that's this guy up here that we imported,
- 01:08 whereas this guy is this thing up here that we imported, right?
- 01:12 And here we've got several things, I'm going to put these on separate lines so
- 01:16 we can easily read these.
- 01:17 So, let's set the name =, and we want to do TableStyleMedium9, and
- 01:24 these are sort of the styles we looked at with the graphs.
- 01:29 You can put TableStyleMedium2, 7, 9,
- 01:34 you can do instead of Medium, you can do Light, right?
- 01:39 So we're going to do 9, and
- 01:41 you can just play around with these if you want to see what these to do.
- 01:46 It will basically just changes the colors of our table, and
- 01:48 unfortunately again, there's not really good documentation that explains
- 01:52 what all of these different numbers are.
- 01:54 You just kind of have to sort of play around with it, so, okay, name.
- 01:57 Now we need to say showFirstColumn and notice the capitalization here,
- 02:02 and we don't want to show the first column.
- 02:05 So let's say that's False because that's our header, right?
- 02:09 And let's go showLastColumn and that's also going to be set to False.
- 02:15 And then we can go showRowStripes, and set that equal True to make these striped.
- 02:22 And then showColumnStripes and let's set that equal
- 02:28 to True as well, okay, so that's the tab style.
- 02:33 Now we need to sort of say, hey, use the style for this tab.
- 02:37 So to do that,
- 02:40 we go tab.tableStyleIinfo = style.
- 02:49 So notice the capitalization here lowercase, uppercase, uppercase,
- 02:54 and then this style is just the style variable that we just defined here, okay?
- 03:00 So now we just need to add this table to our spreadsheet.
- 03:04 So we can go ws.add_table, and then we want to pass in tab,
- 03:10 which is what we named our table up here.
- 03:13 And then here we'll save this as table.xlsx.
- 03:16 So let's go ahead and save this, and we save this file as table.py.
- 03:19 So let's head over here, and
- 03:22 let's go python table.py, and that worked.
- 03:26 So then let's come back over here and find our table, that xlsx file,
- 03:31 open it up, and here we go, and we can resize these if we want, like that.
- 03:36 So and this is our table, and that's all there is to it.
- 03:38 So if you click on these things,
- 03:40 you can sort by different things from A to Z that changes it around, from Z to A.
- 03:46 You could sort by Largest Smallest salaries,
- 03:48 all the things you would do with the table and that's cool.
- 03:51 So notice this, column is sort of shaded,
- 03:56 we could change that if we want it to.
- 04:00 So we showColumnStripes, if we set that to false, for instance, and save this.
- 04:06 And then open this guy again, you can see now we just have straight up stripes for
- 04:10 our rows, we can remove those stripes if we wanted to as well.
- 04:14 We can set those to False, right, if we saved this and ran it.
- 04:20 And then open this, right,
- 04:22 we just get sort of all the same color, which is not as useful.
- 04:27 At least in my opinion, set this back to True,
- 04:30 remember I mentioned we could do different of these.
- 04:33 If we wanted to go I don't know 5, I have no idea what this is,
- 04:37 we save it and run it and then open it again.
- 04:40 Okay, now it's purple, right?
- 04:42 So again, you just kind of have to play around with those numbers to see what
- 04:45 the different colours are.
- 04:46 And I wish there was a better way to do it, but
- 04:48 there's just really not good documentation on that, anywhere on the web.
- 04:51 So that's pretty much tables, so that's all for this video.
- 04:54 And in the next video, we'll look at adding images to our spreadsheets.
Lesson notes are only available for subscribers.