python2 对excel表格操作完整示例

 #!/usr/bin/env python2 # -*- coding: utf-8 -*- """ Created on Sat Dec 2 15:40:35 2017 @author: 260207 """ from xlutils.copy import copy import xlrd import xlwt def set_style(name = 'Times New Roman',height = 6,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     #定义字体属性 return style def write_excel(bomcode ): #创建标准件模板 excel = xlwt.Workbook() #创建工作簿 import datetime dateTime=datetime.datetime.now().strftime('%Y-%m-%d') sheet1 = excel.add_sheet(u'标贴防错系统',cell_overwrite_ok=True) #生成表头，即第一列 sheet1.write(0,0,u'订单编码',set_style('Times New Roman',180)) sheet1.write(0,1,u'当前时间',set_style('Times New Roman',180)) sheet1.write(0,2,u'检测总量', set_style('Times New Roman', 180)) sheet1.write(0,3,u'出错数量', set_style('Times New Roman', 180)) sheet1.write(0, 4, u'正确数量', set_style('Times New Roman', 180)) sheet1.write(0,5,u'合格率', set_style('Times New Roman', 180)) excel.save(file_dir+str(dateTime)+'.xls') #保存文件 # def seefile(file_dir): import os L=[] for root, dirs, files in os.walk(file_dir): for file in files: if os.path.splitext(file)[1] == '.xls': L.append(os.path.join(root, file)) return L def add_excel(passflag,row,error,true): # 添加内容 import datetime dateTime = datetime.datetime.now().strftime('%Y-%m-%d') # 查看时间 nowTime = datetime.datetime.now().strftime('%H:%M:%S') workbook = xlrd.open_workbook(file_dir+str(dateTime)+'.xls','r+') new_excel = copy(workbook) ws = new_excel.get_sheet(0) # 索引到表格 ws.write(row, 0, str(bomcode), set_style('Times New Roman', 180)) ws.write(row, 1, str(nowTime), set_style('Times New Roman', 180)) if passflag == 1: ws.write(row, 3, error+1, set_style('Times New Roman', 180)) ws.write(row, 4, true, set_style('Times New Roman', 180)) else: ws.write(row, 4, true+1, set_style('Times New Roman', 180)) ws.write(row, 3, error, set_style('Times New Roman', 180)) ws.write(row, 2, error+true+1, set_style('Times New Roman', 180)) new_excel.save(file_dir+str(dateTime)+'.xls') def pass_rate(row): import datetime dateTime = datetime.datetime.now().strftime('%Y-%m-%d') workbook112 = xlrd.open_workbook(file_dir+str(dateTime)+'.xls','r+') all_excel = copy(workbook112) ws1 = all_excel.get_sheet(0) workbook_position = workbook112.sheet_by_index(0) all_value=workbook_position.cell(row,2).value true_value=workbook_position.cell(row,4).value ws1.write(row, 5, round(float(true_value)/(all_value),2), set_style('Times New Roman', 180)) all_excel.save(file_dir+str(dateTime)+'.xls') def data_analysis(bomcode): import datetime dateTime = datetime.datetime.now().strftime('%Y-%m-%d') filename = seefile(file_dir) filename = list(reversed(filename)) #增加异常，文件名需按最新时间排列 if (filename ==[]) or (filename is None) or (dateTime!=(filename[0])[-14:-4]) : write_excel(bomcode) workbook = xlrd.open_workbook(file_dir+str(dateTime)+'.xls','r+') workbook_position = workbook.sheet_by_index(0) cols_0 = workbook_position.col_values(0) if bomcode in cols_0: row_error = cols_0.index(bomcode) error=workbook_position.cell(row_error,3).value true=workbook_position.cell(row_error,4).value row = row_error else : error = 0 true = 0 row = len(cols_0) add_excel(passflag,row,error,true) pass_rate(row) if __name__ == '__main__': file_dir ='C:/Users/Administrator/Desktop/Data_analysis/' bomcode='21122' passflag =0 data_analysis(bomcode) #  add_excel(bomcode,passflag,row)