python拆分excel并命名_python自动拆分某个文件下的某个excel，按照一定数量去拆分为对个excel...

#!/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("拆分完成...")