python2 对excel表格操作完整示例

本文实例讲述了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)

运行后将数据写入如下xls文件：

希望本文所述对大家Python程序设计有所帮助。

来源：https://blog.csdn.net/Dawn__Z/article/details/85013961