Python分割excel文件

作者： 胡大叔

出处：www.cnblogs.com/yissheng/p/…

跟大叔学写分割excel脚本，稍有改动，补充拆分为多个文件脚本。

多行excel分割为多个sheet

# -*- coding=utf-8 -*-

import xlrd
import xlwt
import math

limit = input('请输入分割行数：')
readBook = input('请输入待处理文件路径：')
saveBook = input('请输入保存文件路径：')

if limit is None or limit == '':
    limit = 100
limit = int(limit)
print('分割行数：', limit)

data = xlrd.open_workbook(readBook)
dTable = data.sheets()[0]
nRows = dTable.nrows
print('待处理文件共', nRows, '行')
nCols = dTable.ncols
print('待处理文件共', nCols, '列')
nSheets = math.ceil(nRows / limit)
print('拆分为', nSheets, '个sheet')

workbook = xlwt.Workbook(encoding='utf-8')
for s in range(nSheets):
    worksheet = workbook.add_sheet(str(s))
    for r in range(limit):
        rowNum = r + (s * limit)
        if rowNum >= nRows:
            break
        content = dTable.row_values(rowNum)
        for c in range(nCols):
            worksheet.write(r, c, content[c])
workbook.save(saveBook)
print('文件分割完毕!')

多行excel分割为多个文件

# -*- coding=utf-8 -*-

import xlrd
import xlwt
import math
import os

# 10
limit = input('请输入分割行数：')
# d:/test.xls
readBook = input('请输入待处理文件路径：')
# d:/result.xls
saveBook = input('请输入分割文件路径：')

filePath, tempFileName = os.path.split(saveBook)
fileName, fileExt = os.path.splitext(tempFileName)

if limit is None or limit == '':
    limit = 100
limit = int(limit)
print('分割行数：', limit)

data = xlrd.open_workbook(readBook)
dTable = data.sheets()[0]
nRows = dTable.nrows
print('待处理文件共', nRows, '行')
nCols = dTable.ncols
print('待处理文件共', nCols, '列')
nSheets = math.ceil(nRows / limit)
print('拆分为', nSheets, '个文件')

for s in range(nSheets):
    workbook = xlwt.Workbook(encoding='utf-8')
    worksheet = workbook.add_sheet('sheet0')
    for r in range(limit):
        rowNum = r + (s * limit)
        if rowNum >= nRows:
            break
        content = dTable.row_values(rowNum)
        for c in range(nCols):
            worksheet.write(r, c, content[c])
    file = os.path.join(filePath, fileName + str(s) + fileExt)
    workbook.save(file)
    print('文件', file, '已保存')
print('文件分割完毕!')