类似于筛选
# -*- coding: UTF-8 -*-
import xlrd
import xlwt
import os
import xlutils
from xlutils.copy import copy
xlsx_files = [f for f in os.listdir('.') if os.path.isfile(f) and f.endswith('.xlsx') or f.endswith('.xls')]
def print_xlsx_file():
x = 1
for i in xlsx_files:
print(str(x)+"."+i)
x+=1
def select_xlsx_file():
number = input("请输入您选择的序号:")#人为习惯的序号从1开始
return xlsx_files[int(number)-1]
def select_sheet_index():
index = input("请输入您选择sheet的序号")#人为习惯的序号从1开始
return int(index)
def select_sort_line():
number = input("请您输入您选择作为分类依据的列号:")#人为习惯的序号从1开始
return int(number)
def operate_xlsx_file():
#从输入得到用户三个自定义选项,未做输入合规判断
xl_name = select_xlsx_file()
number = select_sort_line()
index = select_sheet_index()
data = xlrd.open_workbook(xl_name)
workbook = copy(data)
table = data.sheet_by_index(index-1)
list = []
#遍历每一行,找到一行与前面行不同就放入list,就以这个行的值为名字建立一个子工作簿
#相同就将这行向已创建工作簿内拷贝
for i in range(1, table.nrows):#跳过了第0行所以下面拷贝了第一行,第一行就是你选的那个分类依据
value = table.cell(i, number-1).value
if value in list:
list[list.index(value)+1]+=1
for j in range(0, len(table.row(i))):
worksheet = workbook.get_sheet(value)
#print(type(table.row(i)[j].value))只是为了测试这个第i行第j列是什么类型的
#这里有个数据存储模式 list内放置的数据是这样的 value1,value1放了多少行,value2,value2放了多少行
#list[list.index(value)+1]标识现在子工作簿存放到了那一行了,这样做不至于子工作簿不至于错位
worksheet.write(list[list.index(value)+1], j, table.row(i)[j].value)
else:
list.append(value)
list.append(1)
#建立子工作簿
workbook.add_sheet(value)
#拷贝第一行
for j in range(0, len(table.row(0))):
worksheet = workbook.get_sheet(value)
worksheet.write(0, j, table.row(0)[j].value)
for j in range(0,len(table.row(i))):
worksheet = workbook.get_sheet(value)
worksheet.write(1, j, table.row(i)[j].value)
workbook.save("afterSort"+xl_name+".xls")
for i in range(0,len(list),2):
print(list[i]+"有"+str(list[i+1])+"行记录")
def main():
print_xlsx_file()
operate_xlsx_file()
if __name__ == '__main__':
main()