python操作excel实用脚本

 1 import xlrd
 2 
 3 data = xlrd.open_workbook('/home/ppe/workspace/pythonwp/tianranqi_org.xls')
 4 
 5 table = data.sheets()[0]
 6 nrows = table.nrows #行数
 7 ncols = table.ncols #列数
 8 
 9 print('工作薄 行数: {} 列数: {}'.format(table.nrows,table.ncols))
10 
11 colnames =  table.row_values(0) #某一行数据 
12 dict_t = {}
13 for rownum in range(1,nrows):
14     if rownum <= 3:
15         continue
16     
17     row = table.row_values(rownum)
18     if row:
19      # print('{}, {}'.format(rownum,row))
20 
21         if row[7] == '√':
22          jinqifangxiang = '01'
23         else:
24          jinqifangxiang = '02'
25 #     print('--> 序号:{}, 房间号:{}, 表号:{}, 进气口:{}, 卡缄口:{}'.format(row[0],row[5], row[6], jinqifangxiang, row[13]))
26     newrow = {}
27 
28     newrow[0] = int(row[5]) # 户号
29     newrow[1] = row[6] # 表号
30     newrow[2] = jinqifangxiang # 进气口
31     newrow[3] = int(row[13]) # 卡缄口
32 #     print(newrow)
33     
34     dict_t[newrow[0]] = newrow
35     
36 print(dict_t)

打印： {507: {0: 507, 1: '0201623502', 2: '01', 3: 18886663}, 506: {0: 506, 1: '0201623671', 2: '01', 3: 18886571}}

 1 import xlrd
 2 import re
 3 from xlwt import *
 4 from xlutils.copy import copy
 5 
 6 xlsfile = '/home/ppe/workspace/pythonwp/meter'
 7 # 读取要写入文件
 8 xls_dst = xlrd.open_workbook(xlsfile + '.xls')
 9 table = xls_dst.sheets()[0]
10 
11 print('工作薄 行数: {} 列数: {}'.format(table.nrows,table.ncols))
12 
13 for i in range(1,table.nrows):
14     row = table.row_values(i)
15     
16     if row:
17 #         print('{}, '.format(i), row)
18         
19         # 使用正则表达式，求出户号
20         house_num = row[1]
21         p = re.compile('河南省郑州市高新区迎春街与雪松路交叉口西北角朗悦V公馆一期7栋A单元(.*)层(.*)室')
22         no = p.findall(house_num)
23 #         print(no[0])
24 
25         hnum = '{}{}'.format(no[0][0],no[0][1])
26 #         print(hnum)
27 #         print(dict_t[int(hnum)])
28 #         print(dict_t[int(hnum)][1])
29         
30         # 写入指定单元格
31         row_index = i
32         col_index = 2
33         ctype = 1 #  0 empty,1 string, 2 number, 3 date, 4 boolean, 5 error
34         value = dict_t[int(hnum)][1]
35         xf = 0 # 格式化扩展
36         ret = table.put_cell(row_index, col_index, ctype, value, xf)
37         
38         # 写入指定单元格
39         row_index = i
40         col_index = 3
41         ctype = 1 #  0 empty,1 string, 2 number, 3 date, 4 boolean, 5 error
42         value = dict_t[int(hnum)][1]
43         xf = 0 # 格式化扩展
44         ret = table.put_cell(row_index, col_index, ctype, value, xf)
45         
46         # 写入指定单元格
47         row_index = i
48         col_index = 7
49         ctype = 1 #  0 empty,1 string, 2 number, 3 date, 4 boolean, 5 error
50         value = dict_t[int(hnum)][2]
51         xf = 0 # 格式化扩展
52         ret = table.put_cell(row_index, col_index, ctype, value, xf)
53         
54         
55         # 写入指定单元格
56         row_index = i
57         col_index = 27
58         ctype = 1 #  0 empty,1 string, 2 number, 3 date, 4 boolean, 5 error
59         value = str(dict_t[int(hnum)][3])
60         xf = 0 # 格式化扩展
61         ret = table.put_cell(row_index, col_index, ctype, value, xf)
62         
63 
64         
65         
66 #保存xlsfile
67 wb = copy(xls_dst)
68 wb.save(xlsfile + '_new.xls')

需求和截图如下：

填写C/D/E/H/AB五列，C列和D列一样，都是表号；E列如实填写气表底数；H列进气方向：01是左进，02是右进，系统默认的是01，请把右进的修改成02; 最右边的AB列封缄号一列别忘了，就是封签号。

原始表：

目标表：

完成效果：