260 lines
14 KiB
Python
260 lines
14 KiB
Python
from __future__ import annotations
|
||
|
||
from pathlib import Path
|
||
|
||
from openpyxl import Workbook
|
||
from openpyxl.styles import Alignment, Border, Font, PatternFill, Side
|
||
from openpyxl.utils import get_column_letter
|
||
|
||
OUTPUT_PATH = Path(__file__).resolve().parents[1] / "rules" / "finance-rules" / "公司差旅费报销规则.xlsx"
|
||
|
||
ROWS = [
|
||
(1, "北京", "北京", "", "", 500, 450, 450, 500, ""),
|
||
(2, "天津", "6 个中心城区、滨海新区、东丽区、西青区、津南区、北辰区、武清区、宝坻区、静海区、蓟县", "", "", 380, 360, 350, 380, ""),
|
||
(2, "天津", "宁河区", "", "", 320, 300, 280, 320, ""),
|
||
(3, "河北", "石家庄", "", "", 350, 330, 300, 350, ""),
|
||
(3, "河北", "张家口、秦皇岛、廊坊、承德、保定", "张家口市；秦皇岛市；承德市", "张家口市：7-9 月、11-3 月；秦皇岛市：7-8 月；承德市：7-9 月", 350, 300, 250, 350, 420),
|
||
(3, "河北", "雄安新区（不含雄县、安新县、容城县）", "", "", 450, 400, 350, 450, ""),
|
||
(3, "河北", "其他地区", "", "", 310, 290, 250, 310, ""),
|
||
(4, "山西", "太原", "", "", 350, 330, 300, 350, ""),
|
||
(4, "山西", "大同、晋城", "", "", 350, 300, 250, 350, ""),
|
||
(4, "山西", "临汾", "", "", 330, 300, 250, 330, ""),
|
||
(4, "山西", "阳泉、长治、晋中", "", "", 310, 290, 250, 310, ""),
|
||
(4, "山西", "其他地区", "", "", 240, 220, 200, 240, ""),
|
||
(5, "内蒙古", "呼和浩特", "", "", 350, 330, 300, 350, ""),
|
||
(5, "内蒙古", "海拉尔市、满洲里市、阿尔山市", "海拉尔市、满洲里市、阿尔山市", "7-9 月", 320, 300, 250, 320, 380),
|
||
(5, "内蒙古", "二连浩特市", "二连浩特市", "7-9 月", 320, 300, 250, 320, 380),
|
||
(5, "内蒙古", "额济纳市", "额济纳市", "9-10 月", 320, 300, 250, 320, 380),
|
||
(5, "内蒙古", "其他地区", "", "", 320, 300, 250, 320, ""),
|
||
(6, "辽宁", "沈阳", "", "", 350, 330, 300, 350, ""),
|
||
(6, "辽宁", "其他地区", "", "", 330, 300, 250, 330, ""),
|
||
(7, "大连", "大连", "大连", "7-9 月", 350, 300, 300, 350, 420),
|
||
(8, "吉林", "长春", "长春", "7-9 月", 350, 330, 300, 350, 420),
|
||
(8, "吉林", "吉林、延边州、长白山管理区", "吉林、延边州、长白山管理区", "7-9 月", 350, 300, 250, 350, 420),
|
||
(8, "吉林", "其他地区", "", "", 300, 280, 250, 300, ""),
|
||
(9, "黑龙江", "哈尔滨", "哈尔滨", "7-9 月", 350, 330, 300, 350, 420),
|
||
(9, "黑龙江", "牡丹江、伊春、大兴安岭地区、黑河、佳木斯", "牡丹江、伊春、大兴安岭地区、黑河、佳木斯", "6-8 月", 300, 280, 250, 300, 360),
|
||
(9, "黑龙江", "其他地区", "", "", 300, 280, 250, 300, ""),
|
||
(10, "上海", "上海", "", "", 500, 450, 450, 500, ""),
|
||
(11, "江苏", "南京", "", "", 380, 350, 350, 380, ""),
|
||
(11, "江苏", "苏州、无锡、常州、镇江", "", "", 350, 300, 250, 380, ""),
|
||
(11, "江苏", "其他地区", "", "", 350, 300, 250, 360, ""),
|
||
(12, "浙江", "杭州", "", "", 400, 350, 350, 400, ""),
|
||
(12, "浙江", "其他地区", "", "", 340, 300, 250, 340, ""),
|
||
(13, "宁波", "宁波", "", "", 350, 300, 250, 350, ""),
|
||
(14, "安徽", "合肥", "", "", 350, 330, 300, 350, ""),
|
||
(14, "安徽", "其他地区", "", "", 350, 300, 250, 350, ""),
|
||
(15, "福建", "福州", "", "", 380, 350, 300, 380, ""),
|
||
(15, "福建", "泉州、平潭综合实验区", "", "", 350, 300, 250, 380, ""),
|
||
(15, "福建", "其他地区", "", "", 350, 300, 250, 350, ""),
|
||
(16, "厦门", "厦门", "", "", 400, 380, 350, 400, ""),
|
||
(17, "江西", "南昌", "", "", 350, 330, 300, 350, ""),
|
||
(17, "江西", "其他地区", "", "", 350, 300, 250, 350, ""),
|
||
(18, "山东", "济南", "", "", 380, 350, 300, 380, ""),
|
||
(18, "山东", "烟台、威海、日照", "烟台、威海、日照", "7-9 月", 350, 300, 250, 380, 450),
|
||
(18, "山东", "淄博、枣庄、东营、潍坊、济宁、泰安", "", "", 350, 300, 250, 380, ""),
|
||
(18, "山东", "其他地区", "", "", 350, 300, 250, 360, ""),
|
||
(19, "青岛", "青岛", "青岛", "7-9 月", 350, 300, 250, 380, 450),
|
||
(20, "河南", "郑州", "", "", 380, 350, 300, 380, ""),
|
||
(20, "河南", "洛阳", "洛阳", "4-5 月上旬", 330, 300, 250, 330, 390),
|
||
(20, "河南", "其他地区", "", "", 330, 300, 250, 330, ""),
|
||
(21, "湖北", "武汉", "", "", 350, 330, 300, 350, ""),
|
||
(21, "湖北", "其他地区", "", "", 320, 300, 250, 320, ""),
|
||
(22, "湖南", "长沙", "", "", 350, 330, 300, 350, ""),
|
||
(22, "湖南", "其他地区", "", "", 330, 300, 250, 330, ""),
|
||
(23, "广东", "广州", "", "", 450, 400, 400, 450, ""),
|
||
(23, "广东", "珠海", "", "", 450, 400, 350, 450, ""),
|
||
(23, "广东", "佛山、东莞、中山、江门", "", "", 350, 300, 250, 450, ""),
|
||
(23, "广东", "其他地区", "", "", 350, 300, 250, 420, ""),
|
||
(24, "深圳", "深圳", "", "", 450, 400, 400, 450, ""),
|
||
(25, "广西", "南宁", "", "", 350, 330, 300, 350, ""),
|
||
(25, "广西", "桂林、北海", "桂林、北海", "1-2 月、7-9 月", 330, 300, 250, 330, 390),
|
||
(25, "广西", "其他地区", "", "", 330, 300, 250, 330, ""),
|
||
(26, "海南", "海口、文昌、澄迈县", "海口、文昌、澄迈县", "11-2 月", 350, 330, 310, 350, 420),
|
||
(26, "海南", "琼海、万宁、陵水县、保亭县", "琼海、万宁、陵水县、保亭县", "11-3 月", 350, 330, 310, 350, 420),
|
||
(26, "海南", "三沙、儋州、五指山、东方、安定县、屯昌县、临高县、白沙县、昌江县、乐东县、琼中县、洋浦开发区", "", "", 350, 330, 310, 350, ""),
|
||
(26, "海南", "三亚", "三亚", "10-4 月", 400, 380, 350, 400, 480),
|
||
(27, "重庆", "9 个中心城区、北部新区", "", "", 370, 350, 330, 370, ""),
|
||
(27, "重庆", "其他地区", "", "", 300, 280, 260, 300, ""),
|
||
(28, "四川", "成都", "", "", 370, 350, 330, 370, ""),
|
||
(28, "四川", "阿坝州、甘孜州", "", "", 330, 300, 250, 330, ""),
|
||
(28, "四川", "绵阳、乐山、雅安", "", "", 320, 300, 250, 320, ""),
|
||
(28, "四川", "宜宾", "", "", 300, 280, 250, 300, ""),
|
||
(28, "四川", "凉山州", "", "", 330, 300, 250, 330, ""),
|
||
(28, "四川", "德阳、遂宁、巴中", "", "", 310, 290, 250, 310, ""),
|
||
(28, "四川", "其他地区", "", "", 300, 280, 250, 300, ""),
|
||
(29, "贵州", "贵阳", "", "", 370, 350, 300, 370, ""),
|
||
(29, "贵州", "其他地区", "", "", 300, 280, 250, 300, ""),
|
||
(30, "云南", "昆明", "", "", 380, 350, 300, 380, ""),
|
||
(30, "云南", "大理州、丽江市、迪庆州、西双版纳州", "", "", 350, 300, 250, 380, ""),
|
||
(30, "云南", "其他地区", "", "", 330, 300, 250, 330, ""),
|
||
(31, "西藏", "拉萨", "拉萨", "5-10 月", 350, 330, 300, 350, 420),
|
||
(31, "西藏", "其他地区", "其他地区", "5-10 月", 300, 280, 250, 300, 360),
|
||
(32, "陕西", "西安", "", "", 350, 330, 300, 350, ""),
|
||
(32, "陕西", "榆林、延安", "", "", 300, 280, 250, 300, ""),
|
||
(32, "陕西", "杨凌区", "", "", 260, 240, 220, 260, ""),
|
||
(32, "陕西", "咸阳、宝鸡", "", "", 260, 240, 220, 260, ""),
|
||
(32, "陕西", "渭南、韩城", "", "", 260, 240, 220, 260, ""),
|
||
(32, "陕西", "其他地区", "", "", 230, 210, 200, 230, ""),
|
||
(33, "甘肃", "兰州", "", "", 350, 330, 300, 350, ""),
|
||
(33, "甘肃", "其他地区", "", "", 310, 290, 250, 310, ""),
|
||
(34, "青海", "西宁", "西宁", "6-9 月", 350, 330, 300, 350, 420),
|
||
(34, "青海", "玉树州", "玉树州", "5-9 月", 300, 280, 250, 300, 360),
|
||
(34, "青海", "果洛州", "", "", 300, 280, 250, 300, ""),
|
||
(34, "青海", "海北州、黄南州", "海北州、黄南州", "5-9 月", 250, 230, 210, 250, 300),
|
||
(34, "青海", "海东、海南州", "海东、海南州", "5-9 月", 250, 230, 210, 250, 300),
|
||
(34, "青海", "海西州", "海西州", "5-9 月", 200, 200, 200, 200, 240),
|
||
(35, "宁夏", "银川", "", "", 350, 330, 300, 350, ""),
|
||
(35, "宁夏", "其他地区", "", "", 330, 300, 250, 330, ""),
|
||
(36, "新疆", "乌鲁木齐", "", "", 350, 330, 300, 350, ""),
|
||
(36, "新疆", "石河子、克拉玛依、昌吉州、伊犁州、阿勒泰地区、博州、吐鲁番、哈密地区、巴州、和田地区", "", "", 340, 300, 250, 340, ""),
|
||
(36, "新疆", "克州", "", "", 320, 300, 250, 320, ""),
|
||
(36, "新疆", "喀什地区", "", "", 300, 280, 250, 300, ""),
|
||
(36, "新疆", "阿克苏地区", "", "", 300, 280, 250, 300, ""),
|
||
(36, "新疆", "塔城地区", "", "", 300, 280, 250, 300, ""),
|
||
(37, "港澳台", "香港、澳门、台湾", "", "", 450, 400, 350, 500, ""),
|
||
(38, "国外", "国外", "", "", 700, 600, 500, 700, ""),
|
||
]
|
||
|
||
|
||
def build_workbook() -> Workbook:
|
||
workbook = Workbook()
|
||
worksheet = workbook.active
|
||
worksheet.title = "差旅住宿费标准"
|
||
headers = [
|
||
"序号",
|
||
"地区",
|
||
"地区（城市）",
|
||
"旺季地区",
|
||
"旺季期间",
|
||
"公司级管理人员、高层经理（P7及以上）",
|
||
"中层经理、基层经理（P4-P6、外聘专家）",
|
||
"其他员工",
|
||
"超标限额",
|
||
"旺季超标限额",
|
||
]
|
||
|
||
worksheet.append(["差旅住宿费标准"])
|
||
worksheet.merge_cells(start_row=1, start_column=1, end_row=1, end_column=len(headers))
|
||
worksheet["A1"].font = Font(bold=True, size=16, color="FFFFFF")
|
||
worksheet["A1"].fill = PatternFill("solid", fgColor="1F4E78")
|
||
worksheet["A1"].alignment = Alignment(horizontal="center")
|
||
worksheet.append(headers)
|
||
for row in ROWS:
|
||
worksheet.append(row)
|
||
|
||
header_fill = PatternFill("solid", fgColor="D9EAF7")
|
||
thin = Side(style="thin", color="B7C9D6")
|
||
for cell in worksheet[2]:
|
||
cell.font = Font(bold=True)
|
||
cell.fill = header_fill
|
||
cell.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)
|
||
cell.border = Border(left=thin, right=thin, top=thin, bottom=thin)
|
||
for row in worksheet.iter_rows(min_row=3, max_row=worksheet.max_row):
|
||
for cell in row:
|
||
cell.alignment = Alignment(vertical="center", wrap_text=True)
|
||
cell.border = Border(left=thin, right=thin, top=thin, bottom=thin)
|
||
for cell in row[5:]:
|
||
cell.alignment = Alignment(horizontal="center", vertical="center")
|
||
|
||
worksheet.freeze_panes = "A3"
|
||
worksheet.auto_filter.ref = f"A2:J{worksheet.max_row}"
|
||
widths = [8, 12, 42, 28, 28, 22, 26, 12, 12, 14]
|
||
for index, width in enumerate(widths, start=1):
|
||
worksheet.column_dimensions[get_column_letter(index)].width = width
|
||
worksheet.row_dimensions[1].height = 26
|
||
worksheet.row_dimensions[2].height = 42
|
||
for index in range(3, worksheet.max_row + 1):
|
||
worksheet.row_dimensions[index].height = 36
|
||
|
||
subsidy_sheet = workbook.create_sheet("出差补助标准")
|
||
subsidy_headers = [
|
||
"补助类型",
|
||
"项目",
|
||
"港澳台",
|
||
"直辖市/特区",
|
||
"西藏",
|
||
"新疆-乌鲁木齐",
|
||
"新疆-其他",
|
||
"其他地区",
|
||
"国外",
|
||
]
|
||
subsidy_rows = [
|
||
("伙食补助", "自行解决餐食", 75, 65, 65, 55, 55, 55, 140),
|
||
("基本补助", "基本出差补贴", 35, 35, 105, 75, 135, 35, 35),
|
||
("合计", "", 110, 100, 170, 130, 190, 90, 175),
|
||
]
|
||
subsidy_sheet.append(["出差补助标准"])
|
||
subsidy_sheet.merge_cells(start_row=1, start_column=1, end_row=1, end_column=len(subsidy_headers))
|
||
subsidy_sheet["A1"].font = Font(bold=True, size=16, color="FFFFFF")
|
||
subsidy_sheet["A1"].fill = PatternFill("solid", fgColor="1F4E78")
|
||
subsidy_sheet["A1"].alignment = Alignment(horizontal="center")
|
||
subsidy_sheet.append(subsidy_headers)
|
||
for row in subsidy_rows:
|
||
subsidy_sheet.append(row)
|
||
subsidy_sheet.append(["备注", "注 1：新疆分公司同事出差至乌鲁木齐外的其他新疆地区，基本补助标准为 95 元。"])
|
||
subsidy_sheet.append(["备注", "注 2：西藏分公司同事出差至拉萨市外的其他西藏地区，基本补助标准为 35 元。"])
|
||
subsidy_sheet.merge_cells(start_row=6, start_column=2, end_row=6, end_column=len(subsidy_headers))
|
||
subsidy_sheet.merge_cells(start_row=7, start_column=2, end_row=7, end_column=len(subsidy_headers))
|
||
for cell in subsidy_sheet[2]:
|
||
cell.font = Font(bold=True)
|
||
cell.fill = header_fill
|
||
cell.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)
|
||
cell.border = Border(left=thin, right=thin, top=thin, bottom=thin)
|
||
for row in subsidy_sheet.iter_rows(min_row=3, max_row=7):
|
||
for cell in row:
|
||
cell.alignment = Alignment(vertical="center", wrap_text=True)
|
||
cell.border = Border(left=thin, right=thin, top=thin, bottom=thin)
|
||
for cell in subsidy_sheet[5]:
|
||
cell.font = Font(bold=True)
|
||
cell.fill = PatternFill("solid", fgColor="E2F0D9")
|
||
subsidy_sheet.freeze_panes = "A3"
|
||
subsidy_sheet.auto_filter.ref = "A2:I5"
|
||
subsidy_widths = [14, 18, 12, 16, 12, 18, 16, 14, 12]
|
||
for index, width in enumerate(subsidy_widths, start=1):
|
||
subsidy_sheet.column_dimensions[get_column_letter(index)].width = width
|
||
subsidy_sheet.row_dimensions[1].height = 26
|
||
subsidy_sheet.row_dimensions[2].height = 36
|
||
for index in range(3, 8):
|
||
subsidy_sheet.row_dimensions[index].height = 28
|
||
|
||
source_sheet = workbook.create_sheet("来源说明")
|
||
source_sheet.append(["来源文件", "页码", "说明"])
|
||
source_sheet.append(
|
||
[
|
||
"远光软件2026费用报销说明手册.pdf",
|
||
"第 13-19 页",
|
||
"依据 PDF 附件 3《差旅住宿费标准》整理为默认支撑表。",
|
||
]
|
||
)
|
||
source_sheet.append(
|
||
[
|
||
"远光软件2026费用报销说明手册.pdf",
|
||
"第 20 页",
|
||
"依据 PDF 附件 4《出差补助标准》整理为默认支撑表。",
|
||
]
|
||
)
|
||
for row in source_sheet.iter_rows():
|
||
for cell in row:
|
||
cell.alignment = Alignment(wrap_text=True, vertical="center")
|
||
cell.border = Border(left=thin, right=thin, top=thin, bottom=thin)
|
||
for cell in source_sheet[1]:
|
||
cell.font = Font(bold=True)
|
||
cell.fill = header_fill
|
||
source_sheet.column_dimensions["A"].width = 34
|
||
source_sheet.column_dimensions["B"].width = 14
|
||
source_sheet.column_dimensions["C"].width = 56
|
||
return workbook
|
||
|
||
|
||
def main() -> None:
|
||
OUTPUT_PATH.parent.mkdir(parents=True, exist_ok=True)
|
||
workbook = build_workbook()
|
||
workbook.save(OUTPUT_PATH)
|
||
print(OUTPUT_PATH)
|
||
print(f"rows={len(ROWS)}")
|
||
|
||
|
||
if __name__ == "__main__":
|
||
main()
|