Load Existing Excel File and Grab One Cell

Quick reference

• 00:04 All right, in this video I want to show you how to load an existing Excel file and
• 00:08 grab one cell of information from that file.
• 00:10 So I've created a new Excel document called pizza.xlsx.
• 00:15 And you can see it's just very basic, we've got two columns, we've got names and
• 00:18 favorite pizzas.
• 00:18 This column has names, this column has sort of made up favorite pizza toppings.
• 00:23 So I save this in the same directory where we've been working so far /c/python-excel.
• 00:29 If we come here and tap the ls command to list the stuff in this directory we can
• 00:33 see sure enough there it is.
• 00:35 You can also open a Windows Explorer, navigate to that same Python Excel
• 00:39 directory, and you can see here it is pizza.xlsx.
• 00:42 So I've also created a new file to write our code in, I've named it load.py.
• 00:47 So just like in the last few videos,
• 00:49 we want to use these two lines of code to import openpyxl.
• 00:52 And in this video, we're going to be looking at this load_workbook one.
• 00:55 We didn't actually look at this, in the last couple of videos, but now,
• 00:57 we want to look at it.
• 00:58 So, to load an existing Excel spreadsheet, it's actually really easy.
• 01:01 Let's create a variable called wb, it stands for workbook.
• 01:04 And we want to set that equal to load_workbook, and this is a function.
• 01:10 And you'll notice that's this thing, right here.
• 01:12 That's this thing we're importing from openpyxl's this function.
• 01:15 And inside of here,
• 01:17 we want to pass in the file name of the Excel spreadsheet we want to load.
• 01:22 So ours is pizza.xlsx.
• 01:23 Now we can do this, this is called a relative path.
• 01:27 And we can do this because I saved this file in the same directory that our code
• 01:32 is saved in.
• 01:33 But if your Excel spreadsheet was in a different file somewhere,
• 01:36 you can use the absolute path.
• 01:37 So for instance, if it was in c/:python-excel/pizza,
• 01:42 we could do this as well and this also works.
• 01:46 But since this file is saved in this directory, and this load.py file
• 01:51 is also saved in this directory, we could just use a relative path here.
• 01:56 So this will load this current Excel spreadsheet and
• 01:59 assign it to this workbook variable.
• 02:02 Now we want to grab the active sheet.
• 02:06 And our spreadsheet only has one sheet but we still need to define it.
• 02:09 So let's go ws for worksheet, and that will be the wb.active and
• 02:13 we've looked at this in the last video.
• 02:15 So now we have this worksheet, and now we can grab information from it.
• 02:20 So let's grab a specific cell and
• 02:23 we could do that by just calling the worksheet variable and
• 02:28 then specifying the cell we want to grab, so let's grab A2.
• 02:33 And if you're familiar with Excel spreadsheets,
• 02:36 you should be you know that we have columns and rows.
• 02:38 So column A is this column right here, and
• 02:41 row 2 would be John, so A2 would be John, right?
• 02:46 So we've got this.
• 02:47 Now let's assign this to a variable, so let's call sell equals whatever this is.
• 02:52 So now we can print this out.
• 02:56 And if we save this and run load.py from our terminal, so
• 03:00 let's go python load.py, we see we get this object back.
• 03:05 Now this is not very useful this is a python object.
• 03:08 And python is an object oriented programming language,
• 03:11 so it returns an object and like I said that's not useful.
• 03:14 So if we want the actual value of what's in that object,
• 03:18 we can come down here and call.value.
• 03:21 So if we save this and run it, now it should print out John.
• 03:26 Let's say we want to change what's in A1 from John to something else.
• 03:31 How do we do that?
• 03:32 Well, let's comment this out, and let's go change cell value.
• 03:38 We could just grab this thing, whatever the cell is, and
• 03:41 then set it equal to something else.
• 03:43 So if we wanted to change it from John to Johnny, for instance, we could.
• 03:49 And now if we want to print that out again to our terminal,
• 03:55 We could grab this whole thing, Set that equal to that and
• 03:59 then print this out, if we save this and run it.
• 04:03 Now it says Johnny.
• 04:05 So that's great.
• 04:06 we've changed the file, but we haven't actually saved this.
• 04:09 So if we close this program, our Excel spreadsheet won't have actually changed.
• 04:13 So let's get rid of the print function and let's now save this.
• 04:16 And to save this, we just call our workbook which is called workbook,
• 04:20 and then we pass in .save.
• 04:22 Now this is a function, and now we need to give this a name, so
• 04:25 maybe we want to just overwrite the original file.
• 04:28 Maybe we want to save this as a different file,
• 04:30 just in case we need to reference back the old file.
• 04:33 So I'm going to name this one pizza2.
• 04:35 So let's go ahead and save this and now run it.
• 04:39 And it seems like nothing happens.
• 04:41 But now if we type in our ls command, we can see now there's this pizza2.xlsx file.
• 04:47 And if we head over to our Windows Explorer,
• 04:50 in our Python Excel directory we see sure enough there's this new pizza2.xlsx file.
• 04:56 If we open it, boom, we see now A2 is Johnny.
• 04:59 So that's all there is to opening an existing Excel file,
• 05:02 grabbing a single cell, and then changing the cell and saving the workbook.
• 05:06 Pretty straightforward and pretty easy.
• 05:09 In the next video, we'll look at another way to modify individual cells.

Lesson notes are only available for subscribers.