Using Python to Read and Write Excel Files with xlrd and xlwt

In many work and study scenarios, handling Excel files manually can be error‑prone; this guide shows how to use Python to automate Excel operations.

Tools : Python 3.7, PyCharm, Excel, and the xlrd & xlwt libraries.

Reading Excel files : The example opens a file named "股票数据.xlsx" using xlrd.open_workbook , retrieves the first sheet, iterates over rows and columns, converts date cells with xlrd.xldate_as_tuple , formats numbers to two decimal places, and prints cell values. It also demonstrates obtaining the last cell type, the first row values, and slicing a row range.

<code>import xlrd
# 使用xlrd模块的open_workbook函数打开指定Excel文件并获得Book对象（工作簿）
wb = xlrd.open_workbook('股票数据.xlsx')
sheetname = wb.sheet_names()[0]
sheet = wb.sheet_by_name(sheetname)
print(sheet.nrows, sheet.ncols)
for row in range(sheet.nrows):
    for col in range(sheet.ncols):
        value = sheet.cell(row, col).value
        if row > 0:
            if col == 0:
                value = xlrd.xldate_as_tuple(value, 0)
                value = f'{value[0]}年{value[1]:02d}月{value[2]:02d}日'
            else:
                value = f'{value:.2f}'
        print(value, end='\t')
    print()
last_cell_type = sheet.cell_type(sheet.nrows - 1, sheet.ncols - 1)
print(last_cell_type)
print(sheet.row_values(0))
print(sheet.row_slice(3, 0, 5))
</code>

Writing Excel files : Using xlwt.Workbook to create a workbook, adding a sheet, writing a header row, and populating student names and random scores into cells. The workbook is saved as "考试成绩表.xlsx".

<code>import random
import xlwt
student_names = ['关羽', '张飞', '赵云', '马超', '黄忠']
scores = [[random.randint(40, 100) for _ in range(3) ] for _ in range(5)]
wb = xlwt.Workbook()
sheet = wb.add_sheet('一年级二班')
titles = ('姓名', '语文', '数学', '英语')
for index, title in enumerate(titles):
    sheet.write(0, index, title)
for row in range(len(scores)):
    sheet.write(row + 1, 0, student_names[row])
    for col in range(len(scores[row])):
        sheet.write(row + 1, col + 1, scores[row][col])
wb.save('考试成绩表.xlsx')
</code>

Excel style adjustments : Demonstrates using xlwt.XFStyle , Pattern , and Alignment to set header background color, font alignment, row height, and column width.

<code>header_style = xlwt.XFStyle()
pattern = xlwt.Pattern()
pattern.pattern = xlwt.Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 5
header_style.pattern = pattern
align = xlwt.Alignment()
align.vert = xlwt.Alignment.VERT_CENTER
align.horz = xlwt.Alignment.HORZ_CENTER
header_style.alignment = align
sheet.row(0).set_style(xlwt.easyxf('font:height 20*40'))
sheet.col(index).width = 20 * 200
sheet.write(0, index, title, header_style)
</code>

Other libraries such as openpyxl and pandas can also be used for more advanced Excel manipulation and data analysis.

By mastering these Python techniques, users can automate many routine Excel tasks, like merging multiple workbooks or extracting specific data, improving efficiency in daily office work.