Appending Data to Spreadsheets

00:00 Now that you feel comfortable working with openpyxl syntax for reading Excel spreadsheets, it’s time to learn how to start creating them. Before you try and create a complex spreadsheet, however, we’ll start by appending to an existing spreadsheet.

00:13 Go ahead and make a new script. I’m going to call mine. hello_append.py, since this will be a very short example.

00:21 So, like before, from openpyxl import load_workbook,

00:27 then grab that workbook by load_workbook() and pass in the filename. This one was "hello_world.xlsx". Then, the sheet will be the active worksheet. So workbook.active.

00:41 So, just like before, you can say sheet and—this time—put some data into "C1", and just put something in here, like "writing!".

00:49 Now, since this hasn’t hit the worksheet yet, you need to save that, so say workbook.save(), pass in a filename and—to make sure nothing gets overwritten—say "hello_world_append.xlsx". Save this. So, looking at this, you’re going to load the workbook and the sheet, and then now, you’re going to insert a value into C1, and then you’re going to save that workbook with a new name.

01:15 Go ahead and run this. python hello_append.py.

01:20 No errors and a new spreadsheet opened up. Okay. And look at that! You still see hello world!, and now, C1 has writing!. This might not seem like much, but it’s a big step towards creating a new spreadsheet.

01:36 Let’s go back to that first script and look into what happened a little deeper. So, I’m going to go back to hello_world.py, or actually, hello_openpyxl.py.

01:45 So, the big lines here are this third line where you made the new Workbook—and note that this Workbook is not actually an Excel workbook.

01:53 It’s just openpyxl’s model of a Workbook, that represents a spreadsheet. In these lines, you actually assign values to the Cell object.

02:02 Finally, this last line here is the most important. Without this, all of this code would stay within Python. When you save the workbook, that’s what actually creates the Excel spreadsheet, using the information that was stored in the Workbook model.

02:16 If this seemed a bit confusing, let’s go over to the interpreter and see if we can make it a little clearer. I’m going to open up bpython, bring this up a bit and—like before—do the imports. So, from openpyxl import load_workbook.

02:30 Set the workbook up and set a sample: we’ll say "hello_openpyxl.py".

02:37 And, of course, I grabbed the wrong sheet—or, the Python file. So, grab the "hello_world.xlsx" Excel spreadsheet.

02:46 Now, sheet will be a workbook.active. Something that might help here is to define a new function called print_rows().

02:54 And what this will do is for row in sheet.iter_rows()—and you’ll want to go through the whole sheet, grab those values, set values_only equal to True, and we’ll just print(row).

03:05 So now, if you call this

03:09 and make sure the parentheses are closed, you should see that first row prints out from the original spreadsheet. So, from that first script, you already know how you can access and set values to those cells.

03:20 So, for "A1", try setting this equal to "value", and now when you take a look at "A1"—

03:27 and make sure you grab the .value off of it—you’ll see that it now says 'value'. Another way you can work with cells is by assigning them to a variable.

03:35 So you can say cell = sheet at "A1", and now when you take a look at cell.value, you should see 'value'. From here, you can change the values, so say, cell.value = "hey", and now if you take a look at it, you’ll see, 'hey'.

03:52 So now, take a look at print_rows() and you should see 'hey', instead of 'hello'. Now, something interesting here—say, something like sheet at "B10" is something like "test". Now when you call print_rows(), something interesting is going to happen.

04:08 You’re going to see all these blank rows show up now, until you get to row 10 with 'test' in column B. openpyxl is only going to load into memory cells of the workbook that it thinks are useful to you. So before any data was present in row 10, openpyxl only thought you needed that first row.

04:26 Now that you have something there, openpyxl needs placeholders for all of these extra cells. All right! Now you’ve practiced appending to an existing spreadsheet and taken a bit of a deeper dive into how openpyxl handles Excel spreadsheets.

04:42 In the next video, you’re going to see how to manage the rows and columns of an Excel spreadsheet using openpyxl.