python读写excel遇到的几个坑 |
您所在的位置：网站首页 › 可读写文件自己变只读会是什么原因 › python读写excel遇到的几个坑 |
|
python读写excel遇到的几个坑 因为有一个文件需要处理，因此开始学习用python处理excel文件，以前一直用excel 的VBA，但学习了python之后，感觉python的功能强大，想用python来处理一下。 一、用什么库的坑 如果是只读excel ，推荐直接xlrd，速度快，兼容性也很高，非常好用 写：先用xlwt,速度也很快，但不兼容excel的xlsx,只能写xls,这倒方便了处理xlsx时，用xlrd，写用xlwt,互不干扰，遍历选择文件时比较好操作。 后来考虑到文件的统一性，改用了openpyxl ，可以读写xlsx，把整个程序改了一遍，很是难受，而且先用时，感觉速度极慢，3000多行的文件，用前两个库读写，几秒完事，用openpyxl ，处理了几分钟。。（不过不是库的错，是引用的问题，后面会提到） from openpyxl import Workbook # 新建时导入这个 from openpyxl import load_workbook # 读取时导入这个 二、怎么处理数据的坑 把excel的数据一个一个处理，当然是最保险，但几个库里都很行的处理方式，要找到最快的，以最后使用的openpyxl 为例 wb=load_workbook(path) ws=wb.active print (os.path.splitext(excelname)[0],'文件 列数： ',ws.max_column,'文件 行数： ',\ ws.max_row) cell_range=ws['a:d']直接就读出来A列到D列，比一行一行读还要快 顺便提一句，用xlrd读出来的数据格式比较特殊，尤其是时间类的，如9:30,直接读取是一个浮点数，处理数据用了好长时间，而openpyxl 读取出来的数据和原格式差不多少，如上例一般只需要 cell_range[列][行].value 便可获取，读取的空值是NONe; 读取的数据，最后我用数组表示，单个数组内为字典，字典内的key自定义，value为excel表内数据。 array = {'arr_time':'','arr_num':'','arr_hands':'','arr_pen':'','arr_count':''} array1 = {'arr_time':'','arr_num':'','arr_hands':'','arr_pen':'','arr_count':'','arr_rate':''} if (cell_range[0][rownum].value)=="时间": #返回是list生成器 pass else: data1=0.0 data2=0.0 data3=0.0 if (datetime.datetime.strptime(\ str(cell_range[0][rownum].value),"%H:%M:%S") maxnum: maxnum=data1 maxtotal=zonge else: if data1==maxnum: maxtotal+=zonge dat1.append(array1) dat.append(array)最好还要对数据排列，于是找到这个： # 多级排序,先按照成交，再按照zonge排序 dat=sorted(dat,key=lambda e:(e.__getitem__('arr_num'),\ e.__getitem__('arr_count')),reverse=True) dat1=sorted(dat1,key=lambda e:(e.__getitem__('arr_num'),\ e.__getitem__('arr_count')),reverse=True)同时，对排列后不需要的字典项，进行删除 for data in dat1: del data['arr_count'] #dat1为列表，表中为字典项data，在表格2中，不体现count，删除字典项当然最后是写入，这里也有快捷方式 打开表单sheet名称：处理数据2 if "处理数据2" in wb.sheetnames: ws3=wb["处理数据2"] wb.remove(ws3) #pass ws3 = wb.create_sheet("处理数据2") ws3=wb["处理数据2"]插入表头 ws3.append(['时间','成交','现手','笔数','总额/笔数'])插入内容和设置宽度 内容就采用了元组，元组的值只取用字典的值，这样就快多了，原来处理速度慢，就是这里出了问题。 ws3.append(tuple(data.values())) ws3.column_dimensions['E'].width = 12.0写入文件的打开与保存 excel_path='统计总表.xlsx' wb1=Workbook() wb1.save(excel_path)三、表单sheet排列的坑 因为写入sheet时，记得是可以排列sheet 的顺序的，找了半天，才想起来是VBA中的用法，在python中还未找到如果进入就让某sheet为激活状态，或设置sheet的index=0 如下的例子是插在最前面，如插在后面，就把0去除，即为默认。 wb2sheet = wb1.create_sheet('统计表1',0) # 创建一个工作表整个程序代码： #-*-coding:utf-8 -*- # Time:2020/02/20 10:59 # Author:W.J.Ymhld #import xlrd #读excel表 import os #读取文件路径用 import datetime #判断时间 #from xlrd import xldate_as_tuple import time #import xlwt #写excel表 from openpyxl import Workbook # 新建时导入这个 from openpyxl import load_workbook # 读取时导入这个 #import openpyxl print(" ") print(" _____ _____ _____ _____ ____ ") print(" ___|\ \ |\ \ / /||\ \ _____ | |") print("| |\ \ | \ \ / / || | | / /| | |") print("| | | || \____\/ / /\/ / | || | |") print("| | /_ _ / \ | / / / / /_ \ \/ ____ | |") print("| |\ \ \|___/ / / | // \ \ \ | | | |") print("| | | | / / / | |/ \ | || | | |") print("|____|/____/| /____/ / |\ ___/\ \| /||\____\|____|") print("| / || |` | / | | | \______/ || | | |") print("|____|_____|/ |_____|/ \|___|/\ | | | \|____|____|") print(" \( )/ )/ \( \|____|/ \( )/ ") print(" ' ' ' ' )/ ' ' ") print(" ' ") #读取excel文件 def createxcel(path): dat = []#创建空list 表中项为字典，array dat1=[] wb=load_workbook(path) ws=wb.active print (os.path.splitext(excelname)[0],'文件 列数： ',ws.max_column,'文件 行数： ',\ ws.max_row) cell_range=ws['a:d'] #整块读取，读出为cell_rang[列][行】 maxnum=0 maxtotal=0 totalzonge=0 for rownum in range(ws.max_row):#100):# #if (rownum+1)%100==0: #print ('处理第',rownum+1,'行.......') array = {'arr_time':'','arr_num':'','arr_hands':'','arr_pen':'','arr_count':''} array1 = {'arr_time':'','arr_num':'','arr_hands':'','arr_pen':'','arr_count':'','arr_rate':''} if (cell_range[0][rownum].value)=="时间": #返回是list生成器 pass else: data1=0.0 data2=0.0 data3=0.0 if (datetime.datetime.strptime(\ str(cell_range[0][rownum].value),"%H:%M:%S") maxnum: maxnum=data1 maxtotal=zonge else: if data1==maxnum: maxtotal+=zonge dat1.append(array1) dat.append(array) # 多级排序,先按照成交，再按照zonge排序 dat=sorted(dat,key=lambda e:(e.__getitem__('arr_num'),\ e.__getitem__('arr_count')),reverse=True) dat1=sorted(dat1,key=lambda e:(e.__getitem__('arr_num'),\ e.__getitem__('arr_count')),reverse=True) if "处理数据1" in wb.sheetnames: ws2=wb["处理数据1"] wb.remove(ws2) #pass ws2 = wb.create_sheet("处理数据1") ws2=wb["处理数据1"] #ws2.cell(row=0, column=0, value='时间') ws2.append(['时间','成交','现手','笔数','总额']) for data in dat: ws2.append(tuple(data.values())) ws2.column_dimensions['E'].width = 12.0 print('生成表单：处理数据1......') if "处理数据2" in wb.sheetnames: ws3=wb["处理数据2"] wb.remove(ws3) #pass ws3 = wb.create_sheet("处理数据2") ws3=wb["处理数据2"] #ws2.cell(row=0, column=0, value='时间') ws3.append(['时间','成交','现手','笔数','总额/笔数']) for data in dat1: del data['arr_count'] #dat1为列表，表中为字典项data，在表格2中，不体现count，删除字典项 ws3.append(tuple(data.values())) ws3.column_dimensions['E'].width = 12.0 print('生成表单：处理数据2......') wb.save(path) wb.close() #print(totalzonge,maxtotal,maxnum) if len(dat)==0: return 0,0 else: return maxtotal/totalzonge,dat[0]['arr_count'] excellist=[] path=os.getcwd() for file in os.listdir("."): if os.path.splitext(file)[1]=='.xlsx': excellist.append(file) #print (excellist) excel_path='统计总表.xlsx' wb1=Workbook() # 创建一个工作簿 # # wb2sheet = wb1.create_sheet('统计表1',0) # 创建一个工作表 wb2sheet.append(['文件名','最大总额']) wb1sheet = wb1.create_sheet('统计表',0) # 创建一个工作表 wb1sheet.append(['文件名','占总额比%']) sheetrow=2 for excelname in excellist: print('正处理文件........ ',excelname) #print(os.path.splitext(excelname)[0]) if os.path.splitext(excelname)[0][0]=='~' or os.path.splitext(excelname)[0][:4]=='统计总表': print ("临时文件或统计表......",excelname) pass else: wb1sheet.cell(row=sheetrow,column=1).value=os.path.splitext(excelname)[0] maxcount,maxcount1=createxcel(excelname) wb1sheet.cell(row=sheetrow,column=2).value=maxcount*100 print('返回数据： ',maxcount*100,'% ',maxcount1,' \n') #处理数据 wb2sheet.cell(row=sheetrow,column=1).value=os.path.splitext(excelname)[0] wb2sheet.cell(row=sheetrow,column=2).value=maxcount1 sheetrow+=1 wb1sheet.column_dimensions['B'].width = 15.0 wb2sheet.column_dimensions['B'].width = 15.0 # 每天下载多张交易数据表格，用于个人分析，现寻求将多张独立的EXCEL文件批量修改，并生成一份新的EXCEL表格。要求如下 # 1、删除9:30之前的交易数据。 # 2、每份EXCEL文件中自动生成一列，命名为“总额”，值等于“成交”乘以“现手”乘以100。且生成后，将行自动排序，条件一“成交”按从大到小排序，条件二“总额“按从大到小排序。每份EXCEL文件自动保存。 # 3、自动生成一份新的表格，第一列为每份EXCEL文件的文件名，第二列值为对应EXCEL文件中 最大“成交”对应的“总额”相加累计值除以该表中所有“总额”累计值。 # 以上为第一份代码实现的功能。第二份代码要实现的功能和第一份相似，只是“要求2”和“要求3”做了一点调整。具体如下： # 1、删除9:30之前的交易数据。 # 2、每份EXCEL文件中自动生成一列，命名为“总额”，值等于“成交”乘以“现手”乘以100除以笔数。且生成后，将行自动排序，条件一“成交”按从大到小排序，条件二“总额“按从大到小排序。每份EXCEL文件自动保存。 # 3、自动生成一份新的表格，第一列为每份EXCEL文件的文件名，第二列值为对应EXCEL文件中 最大“成交”对应的“总额”中最大值。 wb1.save(excel_path) print('已经生成统计总表.xlsx........ ') input('按任意键退出.....') # |
| CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |