Python 将数据导出为Excel文件

Web项目基本是都是在搞各种平台，系统，所谓的信息化、数字化建设，其中项目中难免会有将页面的数据导出为Excel文件的功能，比如一个执行记录列表、操作日志列表等等。常用的方法，索性封装成一个方法，随时可以调用，召之即来，来之能打。

                                                                     ~~DJ drop the beat~~ ☝

//1.封装好写入Excel文件的方法
def write_excel(data: dict):
    """
    写excel公共方法
    :param data:    {"sheet1":{"head":[],"rows":[[col1[,col2],[col1[,col2]]]]}}
    :return:
    """
    wb = write_open_file()
    if data != None:
        for key in data.keys():
            sheet = wb.add_sheet(key)  # 创建工作页
            keyData = data[key]
            head, rows = [], []
            if "head" in keyData:
                head = keyData["head"]  # excel标题栏
            if "rows" in keyData:
                rows = keyData["rows"]  # excel数据集合
            for index in range(0, len(head)):  # 生成标题
                sheet.write(0, index, head[index])
            rowNum = 1
            for row in rows:
                cellNum = 0
                for cell in row:
                    sheet.write(rowNum, cellNum, cell)
                    cellNum += 1
                rowNum += 1
    return wb

def write_excel_arr(dataArr: list):
    """
    写excel公共方法，多个sheet页
    :param dataArr:    [{"sheet1":{"head":[],"rows":[[col1[,col2],[col1[,col2]]]]}}]
    :return:
    """
    wb = write_open_file()
    if dataArr != None:
        for data in dataArr:
            for key in data.keys():
                sheet = wb.add_sheet(key)  # 创建工作页
                keyData = data[key]
                head, rows = [], []
                if "head" in keyData:
                    head = keyData["head"]  # excel标题栏
                if "rows" in keyData:
                    rows = keyData["rows"]  # excel数据集合
                for index in range(0, len(head)):  # 生成标题
                    sheet.write(0, index, head[index])
                rowNum = 1
                for row in rows:
                    cellNum = 0
                    for cell in row:
                        value = cell
                        if type(cell) == type(datetime.datetime.today()):
                            value = getFormatDate(cell, None)
                        sheet.write(rowNum, cellNum, value)
                        cellNum += 1
                    rowNum += 1
    return wb

//2.调用上面的方法
 @action(detail=False, methods=['get'], url_name="导出测试历史记录")
    def export(self, request):
        name = request.query_params.get("name")
        inst_obj = PerformanceInst.objects.get(name=name)
        logging.info("inst_id%s" % inst_obj.id)
        detail_list = self.get_detail(inst_obj.id)
        rows = list()
        for item in detail_list:
            rows.append(item.values())
        response = export_context('性能测试历史表' + time.strftime('%Y%m%d', time.localtime(time.time())) + '.xls')
        wb = write_excel({"性能测试历史表": {
            "head": ["序列号", "指标名", "主机名", "基准分数", "得分", "状态", "开始时间", "结束时间", "耗时(s)"],
            "rows": rows}})
        wb.save(response)
        return response