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
We are just assigning some value to
and saving the workbook with
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!