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