python自动生成大数据量Excel文件

xlwt模块只支持Excel2007之前的版本，格式为：xls文件，且单个sheet只支持最大行数为65535，一旦数据量超出限制行数，则会报出以下异常：

ValueError: row index was 65536, not allowed by .xls format

此时，openpyxl模块则是一个很不错的选择了，openpyxl 模块用来处理Excel 2007及以上版本的文件，也就是.xlsx、.xlsm、xltx、xltm格式的文件，最大支持行数1048576。

openpyxl模块安装方法：

$ pip install openpyxl

实例：以下脚本实现了从mysql数据库查询数据集自动生成Excel文件

#!/usr/bin/env python3
# -*- coding: UTF-8 -*-

import datetime
import os
import sys

import mysql.connector
from openpyxl import Workbook


def mkdir(path):
    dir = os.path.dirname(path)
    isExists = os.path.exists(dir)
    if not isExists:
        os.makedirs(path)

    return path


def query_mysql(host, port, user, passwd, db, sql):
    try:
        mydb = mysql.connector.connect(host=host, user=user, port=port, passwd=passwd, database=db)
        # 创建游标
        mycursor = mydb.cursor()
        # 执行mysql
        mycursor.execute(sql)
    except Exception as err:
        print("Mysql_ERROR ：%s " % err)
        sys.exit(-1)
    else:
        field = [filed[0] for filed in mycursor.description]
        datas = mycursor.fetchall()
        dict = {'field': field, 'datas': datas}
        mydb.close()
        return dict


def export_excel(path, field, datas):
    try:
        # 写入excel表格
        book = Workbook()
        sheet = book.active
        # 写入 “列名”
        for col, filed in enumerate(field, start=1):
            sheet.cell(row=1, column=col).value = str(filed)
        # 写入 “数据”
        row = 2
        for data in datas:
            for col, d in enumerate(data, start=1):
                sheet.cell(row=row, column=col).value = str(d)
            row += 1
        # 保存
        book.save(path)
    except Exception as err:
        print(err)


def main():
    # 从mysql查询结果集
    host, port, user, passwd, db = '192.168.96.9', '3306', 'root', 'abc123', 'dbname'
    sql = """select * from table;"""
    dict_datas = query_mysql(host, port, user, passwd, db, sql)

    # 写入xlsx文件
    path = './output/导出全表数据_{}.xlsx'.format(datetime.datetime.now().strftime('%Y%m%d%H%M%S'))
    field = dict_datas.get('field')
    datas = dict_datas.get('datas')
    export_excel(mkdir(path), field, datas)


    if __name__ == "__main__":
        main()

openpyxl 参考：https://openpyxl.readthedocs.io/en/stable/