#100DaysOfCode in Python Transcripts
Chapter: Days 70-72: Excel automation with openpyxl
Lecture: Working with cell values
Login or
purchase this course
to watch this video and the rest of the course contents.
0:00
Let's quickly look at pulling specific cell data out of a spreadsheet. Okay so we've imported openpyxl using
0:10
load_orkbook, and we've loaded the workbook financial sample into the wb variable. So let's look at the sheet names we have available.
0:21
And again, we have Finances 2017 and Yearly Totals. That's this stuff here, okay? Now what do we want to do? Well, let's specify the worksheet we
0:34
want to work on, okay? Now, if we want to specify the exact sheet, we've looked at wb.active and we know that you've got a little catch there.
0:43
So if we want to specify the actual one, we actually go workbook and then we just put the name of the tab, or the spreadsheet
0:53
in there that we got from the previous command. Okay so Workbook Finances 2017 is ws1, and there we go. So now, when we write anything using ws1,
1:06
we are going to be pointing to this worksheet here, which is what we want to have a look at. All right, what's first?
1:14
Well, if we want to get a cell, okay, we need to know the coordinates of that cell. So your coordinates are your letters along the top,
1:25
and your numbers down the vertical. And specifically, we want to get the value of the cell, so we're actually going to use the word value.
1:36
So let's look at just randomly, we'll look at C9. Let's say we want to get the data specifically in this cell, C9.
1:45
So we're expecting to see Montana returned. How do we do that? Well, we go ws1. And then we just simply put in the cell coordinates.
1:58
Look at that, ws1['C9']. And look at that, all we got returned was the object, the fact that C9 is a cell in Finances 2017. So what were we missing?
2:11
Well, as I mentioned, we're going to use value, okay? The value attribute. So ws1['C9'].value. And there we go, we're returned with Montana.
2:25
And we can try that again just to prove that wasn't a fluke, 'cause there are a lot of Montanas there. We can go well, what's B9?
2:32
Okay let's say it's the country Canada. Okay? So we'll go ws1['B9'].value. And there's Canada. All right? So nice, very very cool, very easy.
2:48
You can start pulling data out manually this way. All right, let's do something a little more interesting. Let's say for example we have column L here.
3:00
And we've got the profit of all of these different transactions or whatever they happen to be. We can actually collect all of this data
3:11
and get a total for ourselves. So why don't we do that? Let's go to, let's create ourselves a variable here. So profit total equals zero.
3:25
Now what we're going to do is we're going to create a list. We're going to create a list of this column, of the items in this column.
3:36
So we're going to say full column in list L. So we've created a list of the L column. Should actually put a column there.
3:55
We then go for row. So now we've got the column up here. And now we're looking at the rows in this column.
4:06
Okay so we've got the column, we specified L, now we're going down to each, we're going to individually talk to each one of these cells.
4:14
So full row in range. Now we're specifically going to look at a range here. So why don't we go from row two down to row, how about 101.
4:31
Let's try this one here. So we're pretty much looking at exactly 100 cells. So we'll go full row in range. 2, 101. We want to get the cell.
4:48
So we need, remember we need this cell, we need this coordinate, this C9. Okay we need to put that together. So we're going to go
4:56
column so we go L we know that's our column. And then we want a string of the row because the row is a number.
5:03
So column, meaning L, string meaning this row here. Okay? So string rows, that's what our cell is made up of.
5:16
So this is going to generate L2, L3, L4, L5 and so on to 101. Now, we want to go, want to actually add it all together. So profit total.
5:36
Is equal to, we'll make it a float because we know there were actual float things in there. So ws1, because again we're talking of worksheet one.
5:46
And cell, because remember the code just above was generating our cell for us, and .value. And that should be it. Okay we've closed everything off.
6:02
All right, profit total has been populated. So now we can print profit total and there we go. There's a lot of money.
6:12
So 3.2 million dollars pretty much is the profit total of these cells here, two down to 101. So there you have it. We can talk to the cells.
6:28
We've got our cells here. We can talk to them individually and we can then run some quick little scripts on them, some maths.
6:36
I know it's easier in the document just to highlight it and get the sum, but now you know how you can do it on