目录
使用说明
功能
1、一个xlsx文件,可以选择按列拆分成多个文件
2、一个xlsx文件,可以选择按列拆分成一个文件多个sheet,每个分组一个sheet
使用说明:
1、输入文件名改为"input.xlsx",第一行必须为列名称,不要留空行
2、将“mysplit_v7.exe”和“input.xlsx”放到同一个目录中
3、双击“mysplit_v7.exe”运行,可以按需选择列,来拆分多个文件,
4、运行结果保存在当目录新建一个文件夹yyyymmddHHMMSS
注意:
1、若选择列有空值会保存到“空值.xlsx”或者名字为“空值”的sheet
2、输出文件首行加粗,第一列为序号,顺序从1开始
3、如果有很长的数据,拆分后自动转成科学计数格式,解决办法:在该列最后一行随便输入一些字符:aa,筛选列下面流空
代码
# -*- coding:utf-8 -*-
#!/usr/bin/env python
import os
import datetime
import pandas as pd
from openpyxl.styles import Font
from openpyxl import load_workbook
# cur_dir = r"G:\python\project_comm\mysplit"
cur_dir=os.getcwd()
filename="input.xlsx"
fullfilename = cur_dir + os.sep+ filename
def init():
nowtime = datetime.datetime.now()
strnowtime = nowtime.strftime("%Y%m%d%H%M%S")
# print(strnowtime)
ifilepath = cur_dir + os.sep + strnowtime
if not os.path.exists(ifilepath):
os.makedirs(ifilepath)
return ifilepath
def ShezhiYanshi(filename):
book = load_workbook(filename)
sheet = book.active
sheet.cell(1, 1, '序号')
# 设置第一行和第一列的字体不加粗
font = Font(bold=False)
for cell in sheet['1:1']:
# print(cell)
cell.font = font
cell.border = None
for cell in sheet['A:A']:
cell.font = font
cell.border = None
book.save(filename)
def ShezhiYanshi2(filename):
workbook = load_workbook(filename)
# font=workbook.add_format({'border': 1})
for sheet in workbook.worksheets:
sheet.cell(1, 1, '序号')
# 设置第一列的字体不加粗
for cell in sheet['A:A']:
cell.font = None
cell.border = None
font = Font(bold=True)
# 第一行
for cell in sheet['1:1']:
# print(cell)
cell.font = font
cell.border = None
workbook.save(filename)
def HuiZongBiaoGe4(ifilepath):
if not os.path.exists(fullfilename):
print('文件[%s]不存在,请确认!'%(fullfilename))
print('正在读取文件[%s]......\n' %(fullfilename))
data = pd.read_excel(fullfilename, sheet_name=0, keep_default_na=False)
# print(data .columns)
# print(data .columns.values)
# print(type(data .columns.values))
cols=data.columns.values
print("列名称:")
for i in range(0,len(cols)):
print(i+1,cols[i])
prompts=str(r'请选择要拆分的列[1 - '+str(len(cols))+r']: ')
cftype = 1
while True:
try:
num = int(input(prompts))
except ValueError:
print("Not an integer! Please enter an integer.")
continue
else:
if num<1 or num > len(cols):
print("Please enter an integer. from 1 to %d" %(len(cols)+1))
continue
col_1_name = data.columns[num - 1]
prompts1=r'请选择:1、结果输出到一个文件每个分组一个sheet 2、每个分组新建一个文件。[1,2]: '
try:
cftype = int(input(prompts1))
except ValueError:
print("Not an integer! Please enter an integer.")
continue
else:
if cftype != 1 and cftype != 2:
print("Please enter an integer. from 1 or 2")
continue
prompts2=r'开始按列第'+str(num)+r'列:['+str(col_1_name)+r']拆分? (Y/N): '
yn = input(prompts2)
if not yn in('Y','y'):
continue
break
col_1_name = data.columns[num-1]
print('开始按列第%d列:[%s]拆分......' % (num,col_1_name))
print('保存目录:[%s]' % (cur_dir))
grp1=data.groupby(col_1_name)
# print(type(grp1))
# print(len(grp1))
#2、每个分组新建一个文件
if cftype == 2:
i=1
for name,group in grp1:
# print(name)
# print(group)
# print(type(group))
name=str(name)
print('正在拆分[%d-%d][%s][总共%d行]......' % (len(grp1), i, name,len(group)))
# 重新生成各分组的序号
group = group.reset_index(drop=True)
group.index += 1
tmppd = pd.DataFrame(group)
if name !='':
filename=cur_dir + os.sep + name + ".xlsx"
else:
filename=cur_dir + os.sep + '空值' + ".xlsx"
tmppd.to_excel(filename, engine='openpyxl')
i=i+1
ShezhiYanshi2(filename)
print('按列第%d列:[%s]拆分完成。\n保存目录:[%s]' % (num,col_1_name,cur_dir))
#1、结果输出到一个文件每个分组一个sheet
elif cftype == 1:
i = 1
# 生成的文件
filename = cur_dir+os.sep+"output.xlsx"
# 将分组数据写入文件
writer = pd.ExcelWriter(filename, engine='xlsxwriter')
workbook=writer.book
# 增加表格样式
border_fmt = workbook.add_format({'border': 1})
bold = workbook.add_format({'border': 1})
# center_fmt = workbook.add_format({'align': 'center'})
# title_fmt = workbook.add_format({'bold': True, 'font_size': 14})
for name, group in grp1:
# print(name, group)
# print(group.shape) #(行,列)
group = group.reset_index(drop=True)
group.index += 1
group.to_excel(writer, sheet_name=name,index=True)
# worksheet = writer.sheets[name]
# worksheet.write(0, 0, '序号')
writer.save()
ShezhiYanshi2(filename)
print('按列第%d列:[%s]拆分完成。\n保存文件名:[%s]' % (num, col_1_name, filename))
input('按回车键退出!')
if __name__ == '__main__':
cur_dir=init()
HuiZongBiaoGe4(cur_dir)
运行情况
输入文件
input.xlsx
拆分后保存一个文件多个sheet运行结果
拆分后保存多个文件运行结果![](https://i-blog.csdnimg.cn/blog_migrate/ff0f95b6fc96c11bd9dc987d52c80b3b.png)
资源下载
源码和可以执行程序: