“在后台加一个导出所有订单的功能”, “好,这就加” “兼顾不同的版本”, “好,马上改” 原先就写直接写在该功能里,后来又要给财务汇总导出…

所以就有了下面的代码, 包装成通用的函数

这个是使用xlwt生成xls文件

import xlwt
def export_xls(data, export_filename):
    '''data = [{'sheetname': '总览', titles: ['1', '2', '3'], data: [[1,2,3], [2,3,4], [3,4,5]]
    data是数组,如果长度大于1,则有多少个sheet'''
    assert isinstance(data, list)
    wb = xlwt.Workbook()
    # 如果某一列中的数据本身是多行的,加上这个style,不会让这一列的一些数据出现空行
    style = xlwt.XFStyle()
    style.alignment.wrap = 1
    ws_num = len(data)
    wss = []
    max_len = []   # [[], [], []], 每一个sheet, 每一列的最大长度, 导出时, 让显示更正常
    for i in range(ws_num):
        ws = wb.add_sheet(data[i].get('sheetname', ''))
        for idx, title in enumerate(data[i].get('titles', [])):
            ws.write(0, idx, title)     # 写入标题
        max_len.append([0 for _ in range(len(data[i].get('titles', [])))])
        wss.append(ws)
    for idx, ws in enumerate(wss):
        data_lines = data[idx].get('data', [])
        for line, data_line in enumerate(data_lines):
            for col, data_col in enumerate(data_line):
                ws.write(line+1, col, data_col, style)
                # 获取该列最宽的一个
                if len(bytes(data_col, 'GBK')) > max_len[idx][col]: 
                    max_len[idx][col] = len(bytes(data_col, 'GBK')) 
        for colidx in range(len(data[idx].get('titles', []))):
        	# 别问我这250是怎么出来的--凑出来的
            ws.col(colidx).width = max_len[idx][colidx] * 250
    wb.save('%s.xls' % export_filename)

这个是使用openpyxl生成xlst文件

import openpyxl
from openpyxl.styles import Alignment
def export_xlsx(data, export_filename):
    '''data = [{'sheetname': '总览', titles: ['1', '2', '3'], data: [[1,2,3], [2,3,4], [3,4,5]}]
    data是数组,如果长度大于1,则有多少个sheet'''
    assert isinstance(data, list)
    wb = openpyxl.Workbook()
    # 这个alignment和上一个的style功能一样
    alignment = Alignment(wrap_text=True)
    ws_num = len(data)
    wss = []
    max_len = []   # [[], [], []], 每一个sheet, 每一列的最大长度, 导出时, 显示更正常
    for i in range(ws_num):
        if i == 0:
            # 第一个sheet是这么取的, 如果直接create_sheet, 生成的第一个sheet是空的
            ws = wb.active
            ws.title = data[i].get('sheetname', '')
        else:
            ws = wb.create_sheet(data[i].get('sheetname', ''))
        for idx, title in enumerate(data[i].get('titles', [])):
            # 这里是ws['A1'] = **, ws[A2] = **
            col = ord('A') + idx
            ws['%s1' % chr(col)] = title     # 写入标题
        # 初始化每一个sheet,每一列最大宽度为0
        max_len.append([0 for _ in range(len(data[i].get('titles', [])))])
        wss.append(ws)
    for idx, ws in enumerate(wss):
        data_lines = data[idx].get('data', [])
        for line, data_line in enumerate(data_lines):
            for col, data_col in enumerate(data_line):
                cur_col = ord('A') + col
                ws['%s%s' % (chr(cur_col), line+1)] = data_col
                ws['%s%s' % (chr(cur_col), line+1)].alignment = alignment
                if len(bytes(data_col, 'GBK')) > max_len[idx][col]:
                    max_len[idx][col] = len(bytes(data_col, 'GBK'))
        for colidx in range(len(data[idx].get('titles', []))):
            cur_col = ord('A') + colidx
            ws.column_dimensions['%s' % chr(cur_col)].width = max_len[idx][colidx]
    wb.save('%s.xlsx' % export_filename)
>>> data
[{'data': [['bbbbbbbbbbbbbbbbbbbbb', 'ccccccccccccccccccc', 'aaaaaaaaaaaaa'], ['ddddddddddddddddddddd', 'fffffffffffffffffffffffffffff', 'eeeeeeeeeeeeeeeeeee'], 'titles': ['1', '2', '3'], 'sheetname': '总览'}, {'data': [['gggggggggggggggggggggggg', 'dddddddddddddddddddddddd', 'ttttttttttttttttttttttttt'], 'titles': ['2', '3', '4'], 'sheetname': 'XX'}]
>>> export_xls(data, 'helloworld')
>>> export_xlst(data, 'helloworld')