拆分xlsx与csv(python实现)

import openpyxl

import os

import csv

 

def splitExel(path,splitLen):

inwb = openpyxl.load_workbook(path)

sheetnames = inwb.sheetnames

sheetname = sheetnames[0]

sheel = inwb[sheetname]

rows = sheel.max_row

cols = sheel.max_column

 

dirname, saveExelName, extensionname = _splitPath(path)

currentRow = 1

splitcount = 1

outwb = openpyxl.Workbook()

writeTtile = True

for r in range(1, rows+1):

for c in range(1, cols):

if writeTtile:

writeExel(outwb,1,c,sheel.cell(1, c).value)

if r != 1:

writeExel(outwb,currentRow,c,sheel.cell(r, c).value)

if(c >= cols - 1):

print("{0} {1}:{2}".format("title" if writeTtile else "content",currentRow,r))

writeTtile = False

if (currentRow % splitLen == 1 and currentRow != 1) or r >= rows:

splitcountStr = str(splitcount)

savepath = dirname + "\\" + saveExelName + splitcountStr + "." + extensionname

print(savepath)

saveExel(outwb, savepath)

outwb.close()

outwb = openpyxl.Workbook()

splitcount += 1

currentRow = 1

writeTtile = True

currentRow += 1

 

 

def _splitPath(path):

[dirname, filename] = os.path.split(path)

filenameArr = filename.split(".", 1)

filename = filenameArr[0]

extensionname = filenameArr[1]

return dirname, filename, extensionname

 

 

def writeExel(outwb,row,col,value):

sheetnames = outwb.sheetnames;

sheetSize = len(sheetnames)

if sheetSize <= 0:

outws = outwb.create_sheet(index=0)

else:

outws = outwb[sheetnames[0]]

outws.cell(row,col,value)

# for row in range(1, 700):

# for col in range(1 ,4):

# outws.cell(row,col).value = row*2

# print(row)

return outwb

 

def saveExel(workbook,path):

# saveExcel = r'C:\Users\dh295\Desktop\3.xlsx'

saveExcel = path

workbook.save(saveExcel)

 

def splitCsv(path,splitLen):

[dirname, filename, extensionname] = _splitPath(path)

csvreader = None

iterator = None

code='utf-8'

try:

csvfile = open(path,encoding=code)

csvreader = csv.reader(csvfile)

for row in enumerate(csvreader):

break

except UnicodeDecodeError as e:

code = 'gbk'

csvfile = open(path,encoding=code)

csvreader = csv.reader(csvfile)

titleRow = None

splitcount = 1

csvfile = open(r"{0}\{1}{2}.{3}".format(dirname,filename,splitcount,extensionname),"w",encoding=code, newline="\n")

write = csv.writer(csvfile)

writeTitle = True

currentRow = 0

for i, row in enumerate(csvreader,start=0):

if i == 0:

titleRow = row

if(writeTitle):

write.writerow(titleRow)

if i != 0:

write.writerow(row)

print("{0} {1}:{2}".format("title" if writeTitle else "content",currentRow,i))

writeTitle = False

if currentRow % splitLen == 0 and currentRow != 0:

csvfile.close()

writeTitle = True

splitcount += 1

csvfile = open(r"{0}\{1}{2}.{3}".format(dirname,filename,splitcount,extensionname),"w",encoding=code,newline="\n")

print(r"{0}\{1}{2}.{3}".format(dirname,filename,splitcount,extensionname))

write = csv.writer(csvfile)

currentRow = 0

currentRow += 1

 

 

 

 

 

def splitExelOrCsv(path, splitLen):

[dirname, filename, extensionname] = _splitPath(path)

if extensionname == "xlsx":

splitExel(path, splitLen)

elif extensionname == "csv":

splitCsv(path,splitLen)

else:

raise RuntimeError("暂不支持该格式，请联系詹志彬")

 

splitExelOrCsv(r'C:\Users\dh295\Desktop\T_TTPassRecord.csv',300)

# splitCsv(r'C:\Users\dh295\Desktop\T_TTPassRecord.csv',300)