python处理excel

来源：互联网发布：淘宝卖大米的营销策略编辑：程序博客网时间：2024/04/30 13:40

辅导员要统计学生打分用来评优，数据有点多...就用Python写个脚本处理

主要用到两个库是xlrd和xlwt 使用方法可以去查文档

# -*- coding: UTF-8 -*-import xlrdimport xlwt#创建待写入exceloWorkBook = xlwt.Workbook(encoding = 'ascii')#创建待写入表格oWorkSheet = oWorkBook.add_sheet('first')#打开原excel，对象赋给oData，待处理文件命名为source.xlsoData = xlrd.open_workbook('source.xls')#获取表格1tTable = oData.sheets()[0]#获取行，列数iRow = tTable.nrowsiCol = tTable.ncols#迭代求和for eachRowItem in range(1, iRow):dSum = 0for eachColItem in range(2, iCol):dSum += tTable.cell(eachRowItem, eachColItem).value#写入表格oWorkSheet.write(eachRowItem, iCol, dSum)#添加总分栏oWorkSheet.write(0, 12, 'sum')#填充原数据for eachRowItem in range(0, iRow):for eachColItem in range(0, iCol):oWorkSheet.write(eachRowItem, eachColItem, tTable.cell(eachRowItem, eachColItem).value)#保存表格oWorkBook.save('result.xls')

# -*- coding: UTF-8 -*-import xlrdimport xlwtimport os#创建待写入exceloWorkBook = xlwt.Workbook(encoding = 'ascii')#创建待写入表格oWorkSheet = oWorkBook.add_sheet('first')#创建路径dirPath = os.getcwd() + r'\collect'#获取文件个数，初始化列表for parentname, dirname, fileName in os.walk(dirPath):iLen = len(fileName)aSum = list()for i in range(iLen):aSum.append(0)#迭代打开文件，求和for parentname, dirname, fileName in os.walk(dirPath):for eachFile in fileName:dirn = dirPath + '\\' + eachFile oData = xlrd.open_workbook(dirn)tTable = oData.sheets()[0]iRow = tTable.nrowsfor eachRow in range(1, iRow):aSum[eachRow-1] += tTable.cell(eachRow, 12).value#求平均 写入表格for eachItem in range(len(aSum)):aSum[eachItem] = aSum[eachItem] * 1.0 / iLenoWorkSheet.write(eachItem, 0, aSum[eachItem])#保存表格oWorkBook.save('resultFinal.xls')

0 0