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)
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
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)
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)
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))
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)
'''五、特定单元格读取'''
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:
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)
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,
})
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 + ')'
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)):
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
def _rangename2d(rlo, clo, rhi, chi):
import xlrd
return "%s:%s" % (xlrd.cellnameabs(rlo, clo), xlrd.cellnameabs(rhi, chi))
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
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:
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,
})
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
'''