Author: Uncle Hu

Source: www.cnblogs.com/yissheng/p/…

Learn to write split Excel script with uncle, slightly changed, add split into multiple file scripts.

Multiple lines of Excel split into multiple sheets

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

import xlrd
import xlwt
import math

limit = input('Please enter number of split rows:')
readBook = input('Please enter file path to be processed:')
saveBook = input('Please enter the path to save the file:')

if limit is None or limit == ' ':
    limit = 100
limit = int(limit)
print('Split rows:', limit)

data = xlrd.open_workbook(readBook)
dTable = data.sheets()[0]
nRows = dTable.nrows
print('Total files to be processed', nRows, 'row')
nCols = dTable.ncols
print('Total files to be processed', nCols, 'column')
nSheets = math.ceil(nRows / limit)
print('Split into', nSheets, 'a 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('File split complete! ')
Copy the code

Multi-line Excel is split into multiple files

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

import xlrd
import xlwt
import math
import os

# 10
limit = input('Please enter number of split rows:')
# d:/test.xls
readBook = input('Please enter file path to be processed:')
# d:/result.xls
saveBook = input('Please enter split file path:')

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

if limit is None or limit == ' ':
    limit = 100
limit = int(limit)
print('Split rows:', limit)

data = xlrd.open_workbook(readBook)
dTable = data.sheets()[0]
nRows = dTable.nrows
print('Total files to be processed', nRows, 'row')
nCols = dTable.ncols
print('Total files to be processed', nCols, 'column')
nSheets = math.ceil(nRows / limit)
print('Split into', nSheets, 'File')

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', file, 'Saved')
print('File split complete! ')

Copy the code