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()