python做excel表格数据抓取_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:\pymingming\10.23\zhu.xlsx'))

keys = reg(read(r'J:\pymingming\10.23\zhu.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