<jupyter_start><jupyter_text>測試資料<jupyter_code># 表格資料title
data = [
    {"route_id": "0001", "route_desc": "路線1", "num_of_people": 100, 
     "origin_amt": 1000, "act_amt": 600, "subsidy_amt": 400, "avg_subsidy_amt_by_people": 4},
    {"route_id": "0002", "route_desc": "路線2", "num_of_people": 100, 
     "origin_amt": 1000, "act_amt": 600, "subsidy_amt": 400, "avg_subsidy_amt_by_people": 4},
    {"route_id": "0003", "route_desc": "路線3", "num_of_people": 100, 
     "origin_amt": 1000, "act_amt": 600, "subsidy_amt": 400, "avg_subsidy_amt_by_people": 4},
]

df = pd.DataFrame(data, columns=["route_id", "route_desc", "num_of_people", "origin_amt", 
                                 "act_amt", "subsidy_amt", "avg_subsidy_amt_by_people"])
df
df.columns=["路線\n編號", "路線\n名稱", "使用轉乘優惠\n人數", "原始票收金額", "實際交易金額", 
            "優惠補貼金額", "平均每人\n優惠金額"]<jupyter_output><empty_output><jupyter_text>EXCEL輸出格式產生<jupyter_code>wb = Workbook()
ws = wb.active
# 設定print area
# https://openpyxl.readthedocs.io/en/stable/print_settings.html
ws.print_options.horizontalCentered = True
# ws.print_options.verticalCentered = True
ws.print_area = 'A1:G10'
# 設定輸出表格字體參數
table_title_ft = Font(name='標楷體', color='000000', size=14, bold=True)
table_ft = Font(name='標楷體', color='000000', size=14)

# 表格格線設定
table_border = Border(left=Side(border_style='thin', color='000000'),
                      right=Side(border_style='thin', color='000000'),
                      top=Side(border_style='thin', color='000000'),
                      bottom=Side(border_style='thin', color='000000'))
ws.insert_rows(1)  # 在第一行插入一行
ws.merge_cells('A1:G1')  # 欄位
ws["A1"] = '表1 ○年○月○○客運公司○○○公車轉乘第一段票免費補貼金額申請表'
ws["A1"].font = table_title_ft
ws["A1"].alignment = Alignment(horizontal='center')
# DataFrame資料填入sheet row
for row in dataframe_to_rows(df, index=False, header=True):
    ws.append(row)
# Table加入格線
rows = ws["A3:G6"]
for row in rows:
    for cell in row:
        cell.border = table_border
        cell.font = table_ft
# 自動指定欄位寬度
# https://stackoverflow.com/questions/13197574/openpyxl-adjust-column-width-size
# from openpyxl.utils import get_column_letter

# column_widths = []
# for row in data:
#     for i, cell in enumerate(row):
#         if len(column_widths) > i:
#             if len(cell) > column_widths[i]:
#                 column_widths[i] = len(cell)
#         else:
#             column_widths += [len(cell)]

# for i, column_width in enumerate(column_widths):
#     ws.column_dimensions[get_column_letter(i+1)].width = column_width
# 直接指定欄位寬度
# https://stackoverflow.com/questions/53906532/is-it-possible-to-change-the-column-width-using-openpyxl/53906585
ws.column_dimensions['A'].width = 10
ws.column_dimensions['B'].width = 10
ws.column_dimensions['C'].width = 23
ws.column_dimensions['D'].width = 23
ws.column_dimensions['E'].width = 23
ws.column_dimensions['F'].width = 23
ws.column_dimensions['G'].width = 23
# Table欄位title style設定
table_align_style = Alignment(wrapText=True, horizontal='center', vertical='center')

for rows in ws['A3':'G3']:
    for cell in rows:
        cell.alignment = table_align_style
# ws["A":"G"].alignment = Alignment(wrapText=True, horizontal='center')
# ws['A1'].alignment = Alignment(wrapText=True)
# rows = sheet["A1:C3"]
# for row in rows:
#     for cell in row:
#         cell.border = border
wb.save("report_01_output.xlsx")<jupyter_output><empty_output>