Managing Rows and Columns

00:00 When working with spreadsheets, you often have to add or remove rows and columns. openpyxl makes this easy by adding a set of methods to the sheet class: .insert_rows(), .delete_rows(), .insert_cols(), and .delete_cols().

00:13 They can each take two arguments: an index to perform the insert or delete, and then how many rows to insert or delete. So, let’s go back to the hello world! example and mess around with these new methods. I’m going to open up bpython, I’m going to import load_workbook,

00:29 I’m going to load the workbook, and then grab that active sheet. And then to help see how you’re changing the workbook, go ahead and define that print_rows() function again.

00:39 So, for row in sheet.iter_rows(), and then grab the values_only as True,

00:45 then print the row. So if you go to print_rows(), you should see ('hello', 'world!'), again. Note that all the changes we did in previous videos were not saved into this workbook, so it’s still like it’s always been. All right.

00:59 Let’s try out inserting a column. sheet.insert_cols() and start with an idx (index) of 1, which should insert a column before the first column in the worksheet.

01:10 So now if you do print_rows(), you should see a blank cell in the first column.

01:15 Let’s try this again, and this time insert the columns at idx=3 and then say amount=5. And, of course, make sure there’s the right number of parentheses.

01:27 All right. So now when you print the rows, you should see None in the first column, and then now, starting at column 3, you’ll have five empty columns. That’s pretty cool.

01:38 You can go ahead and delete that by using .delete_cols() and just like where you inserted them start at idx=3 and say amount=5, and then now, delete that first one too.

01:49 So, .delete_cols() and then just pass in idx=1. Now, you can print_rows() and you should be right back where you started with ('hello', 'world!').

01:57 So now, try out inserting rows. So say, sheet.insert_rows() and set the idx=1. And now, if you print the rows, you should see a blank row pop up.

02:11 Like the columns, you can also set the amount of rows to insert. So, .insert_rows() at idx=1 and then say amount=3.

02:20 Run that, and then print the rows and you’ll see that there’s three new blank rows. You can then clean this up by saying .delete_rows(), so, start at idx=1, and now say amount=4.

02:33 And now, when you print the rows again you should be back to just ('hello', 'world!').

02:38 Keep in mind that inserting occurs before the index, so just like if you were to highlight a cell in Excel and insert by right-clicking. So, when you did this line of code here and inserted three rows at index 1, it put those right at the start here.

02:55 So the first three rows are the new rows and the fourth row is the original one that was added. All right, now that you can handle managing your rows and columns, let’s move on to managing individual sheets.