python 处理excel实例,这是实际工作中需要每天导出一个excel表格数据给相关管理人员,里面有涉及数据库连接,excel处理等技术点,用法仅供参考.
[root@locahost python]# cat demo.py
#!/usr/bin/env python
#coding=utf8
from pyExcelerator import *
import MySQLdbimport time
############################### set day time #设置日期
today=time.strftime('%Y%m%d',time.localtime(time.time()))
yestoday=time.strftime('%Y%m%d',time.localtime(time.time()-24*60*60))
############################### set excel #设置字体
fnt1 = Font()fnt1.height = 15*0x10
#这里设置边框
borders = Borders()
borders.left = 1
borders.right = 1
borders.top = 1
borders.bottom = 1
#这里设置对齐方式
al = Alignment()
al.horz = Alignment.HORZ_CENTER
al.vert = Alignment.VERT_CENTER
#al.wrap = Alignment.WRAP_AT_RIGHT
#整合附加样式
style = XFStyle()
style.borders = borders
style.alignment = al
style.font = fnt1
#初始化一个excel文件
w = Workbook()
ws = w.add_sheet(u'工作薄名称')
ws.write(0,0,u'序号',style)
ws.write(0,1,u'用户标识',style)
ws.write(0,2,u'昵称',style)
ws.write(0,3,u'手机号码',style)
ws.write(0,4,u'资金账号',style)
ws.write(0,5,u'报名日期',style)
################## database configure
DB_IP='192.168.1.24'
DB_NAME='mydatabase'
DB_USER='root'
PASSWORD='admin'
TB='your_table_name'
conn = MySQLdb.connect(host=DB_IP, user=DB_USER,passwd=PASSWORD,db=DB_NAME,charset='utf8')
cursor = conn.cursor()
################### common function define
def get_table_all_id():
#获取表所有id
R = cursor.execute(select id from %s order by register_date desc%(TB))
return cursor.fetchall()
def get_yestody_add_user_id():
#获取昨天新增用户的id
cursor.execute(select count(*) from fundaccount_register_log where register_date=%s%(yestoday))
for s in cursor.fetchone():
if(s>0):
cursor.execute(select id from fundaccount_register_log where register_date=%s%(yestoday)) return cursor.fetchall()
else:
return
def get_all_user():
#获取总参赛人数
n = cursor.execute(select count(*) from %s%(TB))
for r in cursor.fetchone():
return r
def get_user_detail(id):
#根据用户id获取记录详细信息
n = cursor.execute(select user_id,nick,mphone,fund_account,register_date from fundaccount_register_log where id=%s%(id))
for d in cursor.fetchall():
return d
def close_connect():
#关闭指针连接和数据库连接
cursor.close()
conn.close()
#################### process 部分
def do_every_day_work():
IDS=get_yestody_add_user_id()
j=1
for ids in IDS:
for id in ids:
d=get_user_detail(id)
ws.write(j,0,id,style)
ws.write(j,1,d[0],style)
ws.write(j,2,d[1],style)
ws.write(j,3,d[2],style)
ws.write(j,4,d[3],style)
ws.write(j,5,d[4],style)
j+=1
w.save(u'my.xls')
close_connect()
do_every_day_work()