python 将表格多个列数据放到同一个单元格中

 

表格模板:

目的将卡片1到卡片5的所有数据组合起来到一个单元格中如下入F列中（工作中为了避免手动复制粘贴），其余不变，因为数据太多  自己一个一个复制工作效率太低，所以写这个脚本是为了方便自己
有需要的也可进行修改后使用。
运行结果如下：

反过来 以“;”分隔上图F列  结果显示为 卡片1 卡片2 卡片3 的结果
def readExcelDataByName(filename, sheetName):
    '''读取Excel文件和表名'''
    wb = xlrd.open_workbook(filename)
    # sheet=data.sheet_by_index(0)#通过索引顺序获取，0表示第一张表
    # sheets = data.sheet_names()#获取文件中的表名
    sheet = wb.sheet_by_name(sheetName)
    ncols = sheet.ncols
    # 获取行数
    nrows = sheet.nrows
    print("nrows %d, ncols %d" % (nrows, ncols))
    row_list = []
    work_book = xlwt.Workbook("11.xls")
    sheet1 = work_book.add_sheet("Sheet1")
    k = 1
    for j in range(1, nrows):
        # 获取单元格
        for i in range(6, ncols):
            data = sheet.cell_value(j, i)
            if data == "":
                continue
            else:
                row_list.append(str(k) + "." + data + "
")
            k = k + 1
        # print(row_list)

        sheet1.write(j, 5, row_list)
        row_list.clear()
        k = 1
    for m in range(0,7):
        for n in range(0,5):#列
            data=sheet.cell_value(m, n)
            sheet1.write(m, n, data)
    if os.path.exists("11.xls"):
        os.remove("11.xls")
        work_book.save("11.xls")
    else:
        work_book.save("11.xls")
if __name__ == '__main__':
    readExcelDataByName('模板.xlsx', 'Sheet1')

　　

#!user/bin/env python
# coding=utf-8
# @Author  : Dang
# @Time    : 2018/5/22 17:25
# @Email   : 1370465454@qq.com
# @File    : Test_SplitExcel.py
# @Description:对excel单元格已；进行分隔，完成后需要对
import xlrd
import xlwt
import os
import re


def readExcelDataByName(filename, sheetName, num,save_filename):
    """
        :param filename:输入文件路径和名字+后缀
        :param sheetName:输入表名
        :param num:输入要分隔的列
        :param save_filename:要保存的文件名称
    """
   
    wb = xlrd.open_workbook(filename)
    # sheet=data.sheet_by_index(0)#通过索引顺序获取，0表示第一张表
    # sheets = data.sheet_names()#获取文件中的表名
    sheet = wb.sheet_by_name(sheetName)
    ncols = sheet.ncols
    # print(ncols)
    # 获取行数
    nrows = sheet.nrows
    print("nrows %d, ncols %d" % (nrows, ncols))
    row_list = []
    work_book = xlwt.Workbook()
    sheet1 = work_book.add_sheet(sheetName[:-1], cell_overwrite_ok=True)
    for m in range(0, nrows):
        for n in range(0, num):  # 列
            data_init = sheet.cell_value(m, n)
            sheet1.write(m, n, data_init)
    k=0
    for m in range(1, nrows):
        data = sheet.cell_value(m, num)
        row_list = re.split("；", data.replace("
", ""))
        for n in range(0, len(row_list)):  # 列
            sheet1.write(m, n + num, row_list[n])  # 从第6列写入
        k=k+len(row_list)
        # print(m)
        # print(row_list)
        row_list.clear()
    # print(k-nrows)

    print("大约有 %d个用例，此处只做大概统计，具体需要根据实际情况减去相应值" %(k-nrows))

    for i in range(num, ncols):
        data_add = sheet.cell_value(0, i)
        # print(kapian)
        sheet1.write(0, i, data_add)
    if os.path.exists(save_filename + sheetName + ".xls"):
        os.remove(save_filename + sheetName + ".xls")
        work_book.save(save_filename + sheetName + ".xls")
    else:
        work_book.save(save_filename + sheetName + ".xls")
    print("转换完成，请查看 %s%s.xls的文档。" %(save_filename,sheetName))
if __name__ == '__main__':
    """
        :param filename:输入文件路径和名字+后缀
        :param sheetName:输入表名
        :param num:输入要分隔的列
        :param save_filename:要保存的文件路径和名称，要保存文件名会和表名自动组合形成新的文件 如：分隔结果文档Sheet1.xls
    """
    readExcelDataByName('11.xls', 'Sheet1', 6,"结果文档")