如何使用xlsxwriter格式化索引列? [英] How can I format the index column(s) with xlsxwriter?

问题描述

我正在使用 xlsxwriter 和 set_column 函数来格式化excel输出中的列.

I'm using xlsxwriter and the set_column function that format the columns in my excel outputs.

但是，当将格式应用于索引列时(或在多索引的情况下为索引列)，似乎会被忽略.

However, formatting seems to be ignored when applied to the index column (or index columns in case of multi index).

我找到了一种解决方法，到目前为止，是使用 reset_index 引入伪造的索引，然后将 index = False 传递给to_excel函数，但是该函数很好的合并功能多索引也将消失.

I've found a workaround, so far is to introduce a fake index with reset_index then pass index=False to the to_excel function but then the nice merging feature of the multi index will be gone too.

有什么想法吗?

import pandas as pd
import numpy as np

from Config import TEMP_XL_FILE

def temp():
    ' temp'
    pdf = pd.DataFrame(np.random.randn(6,4), columns=list('ABCD'))
    pdf.set_index('A', drop=True, inplace=True)
    writer = pd.ExcelWriter(TEMP_XL_FILE, engine='xlsxwriter')
    pdf.to_excel(writer, 'temp')
    workbook = writer.book
    worksheet = writer.sheets['temp']
    tempformat = workbook.add_format({'num_format': '0%', 'align': 'center'})
    worksheet.set_column(-1, 3, None, tempformat)
    writer.save()

if __name__ == '__main__':
    temp()

推荐答案

大熊猫ExcelWriter覆盖索引列中的XlsxWriter格式. 为了防止这种情况，请将熊猫header_style更改为None

The pandas ExcelWriter overwrites the XlsxWriter formats in the index columns. To prevent that, change the pandas header_style to None

header_style = {"font": {"bold": True},
                "borders": {"top": "thin",
                            "right": "thin",
                            "bottom": "thin",
                            "left": "thin"},
                "alignment": {"horizontal": "center",
                              "vertical": "top"}} 

要这样做:

import pandas.io.formats.excel

pandas.io.formats.excel.header_style = None

另请参见

• xlsxwriter不将格式应用于标题行的数据帧-Python Pandas
• 熊猫饲养:AttributeError:模块'pandas.核心"没有属性格式"

• xlsxwriter not applying format to header row of dataframe - Python Pandas
• Pandas raising: AttributeError: module 'pandas.core' has no attribute 'format'

这篇关于如何使用xlsxwriter格式化索引列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！