格式化Excel列标题以获得更好的可见性和颜色 [英] Format Excel Column header for better visibility and Color

问题描述

我浏览了许多帖子，但没有找到执行以下操作的确切方法. 很抱歉附加屏幕截图(只是为了更好的可见性)，我也会写它. 基本上看起来像-

I have gone through many posts but did not found the exact way to do the below. Sorry for attaching screenshot(Just for better visibility) as well , I will write it also. Basically it looks like -

Name_of_the_Man Address_of_Man  City
Jordan           NC             LMN

输入csv看起来像

需要的输出

我有这段代码，可以选择csv并附加为excel中的工作表.

I have this code with me that picks the csv and attach as sheet in excel.

writer = pd.ExcelWriter('final.xlsx'), engine='xlsxwriter')
for f in glob.glob(os.path.join(Path, "*.csv")):
         df = pd.read_csv(f)
         df.to_excel(writer, sheet_name=os.path.basename(f))
writer.save()

我想要我的csv文件-列标题之间的空间和颜色都很好.我已经通过此链接

I want my csv file - having good space in between and color for the column header.I have went through this link Python - change header color of dataframe and save it to excel file but it's not serving the purpose - It is coloring the sheet itself apart from column.

更新: 得到了下面的答案.也想知道这是否可能只是一个想法

Update: Got the answer below . Also wondering if that can be possible just a thought

推荐答案

您可以将 Pandas Excel输出与用户一起使用定义的标头格式和解决方案，用于按内容更改宽度:

You can use Pandas Excel output with user defined header format with solution for change width by content:

writer = pd.ExcelWriter("file.xlsx", engine='xlsxwriter')

# Convert the dataframe to an XlsxWriter Excel object. Note that we turn off
# the default header and skip one row to allow us to insert a user defined
# header. Also remove index values by index=False
df.to_excel(writer, sheet_name='Sheet1', startrow=1, header=False, index=False)

workbook  = writer.book
worksheet = writer.sheets['Sheet1']
# Add a header format.
header_format = workbook.add_format({
    'bold': True,
    'fg_color': '#ffcccc',
    'border': 1})
for col_num, value in enumerate(df.columns.values):
    worksheet.write(0, col_num, value, header_format)

    column_len = df[value].astype(str).str.len().max()
    # Setting the length if the column header is larger
    # than the max column value length
    column_len = max(column_len, len(value)) + 3
    print(column_len)
    # set the column length
    worksheet.set_column(col_num, col_num, column_len)

# Close the Pandas Excel writer and output the Excel file.
writer.save()

更改了解决方案:

writer = pd.ExcelWriter('final.xlsx'), engine='xlsxwriter')
for f in glob.glob(os.path.join(Path, "*.csv")):
         df = pd.read_csv(f)
         df.to_excel(writer, sheet_name=os.path.basename(f))

        workbook  = writer.book
        worksheet = writer.sheets[os.path.basename(f)]
        # Add a header format.
        header_format = workbook.add_format({
            'bold': True,
            'fg_color': '#ffcccc',
            'border': 1})
        for col_num, value in enumerate(df.columns.values):
            worksheet.write(0, col_num, value, header_format)
            column_len = df[value].astype(str).str.len().max()
            # Setting the length if the column header is larger
            # than the max column value length
            column_len = max(column_len, len(value)) + 3
            print(column_len)
            # set the column length
            worksheet.set_column(col_num, col_num, column_len)

writer.save()

writer = pd.ExcelWriter("file.xlsx", engine='xlsxwriter')

#skip 2 rows
df.to_excel(writer, sheet_name='Sheet1', startrow=2, header=False, index=False)

workbook  = writer.book
worksheet = writer.sheets['Sheet1']
# Add a header format.
header_format = workbook.add_format({
    'bold': True,
    'fg_color': '#ffcccc',
    'border': 1})

#create dictionary for map length of columns 
d = dict(zip(range(25), list(string.ascii_uppercase)))
#print (d)

max_len = d[len(df.columns) - 1]
print (max_len)
#C
#dynamically set merged columns in first row
worksheet.merge_range('A1:' + max_len + '1', 'This Sheet is for Personal Details')

for col_num, value in enumerate(df.columns.values):
    #write to second row
    worksheet.write(1, col_num, value, header_format)

    column_len = df[value].astype(str).str.len().max()
    column_len = max(column_len, len(value)) + 3
    worksheet.set_column(col_num, col_num, column_len)

# Close the Pandas Excel writer and output the Excel file.
writer.save()

这篇关于格式化Excel列标题以获得更好的可见性和颜色的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！