使用python从MySQL生成discuz的excel报表
要求：每周从discuz的mysql数据库读取数据，生成所需要的xecel报表，模板格式如下：
同时生成统计图：
附，代码如下（需要根据上面的表格先做好excel模板）：
#!/usr/bin/env python
# -*- coding: cp936 -*-
__author__ = ‘Max Shu’
import sys
import string
import os
import time
import datetime
import struct
import MySQLdb
import openpyxl
from openpyxl.styles import Style, PatternFill, Border, Side, Alignment, Protection, Font
from openpyxl.charts import LineChart, Reference, Series
from openpyxl.cell import coordinate_from_string, get_column_letter, get_column_interval, column_index_from_string
class BCDISCUZ_Statistics():
conn = None
usersTotal = 0
usersActivityTotal = 0
usersZombieTotal = 0
postsMaxOfSingleUser = 0
threadsMaxOfSingleUser = 0
postsTotal = 0
threadsTotal = 0
postsMaxOfSingleThread = 0
pvMaxOfSingleThread = 0
forumsTotal =0
postsMaxOfSingleForum = 0
threadsMaxOfSingleForum = 0
def __init__(self):
try:
self.conn = MySQLdb.connect(host = “localhost”,
user = “root”,
passwd = “xxxxx”,
db = “discuz_ecloud”)
except MySQLdb.Error, e:
print “Error %d: %s” % (e.args[0], e.args[1])
print “ERROR: Can’t connect to MySQL( localhost:3306 )”
self.conn = None
return
def __del__(self):
if self.conn != None:
self.conn.commit()
self.conn.close()
def Print_All_Statistics_Value(self):
print ” “
print “usersTotal: %d” % self.usersTotal
print “usersActivityTotal: %d” % self.usersActivityTotal
print “usersZombieTotal: %d” % self.usersZombieTotal
print “postsMaxOfSingleUser: %d” % self.postsMaxOfSingleUser
print “threadsMaxOfSingleUser: %d” % self.threadsMaxOfSingleUser
print ” “
print “postsTotal: %d” % self.postsTotal
print “threadsTotal: %d” % self.threadsTotal
print “postsMaxOfSingleThread: %d” % self.postsMaxOfSingleThread
print “pvMaxOfSingleThread: %d” % self.pvMaxOfSingleThread
print ” “
print “forumsTotal: %d” % self.forumsTotal
print “postsMaxOfSingleForum: %d” % self.postsMaxOfSingleForum
print “threadsMaxOfSingleForum: %d” % self.threadsMaxOfSingleForum
print ” “
def Get_All_Statistics_Value(self):
self.Get_Users_Total()
self.Get_Users_Activity_Total()
self.Get_Users_Zombie_Total()
self.Get_Posts_Max_Of_Single_User()
self.Get_Threads_Max_Of_Single_User()
self.Get_Posts_Total()
self.Get_Threads_Total()
self.Get_Posts_Max_Of_Single_Thread()
self.Get_PV_Max_Of_Single_Thread()
self.Get_Forums_Total()
self.Get_Posts_Max_Of_Single_Forum()
self.Get_Threads_Max_Of_Single_Forum()
def Get_Users_Total(self):
“””用户数”””
if self.conn == None:
return 0
try:
cursor = self.conn.cursor ()
cursor.execute (“SELECT COUNT(*) FROM pre_common_member;”)
result_set = cursor.fetchall ()
# for row in result_set:
# print “%s” % (row[0])
# print “Number of rows returned: %d” % cursor.rowcount
cursor.close ()
self.usersTotal = result_set[0][0]
return self.usersTotal
except MySQLdb.Error, e:
print “Error %d: %s” % (e.args[0], e.args[1])
return 0
def Get_Users_Activity_Total(self):
“””活跃用户数”””
if self.conn == None:
return 0
try:
cursor = self.conn.cursor ()
cursor.execute (“SELECT COUNT(*) FROM pre_common_member_status WHERE lastvisit >= UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 WEEK));”)
result_set = cursor.fetchall ()
cursor.close ()
self.usersActivityTotal = result_set[0][0]
return self.usersActivityTotal
except MySQLdb.Error, e:
print “Error %d: %s” % (e.args[0], e.args[1])
return 0
def Get_Users_Zombie_Total(self):
“””僵尸用户数”””
if self.conn == None:
return 0
try:
cursor = self.conn.cursor ()
cursor.execute (“SELECT COUNT(*) FROM pre_common_member_status WHERE lastvisit <= UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 3 MONTH));”)
result_set = cursor.fetchall ()
cursor.close ()
self.usersZombieTotal = result_set[0][0]
return self.usersZombieTotal
except MySQLdb.Error, e:
print “Error %d: %s” % (e.args[0], e.args[1])
return 0
def Get_Posts_Max_Of_Single_User(self):
“””单用户最多帖子数”””
if self.conn == None:
return 0
try:
cursor = self.conn.cursor ()
cursor.execute (“SELECT MAX(posts) FROM pre_common_member_count;”)
result_set = cursor.fetchall ()
cursor.close ()
self.postsMaxOfSingleUser = result_set[0][0]
return self.postsMaxOfSingleUser
except MySQLdb.Error, e:
print “Error %d: %s” % (e.args[0], e.args[1])
return 0
def Get_Threads_Max_Of_Single_User(self):
“””单用户最多主题数”””
if self.conn == None:
return 0
try:
cursor = self.conn.cursor ()
cursor.execute (“SELECT MAX(threads) FROM pre_common_member_count;”)
result_set = cursor.fetchall ()
cursor.close ()
self.threadsMaxOfSingleUser = result_set[0][0]
return self.threadsMaxOfSingleUser
except MySQLdb.Error, e:
print “Error %d: %s” % (e.args[0], e.args[1])
return 0
def Get_Posts_Total(self):
“””帖子数”””
if self.conn == None:
return 0
try:
cursor = self.conn.cursor ()
cursor.execute (“SELECT COUNT(*) FROM pre_forum_post;”)
result_set = cursor.fetchall ()
cursor.close ()
self.postsTotal = result_set[0][0]
return self.postsTotal
except MySQLdb.Error, e:
print “Error %d: %s” % (e.args[0], e.args[1])
return 0
def Get_Threads_Total(self):
“””主题数”””
if self.conn == None:
return 0
try:
cursor = self.conn.cursor ()
cursor.execute (“SELECT COUNT(*) FROM pre_forum_thread;”)
result_set = cursor.fetchall ()
cursor.close ()
self.threadsTotal = result_set[0][0]
return self.threadsTotal
except MySQLdb.Error, e:
print “Error %d: %s” % (e.args[0], e.args[1])
return 0
def Get_Posts_Max_Of_Single_Thread(self):
“””单主题最多帖子数”””
if self.conn == None:
return 0
try:
cursor = self.conn.cursor ()
cursor.execute (“SELECT MAX(t1.c1) FROM (SELECT COUNT(*) AS c1 FROM pre_forum_post GROUP BY tid) AS t1;”)
result_set = cursor.fetchall ()
cursor.close ()
self.postsMaxOfSingleThread = result_set[0][0]
return self.postsMaxOfSingleThread
except MySQLdb.Error, e:
print “Error %d: %s” % (e.args[0], e.args[1])
return 0
def Get_PV_Max_Of_Single_Thread(self):
“””单主题最大PV值”””
if self.conn == None:
return 0
try:
cursor = self.conn.cursor ()
cursor.execute (“SELECT MAX(views) FROM pre_forum_thread;”)
result_set = cursor.fetchall ()
cursor.close ()
self.pvMaxOfSingleThread = result_set[0][0]
return self.pvMaxOfSingleThread
except MySQLdb.Error, e:
print “Error %d: %s” % (e.args[0], e.args[1])
return 0
def Get_Forums_Total(self):
“””版块数”””
if self.conn == None:
return 0
try:
cursor = self.conn.cursor ()
cursor.execute (“SELECT COUNT(*) FROM pre_forum_forum WHERE STATUS=1 AND TYPE <> ‘group’;”)
result_set = cursor.fetchall ()
cursor.close ()
self.forumsTotal = result_set[0][0]
return self.forumsTotal
except MySQLdb.Error, e:
print “Error %d: %s” % (e.args[0], e.args[1])
return 0
def Get_Posts_Max_Of_Single_Forum(self):
“””单版块最多帖子数”””
if self.conn == None:
return 0
try:
cursor = self.conn.cursor ()
cursor.execute (“SELECT MAX(posts) FROM pre_forum_forum;”)
result_set = cursor.fetchall ()
cursor.close ()
self.postsMaxOfSingleForum = result_set[0][0]
return self.postsMaxOfSingleForum
except MySQLdb.Error, e:
print “Error %d: %s” % (e.args[0], e.args[1])
return 0
def Get_Threads_Max_Of_Single_Forum(self):
“””单版块最多主题数”””
if self.conn == None:
return 0
try:
cursor = self.conn.cursor ()
cursor.execute (“SELECT MAX(threads) FROM pre_forum_forum;”)
result_set = cursor.fetchall ()
cursor.close ()
self.threadsMaxOfSingleForum = result_set[0][0]
return self.threadsMaxOfSingleForum
except MySQLdb.Error, e:
print “Error %d: %s” % (e.args[0], e.args[1])
return 0
def Write_Excel_File(self):
fileDir = u”Excel周报”
fileNamePre = u”论坛现网环境业务统计周报-“
dateStr = time.strftime(‘%Y%m%d’, time.localtime())
# delete old file for today.
fileName = os.path.join(fileDir, fileNamePre+dateStr+u”.xlsx”)
fileName = fileName.encode(“cp936”)
if os.path.isfile(fileName):
os.remove(fileName)
# get newest file for before.
newestFileName = “”
oldFileDate = “”
fileDir = fileDir.encode(“cp936”)
for file in os.listdir(fileDir):
fileDate = file[len(fileNamePre):file.index(“.xlsx”)]
if oldFileDate < fileDate:
oldFileDate = fileDate
newestFileName = file
# open the newest file to operation.
border=Border(left=Side(border_style=’thin’, color=’FF000000′),
right=Side(border_style=’thin’, color=’FF000000′),
top=Side(border_style=’thin’, color=’FF000000′),
bottom=Side(border_style=’thin’, color=’FF000000′))
workBook = openpyxl.load_workbook(os.path.join(fileDir, newestFileName))
sheet = workBook.get_sheet_by_name(u”Statistics”)
row = sheet.max_row + 1
# date
sheet.cell(‘A’+str(row)).value = datetime.date.today()
sheet.cell(‘A’+str(row)).number_format = “yyyy/mm/dd”
sheet.cell(‘A’+str(row)).style = Style(border=border, number_format=’yyyy/mm/dd’)
# user
sheet.cell(‘B’+str(row)).value = self.usersTotal
sheet.cell(‘B’+str(row)).style = Style(border=border)
sheet.cell(‘C’+str(row)).value = self.usersActivityTotal
sheet.cell(‘C’+str(row)).style = Style(border=border)
sheet.cell(‘D’+str(row)).value = self.usersZombieTotal
sheet.cell(‘D’+str(row)).style = Style(border=border)
sheet.cell(‘E’+str(row)).value = self.postsMaxOfSingleUser
sheet.cell(‘E’+str(row)).style = Style(border=border)
sheet.cell(‘F’+str(row)).value = self.threadsMaxOfSingleUser
sheet.cell(‘F’+str(row)).style = Style(border=border)
# post
sheet.cell(‘G’+str(row)).value = self.postsTotal
sheet.cell(‘G’+str(row)).style = Style(border=border)
sheet.cell(‘H’+str(row)).value = self.threadsTotal
sheet.cell(‘H’+str(row)).style = Style(border=border)
sheet.cell(‘I’+str(row)).value = self.postsMaxOfSingleThread
sheet.cell(‘I’+str(row)).style = Style(border=border)
sheet.cell(‘J’+str(row)).value = self.pvMaxOfSingleThread
sheet.cell(‘J’+str(row)).style = Style(border=border)
# forum
sheet.cell(‘K’+str(row)).value = self.forumsTotal
sheet.cell(‘K’+str(row)).style = Style(border=border)
sheet.cell(‘L’+str(row)).value = self.postsMaxOfSingleForum
sheet.cell(‘L’+str(row)).style = Style(border=border)
sheet.cell(‘M’+str(row)).value = self.threadsMaxOfSingleForum
sheet.cell(‘M’+str(row)).style = Style(border=border)
# chart operation
sheetChart = workBook.get_sheet_by_name(u”Chart”)
chartTopPos = 5
chartInterval = 5
chartLeftPos = 10
chartWidth = 1200
realValueStartRow = 3
labels = Reference(sheet, (realValueStartRow, column_index_from_string(‘A’)), (sheet.max_row, column_index_from_string(‘A’)))
# No.1 chart
values = Reference(sheet, (realValueStartRow, column_index_from_string(‘B’)), (sheet.max_row, column_index_from_string(‘B’)))
series1 = Series(values, title=u”总用户数”, labels=labels)
chartHeight = 300
self.Execl_Insert_Chart(sheetChart, u”总用户数”, chartLeftPos, chartTopPos, chartWidth, chartHeight, series1)
# No.2 chart
values = Reference(sheet, (realValueStartRow, column_index_from_string(‘C’)), (sheet.max_row, column_index_from_string(‘C’)))
series1 = Series(values, title=u”活跃用户数”, labels=labels)
chartTopPos += (chartHeight + chartInterval)
self.Execl_Insert_Chart(sheetChart, u”活跃用户数”, chartLeftPos, chartTopPos, chartWidth, chartHeight, series1)
values = Reference(sheet, (realValueStartRow, column_index_from_string(‘G’)), (sheet.max_row, column_index_from_string(‘G’)))
series1 = Series(values, title=u”总贴数”, labels=labels)
chartTopPos += (chartHeight + chartInterval)
self.Execl_Insert_Chart(sheetChart, u”总贴数”, chartLeftPos, chartTopPos, chartWidth, chartHeight, series1)
values = Reference(sheet, (realValueStartRow, column_index_from_string(‘H’)), (sheet.max_row, column_index_from_string(‘H’)))
series1 = Series(values, title=u”总主题数”, labels=labels)
chartTopPos += (chartHeight + chartInterval)
self.Execl_Insert_Chart(sheetChart, u”总主题数”, chartLeftPos, chartTopPos, chartWidth, chartHeight, series1)
# save today file
workBook.save(fileName)
def Execl_Insert_Chart(self, sheet, title, left, top, width, height, *series):
chart = LineChart()
chart.title = title
for serial in series:
chart.append(serial)
chart.drawing.left = left
chart.drawing.top = top
chart.drawing.width = width
chart.drawing.height = height
chart.margin_left = width – 100
sheet.add_chart(chart)
if __name__ == ‘__main__’:
bcdiscuz = BCDISCUZ_Statistics()
if bcdiscuz.conn == None:
exit(1)
bcdiscuz.Get_All_Statistics_Value()
bcdiscuz.Print_All_Statistics_Value()
bcdiscuz.Write_Excel_File()
bcdiscuz = None
exit(0)