A case of Python Xlsxwriter manipulating Excel

# Encoding:utf-8

#!/usr/bin/python

#author: Zhangdonghong

#email: [Email protected]

#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")

# Open Database connection

db = MySQLdb.connect ("10.40.214.9", "Hive", "Hive", "emails")

# get an action cursor using the cursor () method

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 Query Statement

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

# Execute SQL statement

Cursor.execute (SQL2)

Except

Print "Error:insert data to Tmp_week_result fail."

sql = ' select * from Tmp_week_result; '

Try

# Execute SQL statement

Cursor.execute (SQL)

# Get a list of all records

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 Results

#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 ["Mail Field", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday"]:

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 $ $H ',

' 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 weeks to send information statistics "})

Chart.set_y_axis ({' Name ': U ' unit: Sealed '})

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 weeks Send Statistics"})

Chart.set_y_axis ({' Name ': U ' unit: Sealed '})

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 Weeks Send Statistics"})

Chart.set_y_axis ({' Name ': U ' unit: Sealed '})

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.3 Weeks Send Statistics"})

Chart.set_y_axis ({' Name ': U ' unit: Sealed '})

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 weeks Send Statistics"})

Chart.set_y_axis ({' Name ': U ' unit: Sealed '})

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 weeks Send Statistics"})

Chart.set_y_axis ({' Name ': U ' unit: Sealed '})

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 weeks Send Statistics"})

Chart.set_y_axis ({' Name ': U ' unit: Sealed '})

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 weeks Send Statistics"})

Chart.set_y_axis ({' Name ': U ' unit: Sealed '})

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 weeks Send Statistics"})

Chart.set_y_axis ({' Name ': U ' unit: Sealed '})

Chart.set_size ({' width ': 753, ' height ': 358})

#chart. Set_style (17)

Worksheet.insert_chart (' I162 ', chart)

Else

Print (' no corresponding table ')

# Close the database connection

Workbook.close ()

Db.close ()

If __name__== "__main__":

GetInfo ()

This article is from the "Zhangdh Open Space" blog, so be sure to keep this source http://linuxblind.blog.51cto.com/7616603/1711947

A case of Python Xlsxwriter manipulating Excel