Automating Excel Data Processing with Python (xlrd & xlwt)

A supervisor provided three Excel files, each containing four sheets with over 250 columns per sheet, resulting in more than 3,000 columns that needed to be manually inserted, deleted, copied, and summed within four days—a repetitive and time‑consuming task.

To avoid the manual effort, the author wrote a Python script of about 30 lines using the xlrd library to read workbooks and the xlwt library to create new workbooks, automating sheet handling, row/column extraction, value retrieval, and aggregation of specific column groups.

The script demonstrates opening a workbook, listing sheet names, accessing sheets by index or name, retrieving row and column counts, reading cell values, creating a new workbook, adding sheets, writing values, and saving the result. It also includes loops that slice rows, sum groups of six columns, and write the computed sums back to the new Excel file.

# coding=utf-8
import xlrd
import xlwt

workbook = xlrd.open_workbook('2014年排放量.xlsx')
sheet_names = workbook.sheet_names()

#通过索引顺序获取一个工作表
sheet0 = workbook.sheets()[0]
sheet1 = workbook.sheet_by_index(1)
sheet2 = workbook.sheet_by_index(2)
sheet3 = workbook.sheet_by_index(3)
sheet4 = workbook.sheet_by_index(4)
sheet5 = workbook.sheet_by_index(5)
sheet6 = workbook.sheet_by_index(6)
sheet7 = workbook.sheet_by_index(7)
sheet8 = workbook.sheet_by_index(8)
#通过名称获取一个工作表
#table = data.sheet_by_name(u'Sheet1')

#获取多少行、列
sht0_rows = sheet0.nrows
sht0_cols = sheet0.ncols

#获取指定单元格的值
temp = sheet0.cell(0,2)

#获取指定行、列的值
temp_col0 = sheet0.col_values(0)
temp_row0 = sheet0.row_values(0)
temp_col1 = sheet0.col_values(1)
temp_row1 = sheet0.row_values(1)

#需要204个单元格 一共tb0_rows个 减去前两个

'''for i in range(204/6):#循环34次
 for j in temp_row:
 j[2:8]'''

#wk = xlwt.Workbook(encoding='utf-8')
#wk_lst = []#创建sheet列表
#for shtname in sheet_names:
 #print(shtname)
 wk_lst.append(wk.add_sheet(shtname,cell_overwrite_ok=True))
#print(len(wk_lst))

'''测试插入表格
for c in range(len(temp_col0)):
 wk_lst[0].write(c,0,temp_col0[c])
 #print(temp_col[c])'''

#需要读的sheet列表
xlrd_sheet_list = [sheet0,sheet1,sheet2,sheet3]

for tm in range(len(wk_lst)):
 if tm<4:
 '''1. 创建样表'''
 for x in range(2):
 #print('----------x:',x)
 temp_col = sheet0.col_values(x)
 for c in range(len(temp_col)):
 #print('--------c:',c)
 #print(temp_col[c])
 wk_lst[tm].write(c,x,temp_col[c])
 temp_row = sheet0.row_values(0)
 for r in range(len(temp_row)-2):
 #print(tm,len(temp_row))
 wk_lst[tm].write(0,r+2,temp_row[r+2])

 '''2. 写入工作区域'''
 for r in range(2,13):#创建工作行
 tmp_row = xlrd_sheet_list[tm].row_values(r)
 w = tmp_row[2:206] # 切片获取该行工作列
 #print(len(w) / 6)
 x = 0
 for i in range(int(len(w) / 6)):
 sum_pf = round(w[0 + x] + w[1 + x] + w[2 + x] + w[3 + x] + w[4 + x] + w[5 + x], 2)
 wk_lst[tm].write(r,2+x,sum_pf)
 print(sum_pf)
 x += 6
 print("----------------------------------------r:",r,2+x,sum_pf)

wk.save('nb.xls')

'''#测试创建excel文件
wkt = xlwt.Workbook()
ws = wkt.add_sheet('CO')
ws.write(0,0,'1')
wkt.save('fuck.xls')'''

'''#临时注释 一会儿放开
for r in range(2,13):#创建工作行
 tmp_row = sheet0.row_values(r)
 w = tmp_row[2:206] # 切片获取该行工作列
 #print(len(w) / 6)
 x = 0
 y = 0
 for i in range(int(len(w) / 6)):
 #wk_lst[0].write(2+)
 print(round(w[0 + x] + w[1 + x] + w[2 + x] + w[3 + x] + w[4 + x] + w[5 + x], 2))
 x += 6
 print("----------------------------------------",r)
'''