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')
