case_count.py

# -*- coding: utf-8 -*-
import xlrd
import xlwt
import os


def get_allexceldir(casepath):
    """traverse target path to get all excels"""

    allexcelnamelist = []
    allexceldirlist = []
    for (path, dirs, files) in os.walk(casepath):
        for i in range(0, len(files)):
            if ".xlsx" in files[i] and "~$" not in files[i]:
                allexcelnamelist.append(files[i])
                exceldir = os.path.join(path, files[i])
                allexceldirlist.append(exceldir)
            else:
                pass
    # print "------------------"
    print "allexcelnamelist: ", allexcelnamelist
    return allexceldirlist


class ReadExcel:
    def __init__(self, excelfile):
        """open excel and get sheet"""

        workbook = xlrd.open_workbook(excelfile)
        allsheet = workbook.sheet_names()
        sheet_name = allsheet[0]
        self.sheet = workbook.sheet_by_name(sheet_name)

    def get_casenum(self):
        """1st column"""

        tmp = []
        first_col = self.sheet.col_values(0)
        for i in range(0, len(first_col)):
            try:
                first_col[i] + 1
            except TypeError:
                pass
            else:
                tmp.append(int(first_col[i]))
        return tmp

    def get_featuresets(self):
        """get all Feature Sets"""

        col_idx = 0
        row1 = self.sheet.row(0)
        for i in range(0, len(row1)):
            if str(row1[i]).split(":u")[1] == "'Feature Sets'":
                col_idx = i
            elif str(row1[i]).split(":u")[1] == "'Test Feature'":
                col_idx = i
            else:
                pass
        fs = self.sheet.col_values(col_idx)

        if 'Feature Sets' in fs:
            fs.remove('Feature Sets')
        elif 'Test Feature' in fs:
            fs.remove('Test Feature')
        else:
            pass
        while 1:
            if "" in fs:
                fs.remove("")
            else:
                break
        return fs


def cut_casepath(casepath, excelpath):
    """get info of case path which ready to write"""
    n_a = casepath.split(':')[1].split('\\big')[0].strip('\\').split('\\')
    n_e = excelpath.split(':')[1].split('.xlsx')[0].strip('\\').split('\\')
    for m in n_a:
        if m in n_e:
            n_e.remove(m)
    rtw = n_e
    return rtw

# init some parameters
BigCase = "D:\Project_ST\st-case-design\st-case-design\\big cases"
testfolder = "D:\Project_ST\st-case-design\st-case-design\\big cases\\1st batch customer issue"
SmallCase = "D:\Project_ST\st-case-design\st-case-design\\small cases"
total_casenum_list = []
total_featuresets_list = []
all_readytowrite_casepath = []

excel_list = get_allexceldir(testfolder)
print "excel_list: ", excel_list

for excel in excel_list:
    print excel
    # Read excel
    exl = ReadExcel(excel)

    # get case number
    case_num = exl.get_casenum()
    print "case_num: ", case_num
    total_casenum_list.append(case_num)

    # get the feature sets of each case
    feature_sets = exl.get_featuresets()
    print "feature_sets: ", feature_sets
    total_featuresets_list.append(feature_sets)

print total_casenum_list
print "total_featuresets_list: ", total_featuresets_list


# get readytowrite_casepath
for tmp_ex in range(0, len(excel_list)):
    readytowrite_casepath = cut_casepath(testfolder, excel_list[tmp_ex])
    all_readytowrite_casepath.append(readytowrite_casepath)
print "all_readytowrite_casepath: ", all_readytowrite_casepath

# new a workbook
w = xlwt.Workbook()
sheet = w.add_sheet('feature sets count', cell_overwrite_ok=True)

# start writing into excel
newfe = ""
row_index = 0
readytowrite_fe = []

# 1 loop:遍历所有excel的feature列表
for excel_id in range(0, len(total_featuresets_list)):
    print "写入第%s个excel"%excel_id

    # 2 loop:遍历每个excel里有多少个case
    # total_casenum_list[excel_id]:[1, 2, 3]
    for case_id in range(0, len(total_casenum_list[excel_id])):
        print "写入第%s条case"%case_id

        # 处理一个case的feature使之存储为列表，以准备写入表格
        if "+" in total_featuresets_list[excel_id][case_id]:

            if " + " in total_featuresets_list[excel_id][case_id]:
                newfe = total_featuresets_list[excel_id][case_id].replace(" + ", "**")
                print newfe
                if "+ " in total_featuresets_list[excel_id][case_id]:
                    newfe = newfe.replace("+ ", "**")
                    print newfe
                if " +" in total_featuresets_list[excel_id][case_id]:
                    newfe = newfe.replace(" +", "**")
                    print newfe
            elif "+ " in total_featuresets_list[excel_id][case_id]:
                    newfe = total_featuresets_list[excel_id][case_id].replace("+ ", "**")
                    print newfe
                    if " +" in total_featuresets_list[excel_id][case_id]:
                        newfe = newfe.replace(" +", "**")
                        print newfe
            elif " +" in total_featuresets_list[excel_id][case_id]:
                    newfe = total_featuresets_list[excel_id][case_id].replace(" +", "**")
                    print newfe
            else:
                newfe = total_featuresets_list[excel_id][case_id].replace("+", "**")

            readytowrite_fe = newfe.split("**")

        else:
            readytowrite_fe.append(total_featuresets_list[excel_id][case_id])

        print "readytowrite_fe: ", readytowrite_fe

        # 3 loop:根据每个case对应的feature，写入多行数据，内容包括：casepath、casenum、casefeature
        # readytowrite_fe:[a,b,C,j,k]
        for feature_id in range(0, len(readytowrite_fe)):
            print "feature_id:", feature_id
            print "case_id:", case_id
            print "in loop row_index: ", row_index

            # write path in a row
            for col_index in range(0, len(all_readytowrite_casepath[excel_id])):
                print "col_index: ", col_index
                sheet.write(row_index, col_index, all_readytowrite_casepath[excel_id][col_index])

            # write case num in a row
            # sheet.write(row_index, len(all_readytowrite_casepath[excel_id]), total_casenum_list[excel_id][case_id])
            sheet.write(row_index, 9, total_casenum_list[excel_id][case_id])

            # write feature in a row
            sheet.write(row_index, 10, readytowrite_fe[feature_id])

            row_index = row_index + 1

        # row_index = row_index + 1
        print row_index
        # reset readytowrite_fe
        readytowrite_fe = []

w.save('count.xls')

 

转载于:https://www.cnblogs.com/azure-west/p/6870376.html