pandas导出excel

工具类cvs util.py

#!/usr/bin/env python
# -*- coding: utf-8 -*-

import csv
import codecs
import io


class UTF8Recoder:
    """
    Iterator that reads an encoded stream and reencodes the input to UTF-8
    """

    def __init__(self, f, encoding):
        self.reader = codecs.getreader(encoding)(f)

    def __iter__(self):
        return self

    def __next__(self):
        return self.reader.next().encode("utf-8")


class UnicodeReader:
    """
    A CSV reader which will iterate over lines in the CSV file "f",
    which is encoded in the given encoding.
    """

    def __init__(self, f, dialect=csv.excel, encoding="utf-8", **kwds):
        f = UTF8Recoder(f, encoding)
        self.reader = csv.reader(f, dialect=dialect, **kwds)

    def __next__(self):
        row = next(self.reader)
        return [str(s, "utf-8") for s in row]

    def __iter__(self):
        return self


class UnicodeWriter:
    """
    A CSV writer which will write rows to CSV file "f",
    which is encoded in the given encoding.
    """

    def __init__(self, f, dialect=csv.excel, encoding="utf-8", **kwds):
        # Redirect output to a queue
        self.queue = io.StringIO()
        self.writer = csv.writer(self.queue, dialect=dialect, **kwds)
        self.stream = f
        self.encoder = codecs.getincrementalencoder(encoding)()

    def writerow(self, row):
        self.writer.writerow([s.encode("utf-8") for s in row])
        # Fetch UTF-8 output from the queue ...
        data = self.queue.getvalue()
        data = data.decode("utf-8")
        # ... and reencode it into the target encoding
        data = self.encoder.encode(data)
        # write to the target stream
        self.stream.write(data)
        # empty queue
        self.queue.truncate(0)

    def writerows(self, rows):
        for row in rows:
            self.writerow(row)


def update_excel_format(writer):
    from openpyxl.utils import get_column_letter

    workbook = writer.book
    for sheet_name in writer.sheets:
        worksheet = writer.sheets[sheet_name]
        rows = worksheet.dim_rowmax + 1
        cols = get_column_letter(worksheet.dim_colmax + 1)
        bord_format = workbook.add_format({"border": 1})
        head_format = workbook.add_format({"border": 1, "bg_color": "#A9A9A9"})
        cell_format = workbook.add_format({"align": "center", "valign": "vcenter"})
        worksheet.conditional_format("A1:XFD1", {"type": "no_blanks", "format": head_format})
        if rows > 1:
            worksheet.set_column(f"A:{cols}", None, cell_format)
            worksheet.conditional_format(
                f"A2:{cols}{rows}", {"type": "no_errors", "format": bord_format}
            )

1 数据组装

for report in query.all():
    data.append(
        {
            "id": report.id,
            "uuid": report.uuid,
            "report_ts": str(report.report_ts)[:19],
            "update_ts": str(report.update_ts)[:19],
            "org_name": report.org_src.title,
        }
    )

2 列明指定, 数据填充

import pandas as pd
# 导出xlsx文件
ts = datetime.datetime.now().strftime("%Y年%m月%d日%H时%M分%S秒")
file_name = "列表_{}.xlsx".format(ts)
file_path = "{}/{}".format(temp_dir, file_name)
df_data = pd.DataFrame(data)
column_map = {
    "uuid": "ID",
    "report_ts": "提交时间",
    "update_ts": "更新时间",
}
df_data = df_data[list(column_map.keys())]
df_data = df_data.rename(columns=column_map)
writer = pd.ExcelWriter(file_path)
df_data.to_excel(writer, index=False)
# 格式美化. 从cvs_util类引用
update_excel_format(writer)
writer.close()