A case of Python Xlsxwriter manipulating Excel

Source: Internet
Author: User

# 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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.