python 对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/260207/Desktop/airfour/Data_analysis/'
    bomcode='21122'
    passflag =0
    data_analysis(bomcode)
#    add_excel(bomcode,passflag,row)