时间戳

首页 > 解决方案 > Pandas xlsxwriter 将数据框写入 excel 并实现列宽和边框相关格式

python - Pandas xlsxwriter 将数据框写入 excel 并实现列宽和边框相关格式

问题描述

背景：

我正在使用 Pandas 并有一个数据框“df”，我打算将其写入 Excel 工作表。我使用下面的代码并获取输出 Excel 表，如附加快照“Present.JPG”所示：礼物.JPG

import pandas as pd
import xlsxwriter

writer = pd.ExcelWriter('output.xlsx', engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1')
writer.save()

问题描述： 我想将数据框写入 Excel 并合并以下更改。

1) 去掉指示索引的第一列
2) 在所有列上实现文本换行（以自动调整每个列的宽度）
3) 绘制粗边框 A1 到 C4、D1 到 F4 和列 G

最终，我希望 Excel 表看起来像快照“Desired.JPG”中所示：所需.JPG

尝试到现在： 我尝试了以下命令，但它们将边框覆盖到单元格的内容上。此外，我无法弄清楚如何将边框（和文本换行）扩展到单个单元格之外。

writer = pd.ExcelWriter("output.xlsx", engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1')
workbook=writer.book
worksheet= writer.sheets['Sheet1']

full_border = workbook.add_format({"border":1,"border_color": "#000000"})
link_format = workbook.add_format({'text_wrap': True})

worksheet.write("D3", None, full_border)
worksheet.write("E1", None, link_format)

writer.save()

标签： pythonexcelpandasdataframexlsxwriter

解决方案

我参加聚会有点晚了，但这是您要找的东西：

import xlsxwriter
import pandas as pd

df = pd.DataFrame({
    'Class': ['A', 'A', 'A'],
    'Type': ['Mary', 'John', 'Michael'],
    'JoinDate YYYY-MM-DD': ['2018-12-12', '2018-12-12', '2018-12-15'],
    'Weight': [150, 139, 162],
    'Height': [166.4, 160, 143],
    'Marks': [96, 89, 71],
    'LastDate YYYY-MM-DD': ['2020-01-17', '2020-01-17', '2020-01-17']
})

with pd.ExcelWriter('output.xlsx', engine='xlsxwriter') as writer:
    # remove the index by setting the kwarg 'index' to False
    df.to_excel(excel_writer=writer, sheet_name='Sheet1', index=False)

    workbook = writer.book
    worksheet = writer.sheets['Sheet1']

    # dynamically set column width
    for i, col in enumerate(df.columns):
        column_len = max(df[col].astype(str).str.len().max(), len(col) + 2)
        worksheet.set_column(i, i, column_len)

    # wrap the text in all cells
    wrap_format = workbook.add_format({'text_wrap': True, 'align': 'center'})
    worksheet.set_column(0, len(df.columns) - 1, cell_format=wrap_format)

    # mimic the default pandas header format for use later
    hdr_fmt = workbook.add_format({
        'bold': True,
        'border': 1,
        'text_wrap': True,
        'align': 'center'
    })

    def update_format(curr_frmt, new_prprty, wrkbk):
        """
        Update a cell's existing format with new properties
        """
        new_frmt = curr_frmt.__dict__.copy()

        for k, v in new_prprty.items():
            new_frmt[k] = v

        new_frmt = {
            k: v
            for k, v in new_frmt.items()
            if (v != 0) and (v is not None) and (v != {}) and (k != 'escapes')
        }

        return wrkbk.add_format(new_frmt)

    # create new border formats
    header_right_thick = update_format(hdr_fmt, {'right': 2}, workbook)
    normal_right_thick = update_format(wrap_format, {'right': 2}, workbook)
    normal_bottom_thick = update_format(wrap_format, {'bottom': 2}, workbook)
    normal_corner_thick = update_format(wrap_format, {
        'right': 2,
        'bottom': 2
    }, workbook)

    # list the 0-based indices where you want bold vertical border lines
    vert_indices = [2, 5, 6]

    # create vertical bold border lines
    for i in vert_indices:
        # header vertical bold line
        worksheet.conditional_format(0, i, 0, i, {
            'type': 'formula',
            'criteria': 'True',
            'format': header_right_thick
        })
        # body vertical bold line
        worksheet.conditional_format(1, i,
                                     len(df.index) - 1, i, {
                                         'type': 'formula',
                                         'criteria': 'True',
                                         'format': normal_right_thick
                                     })
        # bottom corner bold lines
        worksheet.conditional_format(len(df.index), i, len(df.index), i, {
            'type': 'formula',
            'criteria': 'True',
            'format': normal_corner_thick
        })
    # create bottom bold border line
    for i in [i for i in range(len(df.columns) - 1) if i not in vert_indices]:
        worksheet.conditional_format(len(df.index), i, len(df.index), i, {
            'type': 'formula',
            'criteria': 'True',
            'format': normal_bottom_thick
        })

推荐阅读