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
df = pd.read_excel(read_excel_path)
Split_item_list = df[Split_item].unique()
print(Split_item_list)
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('数据保存完毕')