Python E化-Excel_split

Excel_split

import pymysql
import xlwt
from openpyxl import load_workbook
from openpyxl.styles import Font, colors, Alignment,PatternFill,numbers,Border,Side

print("拆分后的总文件不能含有公式")
excel_name=input('请输入input文件夹中的文件名（需以.xlsx结尾），比如“testpaper.xlsx”   ')
print("输入成功，拆分后的总文件请见“output")
read_excel_path=r"D:\Python_E\others\Excel_split\input\{}".format(excel_name)
write_excel_path=r"D:\Python_E\others\Excel_split\output\{}".format(excel_name)
ExcelWriter_path=r"D:\Python_E\others\Excel_split\output"
wb = load_workbook(read_excel_path)
sheet = wb.active
rows=sheet.max_row
columns=sheet.max_column



#### 變更格式的函數要放在最前面，方便引用
def change_Format(sheet):
    rows=sheet.max_row
    columns=sheet.max_column

    #设定第六列的时间格式
    for j in range(2,rows+1):
        sheet.cell(row=j, column=7).number_format = 'yyyy/mm/dd'

    # 调整边框
    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'))
    for j in range(1,rows+1):
        for i in range(1,columns+1):
            sheet.cell(row=j, column=i).border = border

    # 调整表格字体格式
    for j in range(1,rows+1):
        for i in range(1,columns+1):
            sheet.cell(row=j, column=i).font = Font(name="Arial",size=10,color=colors.BLACK,bold=False)
            sheet.cell(row=j, column=i).alignment = Alignment(horizontal='left', vertical='center',wrap_text = False)


    # 调整标题的格式和对齐
    for i in range(1,columns+1):
        # 调整标题的格式
        sheet.cell(row=1, column=i).font = Font(name="Arial",size=10,color=colors.WHITE,bold=True)
        # 设定单元格对齐
        sheet.cell(row=1, column=i).alignment = Alignment(horizontal='left', vertical='center',wrap_text = True)
    
    # 调整标题的颜色
    for i in range(1,columns+1):
        sheet.cell(row=1, column=i).fill = PatternFill(fill_type='solid', fgColor="00B2EE")

    #设定第一行的行高
    sheet.row_dimensions[1].height = 50

    #设置列宽
    size=10
    size2=35
    sheet.column_dimensions['A'].width = size
    sheet.column_dimensions['B'].width = size
    sheet.column_dimensions['C'].width = size
    sheet.column_dimensions['D'].width = size
    sheet.column_dimensions['E'].width = size
    sheet.column_dimensions['F'].width = size
    sheet.column_dimensions['G'].width = size
    sheet.column_dimensions['H'].width = size
    sheet.column_dimensions['I'].width = size
    sheet.column_dimensions['J'].width = size
    sheet.column_dimensions['K'].width = size
    sheet.column_dimensions['L'].width = size
    sheet.column_dimensions['M'].width = size
    sheet.column_dimensions['N'].width = size
    sheet.column_dimensions['O'].width = size
    sheet.column_dimensions['P'].width = size
    sheet.column_dimensions['Q'].width = size
    sheet.column_dimensions['R'].width = size
    sheet.column_dimensions['S'].width = size
    sheet.column_dimensions['T'].width = size
    sheet.column_dimensions['U'].width = size
    sheet.column_dimensions['V'].width = size
    sheet.column_dimensions['W'].width = size
    sheet.column_dimensions['X'].width = size
    sheet.column_dimensions['Y'].width = size
    sheet.column_dimensions['Z'].width = size




def Split_excel(read_excel_path,ExcelWriter_path,Split_item):
    #''''''将总报表拆分成子报表''''''''
    import pandas as pd
    # 读取待拆分的Excel文件 
    df = pd.read_excel(read_excel_path)
    # 获取拆分条件：去重
    Split_item_list = df[Split_item].unique()
    print(Split_item_list)
    # 按拆分条件分别保存新的Excel文件
    for Split_mark in Split_item_list:
        training_excel_file = df[df[Split_item] == Split_mark]
        training_excel_file.to_excel(ExcelWriter_path+'\\'+Split_mark +'.xlsx', index=False)
    print('拆分完成！')

    #更改子报表的格式，删除最后的多余栏位
    for Split_mark in Split_item_list:
        x_wb= load_workbook(ExcelWriter_path+'\\'+Split_mark +'.xlsx')
        x_sheet = x_wb.active
        x_sheet.title=Split_mark
        change_Format(x_sheet)
        
        x_wb.save(ExcelWriter_path+'\\'+Split_mark +'.xlsx')
        x_wb.close()
    print('格式调整完成！')
    print('栏位删除完成！')


Split_item=input('請輸入拆分的字段名  ')
Split_excel(read_excel_path,ExcelWriter_path,Split_item)



wb.save(read_excel_path)
wb.close()
print('数据保存完毕')