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列封缄号一列别忘了，就是封签号。
原始表：
目标表：
完成效果：
