#!/usr/bin/env python
# -*- coding:utf-8 -*-
import xlrd
from pyexcelerate import Workbook
import os
"""
excel 文件拆分
1、指定源文件路径
2、指定拆分行数大小
3、输出累计金额和每个文件累计金额
"""
# 读取源excel所有行,不提取首行
def getRows():
data = []
workbooks = xlrd.open_workbook(parth)
sheet1=workbooks.sheets()[0]
for index in range(1,sheet1.nrows):
item=[]
item.append(sheet1.cell(index, 0).value.replace('\n', '').replace('\r', ''))
item.append(int(sheet1.cell(index, 1).value))
item.append(sheet1.cell(index, 2).value)
data.append(item)
return data
# 把数据写到excel中 写成多列
def writeExcel(datas):
totalFell=0
for index,data in enumerate(datas):
workbook = Workbook()
sheet = workbook.new_sheet('split_result')
sheetTotalFell=0
for row,item in enumerate(data):
sheetTotalFell=sheetTotalFell+float(item[2])
if row==0:
sheet[1][1].value='保批单号'
sheet[1][2].value='期次'
sheet[1][3].value='手续费'
sheet[2][1].value = item[0]
sheet[2][2].value = item[1]
sheet[2][3].value = item[2]
else:
sheet[row+2][1].value = item[0]
sheet[row+2][2].value = item[1]
sheet[row+2][3].value = item[2]
totalFell=totalFell+sheetTotalFell
sumFell=f'{sheetTotalFell:.2f}'
print("文件"+str(index+1)+":"+sumFell)
workbook.save(getFileName().replace("-split","-split-"+str(index+1)+'-'+sumFell))
print("总金额:"+f'{totalFell:.2f}')
# 根据源文件创建新的文件名,与源文件同一目录下
def getFileName():
return os.path.join(os.path.dirname(parth), os.path.split(parth)[1].replace(".xlsx","-split.xlsx"))
# 源文件路径
parth = r"C:\Users\Administrator\Desktop\新建文件夹 (2)\无标题.xlsx";
# 拆分文件是 每个文件多少行
pageSize=10000
pages = 1
if __name__ == '__main__':
result=[]
#1、读取
datas=getRows()
#2、拆分数组
if (len(datas) % pageSize) == 0:
pages = int(len(datas) / pageSize)
else:
pages = int(len(datas) / pageSize) + 1
for page in range(pages):
item = datas[page * pageSize:(page + 1) * pageSize]
if page == pages - 1:
item = datas[page * pageSize:]
result.append(item)
print("拆分文件总数:"+str(len(result)))
# 3、写excel
writeExcel(result)
print("拆分完成...")