1 import os
2
3 import pandas as pd
4 # 创建MySQL连接
5 from openpyxl import load_workbook
6 from openpyxl.styles import Alignment
7 from openpyxl.worksheet.datavalidation import DataValidation
8
9 # 输入和输出文件路径
10 input_file = r'D:\\tmp_files\\20241011\\input\\渠道店铺信息批量导入模板0925.xlsx'
11 # input_file = r'D:\\tmp_files\\20241011\\input\\渠道店铺信息批量导入模板-增量更新 (3.3).xlsx'
12 output_dir = r'D:\\tmp_files\\20241011\\out'
13 # 每个文件的行数
14 rows_per_file = 8000
15 # 读取输入文件
16 df = pd.read_excel(input_file)
17 # 总行数
18 total_rows = len(df)
19 # 计算需要的文件数
20 num_files = (total_rows // rows_per_file) + (1 if total_rows % rows_per_file != 0 else 0)
21 print(f"{input_file}\n共计{total_rows}行数据,按每个文件{rows_per_file}行数据,需要拆分成{num_files}个文件。")
22 # 创建输出目录如果不存在的话
23 os.makedirs(output_dir, exist_ok=True)
24 # 拆分并保存文件
25 for i in range(num_files):
26 # 生成输出文件路径
27 output_file = os.path.join(output_dir, f'渠道店铺信息批量导入模板0925_part{i + 1}.xlsx')
28 start_row = i * rows_per_file
29 end_row = min(start_row + rows_per_file, total_rows)
30 print(f'{output_file} >> {start_row}行到{end_row}行;')
31 # 获取当前文件的 DataFrame
32 df_split = df.iloc[start_row:end_row]
33 df_split.to_excel(output_file, index=False)
34 wb = load_workbook(output_file)
35 ws = wb.active
36 for cell in ws[1]:
37 cell.alignment = Alignment(wrap_text=True, horizontal='center', vertical='center')
38 ws.row_dimensions[1].height = 144
39 # 定义数据验证规则（例如，只允许输入列表中的值）
40 # dv2 = DataValidation(type="list", formula1='""', showErrorMessage=True)
41 dv1 = DataValidation(type="list", formula1='"京东小时达,美团闪购,抖音,线下,Apple授权专营店小程序,企业购 Apple 授权专营店,饿了么,高德地图,天猫,京东自营"',
42 showErrorMessage=True)
43 dv2 = DataValidation(type="list", formula1='"自主收银,商场收银"', showErrorMessage=True)
44 dv3 = DataValidation(type="list", formula1='"税控盘票,全电票"', showErrorMessage=True)
45 dv4 = DataValidation(type="list", formula1='"商场开票,分公司,子母公司,子公司的分公司,关联公司的分公司,关联公司,同一法人,拥有共同持股人,个人独资,个体"',
46 showErrorMessage=True)
47 dv1_range = f"B2:B{rows_per_file}"
48 dv2_range = f"C2:C{rows_per_file}"
49 dv3_range = f"D2:D{rows_per_file}"
50 dv4_range = f"T2:T{rows_per_file}"
51 dv1.add(dv1_range)
52 dv2.add(dv2_range)
53 dv3.add(dv3_range)
54 dv4.add(dv4_range)
55 # 将数据验证添加到工作表
56 ws.add_data_validation(dv1)
57 ws.add_data_validation(dv2)
58 ws.add_data_validation(dv3)
59 ws.add_data_validation(dv4)
60 wb.save(output_file)
61 print('All files have been saved successfully.')
