import pandas as pd from openpyxl import Workbook from openpyxl.styles import Font, PatternFill, Alignment, Border, Side from openpyxl.utils.dataframe import dataframe_to_rows from openpyxl.worksheet.datavalidation import DataValidation import datetime from io import BytesIO def create_accounts_receivable_template(): """创建应收应付往来账表格模板""" # 创建工作簿 wb = Workbook() # 删除默认的工作表 wb.remove(wb.active) # 定义样式 header_font = Font(bold=True, color="FFFFFF") header_fill = PatternFill(start_color="366092", end_color="366092", fill_type="solid") border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin')) center_align = Alignment(horizontal='center', vertical='center') # 1. 创建《客户/供应商信息表》 ws_info = wb.create_sheet("客户供应商信息表", 0) info_headers = ['序号', '客户/供应商编号', '类型', '单位全称', '联系人', '电话', '地址', '备注'] info_data = [ [1, 'C0001', '客户', 'XX科技有限公司', '张经理', '138-XXXX-XXXX', '北京市海淀区XX路', '核心客户'], [2, 'S0001', '供应商', 'YY原材料厂', '李厂长', '139-XXXX-XXXX', '上海市浦东新区XX工业区', '月结30天'], [3, 'C0002', '客户', 'ZZ贸易公司', '王总', '136-XXXX-XXXX', '广州市天河区XX大厦', '新客户'], [4, 'S0002', '供应商', 'ABC包装材料', '赵经理', '137-XXXX-XXXX', '深圳市宝安区XX园区', '季度结算'] ] # 写入表头 for col, header in enumerate(info_headers, 1): cell = ws_info.cell(row=1, column=col, value=header) cell.font = header_font cell.fill = header_fill cell.border = border cell.alignment = center_align # 写入数据 for row_idx, row_data in enumerate(info_data, 2): for col_idx, value in enumerate(row_data, 1): cell = ws_info.cell(row=row_idx, column=col_idx, value=value) cell.border = border # 设置列宽 column_widths = [8, 16, 10, 20, 10, 15, 20, 15] for i, width in enumerate(column_widths, 1): ws_info.column_dimensions[chr(64 + i)].width = width # 2. 创建《应收账款明细表》 ws_ar = wb.create_sheet("应收账款明细表", 1) ar_headers = ['序号', '发票/单据号', '业务日期', '客户编号', '客户名称', '摘要', '应收金额', '已收金额', '余额', '到期日', '账龄（天）', '状态', '备注'] # 写入表头 for col, header in enumerate(ar_headers, 1): cell = ws_ar.cell(row=1, column=col, value=header) cell.font = header_font cell.fill = header_fill cell.border = border cell.alignment = center_align # 示例数据 today = datetime.datetime.now() ar_data = [ [1, 'FP2024001', today.replace(day=1), 'C0001', 'XX科技有限公司', '销售A产品100套', 50000, 50000, 0, today.replace(day=31), 0, '已结清', '银行转账'], [2, 'FP2024002', today.replace(day=5), 'C0002', 'ZZ贸易公司', '提供B服务', 30000, 10000, 20000, today.replace(month=today.month+1, day=4), (today - today.replace(month=today.month+1, day=4)).days, '未结清', '部分收款'], [3, 'FP2024003', today.replace(day=10), 'C0001', 'XX科技有限公司', '销售C产品50套', 25000, 0, 25000, today.replace(month=today.month+1, day=9), (today - today.replace(month=today.month+1, day=9)).days, '未结清', '待收款'] ] # 写入数据 for row_idx, row_data in enumerate(ar_data, 2): for col_idx, value in enumerate(row_data, 1): cell = ws_ar.cell(row=row_idx, column=col_idx, value=value) cell.border = border # 设置公式（余额列） for row in range(2, len(ar_data) + 2): ws_ar.cell(row=row, column=9).value = f'=G{row}-H{row}' # 设置列宽 ar_widths = [8, 15, 12, 12, 20, 20, 12, 12, 12, 12, 12, 10, 15] for i, width in enumerate(ar_widths, 1): ws_ar.column_dimensions[chr(64 + i)].width = width # 添加数据验证（客户编号下拉菜单） dv_ar = DataValidation(type="list", formula1='"C0001,C0002"') dv_ar.add('D2:D100') ws_ar.add_data_validation(dv_ar) # 状态列数据验证 dv_status = DataValidation(type="list", formula1='"未结清,已结清,部分结清"') dv_status.add('L2:L100') ws_ar.add_data_validation(dv_status) # 3. 创建《应付账款明细表》 ws_ap = wb.create_sheet("应付账款明细表", 2) ap_headers = ['序号', '发票/单据号', '业务日期', '供应商编号', '供应商名称', '摘要', '应付金额', '已付金额', '余额', '到期日', '账龄（天）', '状态', '备注'] # 写入表头 for col, header in enumerate(ap_headers, 1): cell = ws_ap.cell(row=1, column=col, value=header) cell.font = header_font cell.fill = header_fill cell.border = border cell.alignment = center_align # 示例数据 ap_data = [ [1, 'GY2024001', today.replace(day=2), 'S0001', 'YY原材料厂', '采购原材料一批', 80000, 50000, 30000, today.replace(month=today.month+1, day=1), (today - today.replace(month=today.month+1, day=1)).days, '未结清', ''], [2, 'GY2024002', today.replace(day=8), 'S0002', 'ABC包装材料', '采购包装材料', 15000, 0, 15000, today.replace(month=today.month+1, day=7), (today - today.replace(month=today.month+1, day=7)).days, '未结清', ''] ] # 写入数据 for row_idx, row_data in enumerate(ap_data, 2): for col_idx, value in enumerate(row_data, 1): cell = ws_ap.cell(row=row_idx, column=col_idx, value=value) cell.border = border # 设置公式（余额列） for row in range(2, len(ap_data) + 2): ws_ap.cell(row=row, column=9).value = f'=G{row}-H{row}' # 设置列宽 for i, width in enumerate(ar_widths, 1): ws_ap.column_dimensions[chr(64 + i)].width = width # 添加数据验证 dv_ap = DataValidation(type="list", formula1='"S0001,S0002"') dv_ap.add('D2:D100') ws_ap.add_data_validation(dv_ap) dv_status_ap = DataValidation(type="list", formula1='"未结清,已结清,部分结清"') dv_status_ap.add('L2:L100') ws_ap.add_data_validation(dv_status_ap) # 4. 创建《往来账龄分析汇总表》 ws_summary = wb.create_sheet("往来账龄分析汇总表", 3) # 应收账款汇总部分 ws_summary.merge_cells('A1:B1') ws_summary['A1'] = '应收账款汇总' ws_summary['A1'].font = Font(bold=True, size=14) ws_summary['A1'].alignment = center_align summary_ar_headers = ['项目', '金额'] summary_ar_data = [ ['应收账款总额', '=SUM(应收账款明细表!G:G)'], ['已收款总额', '=SUM(应收账款明细表!H:H)'], ['当前应收账款余额', '=SUM(应收账款明细表!I:I)'] ] # 写入应收账款汇总表头和数据 for col, header in enumerate(summary_ar_headers, 1): cell = ws_summary.cell(row=2, column=col, value=header) cell.font = header_font cell.fill = header_fill cell.border = border cell.alignment = center_align for row_idx, row_data in enumerate(summary_ar_data, 3): for col_idx, value in enumerate(row_data, 1): cell = ws_summary.cell(row=row_idx, column=col_idx, value=value) cell.border = border # 应付账款汇总部分 ws_summary.merge_cells('D1:E1') ws_summary['D1'] = '应付账款汇总' ws_summary['D1'].font = Font(bold=True, size=14) ws_summary['D1'].alignment = center_align summary_ap_headers = ['项目', '金额'] summary_ap_data = [ ['应付账款总额', '=SUM(应付账款明细表!G:G)'], ['已付款总额', '=SUM(应付账款明细表!H:H)'], ['当前应付账款余额', '=SUM(应付账款明细表!I:I)'] ] # 写入应付账款汇总表头和数据 for col, header in enumerate(summary_ap_headers, 1): cell = ws_summary.cell(row=2, column=col+3, value=header) cell.font = header_font cell.fill = header_fill cell.border = border cell.alignment = center_align for row_idx, row_data in enumerate(summary_ap_data, 3): for col_idx, value in enumerate(row_data, 1): cell = ws_summary.cell(row=row_idx, column=col_idx+3, value=value) cell.border = border # 设置列宽 summary_widths = [20, 15, 5, 20, 15] for i, width in enumerate(summary_widths, 1): ws_summary.column_dimensions[chr(64 + i)].width = width # 保存到BytesIO对象 output = BytesIO() wb.save(output) output.seek(0) return output # 生成Excel文件 excel_file = create_accounts_receivable_template() # 保存为文件（在实际部署中，您可能希望直接提供下载） with open("应收应付往来账管理系统.xlsx", "wb") as f: f.write(excel_file.getvalue()) print("Excel文件已生成：应收应付往来账管理系统.xlsx")