Appending Data to Spreadsheets
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.
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: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.
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
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.
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.
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,
So now, take a look at
print_rows() and you should see
'hey', instead of
'hello'. Now, something interesting here—say, something like
"B10" is something like
"test". Now when you call
print_rows(), something interesting is going to happen.
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.
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.
Become a Member to join the conversation.