Pandasを使用してスタイル付きExcelを描画

import pandas as pd
import openpyxl

#    DataFrame
data= pd.DataFrame(data=np.random.randn(6,3),columns=["a",'b','c'])

#filename       
filename = 'test.xlsx'
writer = pd.ExcelWriter(filename,engine='openpyxl')
data.to_excel(writer, sheet_name='Sheet1',index=False)
worksheet = writer.sheets['Sheet1']

処理スタイル

#    
worksheet.freeze_panes = 'A2'
#      
worksheet.freeze_panes = 'B2'

各列のデータフォーマットを変更するには、次の手順に従います.

#       
for i in worksheet.columns:
    i[0].fill = openpyxl.styles.PatternFill("solid", fgColor="FF9933")  #       
    i[0].font = openpyxl.styles.Font(name='Calibri',size=11,bold=True,italic=False,vertAlign=None,underline='none',strike=False,color='FF000000')         
    #        
    for cell in i[1:]:
        cell.number_format = '#,##0_-'  #    
        cell.number_format = '0.00%;-0.00%' #   
        cell.number_format = '$#,##0.00;-$#,##0.00'  #    

行によるスタイルの変更:

#  
qtyindex = None
for row in worksheet.rows:    #    c  
    for j in row:
        if j.value =='c':
            qtyindex = j.col_idx-1      
    break
a = 0
for row in worksheet.rows:
    a +=1
    if qtyindex and status_index and a>1:   #      c     0 :     
        if row[qtyindex].value > 0 :  
            for cell in row:
                cell.fill = openpyxl.styles.PatternFill("solid", fgColor="FFB6C1")

各行の幅を固定します.

for i in range(1,data.shape[1]+1):           A,B...  
        str = ''
        while (not (i // 26 == 0 and i % 26 == 0)):
            temp = 25
            if (i % 26 == 0):
                str += chr(temp + 65)
            else:
                str += chr(i % 26 - 1 + 65)
            i //= 26
        writer.sheets['Sheet1'].column_dimensions[str[::-1]].width = 16
#     'A,B,C,D..'        

ファイルの保存

writer.save()