Python xlsxwriter操作excel一例

43213

# encoding: utf-8
#!/usr/bin/python
#author: zhangdonghong
#email: zhangdonghongemail@163.com
#date: 2014-12-06

import MySQLdb
import datetime,time
import xlsxwriter

def getInfo():
   #now = datetime.datetime.now().strftime('%Y-%m-%d')
   Monday = datetime.datetime.fromtimestamp(time.time()-7*24*3600).strftime("%Y-%m-%d")
   Tuesday = datetime.datetime.fromtimestamp(time.time()-6*24*3600).strftime("%Y-%m-%d")
   Wednesday = datetime.datetime.fromtimestamp(time.time()-5*24*3600).strftime("%Y-%m-%d")
   Thursday = datetime.datetime.fromtimestamp(time.time()-4*24*3600).strftime("%Y-%m-%d")
   Friday = datetime.datetime.fromtimestamp(time.time()-3*24*3600).strftime("%Y-%m-%d")
   Saturday = datetime.datetime.fromtimestamp(time.time()-2*24*3600).strftime("%Y-%m-%d")
   Sunday = datetime.datetime.fromtimestamp(time.time()-1*24*3600).strftime("%Y-%m-%d")


   # 打开数据库连接
   db = MySQLdb.connect("10.40.214.9","hive","hive","emails" )

   # 使用cursor()方法获取操作游标
   cursor = db.cursor()

   workbook = xlsxwriter.Workbook('mail_week_report.xlsx')
   worksheet = workbook.add_worksheet()
   yellow = workbook.add_format({'border':1,'bg_color':'yellow','font_size':12})

   prow=0
   # SQL 查询语句
   for table in ['mail22','mail238','mail25','mail30','mail31','mail32','mail34','mail63','mail74']:
      sql1 = "INSERT INTO tmp_week_result(mail_domain) SELECT %s_base FROM mail_domain_base;" %(table)
      try:
         cursor.execute(sql1)
      except:
         print "Error: insert mail_domain to tmp_week_result fail."
      i=1
      for date in [Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday]:
         sql2 = "UPDATE tmp_week_result a JOIN %s b SET a.sent_number%d = b.sent_number  WHERE a.mail_domain = b.mail_domain AND b.add_time > '%s 00:00:00' AND b.add_time < '%s 23:59:59';" % (table, i, date, date)
         i = i+1
         try:
            # 执行SQL语句
            cursor.execute(sql2)
         except:
            print "Error: insert data to tmp_week_result fail."

      sql = 'select * from tmp_week_result;'
      try:
         # 执行SQL语句
         cursor.execute(sql)
         # 获取所有记录列表
         results = cursor.fetchall()
         #print results

         j=prow+1
         for row in results:
            mail_domain = row[0]
            sent_number1 = row[1]
            sent_number2 = row[2]
            sent_number3 = row[3]
            sent_number4 = row[4]
            sent_number5 = row[5]
            sent_number6 = row[6]
            sent_number7 = row[7]
            # 打印结果
            #print "%s,%d,%d,%d,%d,%d,%d,%d" % (mail_domain, sent_number1,sent_number2,sent_number3,sent_number4,sent_number5,sent_number6,sent_number7)

            i=0
            for value in ["邮件域","星期一","星期二","星期三","星期四","星期五","星期六","星期日"]:
               worksheet.write(prow,i, value.decode('utf-8'),yellow)
               i = i+1
            worksheet.write(j, 0, row[0])
            worksheet.write(j, 1, row[1])
            worksheet.write(j, 2, row[2])
            worksheet.write(j, 3, row[3])
            worksheet.write(j, 4, row[4])
            worksheet.write(j, 5, row[5])
            worksheet.write(j, 6, row[6])
            worksheet.write(j, 7, row[7])
            j=j+1
         prow=prow+20

      except:
         print "Error: can't write data to sheet."
      sql3 = "TRUNCATE TABLE tmp_week_result;"
      cursor.execute(sql3)

   def chart_series(cur_row):
      chart.add_series({
            'name':   '=Sheet1!$A$'+cur_row,
            'categories':   '=Sheet1!$B$1:$H$1',
            'values':   '=Sheet1!$B$'+cur_row+':$H$'+cur_row,
            'line':   {'color': 'black'},
      })

for mailName in ['mail22','mail238','mail25','mail30','mail31','mail32','mail34','mail63','mail74']:

      chart = workbook.add_chart({'type': 'column'})

      if mailName == 'mail22':
         for row in range(2, 12):
            chart_series(str(row))
         chart.set_title({'name': u"10.10.10.22周发送信息统计"})
         chart.set_y_axis({'name':  u'单位:封'})
         chart.set_size({'width': 753, 'height':358})
         #chart.set_style(11)
         worksheet.insert_chart('I2', chart)
      elif mailName == 'mail238':
         for row in range(22, 32):
            chart_series(str(row))
         chart.set_title({'name': u"10.0.3.238周发送统计信息"})
         chart.set_y_axis({'name':  u'单位:封'})
         chart.set_size({'width': 753, 'height':358})
         #chart.set_style(12)
         worksheet.insert_chart('I22', chart)
      elif mailName == 'mail25':
         for row in range(42, 52):
            chart_series(str(row))
         chart.set_title({'name': u"10.20.10.25周发送统计信息"})
         chart.set_y_axis({'name':  u'单位:封'})
         chart.set_size({'width': 753, 'height':358})
         #chart.set_style(13)
         worksheet.insert_chart('I42', chart)
      elif mailName == 'mail30':
         for row in range(62, 72):
            chart_series(str(row))
         chart.set_title({'name': u"10.0.3.30周发送统计信息"})
         chart.set_y_axis({'name':  u'单位:封'})
         chart.set_size({'width': 753, 'height':358})
         #chart.set_style(14)
         worksheet.insert_chart('I62', chart)
      elif mailName == 'mail31':
         for row in range(82, 92):
            chart_series(str(row))
         chart.set_title({'name': u"10.0.3.31周发送统计信息"})
         chart.set_y_axis({'name':  u'单位:封'})
         chart.set_size({'width': 753, 'height':358})
         #chart.set_style(15)
         worksheet.insert_chart('I82', chart)
      elif mailName == 'mail32':
         for row in range(102, 112):
            chart_series(str(row))
         chart.set_title({'name': u"10.0.3.32周发送统计信息"})
         chart.set_y_axis({'name':  u'单位:封'})
         chart.set_size({'width': 753, 'height':358})
         #chart.set_style(16)
         worksheet.insert_chart('I102', chart)
      elif mailName == 'mail34':
         for row in range(122,132):
            chart_series(str(row))
         chart.set_title({'name': u"10.0.3.34周发送统计信息"})
         chart.set_y_axis({'name':  u'单位:封'})
         chart.set_size({'width': 753, 'height':358})
         #chart.set_style(17)
         worksheet.insert_chart('I122', chart)
      elif mailName == 'mail63':
         for row in range(142,152):
            chart_series(str(row))
         chart.set_title({'name': u"10.20.10.63周发送统计信息"})
         chart.set_y_axis({'name':  u'单位:封'})
         chart.set_size({'width': 753, 'height':358})
         #chart.set_style(17)
         worksheet.insert_chart('I142', chart)
      elif mailName == 'mail74':
         for row in range(162,172):
            chart_series(str(row))
         chart.set_title({'name': u"10.0.3.22周发送统计信息"})
         chart.set_y_axis({'name':  u'单位:封'})
         chart.set_size({'width': 753, 'height':358})
         #chart.set_style(17)
         worksheet.insert_chart('I162', chart)
      else:
         print('没有对应的表')

   # 关闭数据库连接
   workbook.close()
   db.close()

if __name__=="__main__":

   getInfo()