公众号,欢迎关注
python操作excel还是挺简单的
主要有两个库,openpyxl和xlwt,但是由于openpyxl只能支持2010以上的版本,mac上的numbers无法打开,所以就改用了xlwt
下面是我工作中写的一个脚本,其中还有一些使用MySQLdb查询数据库的代码,贴在这里,留着备查,如果还能帮到某个人的话,那就更好了
# -*-coding:utf-8-*-
from __future__ import division
import MySQLdb
import sys
import datetime
import time
from os.path import basename
import xlwt
reload(sys)
sys.setdefaultencoding('utf-8')
import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from email.mime.application import MIMEApplication
config = {
'sender': 'notice@test.com',
'password': 'test',
'receivers': ['test@test.com'],
"courseIds": ['156', '157']
}
def send(sender, password, receivers=[], message='', attach='', subject=''):
"""发送邮件"""
try:
mail_server = smtplib.SMTP_SSL('smtp.exmail.qq.com', 465)
mail_server.login(sender, password)
msg = MIMEMultipart('alternative')
msg['Subject'] = subject
msg['From'] = config['sender']
# msg['CC'] = config['receivers']
part = MIMEText(message, 'html')
msg.attach(part)
if attach != '':
with open(xlsx, "rb") as f:
part_app = MIMEApplication(f.read(), Name=basename(xlsx))
part_app['Content-Disposition'] = 'attachment; filename="%s"' % basename(xlsx)
msg.attach(part_app)
mail_server.sendmail(sender, receivers, msg.as_string())
print "Successfully sent email"
mail_server.quit()
except smtplib.SMTPException as e:
print e
print "Error: unable to send email"
outwb = xlwt.Workbook(encoding="utf-8")
# 线上需要charset参数, 否则输出excel乱码
#db = MySQLdb.connect("localhost", "root", "", "test", charset='utf8')
cursor = db.cursor()
sheet_index = 0
def getWsType(ws_type):
if ws_type == 1:
return '考研'
elif ws_type == 2:
return '四六级'
else:
return 'unknown'
for courseId in config["courseIds"]:
statistic_map_list = []
sql = 'select id from lesson where courseId = ' + courseId + ' and begin < ' + str(
time.mktime(datetime.datetime.now().timetuple()) * 1000) + ' order by begin'
cursor.execute(sql)
lesson_ids = cursor.fetchall()
total_count_sql = 'select count(0) from `order` where goodsNo=(select goodsNo from course where id = ' + courseId + ') and state = 1'
cursor.execute(total_count_sql)
total_count = cursor.fetchall()
total = total_count[0][0]
print total
for lesson_id in lesson_ids:
statistic_map = {}
lesson_info_sql = 'select title, id, begin, (select name from teacher where id = teacherId) as teacher, classify, needSmsNotice, (select title from lessongroup where id = groupId) as groupTitle from lesson where id = ' + str(
lesson_id[0])
cursor.execute(lesson_info_sql)
lesson_info = cursor.fetchall()
lessonTitle = lesson_info[0][0]
statistic_map['lessonTitle'] = lessonTitle
lessonId = lesson_info[0][1]
statistic_map['lessonId'] = lessonId
startTime = lesson_info[0][2]
statistic_map['startTime'] = datetime.datetime.fromtimestamp(startTime / 1000.0).strftime('%Y-%m-%d %H:%M:%S')
teacher = lesson_info[0][3]
statistic_map['teacher'] = teacher
classify = lesson_info[0][4]
if classify == 0:
statistic_map['classify'] = '默认'
elif classify == 1:
statistic_map['classify'] = '是'
else:
statistic_map['classify'] = '否'
needSmsNotice = lesson_info[0][5]
if needSmsNotice == 1:
statistic_map['needSmsNotice'] = '是'
else:
statistic_map['needSmsNotice'] = '否'
statistic_map['groupTitle'] = lesson_info[0][6]
end_time = startTime + 30 * 60 * 1000
start_time = startTime - 10 * 60 * 1000
live_attendance_count_sql = 'select count(distinct uid) from lessonrecord where ctime <= ' + str(
end_time) + ' and ctime >= ' + str(start_time) + ' and lessonId = ' + str(
lesson_id[0])
cursor.execute(live_attendance_count_sql)
live_attendance_count = cursor.fetchall()
if total != 0:
statistic_map['liveAttendance'] = "{0:.2f}".format(live_attendance_count[0][0] * 100 / total) + '%'
else:
statistic_map['liveAttendance'] = 0
attendance_count_sql = 'select count(distinct uid) from lessonrecord where lessonId = ' + str(lesson_id[0])
cursor.execute(attendance_count_sql)
attendance_count = cursor.fetchall()
if total != 0:
statistic_map['totalAttendance'] = "{0:.2f}".format(attendance_count[0][0] * 100 / total) + '%'
else:
statistic_map['totalAttendance'] = 0
statistic_map_list.append(statistic_map)
sql = 'select title, wsType from course where id = ' + courseId
cursor.execute(sql)
course_and_wstype = cursor.fetchall()
sheet_name = courseId + u' ' + str(course_and_wstype[0][0]) + u' ' + str(total) + '人'
sheet = outwb.add_sheet(courseId)
# sheet = outwb.create_sheet(sheet_name, sheet_index)
sheet.write(0, 0, sheet_name)
sheet.write(1, 0, '课次名称')
sheet.write(1, 1, '课次ID')
sheet.write(1, 2, '开课时间')
sheet.write(1, 3, '授课老师')
sheet.write(1, 4, '主辅课')
sheet.write(1, 5, '是否发短信')
sheet.write(1, 6, '直播到课率')
sheet.write(1, 7, '总到课率')
sheet.write(1, 8, '课程类型')
sheet.write(1, 9, '课次组')
sheet_index += 1
i = 2
for statistic_map in statistic_map_list:
sheet.write(i, 0, statistic_map['lessonTitle'])
sheet.write(i, 1, statistic_map['lessonId'])
sheet.write(i, 2, statistic_map['startTime'])
sheet.write(i, 3, statistic_map['teacher'])
sheet.write(i, 4, statistic_map['classify'])
sheet.write(i, 5, statistic_map['needSmsNotice'])
sheet.write(i, 6, statistic_map['liveAttendance'])
sheet.write(i, 7, statistic_map['totalAttendance'])
sheet.write(i, 8, getWsType(course_and_wstype[0][1]))
sheet.write(i, 9, statistic_map['groupTitle'])
i += 1
xlsx = "attendance-" + datetime.datetime.now().strftime('%Y-%m-%d') + ".xls"
outwb.save(xlsx)
send(config['sender'], config['password'], config['receivers'], '到课率', xlsx, '到课率')