使用python读取excel进行处理,并将结果存储在新的excel
生活所需,领导安排整理出一些文件,整了一个小时才几个快累趴了,手也快抽筋了。网上都说python可以很简单的处理办公文件,想让python大神帮个忙,谁知大神说,java也可以写,开发语言都可以写。
“自己动手,丰衣足食” ---------毛爷爷
可以python代码写出来确实只有短短几行,依赖环境也比较简单
python读取excel
def query_excels(path):
filenames = os.listdir(path)
result = []
list=['城市','人数','平均分数']
result.append(list)
for filename in filenames:
if filename.find('(')>0:
# 各个文件名称为:2021年考试成绩(北京)20220120.xlsx
l=read_excel(filename[filename.find('(')+1:filename.find(')')],path+'\\'+filename)
result.append(l)
write_data_to_excel(path+'\\'+'合计' , result)
python处理excel
def read_excel(city,filePath):
workbook = xlrd.open_workbook(filePath)
sheet = workbook.sheet_by_index(0)
totalValue=0
for rown in range(1,sheet.nrows):
value=0
for i in range(1,38):
value+=int(sheet.cell_value(rown,i))
totalValue+=value
# 将150分成绩转化为百分制
avgvalue=(totalValue/(sheet.nrows-1))*100/150
return [city,sheet.nrows-1,avgvalue]
python结果写入新excel
def write_data_to_excel(name, result):
wbk = xlwt.Workbook(encoding='utf-8')
sheet = wbk.add_sheet('Sheet1', cell_overwrite_ok=True)
for i in range(0,len(result)):
for j in range(0,len(result[i])):
sheet.write(i, j, result[i][j])
wbk.save(name + '.xls')
完整可参考PYTHON代码
import os
import xlwt
import xlrd
def query_excels(path):
filenames = os.listdir(path)
result = []
list=['城市','人数','平均分数']
result.append(list)
for filename in filenames:
if filename.find('(')>0:
# 各个文件名称为:2021年考试成绩(北京)20220120.xlsx
l=read_excel(filename[filename.find('(')+1:filename.find(')')],path+'\\'+filename)
result.append(l)
write_data_to_excel(path+'\\'+'合计' , result)
def read_excel(city,filePath):
workbook = xlrd.open_workbook(filePath)
sheet = workbook.sheet_by_index(0)
totalValue=0
for rown in range(1,sheet.nrows):
value=0
for i in range(1,38):
value+=int(sheet.cell_value(rown,i))
totalValue+=value
# 将150分成绩转化为百分制
avgvalue=(totalValue/(sheet.nrows-1))*100/150
return [city,sheet.nrows-1,avgvalue]
def write_data_to_excel(name, result):
wbk = xlwt.Workbook(encoding='utf-8')
sheet = wbk.add_sheet('Sheet1', cell_overwrite_ok=True)
for i in range(0,len(result)):
for j in range(0,len(result[i])):
sheet.write(i, j, result[i][j])
wbk.save(name + '.xls')
if __name__ == '__main__':
path = r'D:\\考试成绩\\2021 年度\\20220124结果'
query_excels(path)
生活不是一场赛跑,生活是一场旅行,要好好欣赏每一段的风景。