python 实现excel数据的提取和整理

#!coding:utf-8
# Author:pymingming

import xlrd
import re
from xlrd import open_workbook
from xlutils.copy import copy
def read(file, sheet_index=0):
    workbook = xlrd.open_workbook(file)
    sheet = workbook.sheet_by_index(sheet_index)
    # print("工作表名称:", sheet.name, "行数:", sheet.nrows, "列数:", sheet.ncols)
    data = []
    for i in range(0, sheet.nrows):
        data.append(sheet.row_values(i))
    return data
def reg(data):
    regexp = r'MGG_d{5}'
    pat = re.compile(regexp)
    MGG_all = re.findall(pat, str(data))  # 需为string格式
    Mgg_unique = set(MGG_all)
    return Mgg_unique

rexcel = open_workbook("a.xls") # 用wlrd提供的方法读取一个excel文件
rows = rexcel.sheets()[0].nrows # 用wlrd提供的方法获得现在已有的行数
excel = copy(rexcel) # 用xlutils提供的copy方法将xlrd的对象转化为xlwt的对象
table = excel.get_sheet(0) # 用xlwt对象的方法获得要操作的sheet
values = reg(read(r'J:pymingming10.23zhu.xlsx'))
keys = reg(read(r'J:pymingming10.23zhu.xlsx'))
row = rows
for (value,key) in zip(values,keys):
    table.write(row, 0, value) # xlwt对象的写方法，参数分别是行、列、值
    table.write(row, 2, key) # xlwt对象的写方法，参数分别是行、列、值
    row += 1
excel.save("a.xls") # xlwt对象的保存方法，这时便覆盖掉了原来的excel