1 Python利用xlrd,xlwt包实现对Excel按列拆分而后合并

1.1 首先看数据格式

数据共两列，其中第2列为Key,按其数值对原始表格进行拆分

1.2 代码实现

须知：xlrd,xlwt在写入Excel时一个sheet只能操作256之内的列，若合并列数大于256,则需新建sheet

1.2.1 结果如下

1.2.2 原始数据合并后列数大于256时代码实现如下

import xlrd
import xlwt
workbook1 = xlwt.Workbook(encoding='utf-8')
worksheet = workbook1.add_sheet('sheet1')
worksheet1 = workbook1.add_sheet('sheet2')
worksheet2 = workbook1.add_sheet('sheet3')
worksheet3 = workbook1.add_sheet('sheet4')
workbook = xlrd.open_workbook(r'OP011_JD.xlsx')
sheet = workbook.sheet_by_index(0)
nums = sheet.nrows
print(nums)
dic = {}
dic_clo = {}
flag = 0
for i in range(1,nums):
    value,key = sheet.row_values(i)
    if key in dic:
        dic[key] +=1
    else:
        dic[key] =1
        dic_clo[key] =flag + 1
        flag += 1
    if dic_clo[key]<256:
        print('sheet1',i,dic[key], dic_clo[key], value)
        worksheet.write(dic[key], dic_clo[key], value)
    elif dic_clo[key]<511:
        print('sheet2',i,dic[key], dic_clo[key]%255, value)
        worksheet1.write(dic[key], dic_clo[key]%255, value)
    elif dic_clo[key]<766:
        worksheet2.write(dic[key], dic_clo[key]%510, value)
        print('sheet3',i,dic[key], dic_clo[key]%510, value)
    else:
        worksheet3.write(dic[key], dic_clo[key]%765, value)
        print('sheet4',i,dic[key], dic_clo[key]%765, value)
for i in dic_clo:
    if dic_clo[i]<256:
        worksheet.write(0, dic_clo[i], i)
    elif dic_clo[i]<511:
        worksheet1.write(0, dic_clo[i]%255, i)
    elif dic_clo[i]<766:
        worksheet2.write(0, dic_clo[i]%510, i)
    else:
        worksheet3.write(0, dic_clo[i]%765, i)
print('类别：总行数',dic,'类别：列索引',dic_clo)
workbook1.save(r'splite_1.xls')