Using Python xlrd and xlwt for Excel Read/Write Operations

Python manipulation of Excel files mainly relies on the xlrd library for reading and the xlwt library for writing. These tools are essential in automated testing for storing configuration, test data, and validation data, converting database results to JSON, and merging or linking multiple Excel sheets.

The following code snippets demonstrate basic operations without additional explanation.

#encoding:utf8
import xlwt
from xlrd import *
from xlutils.copy import copy
import time
path = r"/Users/xxx/Desktop/excel/write.xlsx"

def write():
    """Write a single cell"""
    rb = xlwt.Workbook()
    sheet = rb.add_sheet(u'sheet1', cell_overwrite_ok=True)
    sheet.write(1, 1, "test")  # write "test" to row 1, column 1
    rb.save(path)

def write02():
    """Write a column of data (read‑only after creation)"""
    f = xlwt.Workbook()               # create workbook
    sheet1 = f.add_sheet(u'sheet1', cell_overwrite_ok=True)  # create sheet
    l_ = range(5)
    x = [42, 4, 14, 14, 14]
    for i in l_:
        sheet1.write(0, i, x[i])   # first row, column i, write data
        sheet1.write(1, i, x[i])
    f.save("2.xlsx")  # save file

def write03():
    """Modify existing data"""
    rb = open_workbook(path)
    wb = copy(rb)
    s = wb.get_sheet(0)
    s.write(0, 1, "test1 data")
    wb.save("test1_data.xls")
    print("write finished")

def style1():
    """Set cell style and write timestamp"""
    font = xlwt.Font()
    font.name = 'Times New Roman'
    font.bold = True
    font.underline = True
    font.italic = True
    font.colour_index = 2
    style0 = xlwt.XFStyle()
    style0.font = font
    wb = xlwt.Workbook()
    ws = wb.add_sheet('A Test Sheet')
    style1 = xlwt.XFStyle()
    style1.num_format_str = 'D-M-Y'
    ws.write(0, 0, 'Test', style0)
    ws.write(2, 2, time.strftime("%Y-%m-%d %X", time.localtime()), style1)  # write current time
    wb.save('test2.xls')

if __name__ == "__main__":
    write()
    write02()
    write03()
    style1()

These examples cover writing single values, writing entire columns, modifying existing worksheets, and applying custom fonts and date formats, providing a practical foundation for Excel automation in Python‑based testing workflows.