分类
大小:29.2M更新:2019-10-17
类别:办公软件系统:WinAll
拆分EXCEL文件.exe是一款根据某一列,拆分成几个不同的EXCEL文件,用户这进行使用Excel文档的时候,经常的会需要使用到数据的拆分操作,可是如果没有好的工具帮助用户的话,就会非常的浪费时间和精力,还有这样的烦恼。
将EXCEL文件拖至EXE文件上,根据提示操作。
import openpyxl
from openpyxl.utils import get_column_letter
import xlrd
import sys, os, collections
from pprint import pprint
from copy import copy
class splitExcel(object):
def __init__(self, sourceFile, titleLine=None, splitColumn=None):
self.sourceFile = sourceFile
self.sourceWorkbook = openpyxl.load_workbook(sourceFile)
self.targetWorkbook = openpyxl.Workbook()
self.targetWorkbook.remove(self.targetWorkbook.active)
# 源工作表(object对象)
self.sourceWorksheet = None
# 最大行数
self.sourceWorkbookMaxRow = None
# 最大列数
self.sourceWorkbookMaxColumn = None
# 源工作表索引号
self.sourceWorksheetIndex = None
# 标题所在行号,用户输入时索引从1开始,内部处理时请留意索引数
# if titleLine == None:
# titleLine = int(input('请输入标题所在行[2]: ').strip())
self.titleLine = titleLine
# 根据哪个列进行拆分,用户输入时索引从1开始,内部处理时请留意索引数
self.splitColumn = splitColumn
# 源工作薄当前行号
self.sbCurrectLine = None
# 目标工作薄各表当前数据行号
self.tbCurrectLines = {}
# 表头文字
self.header = []
# 各表数据
self.data = collections.OrderedDict()
# 保存列宽
self.columnsWidth = collections.OrderedDict()
# 格式
self.formats = {}
def readData(self):
wb = xlrd.open_workbook(self.sourceFile)
ws = wb.sheet_by_index(self.sourceWorksheetIndex)
for x in range(ws.nrows):
if x < self.titleLine:
# 表头
self.header.append(ws.row_values(x))
else:
v = ws.cell(x, self.splitColumn - 1).value
sheetName = self.clearSheetName(v)
# 将表名加入data字典
if sheetName not in self.data.keys():
self.data[sheetName] = []
# 添加数据
self.data[sheetName].append(ws.row_values(x))
def selectSplitSheet(self):
if len(self.sourceWorkbook.sheetnames) == 1:
self.sourceWorksheet = self.sourceWorkbook.active
self.sourceWorksheetIndex = 0
else:
_n_ = 0
print('在工作薄中找到以下工作表:')
for SheetName in self.sourceWorkbook.sheetnames:
print(_n_, SheetName)
_n_ += 1
n = 0
_input = input('请输入要拆分表的序号[0]: ').strip()
if _input != '':
n = int(_input)
self.sourceWorksheet = self.sourceWorkbook.worksheets[n]
self.sourceWorksheetIndex = n
def selectSplitColumn(self):
wb = xlrd.open_workbook(self.sourceFile)
ws = wb.sheet_by_index(self.sourceWorksheetIndex)
# 保存一下最大行数、列数,以供其它(属性)地方使用
self.sourceWorkbookMaxRow = ws.nrows
self.sourceWorkbookMaxColumn = ws.ncols
print('\n在工作表的标题行(第 %s 行)找到以下列: ' % self.titleLine)
for y in range(1, ws.ncols + 1):
print(y, ws.cell(self.titleLine - 1, y - 1).value)
columnNum = input('请输入拆分列号[2]: ').strip()
if columnNum == '':
columnNum = 2
else:
columnNum = int(columnNum)
self.splitColumn = columnNum
def readCellsStyle(self):
ws = self.sourceWorksheet
maxColumn = self.sourceWorkbookMaxColumn
styles = [[None] * (maxColumn + 11) for i in range(self.titleLine + 11)]
fonts = [[None] * (maxColumn + 11) for i in range(self.titleLine + 11)]
borders = [[None] * (maxColumn + 11) for i in range(self.titleLine + 11)]
fills = [[None] * (maxColumn + 11) for i in range(self.titleLine + 11)]
alignments = [[None] * (maxColumn + 11) for i in range(self.titleLine + 11)]
number_formats = [[None] * (maxColumn + 11) for i in range(self.titleLine + 11)]
protections = [[None] * (maxColumn + 11) for i in range(self.titleLine + 11)]
heights = [None] * (self.titleLine + 11)
widths = [None] * (maxColumn + 11)
# isDates = [None] * (maxColumn + 11)
for x in range(1, self.titleLine + 2):
heights[x] = ws.row_dimensions[x].height
# print(x,'height',heights[x])
# for y in range(1, ws.max_column + 1):
for y in range(1, maxColumn + 1):
styles[x][y] = copy(ws.cell(x, y).style.replace('常规', 'Normal'))
fonts[x][y] = copy(ws.cell(x, y).font)
borders[x][y] = copy(ws.cell(x, y).border)
fills[x][y] = copy(ws.cell(x, y).fill)
alignments[x][y] = copy(ws.cell(x, y).alignment)
number_formats[x][y] = copy(ws.cell(x, y).number_format)
protections[x][y] = copy(ws.cell(x, y).protection)
if y not in widths:
widths[y] = ws.column_dimensions[get_column_letter(y)].width
# print(y, get_column_letter(y), ws.column_dimensions[get_column_letter(y)].width)
# if y not in isDates:
# isDates[y] = ws.cell(x, y).is_date
self.formats['heights'] = heights
self.formats['styles'] = styles
self.formats['fonts'] = fonts
self.formats['borders'] = borders
self.formats['fills'] = fills
self.formats['alignments'] = alignments
self.formats['number_formats'] = number_formats
self.formats['protections'] = protections
self.formats['widths'] = widths
# self.formats['isDates'] = isDates
def writeFormatToNewWorkbook(self):
for sheetName in self.data.keys():
ws = self.targetWorkbook[sheetName]
# for x in range(1,self.titleLine+2):
for x in range(1, ws.max_row + 1):
# 表头
if x <= self.titleLine:
xx = x
# ws.row_dimensions[x].height = self.formats['heights'][x]
else:
# 表数据
xx = self.titleLine + 1
height = self.formats['heights'][xx]
if not height == None and height > 0:
ws.row_dimensions[x].height = self.formats['heights'][xx]
for y in range(1, ws.max_column + 1):
# 表头
if x <= self.titleLine:
# 路过空白单元格
# if ws.cell(x, y).value == '':
# continue
width = self.formats['widths'][y]
if not width == None and width > 0:
ws.column_dimensions[get_column_letter(y)].width = self.formats['widths'][y]
xx = x
else:
# 表数据
xx = self.titleLine + 1
ws.cell(x, y).style = self.formats['styles'][xx][y]
ws.cell(x, y).font = self.formats['fonts'][xx][y]
ws.cell(x, y).border = self.formats['borders'][xx][y]
ws.cell(x, y).fill = self.formats['fills'][xx][y]
ws.cell(x, y).alignment = self.formats['alignments'][xx][y]
ws.cell(x, y).number_format = self.formats['number_formats'][xx][y]
ws.cell(x, y).protection = self.formats['protections'][xx][y]
# if x>self.titleLine and self.formats['isDates'][y]:
# ws.cell(x, y).number_format = 'yyyy/mm/dd'
def writeDataToNewWorkbook(self):
for sheetName in self.data.keys():
ws = self.targetWorkbook.create_sheet(sheetName)
# 写入头文字
x = 0 # 行号
for row in self.header:
x += 1
y = 0 # 列号
for cellValue in row:
y += 1
if not cellValue == '':
ws.cell(x, y).value = cellValue
# 写入数据
for row in self.data[sheetName]:
ws.append(row)
def clearSheetName(self, name, replaceAs='-'):
invalidChars = r':\/?*[]:'
for c in invalidChars:
name = name.replace(c, replaceAs).strip()
return name
def selectTitleLine(self):
wb = xlrd.open_workbook(self.sourceFile)
ws = wb.sheet_by_index(self.sourceWorksheetIndex)
# 保存一下最大行数、列数,以供其它(属性)地方使用
self.sourceWorkbookMaxRow = ws.nrows
self.sourceWorkbookMaxColumn = ws.ncols
print('打印所拆分工作表前10行,前5列数据:')
maxY = ws.ncols
if ws.ncols > 5:
maxY = 5
for x in range(10):
tempList = []
for y in range(maxY):
tempList.append(ws.cell(x, y).value)
print('第%s行:' % (x + 1), tempList)
titleLine = 2
n = input('\n请输入标题行所在行号[2]:').strip()
if not n == '':
titleLine = int(n)
self.titleLine = titleLine
def make(self):
self.selectSplitSheet()
self.selectTitleLine()
self.selectSplitColumn()
print('开始读取数据...')
self.readData()
print('开始读取格式...')
self.readCellsStyle()
print('开始写入数据至分表...')
self.writeDataToNewWorkbook()
print('开始写入格式至分表...')
self.writeFormatToNewWorkbook()
def save(self, filename=None):
if filename == None:
splitPath = os.path.split(self.sourceFile)
filename = splitPath[0] + '/拆分_' + splitPath[1]
self.targetWorkbook.save(filename)
self.sourceWorkbook.close()
self.targetWorkbook.close()
return filename
class saveWorksheetToWorkbook(object):
def __init__(self, excelFile):
self.excelFile = excelFile
def saveTo(self, savePath=None, addNumToFilename=True):
if savePath == None:
splitPath = os.path.splitext(self.excelFile)
savePath = splitPath[0]
if not os.path.exists(savePath):
os.makedirs(savePath, exist_ok=True)
wb = openpyxl.load_workbook(self.excelFile)
sheetNames = wb.sheetnames
wb.close()
#
# _input=input('正在保存各表至独立工作薄,文件名前是否加序号?[Y] Y/N:').strip().lower()
# if _input=='n':
# addNumToFilename=False
n=0
for sheetName in sheetNames:
n+=1
print('保存', n, sheetName)
wb = openpyxl.load_workbook(self.excelFile)
for ws in wb.worksheets:
if not sheetName == ws.title:
wb.remove(ws)
xh=''
if addNumToFilename:
xh=str(n)
filename='%s/%s%s.xlsx' % (savePath, xh,sheetName)
wb.save(filename)
wb.close()
return savePath
if __name__ == '__main__':
file = r'C:\Users\Eyes\Desktop\汇总.xlsx'
file = sys.argv[1]
se = splitExcel(file)
# file = r'C:\Users\Eyes\Desktop\2019年积分排名-2019-07-09.xlsx'
# se = splitExcel(file, 1, 3)
se.make()
f = se.save()
print('拆分汇总文件:', f)
# f=r'C:\Users\Eyes\Desktop\汇总.拆分.xlsx'
saveTo = saveWorksheetToWorkbook(f)
p = saveTo.saveTo()
print('拆分表保存文件夹:', p)
input('\n完成,按回车键退出。')
思维导图设计软件Crystal Office Maple办公软件Mv9.04 免费版
查看益教课程录播制作软件2017最新版办公软件MV2.3.1.7官方版
查看小白管家(网咖管理运营)办公软件M1.1.1官方版
查看自媒体全平台采集助手办公软件M2.0
查看单位社保费管理客户端办公软件M1.0.001官方版
查看Kutools for Word破解全功能无限期版办公软件MV8.70免费中文版
查看金酷奇进销存系统办公软件Mv3.0
查看SPWM表格生成工具办公软件.43M1.3.2
查看悟空CRM客户端办公软件M9.2.1电脑版
查看三易通服装连锁店管理软件网络版办公软件Mv5.28
查看博奥土地项目整理计价软件办公软件MV2018官方版
查看金山PDF办公软件MV10.1.0.6683官方独立版
查看i神华桌面客户端办公软件Mv1.1.1 官方最新版
查看圆方办公空间规划设计系统办公软件M8.6官方版
查看珍岛t云系统办公软件M3.6官方版
查看Publishit办公软件M3.6.1.0
查看WPS Office 2019专业版(提取纯净版)办公软件MV11.8.2.8053支持VB
查看飞书会议网页端办公软件M3.18.2
查看完美智能分班处理系统办公软件Mv1.0绿色版
查看班牛电脑端办公软件Mv2.0官方版
查看萝卜开会PC客户端办公软件M0.0.3官方版
查看liveuc云视讯平台办公软件Mv3.8官方版
查看WPS Office 2016海南省党政机关专用版办公软件MV10.8.2.6784含直属省区
查看云中书城阅读器办公软件M1.1.1.4235
查看点击查看更多