# codin=utf-8
import os
import xlrd
import xlwt
import time # 引入time模块
print(os.getcwd())
os.chdir(r'D:\\宜昌\\2021-01(1)')
path_execl_file = os.getcwd()
col_key=[]
col_item=[u'file',u'sheet',u'卡号',u'姓名',u'性别',u'身份证号']
item = {}
file = 'x.'
num = 0
def set_style(name, height, bold=False):
style = xlwt.XFStyle() # 初始化样式
font = xlwt.Font() # 为样式创建字体
font.name = name # 'Times New Roman'
font.bold = bold
font.color_index = 4
font.height = height
style.font = font
# style.borders = borders
return style
# 写excel
# def write_excel():
# f = xlwt.Workbook() # 创建工作簿
#
# '''
# 创建第一个sheet:
# sheet1
# '''
# sheet1 = f.add_sheet(u'sheet1', cell_overwrite_ok=True) # 创建sheet
# row0 = [u'业务', u'状态', u'北京', u'上海', u'广州', u'深圳', u'状态小计', u'合计']
# column0 = [u'机票', u'船票', u'火车票', u'汽车票', u'其它']
# status = [u'预订', u'出票', u'退票', u'业务小计']
#
# # 生成第一行
# for i in range(0, len(col_item)):
# sheet1.write(0, i, row0[i], set_style('Times New Roman', 220, True))
#
# # 生成第一列和最后一列(合并4行)
# i, j = 1, 0
# while i < 4 * len(column0) and j < len(column0):
# sheet1.write_merge(i, i + 3, 0, 0, column0[j], set_style('Arial', 220, True)) # 第一列
# sheet1.write_merge(i, i + 3, 7, 7) # 最后一列"合计"
# i += 4
# j += 1
#
# sheet1.write_merge(21, 21, 0, 1, u'合计', set_style('Times New Roman', 220, True))
#
# # 生成第二列
# i = 0
# while i < 4 * len(column0):
# for j in range(0, len(status)):
# sheet1.write(j + i + 1, 1, status[j])
# i += 4
#
# f.save('demo1.xlsx') # 保存文件
# 创建 excel
ff = xlwt.Workbook()
sheet1 = ff.add_sheet(u'sheet1', cell_overwrite_ok=True)
# 生成第一行
for i in range(0, len(col_item)):
sheet1.write(0, i, col_item[i], set_style('Times New Roman', 220, True))
ii = 0
print("读指定路径下execel内容并写指定文件:")
dir_list = os.listdir()
print("-----------------------------------")
for f in dir_list:
# print(f)
data = xlrd.open_workbook(path_execl_file + '\\' + f)
# print(type(data.sheet_names()))
for sheet in data.sheet_names():
# 清空 列名
item.clear()
# 读 sheet
table = data.sheet_by_name(sheet)
# 如果sheet中有内容才读
if table.nrows >= 1 and table.ncols >= 1 :
# 统计 sheet 总行数 总列数
# line += int(table.nrows)
num += table.nrows
print(f + ':' + sheet+':'+"总行数:" + str(table.nrows) + "总列数:" + str(table.ncols))
# cel_B3 = table.cell(3, 2).value
# print(table.cell(0, 0).value)
# 遍历第0行列名 得到sheet中列名与列号 并生成字典
for i in range(table.ncols):
# print(table.cell(0, i).value)
for key in col_item:
if key == table.cell(0, i).value:
item[i] = key
# print(item)
for y in range(table.nrows):
# 定义行数 ,0 行是字段名 ,所以从1行开始定数
ii += 1
if y == 0 :
# 0 行是字段名 上面已写过
continue
else:
# 写列0 文件名 列1为 sheet名
sheet1.write(ii, 0, f)
sheet1.write(ii, 1, sheet)
# 遍历 每个个文件的 字段名的字典item.key 列号 value 字段名
for key,value in item.items():
# line += str(table.cell(y, key).value)
# 遍历 字段名
for i in col_item:
# 如果 字段名i = item.value ,在行号 ii ,列号 col_item.index(i) 中 ,写入内容str(table.cell(y, key).value)
if value == i :
if str(table.cell(y, key).value) == i :
continue
else:
sheet1.write(ii, col_item.index(i), str(table.cell(y, key).value))
# print(str(table.cell(y, key).value))
# sheet1.write(y, col_item.index(i), str(table.cell(y, key).value), set_style('Times New Roman', 220, True))
# 因为最大行数限制,超过60000 ,另外生成一个文件
if ii > 60000:
ff.save(str(time.time()) + '.xls')
ii = 0
print(num)
ff.save('1.xls')
D:\python37\python.exe D:/test/excel_rw.py
D:\test
读指定路径下execel内容并写指定文件:
-----------------------------------
优待卡导入.2019.03.xls:Sheet1:总行数:107总列数:2
宜昌学生卡导入.2018.12.xls:导入:总行数:2452总列数:9
宜昌学生卡导入.2018.12.xls:Sheet2:总行数:2452总列数:14
宜昌学生卡导入.2019.03.xls:导入:总行数:1870总列数:9
宜昌学生卡导入.2019.05.xls:导入:总行数:1525总列数:9
宜昌学生卡导入.2019.05.xls:Sheet2:总行数:1525总列数:14
宜昌学生卡导入.2019.10.xls:导入:总行数:1总列数:9
宜昌学生卡导入.2019.10.xls:Sheet2:总行数:4985总列数:15
宜昌学生卡导入.2020.04.xls:导入:总行数:1538总列数:9
宜昌老年卡导入.2018.12.xls:导入:总行数:4869总列数:7
宜昌老年卡导入.2018.12.xls:Sheet1:总行数:4869总列数:15
宜昌老年卡导入.2019.03.xls:导入:总行数:3300总列数:7
宜昌老年卡导入.2019.05.xls:导入:总行数:3959总列数:7
宜昌老年卡导入.2019.05.xls:Sheet1:总行数:3959总列数:12
宜昌老年卡导入.2019.10.xls:导入:总行数:1总列数:7
宜昌老年卡导入.2019.10.xls:Sheet1:总行数:4630总列数:15
宜昌老年卡导入.2020.04.xls:导入:总行数:6227总列数:7
宜都老年卡导入.2018.12.xls:导入:总行数:554总列数:5
宜都老年卡导入.2018.12.xls:Sheet1:总行数:555总列数:7
宜都老年卡导入.2019.03.xls:导入:总行数:230总列数:5
宜都老年卡导入.2019.05.xls:导入:总行数:831总列数:5
宜都老年卡导入.2019.05.xls:Sheet1:总行数:831总列数:7
宜都老年卡导入.2019.10.xls:导入:总行数:1总列数:5
宜都老年卡导入.2019.10.xls:Sheet1:总行数:496总列数:7
秭归老年卡导入.2018.12.xls:导入:总行数:408总列数:5
秭归老年卡导入.2018.12.xls:Sheet1:总行数:408总列数:8
秭归老年卡导入.2019.03.xls:导入:总行数:291总列数:5
秭归老年卡导入.2019.05.xls:导入:总行数:799总列数:5
秭归老年卡导入.2019.05.xls:Sheet1:总行数:799总列数:8
秭归老年卡导入.2019.10.xls:导入:总行数:1总列数:5
秭归老年卡导入.2019.10.xls:Sheet1:总行数:692总列数:8
55165