在python中使用xlwt操作excel

公众号，欢迎关注

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, '到课率')