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 # 最大行數(shù)
max_column = ws.max_column # 最大列數(shù)
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單元格數(shù)據(jù)
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])
#獲取最大行數(shù)
max1 = sheet1.max_row
max2 = sheet2.max_row
#獲取最大列數(shù)
m1 = sheet1.max_column
m2 = sheet2.max_column
#遍歷其屏，判斷是否是待投資名單上的學(xué)校，若是則寫入新表
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')
