def GenerateFormula_SumRows_Weighted(rows, col, weight_rows, weight_col, num):
    cells = ''
    for row, wtrow in zip(rows, weight_rows):
        location = xlrd.cellnameabs(row, col)
        weight = xlrd.cellnameabs(wtrow, weight_col)
        cells = cells + '%s * %s,' % (location, weight)
    cells = cells[:-1]  # remove the last ,
    formula = '=SUM(%s)/%d' % (cells, num)
    return formula
def GenerateFormula_SumRows(shtname, rows, col):
    cells = ''
    for row in rows:
        location = xlrd.cellnameabs(row, col)
        cells = cells + '\'%s\'!%s,' % (shtname, location)
    cells = cells[:-1]  # remove the last ,
    formula = '=SUM(%s)/%d' % (cells, len(rows))
    return formula
def CalBDRateWithExcel_OneSheet(sht, cols, cols_bdmtrs, cellformat):
    row_refst = 0
    bdstep = len(QPs) - 1
    for cols_bd, residx in zip(cols_bdmtrs, range(1, len(DnScaleRatio))):
        sht.write(
            0, cols_bd,
            'BD-Rate %.2f vs. %.2f' % (DnScaleRatio[residx], DnScaleRatio[0]))
        sht.write_row(1, cols_bd, QualityList)
        for (cls, contents), row_class in zip(ContentsDict.items(),
                                              Rows_Class):
            rows_content = [i * len(QPs) for i in range(len(contents))]
            for row_cont in rows_content:
                for y in range(len(QualityList)):
                    refbr_b = xlrd.cellnameabs(
                        row_class + row_cont + row_refst, cols[0])
                    refbr_e = xlrd.cellnameabs(
                        row_class + row_cont + row_refst + bdstep, cols[0])
                    refq_b = xlrd.cellnameabs(row_class + row_cont + row_refst,
                                              cols[0] + 1 + y)
                    refq_e = xlrd.cellnameabs(
                        row_class + row_cont + row_refst + bdstep,
                        cols[0] + 1 + y)

                    testbr_b = xlrd.cellnameabs(
                        row_class + row_cont + row_refst, cols[residx])
                    testbr_e = xlrd.cellnameabs(
                        row_class + row_cont + row_refst + bdstep,
                        cols[residx])
                    testq_b = xlrd.cellnameabs(
                        row_class + row_cont + row_refst, cols[residx] + 1 + y)
                    testq_e = xlrd.cellnameabs(
                        row_class + row_cont + row_refst + bdstep,
                        cols[residx] + 1 + y)

                    #formula = '=bdrate(%s:%s,%s:%s,%s:%s,%s:%s)' % (
                    #refbr_b, refbr_e, refq_b, refq_e, testbr_b, testbr_e,
                    # testq_b, testq_e)
                    formula = '=bdRateExtend(%s:%s,%s:%s,%s:%s,%s:%s)'\
                              % (refbr_b, refbr_e, refq_b, refq_e, testbr_b,
                                 testbr_e, testq_b, testq_e)
                    sht.write_formula(row_class + row_cont, cols_bd + y,
                                      formula, cellformat)
Exemple #4
0
def WriteRDRecord(sht, base_data, target_data, start_row, bdrate_fmt,
                  float_fmt):
    sht.write(start_row, 0, base_data.ContentClass)
    sht.write(start_row, 1, base_data.ContentName)

    #write base data
    base_start_col = 3
    base_max_rows = WriteRDData(sht, base_data, start_row, base_start_col,
                                float_fmt)

    #write target data
    target_start_col = base_start_col + 4 * len(QualityList) + 1
    target_max_rows = WriteRDData(sht, target_data, start_row,
                                  target_start_col, float_fmt)

    #write bdrate formula
    bdrate_start_col = target_start_col + 4 * len(QualityList) + 1
    total_rows = max(base_max_rows, target_max_rows)
    sht.write(start_row, 2, total_rows)
    for (qty, col) in zip(QualityList, range(len(QualityList))):
        if CalcBDRateInExcel:
            refbr_b = xlrd.cellnameabs(start_row, base_start_col + col * 4 + 2)
            refbr_e = xlrd.cellnameabs(start_row + total_rows - 1,
                                       base_start_col + col * 4 + 2)
            refq_b = xlrd.cellnameabs(start_row, base_start_col + col * 4 + 3)
            refq_e = xlrd.cellnameabs(start_row + total_rows - 1,
                                      base_start_col + col * 4 + 3)

            testbr_b = xlrd.cellnameabs(start_row,
                                        target_start_col + col * 4 + 2)
            testbr_e = xlrd.cellnameabs(start_row + total_rows - 1,
                                        target_start_col + col * 4 + 2)
            testq_b = xlrd.cellnameabs(start_row,
                                       target_start_col + col * 4 + 3)
            testq_e = xlrd.cellnameabs(start_row + total_rows - 1,
                                       target_start_col + col * 4 + 3)

            # formula = '=-bdrate(%s:%s,%s:%s,%s:%s,%s:%s)' % (
            # refbr_b, refbr_e, refq_b, refq_e, testbr_b, testbr_e, testq_b, testq_e)
            formula = '=bdRateExtend(%s:%s,%s:%s,%s:%s,%s:%s)'\
                % (refbr_b, refbr_e, refq_b, refq_e, testbr_b, testbr_e, testq_b, testq_e)
            sht.write_formula(start_row, bdrate_start_col + col, formula,
                              bdrate_fmt)
        else:
            refbrs = [
                base_data.RDPoints[qty][i][2]
                for i in range(len(base_data.RDPoints[qty]))
            ]
            refqtys = [
                base_data.RDPoints[qty][i][3]
                for i in range(len(base_data.RDPoints[qty]))
            ]
            testbrs = [
                target_data.RDPoints[qty][i][2]
                for i in range(len(target_data.RDPoints[qty]))
            ]
            testqtys = [
                target_data.RDPoints[qty][i][3]
                for i in range(len(target_data.RDPoints[qty]))
            ]
            bdrate = BD_RATE(refbrs, refqtys, testbrs, testqtys) / 100.0
            sht.write_number(start_row, bdrate_start_col + col, bdrate,
                             bdrate_fmt)
    return total_rows
Exemple #5
0
print(table1.row_types(0, 0, 2))
#5.特定单元格读取
#取值
print()
print(table1.cell(1, 2).value)
print(table1.cell_value(1, 2))
print(table1.row(1)[2].value)
#取类型
print()
print(table1.cell(1, 2).ctype)
print(table1.cell_type(1, 2))
print(table1.row(1)[2].ctype)
#6.常用技巧:(0,0)转换成A1
print()
print(xlrd.cellname(0, 0))
print(xlrd.cellnameabs(0, 2))
print(xlrd.colname(30))


def read_excel(table, row, col):
    name = table.cell_value(row, col)
    type = table.cell_type(row, col)
    if type == 0:
        name = "'"
    elif type == 1:
        name = name
    elif type == 2 and name % 1 == 0:
        name = int(name)
    elif type == 3:
        #方法1 转换为日期时间
        # date_value = xlrd.xldate.xldate_as_datetime(name,0)
Exemple #6
0
from xlrd import cellname, cellnameabs, colname

print cellname(0, 0), cellname(10, 10), cellname(100, 100)
print cellnameabs(3, 1), cellnameabs(41, 59), cellnameabs(265, 358)
print colname(0), colname(10), colname(100)
Exemple #7
0
import xlrd
from datetime import datetime, date

newpath = os.chdir(r'C:\Users\zhuzhengren\Desktop\测试')
filename = '朱正仁测试用例0724.xlsx'
file = os.path.join(os.getcwd(), filename)
'''1.打开文件'''
xl = xlrd.open_workbook(file)
'''2.获取sheet'''
print(xl.sheet_names())
'''3.获取sheet中的数据'''
table1 = xl.sheet_by_name('进入横屏页面')
print(table1.name)
print(table1.ncols)
print(table1.nrows)
'''4.单元格批量获取'''
print(table1.row_values(6))
print(table1.row_types(0))
'''5.获取特定的单元格数据'''
#取值
print(table1.cell(5, 6).value)
print(table1.cell_value(5, 6))
print(table1.row(5)[6].value)
#取类型
print(table1.cell(5, 6).ctype)
print(table1.cell_type(5, 6))
print(table1.row(5)[6].ctype)
'''6.常用技巧(0,0)转换成A1'''
print(xlrd.cellname(0, 0))
print(xlrd.cellnameabs(0, 0))
Exemple #8
0
from xlrd import open_workbook, cellname, cellnameabs

book = open_workbook('odd.xls')
sheet = book.sheet_by_index(0)

print('sheet_name:%s, sheet_nrows:%s, sheet_ncols:%s' %
      (sheet.name, sheet.nrows, sheet.ncols))

for row_index in range(sheet.nrows):
    for col_index in range(sheet.ncols):
        print(cellname(row_index, col_index), '-',
              sheet.cell(row_index, col_index).value)
        print(cellnameabs(row_index, col_index), '-',
              sheet.cell(row_index, col_index).value)
Exemple #9
0
'''五、特定单元格读取'''

print(table1.cell(1, 2).value)  # 获取第2行,第3列单元格的值

print(table1.cell_value(1, 2))

print(table1.row(1)[2])

print(table1.row(1)[2].value)

print(table1.row(1)[2].ctype)
'''六、常用技巧:(0,0)转换成A1'''

print(xlrd.cellname(0, 0))  # A1 cellname方法把一对行和列索引转换为一个对应的Excel单元格引用

print(xlrd.cellnameabs(
    0, 0))  # $A$1 cellnameabs方法把一对行和列索引转换为一个绝对的Excel单元格引用(如:$A$1)

print(xlrd.colname(0))  # A colname方法把一个列索引转换为Excel列名
'''七、获取表格内不同类型的name'''


def read_excel(table, row, col):
    name = table.cell_value(row, col)
    ctype = table.cell_type(row, col)
    if ctype == 0:
        name = "''"
    elif ctype == 1:
        name = name
    elif ctype == 2 and name % 1 == 0:
        name = int(name)
    elif ctype == 3:
Exemple #10
0
from xlrd import cellname, cellnameabs, colname

print cellname(0,0),cellname(10,10),cellname(100,100)
print cellnameabs(1,0),cellnameabs(41,59),cellnameabs(265,358)
print colname(0),colname(10),colname(100)
Exemple #11
0
def xls2fix(s, settings, output_filename):
    fixture_list = []
    # 与えられたyamlの設定をしておく
    for row in range(s.nrows):
        rows = []
        for col in range(s.ncols):
            rows.append(s.cell(row, col).value)
        if row <= settings.row:
            continue
        fields = {}
        id = 0
        for column, col in enumerate(rows):
            # Excelのカラムがコンバート対象かチェックする
            setting_column = settings.get_setting_column(row, column)
            if setting_column:
                # コンバート対象カラム
                value = col
                if setting_column.type == 'datetime':
                    if value != '':
                        value = str(datetime.datetime(*xldate_as_tuple(value, 0)))
                    else:
                        value = None
                elif setting_column.type == 'char':
                    pass
                elif setting_column.type == 'int':
                    try:
                        if col == u'':
                            value = 0
                        else:
                            value = int(col)
                    except ValueError, UnicodeEncodeError:
                        # 置換できなかった場合、import_dictの中に変換可能なカラムがあるかをチェック
                        if setting_column.name in settings.import_dict:
                            column_dict = settings.import_dict[setting_column.name]
                            try:
                                value = column_dict[col]
                            except KeyError:
                                print >>sys.stderr, u'%s:%sはintでなくdictを使っても変換できない' % (cellnameabs(row, column), col)
                                raise
                        else:
                            print >>sys.stderr, u'%s:%sはintに変換できない' % (cellnameabs(column, row), col)
                            raise
                            value = 0
                elif setting_column.type == 'float':
                    try:
                        value = float(col)
                    except ValueError:
                        value = 0.0
                elif setting_column.type == 'foreign_key':
                    if col == u'':
                        value = 0
                    else:
                        try:
                            value = int(col)
                        except ValueError:
                            # リレーション設定があるか?
                            if setting_column.has_relation():
                                value = setting_column.relation(col)
                            else:
                                print >>sys.stderr, u'%s:%sはリレーションIDに変換できない' % (cellnameabs(row, column), col)
                                raise
                    if value == 0:
                        value = None
                elif setting_column.type == 'boolean':
                    if len(unicode(col)) == 0:
                        value = False
                    else:
                        value = True
                else:
                    print u'存在しないカラムタイプ[%s]を指定されている' % (setting_column.type)
                    raise

                if setting_column.name == 'id':
                    id = int(value)
                else:
                    fields[setting_column.name] = value

        # 未設定カラムを順番に処理する
        for setting_column in settings.settings_none_exist_columns:

            fields[setting_column.name] = str(setting_column.default)

        fixture_list.append({
                'model': settings.model,
                'pk': id,
                'fields': fields,
                })
Exemple #12
0
    Nsaf.write(0, newcol + nsafcol + analysis, "", t_style)
    Nsaf.write(1, newcol + nsafcol + analysis, "", t_style)
    Nsaf.write(2, newcol + nsafcol + analysis, 'Sum NSAF', t2_style)
    Nsaf.write(0, newcol + nsafcol + analysis + 1, "", t_style)
    Nsaf.write(1, newcol + nsafcol + analysis + 1, "", t_style)
    Nsaf.write(2, newcol + nsafcol + analysis + 1, 'Avg NSAF', t2_style)
if Noscaa == 'Yes':
    Nsaf.write(0, newcol + scaacol + nsafcol + analysis, "", t_style)
    Nsaf.write(1, newcol + scaacol + nsafcol + analysis, "", t_style)
    Nsaf.write(2, newcol + scaacol + nsafcol + analysis, 'Sum NSAF', t2_style)
    Nsaf.write(0, newcol + scaacol + nsafcol + analysis + 1, "", t_style)
    Nsaf.write(1, newcol + scaacol + nsafcol + analysis + 1, "", t_style)
    Nsaf.write(2, newcol + scaacol + nsafcol + analysis + 1, 'Avg NSAF', t2_style)

for row_index in range(row, rs.nrows):
    SCrange = cellnameabs(newrow, newcol) + ':' + cellnameabs(newrow, newcol + rs.ncols - 2)
    NSAFrange = cellnameabs(newrow, newcol+scaacol+analysis) + ':' + cellnameabs(newrow, newcol + nsafcol + analysis - 1)
    countifSC = 'COUNTIF(' + SCrange + ', ">0"' + ')'
    countif10SC = 'COUNTIF(' + SCrange + ', ">10"' + ')'
    avgSC = 'AVERAGE(' + SCrange + ')'
    avgNSAF = 'AVERAGE(' + NSAFrange + ')'
    sumNSAF = 'SUM(' + NSAFrange + ')'
    Nsaf.write(newrow, newcol + rs.ncols - 1, Formula(countifSC))
    Nsaf.write(newrow, newcol + rs.ncols, Formula(countif10SC))
    Nsaf.write(newrow, newcol + rs.ncols + 1, Formula(avgSC), avgSC_style)
    if Noscaa == 'No':
        Nsaf.write(newrow, newcol + nsafcol + analysis, Formula(sumNSAF), decimal_style)
        Nsaf.write(newrow, newcol + nsafcol + analysis + 1, Formula(avgNSAF), decimal_style)
    if Noscaa == 'Yes':
        NSAFrange = cellnameabs(newrow, newcol+nsafcol+analysis) + ':' + cellnameabs(newrow, newcol + scaacol + nsafcol + analysis - 1)
        avgNSAF = 'AVERAGE(' + NSAFrange + ')'
Exemple #13
0
for s in wb.sheets():
    print 'Sheet:',s.name
    for row in range(s.nrows):
        values = []
        for col in range(s.ncols):
            values.append(s.cell(row,col).value)
        print ','.join(values)
    print

########NEW FILE########
__FILENAME__ = utilities
from xlrd import cellname, cellnameabs, colname

print cellname(0,0),cellname(10,10),cellname(100,100)
print cellnameabs(3,1),cellnameabs(41,59),cellnameabs(265,358)
print colname(0),colname(10),colname(100)

########NEW FILE########
__FILENAME__ = copy
from xlrd import open_workbook
from xlwt import easyxf
from xlutils.copy import copy

rb = open_workbook('source.xls',formatting_info=True)
rs = rb.sheet_by_index(0)
wb = copy(rb)
ws = wb.get_sheet(0)

plain = easyxf('')
for i,cell in enumerate(rs.col(2)):
Exemple #14
0
def xls2json(s, settings, output_filename):
    json_dict = {}
    json_array = []
    # 与えられたyamlの設定をしておく
    for row in range(s.nrows):
        rows = []
        for col in range(s.ncols):
            rows.append(s.cell(row, col).value)
        if row <= settings.row:
            continue
        fields = {}
        id = 0
        key = None
        for column, col in enumerate(rows):
            # Excelのカラムがコンバート対象かチェックする
            setting_column = settings.get_setting_column(row, column)
            if setting_column:
                # コンバート対象カラム
                value = col

                #==置き換え定義があるなら置き換える
                replace_value_import = setting_column.column.get('replace_value_import')
                if replace_value_import:
                    import_file = settings.path + replace_value_import
                    try:
                        f = open(import_file)
                        r = yaml.load(f.read())
                        replace_value = r
                    except IOError:
                        print >> sys.stderr, u'[%s]を開くことができない' % (import_file)
                else:
                    replace_value = setting_column.column.get('replace_value')

                if replace_value and not replace_value.get(value) is None:
                    value = replace_value[value]

                if setting_column.type == 'key':
                    try:
                        value = int(col)
                    except ValueError:
                        pass
                elif setting_column.type == 'datetime':
                    if value != '':
                        value = str(datetime.datetime(*xldate_as_tuple(value, 0)))
                    else:
                        value = None
                elif setting_column.type == 'string':
                    try:
                        value = str(int(value))
                    except ValueError:
                        pass
                elif setting_column.type == 'int':
                    try:
                        if col == u'':
                            value = 0
                        else:
                            value = int(value)
                    except ValueError, UnicodeEncodeError:
                        # 置換できなかった場合、import_dictの中に変換可能なカラムがあるかをチェック
                        if setting_column.name in settings.import_dict:
                            column_dict = settings.import_dict[setting_column.name]
                            try:
                                value = column_dict[col]
                            except KeyError:
                                print >>sys.stderr, u'%s:%sはintでなくdictを使っても変換できない' % (cellnameabs(row, column), col)
                                raise
                        else:
                            print >>sys.stderr, u'%s:%sはintに変換できない' % (cellnameabs(column, row), col)
                            raise
                            value = 0
                elif setting_column.type == 'float':
                    try:
                        value = float(col)
                    except ValueError:
                        value = 0.0
                elif setting_column.type == 'foreign_key':
                    if col == u'':
                        value = 0
                    else:
                        try:
                            value = int(col)
                        except ValueError:
                            # リレーション設定があるか?
                            if setting_column.has_relation():
                                value = setting_column.relation(col)
                            else:
                                print >>sys.stderr, u'%s:%sはリレーションIDに変換できない' % (cellnameabs(row, column), col)
                                raise
                    if value == 0:
                        value = None
                elif setting_column.type == 'boolean':
                    if len(unicode(col)) == 0:
                        value = False
                    else:
                        value = True
                else:
                    print u'存在しないカラムタイプ[%s]を指定されている' % (setting_column.type)
                    raise

                if setting_column.type == 'key':
                    key = value
                else:
                    fields[setting_column.name] = value

        # 未設定カラムを順番に処理する
        for setting_column in settings.settings_none_exist_columns:

            fields[setting_column.name] = str(setting_column.default)
        # 指定したkeyに代入
        if key is not None:
            json_dict[key] = fields
        else:
            json_array.append(fields)
        key = None
Exemple #15
0
def _rangename2d(rlo, clo, rhi, chi):
    import xlrd
    return "%s:%s" % (xlrd.cellnameabs(rlo, clo), xlrd.cellnameabs(rhi, chi))
Exemple #16
0
def WriteRDRecord(sht, base_data, target_data, start_row, bdrate_fmt, float_fmt,
                  EnablePreInterpolation = False):
    sht.write(start_row, 0, base_data.ContentClass)
    sht.write(start_row, 1, base_data.ContentName)

    #write base data
    base_start_col = 3
    cvx_cols = 2
    if EnablePreInterpolation:
        cvx_cols = 4

    base_max_rows = WriteRDData(sht, base_data, start_row, base_start_col,
                                float_fmt, EnablePreInterpolation)

    #write target data
    target_start_col = base_start_col + cvx_cols * len(QualityList) + 1
    target_max_rows = WriteRDData(sht, target_data, start_row, target_start_col,
                                  float_fmt, EnablePreInterpolation)

    #write bdrate formula
    bdrate_start_col = target_start_col + cvx_cols * len(QualityList) + 1
    total_rows = max(base_max_rows, target_max_rows)
    sht.write(start_row, 2, total_rows)
    for (qty, col) in zip(QualityList, range(len(QualityList))):
        if CalcBDRateInExcel:
            refbr_b = xlrd.cellnameabs(start_row,
                                       base_start_col + col * cvx_cols)
            refbr_e = xlrd.cellnameabs(start_row + total_rows - 1,
                                       base_start_col + col * cvx_cols)
            refq_b = xlrd.cellnameabs(start_row,
                                      base_start_col + col * cvx_cols + 1)
            refq_e = xlrd.cellnameabs(start_row + total_rows - 1,
                                      base_start_col + col * cvx_cols + 1)

            testbr_b = xlrd.cellnameabs(start_row,
                                        target_start_col + col * cvx_cols)
            testbr_e = xlrd.cellnameabs(start_row + total_rows - 1,
                                        target_start_col + col * cvx_cols)
            testq_b = xlrd.cellnameabs(start_row,
                                       target_start_col + col * cvx_cols + 1)
            testq_e = xlrd.cellnameabs(start_row + total_rows - 1,
                                       target_start_col + col * cvx_cols + 1)

            # formula = '=-bdrate(%s:%s,%s:%s,%s:%s,%s:%s)' % (
            # refbr_b, refbr_e, refq_b, refq_e, testbr_b, testbr_e, testq_b, testq_e)
            formula = '=bdRateExtend(%s:%s,%s:%s,%s:%s,%s:%s)'\
                % (refbr_b, refbr_e, refq_b, refq_e, testbr_b, testbr_e, testq_b, testq_e)
            sht.write_formula(start_row, bdrate_start_col + col, formula, bdrate_fmt)

            if EnablePreInterpolation:
                refbr_b = xlrd.cellnameabs(start_row,
                                           base_start_col + col * cvx_cols + 2)
                refbr_e = xlrd.cellnameabs(start_row + total_rows - 1,
                                           base_start_col + col * cvx_cols + 2)
                refq_b = xlrd.cellnameabs(start_row,
                                          base_start_col + col * cvx_cols + 3)
                refq_e = xlrd.cellnameabs(start_row + total_rows - 1,
                                          base_start_col + col * cvx_cols + 3)

                testbr_b = xlrd.cellnameabs(start_row,
                                            target_start_col + col * cvx_cols + 2)
                testbr_e = xlrd.cellnameabs(start_row + total_rows - 1,
                                            target_start_col + col * cvx_cols + 2)
                testq_b = xlrd.cellnameabs(start_row,
                                           target_start_col + col * cvx_cols + 3)
                testq_e = xlrd.cellnameabs(start_row + total_rows - 1,
                                           target_start_col + col * cvx_cols + 3)
                formula = '=bdRateExtend(%s:%s,%s:%s,%s:%s,%s:%s)' \
                    % (refbr_b, refbr_e, refq_b, refq_e, testbr_b, testbr_e, testq_b,
                       testq_e)

                sht.write_formula(start_row + 1, bdrate_start_col + col, formula, bdrate_fmt)
        else:
            refbrs   = [base_data.RDPoints[qty][i][0] for i in range(len(base_data.RDPoints[qty]))]
            refqtys  = [base_data.RDPoints[qty][i][1] for i in range(len(base_data.RDPoints[qty]))]
            testbrs  = [target_data.RDPoints[qty][i][0] for i in range(len(target_data.RDPoints[qty]))]
            testqtys = [target_data.RDPoints[qty][i][1] for i in range(len(target_data.RDPoints[qty]))]
            bdrate = BD_RATE(qty, refbrs, refqtys, testbrs, testqtys)
            if (bdrate != 'Non-monotonic Error'):
                bdrate /=  100.0
                sht.write_number(start_row, bdrate_start_col + col, bdrate, bdrate_fmt)
            else:
                sht.write(start_row, bdrate_start_col + col, bdrate)
            if EnablePreInterpolation:
                refbrs = [base_data.RDPoints[qty][i][2] for i in range(len(base_data.RDPoints[qty]))]
                refqtys = [base_data.RDPoints[qty][i][3] for i in range(len(base_data.RDPoints[qty]))]
                testbrs = [target_data.RDPoints[qty][i][2] for i in range(len(target_data.RDPoints[qty]))]
                testqtys = [target_data.RDPoints[qty][i][3] for i in range(len(target_data.RDPoints[qty]))]
                bdrate = BD_RATE(qty, refbrs, refqtys, testbrs, testqtys)
                if (bdrate != 'Non-monotonic Error'):
                    bdrate /= 100.0
                    sht.write_number(start_row + 1, bdrate_start_col + col, bdrate, bdrate_fmt)
                else:
                    sht.write(start_row + 1, bdrate_start_col + col, bdrate)
    return total_rows
Exemple #17
0
	replacement = r'_HUMAN'
	outprotein = re.sub(pattern, replacement, protein)
	for annotation in anno:
		if protein != "":
			#print annotation
			#print annotation[1]
			p = re.compile(r'(?i)\b(?:%s)\b' % outprotein)
			if bool(p.match(annotation[1])) == True:
				for items in annotation:
					ws.write(newrow, newcol, items)
					wn.write(newrow, newcol, items)
					newcol += 1
					proteinfound = True
				#SC/AA and NSAF information
				newcol = 26
				aalength = cellnameabs(newrow, 7)
				col = 9
				scaalist = list()
				noscaa = list()
				for col_i in range(col, rs.ncols):
					try:
						#scaa = float(rs.cell(row_index, col_i).value) / float(aalength)
						#wn.write(newrow, (newcol + scaacol), scaa)
						if annotation[3] == '-':
							wn.write(newrow, (newcol + scaacol), 0, error_style)
							newcol += 1
						else:	
							wn.write(newrow, (newcol + scaacol), Formula(cellnameabs(newrow,newcol) + '/' + aalength),decimal_style)
							wn.write(newrow, (newcol + nsafcol), Formula('(100*' + cellnameabs(newrow,newcol+scaacol) + ')/' + cellnameabs(rs.nrows-29, newcol+scaacol)),decimal_style)
							newcol += 1
					except:
Exemple #18
0
def xls2fix(s, settings, output_filename):
    fixture_list = []
    # 与えられたyamlの設定をしておく
    for row in range(s.nrows):
        rows = []
        for col in range(s.ncols):
            rows.append(s.cell(row, col).value)
        if row <= settings.row:
            continue
        fields = {}
        id = 0
        for column, col in enumerate(rows):
            # Excelのカラムがコンバート対象かチェックする
            setting_column = settings.get_setting_column(row, column)
            if setting_column:
                # コンバート対象カラム
                value = col
                if setting_column.type == 'datetime':
                    if value != '':
                        value = str(
                            datetime.datetime(*xldate_as_tuple(
                                xldate_from_datetime_tuple(eval(value), 0),
                                0)))
                    else:
                        value = None
                elif setting_column.type == 'char':
                    pass
                elif setting_column.type == 'int':
                    try:
                        if col == u'':
                            value = 0
                        else:
                            value = int(col)
                    except ValueError, UnicodeEncodeError:

                        # 置換できなかった場合、import_dictの中に変換可能なカラムがあるかをチェック
                        print settings.import_dict, "****************"
                        print setting_column.name, "this column is"
                        if setting_column.name in settings.import_dict:
                            print setting_column.name, "this column is"
                            column_dict = settings.import_dict[
                                setting_column.name]
                            try:
                                value = column_dict[col]
                            except KeyError:
                                print >> sys.stderr, u'%s:%sはintでなくdictを使っても変換できない' % (
                                    cellnameabs(row, column), col)
                                raise
                        else:
                            print >> sys.stderr, u'%s:%sはintに変換できない' % (
                                cellnameabs(column, row), col)
                            raise
                            value = 0
                elif setting_column.type == 'float':
                    try:
                        try:
                            tmp = "%s.%sf" % ("%", len(str(col).split('.')[1]))
                            value = float(tmp % float(col))
                        except:
                            value = float(col)
                    except ValueError:
                        value = 0.0
                elif setting_column.type == 'foreign_key':
                    if col == u'':
                        value = 0
                    else:
                        try:
                            value = int(col)
                        except ValueError:
                            # リレーション設定があるか?
                            if setting_column.has_relation():
                                value = setting_column.relation(col)
                            else:
                                print >> sys.stderr, u'%s:%sはリレーションIDに変換できない' % (
                                    cellnameabs(row, column), col)
                                raise
                    if value == 0:
                        value = None
                elif setting_column.type == 'boolean':
                    if len(unicode(col)) == 0:
                        value = False
                    else:
                        value = True
                else:
                    print u'存在しないカラムタイプ[%s]を指定されている' % (setting_column.type)
                    raise

                if setting_column.name == 'id':
                    try:
                        id = int(value)
                    except:
                        id = str(value)
                else:
                    fields[setting_column.name] = value

        # 未設定カラムを順番に処理する
        for setting_column in settings.settings_none_exist_columns:

            fields[setting_column.name] = str(setting_column.default)

        fixture_list.append({
            'model': settings.model,
            'pk': id,
            'fields': fields,
        })
Exemple #19
0
print("获取单元格数据类型:", sheet1.row_types(0))
print("******************************")
#表操作
print("获取第1行的第6-10列的值,不包括第10列:", sheet1.row_values(0, 6, 10))
print("获取第一列,第0-5行的值,不包括第5行:", sheet1.col_values(0, 0, 5))
print("获取第6列的所有的值:", sheet1.col_values(6, 1))
six_col = sheet1.col_values(6, 1)
print(six_col)
print(len(six_col))
print("******************************")
#获取特定单元格的值和类型
print("获取第20行第一列的值:", sheet1.cell_value(19, 0))
print("获取第20行第一列的值:", sheet1.cell(19, 0).value)
print("获取第20行第一列的值:", sheet1.row(19)[0].value)
print("获取第20行第一列的单元格的类型:", sheet1.cell_type(19, 0))
print("获取第20行第一列的单元格的类型:", sheet1.cell(19, 0).ctype)
print("获取第20行第一列的单元格的类型:", sheet1.row(19)[0].ctype)
print("******************************")
#(0,0)转换A1
print("(0,0)转换A1:", xlrd.cellname(0, 0))
print("(0,0)转换A1:", xlrd.cellnameabs(0, 0))
print("(0,0)转换A1:", xlrd.colname(509))
'''数据类型:
空:0
字符串:1
数字:2
日期:3
布尔:4
error:5
'''