pythonexcel汇总_Python汇总excel到总表格

import xlrd

import openpyxl

from openpyxl import load_workbook

from openpyxl import Workbook

source_xls = [("data.xlsx")]

data = []

nw = Workbook()

for i in source_xls:

# wb = xlrd.open_workbook(i)

wb = load_workbook(i)

sheetnames = wb.get_sheet_names()

print(len(sheetnames))

for sh in sheetnames:

new = nw.create_sheet()

ws = wb[sh]

new.title = sh

max_rows = ws.max_row # 最大行数

max_column = ws.max_column # 最大列数

for m in range(1, max_rows + 1):

for n in range(97, 97 + max_column): # chr(97)='a'

n = chr(n) # ASCII字符

i = '%s%d' % (n, m) # 单元格编号

cell1 = ws[i].value # 获取data单元格数据

new[i].value = cell1 # 赋值到test单元格

nw.save("test3.xlsx")

# coding:utf-8

import openpyxl

x = 2

#打开文件

w1 = openpyxl.load_workbook('1.xlsx')

w2 = openpyxl.load_workbook('3.xlsx')

# 新建record的xlsx文件

w3 = openpyxl.Workbook()

sheet3 = w3.active

sheet3.title = 'record'

#获取sheet名称

a = w1.sheetnames

b = w2.sheetnames

#只有一页sheet，取第一页分析

sheet1 = w1.get_sheet_by_name(a[0])

sheet2 = w2.get_sheet_by_name(b[0])

#获取最大行数

max1 = sheet1.max_row

max2 = sheet2.max_row

#获取最大列数

m1 = sheet1.max_column

m2 = sheet2.max_column

#遍历，判断是否是待投资名单上的学校，若是则写入新表

for i in range(2,max2 + 1):

for j in range(2,max1 + 1):

if sheet1.cell(row=j,column=1).value == sheet2.cell(row=i,column=1).value:

for k in range(1,m1+1):

sheet3.cell(row=x,column=k).value = sheet1.cell(row=j,column=k).value

x += 1

#保存

w3.save('record.xlsx')