python openpyxl library – write data or tuple to excel sheet

In our previous post, we have seen how to read excel sheet details like single cell data, multiple rows, sheet names, no. of rows & columns.

In this post we will be exploring the openpyxl capabilities to write cell data or multiple data into excel sheet.

Let’s discuss the manual steps if we want to write some value to excel sheet

• Open workbook from a location
• Create or chose an existing sheet
• Have some data & write into specific cell or cells
• Save the workbook

let’s see how we can implement the above in code

Write into single cell

# xlReader.py
# author - qavalidation.com | youtube.com/qavbox

import openpyxl

wb = openpyxl.load_workbook("./test_data.xlsx")

wb.create_sheet('data')
sh = wb['data']

sh.cell(1, 1).value = "qavbox"
print(sh.cell(1, 1).value)

wb.save("./test_data.xlsx")

We can create a new sheet by using wb.create_sheet('someSheetName')

We are just assigning some value to sh.cell(rowNo, ColNo).value

and saving the workbook with wb.save("filename_path")

Also we can use the sheet default header and row names to write data

#  A is the sheet column name, 1 is the 1st row name 
sh['A1'].value = "QAVBOX"
print(sh.cell(1, 1).value)

sh['A1'] is the 1st column, 1st row cell

Write multiple data like list of tuples into sheet

There are instances where we need to add multiple rows of data into sheet, we can have the test data as list of tuples

testdata = [('username', 'password', 'type'), 
            ('abc', 123, 'valid'), 
            ('def', 456, 'invalid')]

let’s think that this 3 sets of data we need to enter in each row of excel sheet

We have to loop over the list and then each item in list we need to use append() to write into excel sheet, append method will move to the next empty row on sheet and write the data

testdata = [('username', 'password', 'type'), ('abc', 123, 'valid'), ('def', 456, 'invalid')]

for item in testdata:
    sh.append(item)

wb.save("./test_data.xlsx")

if you want to add more data, then with out knowing what is the empty row index, we can add the data using append method

testdata1 = ("qavbox", "qavalidation.com")
sh.append(testdata1)

Note – don’t forget to add the wb.save(“filepath”) at the end, then you will get the updated content on excel sheet.

Hope this helps!