[Python] XlsxWriter - Excel 다루기

 

Pandas - Dataframe과 함께 사용하기

writer = pd.ExcelWriter(out_file, engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1', freeze_panes = (1, 0), index=False)

workbook  = writer.book
worksheet = writer.sheets['Sheet1']
worksheet.set_zoom(80) # zoom 설정
# row, col, width, format
worksheet.set_column(0, 0, 10, workbook.add_format({'num_format': '#,##0', 'border': 1, 'align': 'center', 'text_wrap': True}))

(max_row, max_col) = df.shape
# 필터 추가
worksheet.autofilter(0, 0, max_row, 5)

# 3열에 필터 걸기
worksheet.filter_column_list(3, ['one', 'two'])

writer.save()

 

 

How to Auto-Adjust the Width of Excel Columns with Pandas ExcelWriter

https://towardsdatascience.com/how-to-auto-adjust-the-width-of-excel-columns-with-pandas-excelwriter-60cee36e175e

How to Auto-Adjust the Width of Excel Columns with Pandas ExcelWriter

 

writer = pd.ExcelWriter('test_file.xlsx') 
df.to_excel(writer, sheet_name='my_analysis', index=False, na_rep='NaN')

# Auto-adjust columns' width
for column in df:
    column_width = max(df[column].astype(str).map(len).max(), len(column))
    col_idx = df.columns.get_loc(column)
    writer.sheets['my_analysis'].set_column(col_idx, col_idx, column_width)

writer.save()

 

 

 

Conditional Formatting

worksheet.conditional_format('B3:K12', {'type':     'cell',
                                        'criteria': '>=',
                                        'value':    50,
                                        'format':   format1})

worksheet.conditional_format('B3:K12', {'type':     'cell',
                                        'criteria': '<',
                                        'value':    50,
                                        'format':   format2})

 

(first_row, first_col, last_row, last_col) 형식으로 써도됨

worksheet.conditional_format(0, 0, 4, 1, {...})
worksheet.conditional_format('B1',       {...})
worksheet.conditional_format('C1:E5',    {...})

 

# 전체 데이터 있는 부분에 border 추가 하고싶은데 어떻게 할지 몰라서 no_blanks, blanks 둘다 적용함
worksheet.conditional_format(0, 0, max_row, max_col - 1, {'type': 'no_blanks', 'format': workbook.add_format({'border': 1})})
worksheet.conditional_format(0, 0, max_row, max_col - 1, {'type': 'blanks', 'format': workbook.add_format({'border': 1})})

 

https://xlsxwriter.readthedocs.io/working_with_conditional_formats.html#working-with-conditional-formats

Working with Conditional Formatting — XlsxWriter Documentation