[Python知识库]python - xlwt和openpyxl和xlwtwriter写Excel文件示例

import traceback
import xlwt
import openpyxl
import xlsxwriter
from loguru import logger


def write_Excel_multi_sheets(file_name, excel_prepare_datas):
    """
    数据写入Excel表格，支持一个Excel文件写入多个sheet
    :param file_name: 文件名称,例如： './test.xlsx'
    :param excel_prepare_datas:
           example：
           excel_prepare_datas = [
        {"head": ["时间", "销量"], "data": [["2020-02-04", 1], ["2020-02-03", 2]], "title": "饼图"},
        {"head": ["彩虹", "彩虹桥"], "data": [["是", 1], ["否", 2]], "title": "折线图"}]
        "head":表头, "data":数据, "title": sheet_name
    :return:
    """
    outwb = openpyxl.Workbook()  # 打开一个将写的文件
    index = 0
    for origin_data in excel_prepare_datas:
        title = origin_data.get('title')
        data = origin_data.get('data')
        head = origin_data.get('head')

        outws = outwb.create_sheet(title=title, index=index)  # 在将写的文件创建sheet
        for row in range(1, len(data) + 2):
            for col in range(1, len(head) + 1):
                if row == 1:
                    outws.cell(row, col).value = head[col-1]  # 写文件
                else:
                    outws.cell(row, col).value = data[row - 2][col - 1]
        index += 1
    outwb.save(file_name)  # 一定要记得保存
    return file_name


# 导出excel/csv
def write_to_excel(head_datas, chic_datas, sheet_name1, save_path):
    try:
        wb = xlwt.Workbook(encoding="utf-8")
        ws = wb.add_sheet(sheet_name1, cell_overwrite_ok=True)
        # 表头
        ws.write(0, 0, u"用户ID")
        ws.write(0, 1, u"公司名称")
        ws.write(0, 2, u"用户名称")
        # 设置列宽
        ws.col(0).width = 5000
        ws.col(1).width = 5000
        ws.col(2).width = 5000

        # 不可用，不生效，不理解
        # for index, col_title in enumerate(head_datas):
        #     ws.write(0, index, col_title)
        #     ws.col(index).width = 5000
        # 不可用，不生效，不理解

        # 写数据
        for index, data in enumerate(chic_datas):
            index += 1  # 略过表头从1开始
            pre_query = data[0]
            query = data[1]
            pre_query2 = data[2]

            row = [pre_query, query, pre_query2]

            for i in range(len(row)):
                ws.write(index, i, row[i])
        wb.save(save_path)
    except Exception:
        traceback.print_exc()


def test(expenses):
    # 创建一个新的xlsx文件(如果原有同名文件会被覆盖)
    workbook = xlsxwriter.Workbook("Expenses01.xlsx")

    # 创建一个新的表单，默认名称为 “sheet1”，输入字符参数可指定名称
    worksheet = workbook.add_worksheet()

    # worksheet 默认是从0行、0列开始计数
    row = 0
    col = 0

    # worksheet.write 方法将数据写入 xlsx 表格中
    # 参数依次为：行号、列号、数据、[格式]
    for item, cost in expenses.items():
        worksheet.write(row, col, item)
        worksheet.write(row, col + 1, cost)
        row += 1

    # 显式关闭workbook，若不显式指定，则作用域结束后自动关闭
    workbook.close()


if __name__ == "__main__":
    pass
    # func.1
    # excel_prepare_datas = [
    #     {"head": ["时间", "销量"], "data": [["2020-02-04", 1], ["2020-02-03", 2]], "title": "饼图"},
    #     {"head": ["彩虹", "彩虹桥"], "data": [["是", 1], ["否", 2]], "title": "折线图"}
    # ]
    # file_name = write_Excel_multi_sheets('./test.xlsx', excel_prepare_datas)
    # logger.info(f"file_name = {file_name}")

    # func.2
    # logger.info(f"start ---")
    # save_path = './func2.xlsx'
    # sheet_name1 = 'func2-sheet1'
    # head_datas = ['用户ID', '公司名称', '用户名称']
    # chic_datas = [['001', 'company1', 'user1']]
    # write_to_excel('', chic_datas, sheet_name1, save_path)

    # func.3
    expenses = {
        '类别': '数量',
        'Rent': 1000,
        'Gas':  100,
        'Food':  300,
        'Gym':   50,
    }
    test(expenses)