def write_1_n_liyang_excel(self):
wb = Workbook()
ew = ExcelWriter(workbook=wb)
the_sheet = wb.create_sheet(u"sku不适配车型", 0)
insert_list = (u'liyang_id', u'品牌', u'厂家', u'车系', u'车型', u'年款', u'排量',
u'进气形式', u'最大功率', u'燃料类型', u'机油滤清器', u'滤清器尺寸')
self.write_excel_cell(the_sheet, 1, insert_list)
is_sheet_num = 2
for liyang_id, goods_set in self.liyang_car_goods_dict.iteritems():
if len(goods_set) < 2:
continue
liyang_data = self.liyang_id_data_dict[liyang_id]
for goods_format in goods_set:
goods_size = self.goods_dict[goods_format]
self.write_excel_cell(
the_sheet, is_sheet_num,
(liyang_id, liyang_data['car_brand'],
liyang_data['factory_name'], liyang_data['car_series'],
liyang_data['vehicle_type'], liyang_data['model_year'],
liyang_data['displacement'], liyang_data['intake_style'],
liyang_data['max_power'], liyang_data['fuel_type'],
goods_format, goods_size))
is_sheet_num += 1
ew.save(filename=self.file_parent + '/4.liyang车型对应多个机滤.xlsx')
def main():
if len(sys.argv) != 3:
print_help()
return
excel_file = Workbook()
excel_writer = ExcelWriter(workbook=excel_file)
sheet = excel_file.worksheets[0]
csv_file = open(sys.argv[1], "r")
row = 1
for line in csv_file:
line = line.strip().decode("utf-8", "ignore")
ary = line.split("@")
col = 1
for item in ary:
col_letter = get_column_letter(col)
sheet.cell("%s%s" % (col_letter, row)).value = "%s" % (item.encode("utf-8", "ignore"))
col = col + 1
row = row + 1
excel_writer.save(filename=sys.argv[2])
return
def saveAsExcel(self,
fileName,
sheetName='data',
matrixOnly=False,
appendFile=True):
if not haveOpenpyxl:
raise ImportError(
'openpyxl is required for saving files in Excel (xlsx) format, but was not found.'
)
if not fileName.endswith('.xlsx'):
fileName += '.xlsx'
#create or load the file
if appendFile and os.path.isfile(fileName):
wb = load_workbook(fileName)
newWorkbook = False
else:
wb = Workbook() #create new workbook
newWorkbook = True
ew = ExcelWriter(workbook=wb)
if newWorkbook:
ws = wb.worksheets[0]
ws.title = sheetName
else:
ws = wb.create_sheet()
ws.title = sheetName
#write the data
# trials data
ws.cell('A1').value = 'Trial No'
ws.cell('B1').value = 'Stimulis'
ws.cell('C1').value = 'Step Size'
ws.cell('D1').value = 'Response'
for orienN, stimuli in enumerate(self.stimuli):
rowN = orienN + 2
ws.cell(column=1, row=rowN).value = orienN + 1
ws.cell(column=2, row=rowN).value = stimuli
ws.cell(column=3, row=rowN).value = self.stepSizes[orienN]
ws.cell(column=4, row=rowN).value = self.data[orienN]
# reversals data
ws.cell('E1').value = 'Directions'
ws.cell('F1').value = 'Reversal Numbers'
for revN, revIntens in enumerate(self.directions):
rowN = revN + 2
ws.cell(column=5, row=rowN).value = self.directions[revN]
ws.cell(column=6, row=rowN).value = self.Reversals[revN]
# other data
col = 1
for key in self.otherData.keys():
ws.cell(column=col + 6, row=1).value = key
for rtN, rtIntens in enumerate(self.otherData[key]):
rowN = rtN + 2
ws.cell(column=col + 6, row=rowN).value = rtIntens
col += 1
ew.save(filename=fileName)
def WriteExcel(tFileName, tDict):
if len(tFileName) == 0:
return
# 新建一个workbook
wb = Workbook()
# 新建一个excelWriter
ew = ExcelWriter(workbook=wb)
# 第一个sheet是ws
ws = wb.worksheets[0]
# wb.add_sheet()
# 设置ws的名称
ws.title = "第一主播"
# 设置文件输出路径与名称
ws.cell("A1").value = '23'
ws.cell("B1").value = '34'
# 向某个单元格中写入数据
lIndex = 1
for itorTuple in tDict:
lIndex += 1
cellA = str.format("A%s"% lIndex)
cellB = str.format("B%s"% lIndex)
ws.cell(cellA).value = itorTuple[0]
ws.cell(cellB).value = itorTuple[1]
# 最后保存文件
ew.save(filename=tFileName)
def save_search_data(get_list, session, location=0, num=0):
# location: 是否要获取目标地址,1获取，0不获取
wb = load_workbook(SAVE_FILE_NAME)
ew = ExcelWriter(wb)
ws = wb.get_sheet_by_name(SHEET_NAME)
for i in get_list:
i = i.encode('utf-8')
i += u''
if location == 0:
coordinate = None
else:
coordinate = get_location(i, session)
temp_replace = re.compile('<[\s\S]+?>')
i = temp_replace.sub(' ', i)
i += u'\n'
i = i.decode('string-escape').encode('gbk')
i = unicode(i, 'unicode-escape')
i = i.encode('utf-8')
if coordinate:
num += 1
longitude = coordinate[0]
latitude = coordinate[1]
ws.cell(row=num+1, column=2).value = longitude
ws.cell(row=num+1, column=3).value = latitude
ws.cell(row=num+1, column=1).value = i
elif num == 0:
num += 1
ws.cell(row=num+1, column=1).value = i
ew.save(SAVE_FILE_NAME)
print (num)
return num
def write_to_excel_with_openpyxl(self, records, head_row, save_excel_name="save.xlsx"):
#新建一个workbook
wb = Workbook()
#新建一个excelWriter
ew = ExcelWriter(workbook=wb)
#设置文件输出路径与名称
dest_filename = save_excel_name.decode('utf-8')
#第一个sheet是ws
ws = wb.worksheets[0]
#设置ws的名称
ws.title = "range names"
#写第一行，标题行
for h_x in range(1, len(head_row) + 1):
h_col = get_column_letter(h_x)
#print h_col
ws.cell('%s%s' % (h_col, 1)).value = '%s' % (head_row[h_x - 1])
i = 2
for record in records:
record_list = str(record).strip().split("\t")
for x in range(1, len(record_list) + 1):
col = get_column_letter(x)
ws.cell('%s%s' % (col, i)).value = '%s' % (record_list[x - 1].decode('utf-8'))
i += 1
ew.save(filename=dest_filename)
def write_to_excel(excel_file, data_list, column_width=20.0):
wb = Workbook()
excel_writer = ExcelWriter(workbook=wb)
sheet = wb.worksheets[0]
sheet.name = ''
merge_row = -1
for row_index, row_list in enumerate(data_list):
if len(row_list) == 1: # 合并单元格
merge_row = row_index + 1
for cell_index, cell in enumerate(row_list):
sheet.cell(row=row_index + 1,
column=cell_index + 1).value = cell
# 设置列宽度
max_column = sheet.max_column
for x in range(max_column):
sheet.column_dimensions[chr(65 + x)].width = column_width
# 合并单元格，设置单元格样式
if merge_row >= 0:
sheet.merge_cells('A1:%s1' % (chr(65 + max_column - 1)), 1, 1, 1,
max_column - 1)
cell = sheet.cell('A1')
font = Font(name=u'宋体', size=14, color=BLACK, bold=True)
align = Alignment(horizontal='center', vertical='center')
cell.font = font
cell.alignment = align
excel_writer.save(excel_file)
def save_search_data(get_list, session, location=0, num=0):
# location: 是否要获取目标地址,1获取，0不获取
wb = load_workbook(SAVE_FILE_NAME)
ew = ExcelWriter(wb)
ws = wb.get_sheet_by_name(SHEET_NAME)
for i in get_list:
i = i.encode('utf-8')
i += u''
if location == 0:
coordinate = None
else:
coordinate = get_location(i, session)
temp_replace = re.compile('<[\s\S]+?>')
i = temp_replace.sub(' ', i)
i += u'\n'
i = i.decode('string-escape').encode('gbk')
i = unicode(i, 'unicode-escape')
i = i.encode('utf-8')
if coordinate:
num += 1
longitude = coordinate[0]
latitude = coordinate[1]
ws.cell(row=num + 1, column=2).value = longitude
ws.cell(row=num + 1, column=3).value = latitude
ws.cell(row=num + 1, column=1).value = i
elif num == 0:
num += 1
ws.cell(row=num + 1, column=1).value = i
ew.save(SAVE_FILE_NAME)
print(num)
return num
def write_sku_excel(self):
wb = Workbook()
ew = ExcelWriter(workbook=wb)
the_sheet = wb.create_sheet(u"sku适配车型", 0)
insert_list = (u'云修号', u'car_id', u'品牌', u'车系', u'车型', u'排量', u'年款',
u'名称')
self.write_excel_cell(the_sheet, 1, insert_list)
is_sheet_num = 2
for yun_id, car_id_set in self.goods_car_dict.iteritems():
if len(car_id_set) == 0:
self.write_excel_cell(the_sheet, is_sheet_num,
(yun_id, u'None', u'None', u'None',
u'None', u'None', u'None', u'None'))
is_sheet_num += 1
else:
for car_id in car_id_set:
if car_id in self.not_car_list:
self.not_car_list.remove(car_id)
# id,brand,series,model,power,year,name
goods_car_data = self.car_category_dict[car_id]
self.write_excel_cell(
the_sheet, is_sheet_num,
(yun_id, str(car_id), str(goods_car_data['brand']),
str(goods_car_data['series']),
str(goods_car_data['model']),
str(goods_car_data['power']),
str(goods_car_data['year']),
str(goods_car_data['name'])))
is_sheet_num += 1
ew.save(filename=self.file_parent + '/1.滤清器适配车型.xlsx')
def writer_excel_testresult(self, case_list, result_info_list):
log_msg = a.RiZhi()
log_msg.log_def()
try:
wb = load_workbook(self.casefile_path)
ew = ExcelWriter(workbook=wb)
ws_sheet = wb.get_sheet_by_name(self.testsheetname)
row_sum = len(ws_sheet.rows)
#tmp_case_list = self.read_excel_caseinfo()
#print('excel中读取的caselist',tmp_case_list)
print('执行caselist:', case_list)
print('测试结果：', result_info_list)
print('开始回填结果判断')
i = 0
for case_id in case_list:
#for case_id_tmp in tmp_case_list:
#if case_id_tmp == case_id:
for row in range(3, row_sum):
case_id_TM = ws_sheet.cell('D%s' % row).value
if case_id == case_id_TM:
result_info_tmp = result_info_list[i]
ws_sheet.cell('o%s' % row).value = result_info_tmp
print('%s的结果回填完毕' % case_id)
i = i + 1
print(i)
else:
print('该case未执行，继续往下填写测试结果')
#else:
#print('执行case与用例case不匹配，继续查找。。。')
ew.save('test_result' + '.xlsx')
except Exception as e:
print("读取error %s" % e)
def save_dump(workbook, filename):
if workbook.worksheets == []:
workbook.create_sheet()
writer = ExcelWriter(workbook)
writer.comment_writer = DumpCommentWriter
writer.save(filename)
return True
def save_dump(workbook, filename):
if workbook.worksheets == []:
workbook.create_sheet()
writer = ExcelWriter(workbook)
writer.comment_writer = DumpCommentWriter
writer.save(filename)
return True
def write_to_xlsx_file(content, keys, file_path):
if os.path.exists(file_path) == False:
createFile(file_path)
wb = openpyxl.Workbook()
ew = ExcelWriter(workbook=wb)
wsheet = wb.worksheets[0]
is_close = raw_input('please be sure to close your xlsx file (y/n)?')
if is_close != 'y':
print '未关闭文件，退出程序'
return
rows = [row for row in wsheet.iter_rows(get_content_range_in_xlsx(keys.__len__() + 1))]
rows[0][0].value = TITLE_ANDROID_KEY
rows[0][1].value = TITLE_CN_STRING_VALUE
rows[0][2].value = TITLE_EN_STRING_VALUE
rows[0][3].value = TITLE_REMARKS
for i in range(0, keys.__len__()):
rows[i + 1][0].value = keys[i]
rows[i + 1][1].value = content[keys[i]]
continue
# print rows[i + 1][0].value, rows[i + 1][1].value
ew.save(file_path)
return
def wirteContent(file, data, cell):
wb = load_workbook(file)
ew = ExcelWriter(wb)
ws = wb.get_sheet_by_name(wb.get_sheet_names()[0])
ws.cell(cell).value = data
ws.title = os.path.splitext(file)[0]
ew.save(file)
def save_excel_Eastern(total_info, updata_xlsx_file):
from openpyxl.reader.excel import load_workbook #讀取用
from openpyxl.writer.excel import ExcelWriter #存檔用
wb = load_workbook(updata_xlsx_file)
ws = wb.get_sheet_by_name("工作表1")
row_counts = ws.max_row #取得行列數
range_string = "A{}:H{}".format(row_counts + 1,
row_counts + 10) #一共三筆資料要存放
data = ws.iter_rows(range_string) #取出空的欄位
'''前置區 把抓到資料用城可讀取'''
abc_list = [] #給存放的list 才可以到 row
for i in total_info:
abc_list.append(i)
"""運算區 資料塞進到excel"""
row = 0 #從這 抓進去的資料從第0行開始
for a in data:
column = 0 #從這 抓進去的資列從第0欄開始
for cell in a:
cell.value = abc_list[row][column]
column = column + 1
row = row + 1
ew = ExcelWriter(workbook=wb) #新建立一個 excelWriter
ew.save(filename=updata_xlsx_file)
def main(self):
print '=============start================'
# 10752 110884 211269 311294 411446
min_id = '411446'
file_index = 4
all_truck_car_sql = "select id,car_name,batch_number,car_type_id,car_category_id,car_remark from sc_truck_car where source= '商车网' and batch_number != '' and id > " + min_id + " order by id limit 100000"
print 'all_truck_car_sql : %s' % all_truck_car_sql
truck_array = self.dao.db.get_data(all_truck_car_sql)
truck_index = 1
max_size = 50000
wb = Workbook()
ew = ExcelWriter(workbook=wb)
self.create_heard(wb)
file_name = self.file_name.replace("NUMBER", str(file_index))
for truck_data in truck_array:
# if truck_index > max_size:
# ew.save(filename=file_name)
# truck_index = 1
# wb = Workbook()
# ew = ExcelWriter(workbook=wb)
# self.create_heard(wb)
# file_index += 1
# file_name = self.file_name.replace("NUMBER", str(file_index))
truck_index += 1
truck_id = truck_data['id']
# 数据存第一个sheet
self.car_sheet.cell(row=truck_index, column=1).value = truck_id
self.car_sheet.cell(row=truck_index,
column=2).value = truck_data['car_name']
self.car_sheet.cell(row=truck_index,
column=3).value = truck_data['batch_number']
self.car_sheet.cell(
row=truck_index,
column=4).value = self.type_name[truck_data['car_type_id']]
self.car_sheet.cell(
row=truck_index,
column=5).value = self.car_name[truck_data['car_category_id']]
self.car_sheet.cell(row=truck_index, column=6).value = self.source
self.car_sheet.cell(row=truck_index,
column=7).value = truck_data['car_remark']
# self.car_sheet.write(truck_index, 0, truck_id)
# self.car_sheet.write(truck_index, 1, truck_data['car_name'])
# self.car_sheet.write(truck_index, 2, truck_data['batch_number'])
# self.car_sheet.write(truck_index, 3, self.type_name[truck_data['car_type_id']])
# self.car_sheet.write(truck_index, 4, self.car_name[truck_data['car_category_id']])
# self.car_sheet.write(truck_index, 5, self.source)
# self.car_sheet.write(truck_index, 6, truck_data['car_remark'])
# 第二个sheet
self.add_data_to_other_sheet(truck_id)
if truck_index != 1:
ew.save(filename=file_name)
print 'end'
def save(self, xls_file):
"""Save log items as Excel files.
Args:
xls_file (str): path to the Excel file.
"""
ew = ExcelWriter(workbook=self.ner_log_workbook)
ew.save(filename=xls_file)
def get_data(self):
archive = ZipFile(self.output, 'w', ZIP_DEFLATED, allowZip64=True)
writer = ExcelWriter(self.wb, archive)
try:
writer.write_data()
writer.save()
finally:
archive.close()
return self.output.getvalue()
def guardarWorkbook(workbook):
bufer = BytesIO()
writer = ExcelWriter(
workbook,
ZipFile(bufer, "w", compression=ZIP_STORED, allowZip64=False)
)
writer.save()
bytes = bufer.getvalue()
del(bufer)
return bytes
def main(self, fileName):
print '====start ======'
wb = Workbook()
ew = ExcelWriter(workbook=wb)
self.add_header_to_sheet(wb)
self.add_data_to_sheet(wb)
ew.save(filename=fileName)
print '====end ======'
def lst2xl(records, fileName, row):
'''将list写入excel2007 需要指定写入的行'''
from openpyxl.workbook import Workbook
from openpyxl.writer.excel import ExcelWriter
from openpyxl.cell import get_column_letter
wb = Workbook()
ew = ExcelWriter(workbook=wb)
ws = wb.worksheets[0]
ws.title = '1'
for record in records:
for x in range(1, len(record) + 1):
col = get_column_letter(x)
ws.cell('%s%s' % (col, row)).value = '%s' % (record[x - 1])
row += 1
ew.save(filename=fileName)
def write_excel(filename, sheetname,headings, data):
wb=Workbook()
ew=ExcelWriter(workbook=wb)
ws=wb.worksheets[0]
ws.title=sheetname
#begin to write the headings
row=1
for i,heading in enumerate(headings):
col=get_column_letter(i+1)
ws.cell('%s%s'%(col,row)).value='%s'%(heading)
for row,rowdata in enumerate(data):
for i,celldata in enumerate(rowdata):
col=get_column_letter(i+1)
ws.cell('%s%s'%(col,(row+2))).value='%s'%(celldata)
ew.save(filename)
def export(title,header,rows):
wb = Workbook()
ew = ExcelWriter(workbook = wb)
dest_filename = r'/opt/aoaola/web/html/excel/' + title +'.xlsx'
ws = wb.worksheets[0]
for x in range(1,len(header)+1):
col = get_column_letter(x)
ws.cell('%s%s'%(col, 1)).value = '%s' % (header[x-1])
for i in range(len(rows)):
assert len(rows[i]) == 6
for j in range(len(rows[i])):
ws.cell(row=i+1,column=j).value = rows[i][j]
ew.save(filename = dest_filename)
def txtToCSV(pdf, omit_pages):
for i, item in enumerate(pdf):
pages = layout.get_pages(item)
text = []
for index, page in enumerate(pages):
if index+1 not in omit_pages[i]:
text.append(page)
text = ''.join(text)
sents = sent_tokenize(text)
wb = Workbook()
ew = ExcelWriter(workbook=wb)
dest_filename = item.split('.')[0]+'.xlsx'
ws = wb.worksheets[0]
for index, line in enumerate(sents):
ws.cell(row=index, column=0).value = line
ew.save(filename=dest_filename)
def write_excel(filename, sheetname, headings, data):
wb = Workbook()
ew = ExcelWriter(workbook=wb)
ws = wb.worksheets[0]
ws.title = sheetname
#begin to write the headings
row = 1
for i, heading in enumerate(headings):
col = get_column_letter(i + 1)
ws.cell('%s%s' % (col, row)).value = '%s' % (heading)
for row, rowdata in enumerate(data):
for i, celldata in enumerate(rowdata):
col = get_column_letter(i + 1)
ws.cell('%s%s' % (col, (row + 2))).value = '%s' % (celldata)
ew.save(filename)
def write_not_car_excel(self):
wb = Workbook()
ew = ExcelWriter(workbook=wb)
the_sheet = wb.create_sheet(u"sku不适配车型", 0)
insert_list = (u'car_id', u'品牌', u'车系', u'车型', u'排量', u'年款', u'名称')
self.write_excel_cell(the_sheet, 1, insert_list)
is_sheet_num = 2
for car_id in self.not_car_list:
goods_car_data = self.car_category_dict[car_id]
self.write_excel_cell(
the_sheet, is_sheet_num,
(str(car_id), str(goods_car_data['brand']),
str(goods_car_data['series']), str(goods_car_data['model']),
str(goods_car_data['power']), str(
goods_car_data['year']), str(goods_car_data['name'])))
is_sheet_num += 1
ew.save(filename=self.file_parent + '/2.滤清器不适配车型.xlsx')
def create(self, tables, pdic):
doc = Workbook()
ew = ExcelWriter(workbook=doc)
for table in tables:
sheet = doc.worksheets[0]
sheet.title = table
i = 0
for port in tables[table]:
c = sheet.cell(row=0, column=i)
c.value = port + '[' + ` pdic[port][2] ` + ']'
i += 1
try:
ew.save(filename=self.file_name)
return True
except:
return False
def create(self, tables, pdic):
doc = Workbook()
ew = ExcelWriter(workbook=doc)
for table in tables:
sheet = doc.worksheets[0]
sheet.title = table
i = 0
for port in tables[table]:
c = sheet.cell(row=0, column=i)
c.value = port + "[" + ` pdic[port][2] ` + "]"
i += 1
try:
ew.save(filename=self.file_name)
return True
except:
return False
def main(self):
wb = Workbook()
ew = ExcelWriter(workbook=wb)
car_sheet = wb.create_sheet(u"新老分类", 0)
# 第一行
car_sheet.cell(row=1, column=1).value = u'一级分类'
car_sheet.cell(row=1, column=2).value = u'二级分类'
# 其余行
row_num = 2
for cat_id in self.second_cat_list:
car_sheet.cell(row=row_num, column=1).value = self.cate_id_name[
self.cate_id_parent[cat_id]]
car_sheet.cell(row=row_num,
column=2).value = self.cate_id_name[cat_id]
row_num += 1
ew.save(filename=self.file_name)
class Excel2007Write():
excelWriter = path = workBook = sheet = None;
def __init__(self, path):
self.path = path
self.workBook = Workbook()
self.excelWriter = ExcelWriter(workbook=self.workBook)
def createSheet(self, index, name):
sheet = self.workBook.worksheets[index]
sheet.title = name
return sheet
def set(self, sheet_obj, x, y, value):
col = get_column_letter(x + 1)
sheet_obj.cell('%s%s' % (col, y + 1)).value = value
def save(self):
self.excelWriter.save(filename=path)
def write_to_excel_by_sheet_data_list(excel_file, sheet_data_list):
"""
数据写入多个sheet
:param excel_file:
:param sheet_data_list: sheet 数据字典列表
:return:
"""
wb = Workbook()
excel_writer = ExcelWriter(workbook=wb)
for sheet_index, sheet_data in enumerate(sheet_data_list):
if sheet_index == 0:
sheet = wb.worksheets[sheet_index]
else:
sheet = wb.create_sheet()
data_list = sheet_data['data_list']
column_width = 20.0
merge_row = -1
if 'title' in sheet_data:
sheet.title = sheet_data['title']
if 'column_width' in sheet_data:
column_width = sheet_data['column_width']
for row_index, row_list in enumerate(data_list):
if len(row_list) == 1: # 合并单元格
merge_row = row_index + 1
for cell_index, cell_value in enumerate(row_list):
sheet.cell(row=row_index + 1,
column=cell_index + 1).value = cell_value
# 设置列宽度
max_column = sheet.max_column
for x in range(max_column):
sheet.column_dimensions[chr(65 + x)].width = column_width
# 合并单元格，设置单元格样式
if merge_row >= 0:
sheet.merge_cells('A1:%s1' % (chr(65 + max_column - 1)), 1, 1,
1, max_column - 1)
cell = sheet.cell('A1')
font = Font(name=u'宋体', size=14, color=BLACK, bold=True)
align = Alignment(horizontal='center', vertical='center')
cell.font = font
cell.alignment = align
excel_writer.save(excel_file)
def main(self, brand_name, save_file):
wb = Workbook()
ew = ExcelWriter(workbook=wb)
relation_db_name = str(
self.dao.db.get_data(
"select liyang_table from db_monkey_part_liyang_table_relation where car_brand_name = '"
+ brand_name + "'")[0]['liyang_table'])
model_array = self.dao.db.get_data(
"select id,liyang_factory,liyang_model,liyang_series "
"from db_monkey_part_liyang_base "
"where liyang_brand = '" + brand_name + "' and is_deleted = 'N'")
sheet_index = 0
for model_data in model_array:
id = str(model_data['id'])
# create sheet
sheet_name = str(model_data['liyang_factory']) + "-" + str(
model_data['liyang_series']) + "-" + str(
model_data['liyang_model'])
the_sheet = wb.create_sheet(sheet_name.decode("utf-8"),
sheet_index)
sheet_index += 1
self.create_sheet(the_sheet)
row_num = 1
goods_sql = "select dg.part_name as part_name,dg.oe_number as oe from " \
"(select goods_id " \
"from "+relation_db_name+" " \
"where part_liyang_id = " + id + \
" group by goods_id " \
") dmplr,db_monkey_part_goods_base dg " \
"where dg.uuid = dmplr.goods_id "
goods_array = self.dao.db.get_data(goods_sql)
for goods_data in goods_array:
row_num += 1
the_sheet.cell(row=row_num,
column=1).value = str(goods_data['part_name'])
the_sheet.cell(row=row_num,
column=2).value = str(goods_data['oe'])
ew.save(filename=save_file)
def write_excel(directory,filename, sheetname,headings, data,comment=None):
wb=Workbook()
ew=ExcelWriter(workbook=wb)
ws=wb.worksheets[0]
ws.title=sheetname
#begin to write the headings
row=1
for i,heading in enumerate(headings):
col=get_column_letter(i+1)
ws.cell('%s%s'%(col,row)).value='%s'%(heading)
#write the cell content
for row,rowdata in enumerate(data):
for i,celldataKey in enumerate(rowdata):
col=get_column_letter(i+1)
ws.cell('%s%s'%(col,(row+2))).value='%s'%(rowdata[celldataKey])
if not os.path.exists(directory):
os.makedirs(directory)
ew.save(directory+filename)
def writeToExcel(dict):
# inwb = load_workbook(filename="D:/test/洗涤.xlsx")
wb = Workbook()
ew = ExcelWriter(workbook=wb)
dest_filename = '1122.xlsx'
ws = wb.worksheets[0]
ws.title = "洗涤产品详情"
col_list = ['A', 'B', 'C', 'D']
i = 0
for key in dict:
j = 0
i += 1
if i <= len(dict):
for col in col_list:
data_list = dict[key]
if j < len(data_list):
ws.cell('%s%s' % (col, i)).value = '%s' % (dict[key][j])
j += 1
ew.save(filename=dest_filename)
def write_to_excel_with_openpyxl(df,
columnHeaders,
filepath="save.xlsx",
pageSize=10000):
# 设置文件输出路径与名称
# 新建一个workbook
wb = Workbook()
from zipfile import ZipFile, ZIP_DEFLATED
archive = ZipFile(filepath, 'w', ZIP_DEFLATED)
# 新建一个excelWriter
ew = ExcelWriter(workbook=wb, archive=archive)
# 第一个sheet是ws
ws = wb.worksheets[0]
# 设置ws的名称
ws.title = "range names"
length = len(columnHeaders)
# 写第一行，标题行
for i in range(1, length + 1):
value = columnHeaders[i - 1]
ws.cell(1, i, value)
# 写第二行及其以后的那些行
i = 2
for index, row in df.iterrows():
for j in range(1, length + 1):
# ws.cell(i, j, row[j - 1])
try:
ws.cell(row=i, column=j).value = str(row[j - 1]).encode(
"utf-8", errors="ignore")
except Exception as e:
print(e)
if i % pageSize == 0:
ew.save(filepath)
archive = ZipFile(filepath, 'w', ZIP_DEFLATED)
ew = ExcelWriter(workbook=wb, archive=archive)
i += 1
if i % pageSize != 0:
# 写文件
ew.save(filepath)
print("导出成功:" + filepath)
def sendExcel(fileDir,matrixList,sheetNameList):
#check that input lists have same length (one sheet name for each matrix)
if len(matrixList) != len(sheetNameList):
print 'List of variables being exported and of sheet names must be equal.'
return
#Set up excel workbook
wb = Workbook()
ew = ExcelWriter(workbook = wb)
dest_filename = fileDir
#Create sheet and export data for every matrix being exported
for matrix,sheetN in zip(matrixList,sheetNameList):
ws = wb.create_sheet()
ws.title = sheetN
#For a matrix
if len(matrix.shape) == 2:
#Dump matrix in the Excel sheet
(r,c) = matrix.shape
for element in xrange(c):
col = get_column_letter(element+2) #2, so it will start printing on B
for row in xrange(r):
ws.cell('%s%s'%(col, row+2)).value = matrix[row][element]
#For a vector
elif len(matrix.shape) == 1:
r = matrix.shape
r = r[0]
col = get_column_letter(2) #2, so it will start printing on B
for row in xrange(r):
ws.cell('%s%s'%(col, row+2)).value = matrix[row]
else:
print 'Only exports matrices and vectors!'
print 'Weird element found. \n'
return
## #Create another sheet in same workbook and put Pi
## ws = wb.create_sheet()
## ws.title = 'Pi'
## ws.cell('F5').value = 3.14
ew.save(dest_filename)
print 'Matrix has been exported to Excel.'
class XlsxExcel(AbstractExcel):
'''处理Excel 2007的子类'''
eb = None
def __init__(self, excel_name):
AbstractExcel.__init__(self, excel_name)
def OpenExcel(self):
if not self.exists:
return False
self.wb = load_workbook(self.ename)
return self
def GetSheet(self, num):
self.sh = self.wb.get_sheet_by_name(self.wb.get_sheet_names()[int(num)])
return self
def GetRowsNum(self):
return self.sh.get_highest_row()
def GetColsNum(self):
return self.sh.get_highest_col()
def GetCell(self, _row, _column):
return str(self.sh.cell(row = _row, column = _column).value)
def CreateExcel(self):
self.eb = Workbook()
self.w_wb = ExcelWriter(workbook = self.eb)
return self
def CreateSheet(self, sheet_name):
self.w_sh = self.eb.worksheets[int(sheet_name)]
return self
def SetCell(self, _row, _column, content):
self.w_sh.cell(row = _row, column = _column).value = content
return self
def Save(self):
self.w_wb.save(filename = self.ename)
def lst2xlWithHeaders(records, fileName, row, headers):
"""将list写入excel2007 需要为第一行指定表头
格式：headers = ['id','Stkcd','Reptdt']"""
from openpyxl.workbook import Workbook
from openpyxl.writer.excel import ExcelWriter
from openpyxl.cell import get_column_letter
wb = Workbook()
ew = ExcelWriter(workbook=wb)
ws = wb.worksheets[0]
ws.title = '1'
x = 1
i = 1
for x in range(1, len(headers) + 1):
col = get_column_letter(x)
ws.cell('%s%s' % (col, i)).value = '%s' % (headers[x - 1])
ii = 2
for record in records:
for x in range(1, len(record) + 1):
col = get_column_letter(x)
ws.cell('%s%s' % (col, ii)).value = '%s' % (record[x - 1])
ii += 1
ew.save(filename=fileName)
def write_1_n_excel(self):
wb = Workbook()
ew = ExcelWriter(workbook=wb)
the_sheet = wb.create_sheet(u"sku不适配车型", 0)
insert_list = (u'car_id', u'品牌', u'车系', u'车型', u'排量', u'年款', u'名称',
u'云修号')
self.write_excel_cell(the_sheet, 1, insert_list)
is_sheet_num = 2
for car_id, goods_set in self.car_goods_dict.iteritems():
if len(goods_set) < 2:
continue
goods_car_data = self.car_category_dict[car_id]
for yun_id in goods_set:
self.write_excel_cell(
the_sheet, is_sheet_num,
(str(car_id), str(goods_car_data['brand']),
str(goods_car_data['series']),
str(goods_car_data['model']), str(
goods_car_data['power']), str(goods_car_data['year']),
str(goods_car_data['name']), yun_id))
is_sheet_num += 1
ew.save(filename=self.file_parent + '/3.淘汽车型对应多个机滤.xlsx')
def write_to_excel_with_openpyxl(self,
records,
NL,
exportFields,
save_excel_name="save.xlsx",
title='range names'):
# 新建一个workbook
wb = Workbook()
# 新建一个excelWriter
ew = ExcelWriter(workbook=wb)
# 设置文件输出路径与名称
dest_filename = save_excel_name.decode('utf-8')
# 第一个sheet是ws
ws = wb.worksheets[0]
# 设置ws的名称
ws.title = title
# 写第一行，标题行
exportFields = exportFields.split(',')
j = 1
for h_x in range(0, len(NL)):
if NL[h_x][0] in exportFields:
h_col = get_column_letter(j)
ws.cell('%s%s' % (h_col, 1)).value = '%s' % (NL[h_x][8])
j += 1
# 写第二行及其以后的那些行
i = 2
for record in records:
j = 1
for x in range(0, len(record)):
if NL[x][0] in exportFields:
col = get_column_letter(j)
ws.cell('%s%s' % (col, i)).value = record[x]
j += 1
i += 1
# 写文件
ew.save(filename=dest_filename)
def write_to_excel_with_openpyxl(self,
records,
NL,
exportFields,
save_excel_name="save.xlsx",
title='range names'):
# 新建一个workbook
wb = Workbook()
# 新建一个excelWriter
from zipfile import ZipFile, ZIP_DEFLATED
archive = ZipFile(save_excel_name, 'w', ZIP_DEFLATED)
ew = ExcelWriter(workbook=wb, archive=archive)
# 设置文件输出路径与名称
dest_filename = save_excel_name.decode('utf-8')
# 第一个sheet是ws
ws = wb.worksheets[0]
# 设置ws的名称
ws.title = title
# 写第一行，标题行
exportFields = exportFields.split(',')
j = 1
for h_x in range(0, len(NL)):
if NL[h_x][0] in exportFields:
ws.cell(row=1, column=j).value = '%s' % (NL[h_x][8])
j += 1
# 写第二行及其以后的那些行
i = 2
for record in records:
j = 1
for x in range(0, len(record)):
if NL[x][0] in exportFields:
ws.cell(row=i, column=j).value = record[x]
j += 1
i += 1
# 写文件
ew.save(filename=dest_filename)
def create_excel(self,title,interface_info,all_case_num,all_pass_case_num,all_check_num):
# 新建一个ExcelWriter
excel_work = ExcelWriter(workbook = self.work_book)
# 设置文件输出路径与名称
dest_filename = self.excel_name + self.excel_suffix
# 创建sheet
for sheet_name in self.sheet_names:
ws = self.work_book.create_sheet()
ws.title = sheet_name
# ==============================================================================================================
# 根据sheet名称获取该sheet对象
# 第一个sheet
ws = self.work_book.get_sheet_by_name(self.sheet_names[0])
'''
合并单元格
源码中该方法的定义
def merge_cells(self, range_string=None, start_row=None, start_column=None, end_row=None, end_column=None)
所以可以直接使用后面四个参数
'''
ws.merge_cells(start_row=1, start_column=1, end_row=2, end_column=9)
c_a1 = ws['A1']
c_a3 = ws['A3']
c_b3 = ws['B3']
c_c3 = ws['C3']
c_d3 = ws['D3']
c_e3 = ws['E3']
c_f3 = ws['F3']
c_g3 = ws['G3']
c_h3 = ws['H3']
c_i3 = ws['I3']
# 接口测试设计明细 sheet页的通用样式
general_style = Style(
font=Font(
name=u'微软雅黑',
size=10
),
fill=PatternFill(
start_color='DCE6F1',
end_color='DCE6F1',
fill_type='solid'
),
border=Border(
left=Side(color='000000',border_style='thin'),
right=Side(color='000000',border_style='thin'),
top=Side(color='000000',border_style='thin'),
bottom=Side(color='000000',border_style='thin'),
diagonal=Side(color='000000',border_style='thin'),
)
)
c_a3.value = u'涉及模块'
c_b3.value = u'接口数量'
c_c3.value = u'接口名称'
c_d3.value = u'测试Owner'
c_e3.value = u'契约文档提供'
c_f3.value = u'契约文档确认'
c_g3.value = u'SOA接口是否提供'
c_h3.value = u'预计用例数量'
c_i3.value = u'完成用例数量'
# 设置成通用样式
c_a3.style = c_b3.style = c_c3.style = c_d3.style = c_e3.style = c_f3.style = c_g3.style = c_h3.style = c_i3.style = general_style
# 单元格值
c_a1.value = title + u'接口测试明细'
# 单元格样式
c_a1.style = Style(
# 字体
font=Font(
name=u'微软雅黑',
size=22,
),
# 对齐方式
alignment = Alignment(
# 水平对齐 居中
horizontal = 'center'
),
# 填充
fill=PatternFill(
start_color='FFC000',
end_color='FFC000',
fill_type='solid'
),
border=Border(
left=Side(color='000000',border_style='thin'),
right=Side(color='000000',border_style='thin'),
top=Side(color='000000',border_style='thin'),
bottom=Side(color='000000',border_style='thin'),
diagonal=Side(color='000000',border_style='thin'),
)
)
# ==============================================================================================================
# ==============================================================================================================
# 第二个sheet
ws = self.work_book.get_sheet_by_name(self.sheet_names[1])
ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=6)
ws.merge_cells(start_row=2, start_column=1, end_row=3, end_column=1)
ws.merge_cells(start_row=2, start_column=2, end_row=2, end_column=6)
c_a1 = ws['A1']
c_a2 = ws['A2']
c_b2 = ws['B2']
c_b3 = ws['B3']
c_c3 = ws['C3']
c_d3 = ws['D3']
c_e3 = ws['E3']
c_f3 = ws['F3']
c_a1.value = title + u'接口测试数据统计'
c_a2.value = title
c_b2.value = u'验收结果'
c_b3.value = u'测试用例'
c_c3.value = u'通过用例'
c_d3.value = u'失败用例'
c_e3.value = u'检查点数量'
c_f3.value = u'通过率'
c_a1.style = Style(
font=Font(
name=u'微软雅黑',
size=11,
color='FFFFFF',
bold='bold'
),
# 对齐方式
alignment = Alignment(
# 水平对齐 居中
horizontal = 'center'
),
# 填充
fill=PatternFill(
start_color='0070C0',
end_color='0070C0',
fill_type='solid'
),
)
c_a2.style = Style(
font=Font(
name=u'微软雅黑',
size=11
),
# 对齐方式
alignment = Alignment(
# 水平对齐 居中
horizontal = 'center',
# 垂直居中
vertical = 'center',
),
# 填充
fill=PatternFill(
start_color='00B050',
end_color='00B050',
fill_type='solid'
),
border=Border(
left=Side(color='000000',border_style='thin'),
right=Side(color='000000',border_style='thin'),
top=Side(color='000000',border_style='thin'),
bottom=Side(color='000000',border_style='thin'),
diagonal=Side(color='000000',border_style='thin'),
)
)
c_b2.style = Style(
font=Font(
name=u'微软雅黑',
size=11
),
# 对齐方式
alignment = Alignment(
# 水平对齐 居中
horizontal = 'center',
),
# 填充
fill=PatternFill(
start_color='FCD5B4',
end_color='FCD5B4',
fill_type='solid'
),
border=Border(
left=Side(color='000000',border_style='thin'),
right=Side(color='000000',border_style='thin'),
top=Side(color='000000',border_style='thin'),
bottom=Side(color='000000',border_style='thin'),
diagonal=Side(color='000000',border_style='thin'),
)
)
c_b3.style = Style(
font=Font(
name=u'微软雅黑',
size=11
),
# 对齐方式
alignment = Alignment(
# 水平对齐 居中
horizontal = 'center',
),
border=Border(
left=Side(color='000000',border_style='thin'),
right=Side(color='000000',border_style='thin'),
top=Side(color='000000',border_style='thin'),
bottom=Side(color='000000',border_style='thin'),
diagonal=Side(color='000000',border_style='thin'),
)
)
c_e3.style = Style(
font=Font(
name=u'微软雅黑',
size=11
),
# 对齐方式
alignment = Alignment(
# 水平对齐 居中
horizontal = 'center',
),
border=Border(
left=Side(color='000000',border_style='thin'),
right=Side(color='000000',border_style='thin'),
top=Side(color='000000',border_style='thin'),
bottom=Side(color='000000',border_style='thin'),
diagonal=Side(color='000000',border_style='thin'),
)
)
c_c3.style = Style(
font=Font(
name=u'微软雅黑',
size=11
),
# 对齐方式
alignment = Alignment(
# 水平对齐 居中
horizontal = 'center',
),
# 填充
fill=PatternFill(
start_color='92D050',
end_color='92D050',
fill_type='solid'
),
border=Border(
left=Side(color='000000',border_style='thin'),
right=Side(color='000000',border_style='thin'),
top=Side(color='000000',border_style='thin'),
bottom=Side(color='000000',border_style='thin'),
diagonal=Side(color='000000',border_style='thin'),
)
)
c_d3.style = Style(
font=Font(
name=u'微软雅黑',
size=11
),
# 对齐方式
alignment = Alignment(
# 水平对齐 居中
horizontal = 'center',
),
# 填充
fill=PatternFill(
start_color='FF0000',
end_color='FF0000',
fill_type='solid'
),
border=Border(
left=Side(color='000000',border_style='thin'),
right=Side(color='000000',border_style='thin'),
top=Side(color='000000',border_style='thin'),
bottom=Side(color='000000',border_style='thin'),
diagonal=Side(color='000000',border_style='thin'),
)
)
c_f3.style = Style(
font=Font(
name=u'微软雅黑',
size=11
),
# 对齐方式
alignment = Alignment(
# 水平对齐 居中
horizontal = 'center',
),
# 填充
fill=PatternFill(
start_color='DAEEF3',
end_color='DAEEF3',
fill_type='solid'
),
border=Border(
left=Side(color='000000',border_style='thin'),
right=Side(color='000000',border_style='thin'),
top=Side(color='000000',border_style='thin'),
bottom=Side(color='000000',border_style='thin'),
diagonal=Side(color='000000',border_style='thin'),
)
)
# ==============================================================================================================
# ==============================================================================================================
# 第三个sheet
ws = self.work_book.get_sheet_by_name(self.sheet_names[2])
ws.merge_cells(start_row=2, start_column=1, end_row=3, end_column=1)
ws.merge_cells(start_row=2, start_column=2, end_row=3, end_column=2)
c_a1 = ws['A1']
c_b1 = ws['B1']
c_a1.value = u'涉及接口'
c_b1.value = u'问题说明'
c_a1.style = c_b1.style = Style(
font=Font(
name=u'微软雅黑',
size=11
),
# 对齐方式
alignment = Alignment(
# 水平对齐 居中
horizontal = 'center',
),
# 填充
fill=PatternFill(
start_color='DCE6F1',
end_color='DCE6F1',
fill_type='solid'
),
border=Border(
left=Side(color='000000',border_style='thin'),
right=Side(color='000000',border_style='thin'),
top=Side(color='000000',border_style='thin'),
bottom=Side(color='000000',border_style='thin'),
diagonal=Side(color='000000',border_style='thin'),
)
)
# ==============================================================================================================
# ==============================================================================================================
# 第四个sheet
ws = self.work_book.get_sheet_by_name(self.sheet_names[3])
c_a1 = ws['A1']
c_b1 = ws['B1']
c_c1 = ws['C1']
c_d1 = ws['D1']
c_e1 = ws['E1']
c_a6 = ws['A6']
c_a7 = ws['A7']
c_b7 = ws['B7']
c_c7 = ws['C7']
c_d7 = ws['D7']
c_e7 = ws['E7']
c_a10 = ws['A10']
c_a1.value = u'模块'
c_b1.value = u'概述'
c_c1.value = u'问题归属'
c_d1.value = u'备注'
c_e1.value = u'问题影响'
c_a6.value = u'今日已修复问题'
c_a7.value = u'模块'
c_b7.value = u'概述'
c_c7.value = u'问题归属'
c_d7.value = u'备注'
c_a10.value = u'无需解决问题'
c_a1.style = c_b1.style = c_c1.style = c_d1.style = c_e1.style = c_a7.style = c_b7.style = c_c7.style = c_d7.style = c_e7.style = Style(
font=Font(
name=u'微软雅黑',
size=10
),
# 对齐方式
alignment = Alignment(
# 水平对齐 居中
horizontal = 'center',
),
# 填充
fill=PatternFill(
start_color='DDD9C4',
end_color='DDD9C4',
fill_type='solid'
),
border=Border(
left=Side(color='000000',border_style='thin'),
right=Side(color='000000',border_style='thin'),
top=Side(color='000000',border_style='thin'),
bottom=Side(color='000000',border_style='thin'),
diagonal=Side(color='000000',border_style='thin'),
)
)
c_a6.style = c_a10.style = Style(
font=Font(
name=u'微软雅黑',
size=10
),
# 对齐方式
alignment = Alignment(
# 水平对齐 居中
horizontal = 'center',
),
# 填充
fill=PatternFill(
start_color='FFFF00',
end_color='FFFF00',
fill_type='solid'
),
)
# ==============================================================================================================
# 传入一个对象
#
# interface_info = [
# {
# 'name':u"这是一个接口名称1",
# 'owner':u'沈佳龙',
# 'contract_is_provide':u'是',
# 'is_confirm':u'是',
# 'soa_is_provide':u'是',
# 'case_num':10
# },
# {
# 'name':u"这是一个接口名称2",
# 'owner':u'张三',
# 'contract_is_provide':u'是',
# 'is_confirm':u'是',
# 'soa_is_provide':u'是',
# 'case_num':20
# },
# {
# 'name':u"这是一个接口名称3",
# 'owner':u'李四',
# 'contract_is_provide':u'是',
# 'is_confirm':u'是',
# 'soa_is_provide':u'是',
# 'case_num':20
# },
# ]
# 调用 self.insert_interface_info(case_info)
self.insert_interface_info(title,interface_info,all_case_num,all_pass_case_num,all_check_num)
excel_work.save(filename=dest_filename)
#!/usr/bin/python
# openpyxl operate excel rows
from openpyxl import Workbook
from openpyxl import load_workbook
from openpyxl.writer.excel import ExcelWriter
wb = load_workbook(filename = 'test.xlsx')
ws1 = wb.worksheets[0]
ws2 = ExcelWriter(workbook = wb)
for row in ws1.rows:
for col in row:
ns_name = col.value
ws1.cell(column=2, row=2).value = col.value
ws2.save(filename = 'test2.xlsx')
def main():
# obtin the current dirtory
#current_dir = os.path.dirname(__file__)
# obtain the current directory name
# __file__ is the inner representation of this file
current_dir = os.path.dirname(os.path.abspath(__file__))
# join the path to go into the subfolder
cv_dir = os.path.join(current_dir, 'Bi/CVs/')
work_path = os.path.join(current_dir, 'wos_new.xlsx')
result_path = os.path.join(current_dir, 'new.xlsx')
work_book = load_workbook(work_path)
sheet = work_book.worksheets[0]
result_book = load_workbook(result_path)
result_sheet = result_book.worksheets[0]
result_rows = result_sheet.rows
count = 1
tmp_name = os.walk(cv_dir)
for root, sub_dir, files_list in tmp_name:
for file in files_list:
if file[-4:] == 'docx':
file = os.path.join(root,file)
# the root is in format of /blah/blah/CVs/10007
# and cv_id will be '10007'
cv_id = root.rsplit("/",1)[1]
added_cv_id = str(int(cv_id) + 20000)
# TODO: add exception handler and log file support
document = opendocx(file)
unparsed_text = getdocumenttext(document)
parsed_text = ''
for c in unparsed_text:
parsed_text += c.encode("utf-8")
parsed_cv_text = clean_parse(parsed_text)
#string_len = len(string_text)
cv_len = len(parsed_cv_text)
#print "string length %d" % string_len
#print "cv length %d" % cv_len
author_row = sheet.rows
#print len(author_row)
for i in range(len(author_row)):
wos_id = str(author_row[i][6].value)
if wos_id == added_cv_id:
'''if count == 90:
updated_result = ExcelWriter(workbook = result_book)
updated_result.save(filename = 'new.xlsx')
return'''
# title match
title_string = str(author_row[i][7].value)
title_string = clean_parse(title_string)
wos_len = len(title_string)
#print "before auto_match"
#print parsed_cv_text
#print title_string
result = auto_match(parsed_cv_text, title_string)
ld = result[1]
match_string = result[0]
#print "author %s, cv %s" % (str(author_row[i][6].value), cv_id)
#print ld
result_sheet.cell(row = count, column = 0).value = added_cv_id
result_sheet.cell(row = count, column = 1).value = str(author_row[i][3].value)
result_sheet.cell(row = count, column = 2).value = 'cv-title'
match_len = len(match_string)
print "matched len = %s" % match_len
prob = float(abs(match_len-ld))/match_len
print prob
result_sheet.cell(row = count, column = 3).value = prob
if prob != 0:
result_sheet.cell(row = count, column = 4).value = 'OK'
else:
result_sheet.cell(row = count, column = 4).value = 'Prob is zero'
result_sheet.cell(row = count, column = 5).value = match_string
result_sheet.cell(row = count, column = 6).value = title_string
count += 1
updated_result = ExcelWriter(workbook = result_book)
updated_result.save(filename = 'new.xlsx')
# co-author match
if len(author_row[i]) >= 9:
co_author_str = str(author_row[i][2].value)+str(author_row[i][9].value)
else:
co_author_str = str(author_row[i][2].value)
author_list = co_author_str.split(';')
for co_author in author_list:
co_author = clean_parse(co_author)
result = auto_match(parsed_cv_text, co_author)
ld = result[1]
match_string = result[0]
#print 'co-author'+str(ld)
author_len = len(co_author)
prob = float(abs(author_len-ld))/author_len
result_sheet.cell(row = count, column = 0).value = added_cv_id
result_sheet.cell(row = count, column = 1).value = str(author_row[i][3].value)
result_sheet.cell(row = count, column = 2).value = 'cv-co_author'
result_sheet.cell(row = count, column = 3).value = prob
if prob != 0:
result_sheet.cell(row = count, column = 4).value = 'OK'
else:
result_sheet.cell(row = count, column = 4).value = 'Prob is zero'
result_sheet.cell(row = count, column = 5).value = match_string
result_sheet.cell(row = count, column = 6).value = co_author
count += 1
updated_result = ExcelWriter(workbook = result_book)
updated_result.save(filename = 'new.xlsx')
# institution match
institution = str(author_row[i][4].value)
insti_list = institution.split(';')
for institution in insti_list:
institution = clean_parse(institution)
insti_len = len(institution)
result = auto_match(parsed_cv_text, institution)
ld = result[1]
match_string = result[0]
prob = float(abs(insti_len-ld))/insti_len
result_sheet.cell(row = count, column = 0).value = added_cv_id
result_sheet.cell(row = count, column = 1).value = str(author_row[i][3].value)
result_sheet.cell(row = count, column = 2).value = 'cv-institution'
result_sheet.cell(row = count, column = 3).value = prob
if prob != 0:
result_sheet.cell(row = count, column = 4).value = 'OK'
else:
result_sheet.cell(row = count, column = 4).value = 'Prob is zero'
result_sheet.cell(row = count, column = 5).value = match_string
result_sheet.cell(row = count, column = 6).value = institution
count += 1
updated_result = ExcelWriter(workbook = result_book)
updated_result.save(filename = 'new.xlsx')
print 'another one'
def save(self, filename, i_max = None, j_max = None):
''' save table in ods format '''
if not i_max: i_max = self.table.i_max
if not j_max: j_max = self.table.j_max
# update cells text
self.table.updateTable(i_max, j_max)
# create new xlsx spreadsheet
wb = Workbook()
ew = ExcelWriter(workbook = wb)
dest_filename = filename
ws = wb.worksheets[0]
ws.title = "sheet 1"
# make sure values are up to date
# loop and update the cells value
for i in range(1, i_max):
for j in range(1, j_max):
# update the cell text and condition
cell = self.table.encodeColName(j) + str(i)
c = self.table.getCellAt(i, j)
# FIXME: excel output does not support conditional formating,
# we do fixed formating of the conditional formating
color = [c.color, c.condition_color][c.condition_state]
background_color = [c.background_color, c.condition_background_color][c.condition_state]
ws.cell(cell).style.font.name = c.font_family
ws.cell(cell).style.font.size = int(c.font_size[:-2])
ws.cell(cell).style.font.color = Color('FF' + color.upper()[1:])
if background_color != "default":
ws.cell(cell).style.fill.fill_type = 'solid'
ws.cell(cell).style.fill.start_color = Color('00' + background_color.upper()[1:])
ws.cell(cell).style.fill.end_color = Color('00' + background_color.upper()[1:])
if c.border_left != "none":
ws.cell(cell).style.borders.left.border_style = self.convertXlsBorderWidth(c.border_left)
ws.cell(cell).style.borders.left.color = self.convertXlsBorderColor(c.border_left)
if c.border_right != "none":
ws.cell(cell).style.borders.right.border_style = self.convertXlsBorderWidth(c.border_right)
ws.cell(cell).style.borders.left.color = self.convertXlsBorderColor(c.border_right)
if c.border_top != "none":
ws.cell(cell).style.borders.top.border_style = self.convertXlsBorderWidth(c.border_top)
ws.cell(cell).style.borders.left.color = self.convertXlsBorderColor(c.border_top)
if c.border_bottom != "none":
ws.cell(cell).style.borders.bottom.border_style = self.convertXlsBorderWidth(c.border_bottom)
ws.cell(cell).style.borders.left.color = self.convertXlsBorderColor(c.border_bottom)
# set xls text
if (c.formula and c.formula[0] == '='):
ws.cell(cell).value = c.formula.replace(";",",")
elif c.value_type == 'float':
ws.cell(cell).value = c.value
elif c.value_type == 'date':
ws.cell(cell).value = datetime.strptime(c.date_value, "%Y-%m-%d")
else:
ws.cell(cell).value = unicode(c.text, 'utf-8') + " "
ew.save(filename = filename)
def write_records_to_excel(output, population, markers, alleles = ['A','B']):
wb = Workbook()
ew = ExcelWriter(workbook = wb)
ws = wb.worksheets[0]
ws.title = "Level0"
# add marker columns - 2 columns for each allele
header = ["Individual"] + ["{0}_{1}".format(m,a) for m in markers for a in alleles]
#pdb.set_trace()
ws = write_header(ws, header)
max_depth = get_max_depth(population)
wb = create_additional_sheet(wb, header, max_depth)
pop_keys = population.keys()
pop_keys.sort()
for name_idx, name in enumerate(pop_keys):
# pdb.set_trace()
# get depth of stack
depth = population[name].shape[0]
# write invididual name at all depths
#for level in xrange(max_depth):
# ws = wb.get_sheet_by_name("Level{0}".format(level))
# ws.cell('A{0}'.format(name_idx + 2)).value = name
# ensure there is workbook at max stack depth
for level in xrange(max_depth):
# write the sample id for each row of all levels
ws = wb.get_sheet_by_name("Level{0}".format(level))
ws.cell('A{0}'.format(name_idx + 2)).set_value_explicit(value = name, data_type = 's')
# but only write the genotypes for the sample where there
# is a level
if level < depth:
for marker_idx, marker in enumerate(header[1:]):
cl = get_column_letter(marker_idx + 2)
marker, allele = marker.split('_')
if allele == 'A':
pos = 0
else:
pos = 1
ws.cell('{0}{1}'.format(cl, name_idx + 2)).value = population[name].lix[[str(level)]].lix[[marker]].x[pos]
# check all non-zero entries for similarity and store to summary
ws = wb.get_sheet_by_name("Summary".format(level))
ws.cell('A{0}'.format(name_idx + 2)).set_value_explicit(value = name, data_type = 's')
for marker_idx, marker in enumerate(header[1:]):
cl = get_column_letter(marker_idx + 2)
marker, allele = marker.split('_')
if allele == 'A':
pos = 0
else:
pos = 1
# if only one element, self = self
if depth <= 1:
identical = True
elif (population[name].lix[:,[marker],pos].x == '').all():
identical = True
else:
# don't penalize comparisons having '' in >= one column
empties = population[name].lix[:,[marker],pos].x != ''
genotypes_no_empties = population[name].lix[:,[marker],pos].x[empties]
# just test first element against all elements
identical = (genotypes_no_empties == genotypes_no_empties[0]).all()
if identical:
ws.cell('{0}{1}'.format(cl, name_idx + 2)).value = "T"
else:
ws.cell('{0}{1}'.format(cl, name_idx + 2)).value = "F"
#pdb.set_trace()
ew.save(filename = output)
sheetName = '%s%s' %(condition, picture)
if newWorkbook:
ws = wb.worksheets[0]
ws.title=sheetName
else:
ws=wb.create_sheet()
ws.title=sheetName
def _getExcelCellName(col, row):
"""Returns the excel cell name for a row and column (zero-indexed)
>>> _getExcelCellName(0,0)
'A1'
>>> _getExcelCellName(2,1)
'C2'
"""
return "%s%i" %(get_column_letter(col+1), row+1)
#enter data into Excel sheet
for colN, heading in enumerate(stimOut):
print heading
ws.cell(_getExcelCellName(col=colN, row=0)).value=unicode(heading)
ws.cell('A2').value = unicode(thresh)
ws.cell('B2').value = unicode(threshUpper)
ws.cell('C2').value = unicode(threshLower)
ew.save(filename = fileName)
print 'TA DA!!'
print chardit
list=page.xpath("/html/body/div[1]/div[2]/div[1]/div[1]/form/div[1]/div/div/ul/div/li/a")
#print list
citys=set([i.text for i in list])
#citys=set(["北京"])
print len(citys)
citysnum=0
wb = Workbook() # 创建工作薄
ew = ExcelWriter(workbook=wb) # 写入工作薄对象
ws = wb.worksheets[0] # 默认第一个表格
ws.title = "data"
cellnum = 1
titlelist=["名称","内容","范围","有效期","url","搜索量","点赞量"]
for i in range(len(titlelist)):
ws.cell(row=cellnum, column=i + 1).value = titlelist[i]
ew.save(filename='result.xlsx')
cellnum += 1
for city in citys:
refer = {"area": "", "city": city, "searchType": "new", "searchtitle": "", "shoparea": "", "shopptype": "",
"shopstype": ""}
citydata=post(url,refer)
citypage = etree.HTML(citydata.decode('utf-8'))
pagenum = citypage.xpath("/html/body/div[1]/div[2]/div[1]/div[2]/div[2]/dl/dd[2]/div/p[1]/a/span")[0].text
print 'pagenum:',pagenum
citysnum+=1
for num in range(int(pagenum)):
if num==1:
name=citypage.xpath("/html/body/div[1]/div[2]/div[1]/div[2]/ul/li/div[1]/div/p[1]/a")
act=citypage.xpath("/html/body/div[1]/div[2]/div[1]/div[2]/ul/li/div[1]/div/p[2]")
def roll_call_to_xlsx(year, minRC = 1, maxRC = 1):
"""
Get roll call data for a given year and stores in
"""
congressData = dict()
nameList = list()
voteData = dict()
#Check to see if proper arguments passed in function
if year not in range(1990, datetime.now().year+1):
print "Date out of range, must be between 1990 and current year"
return
#No value was entered, default to max
if maxRC == 1:
maxRC = get_max_roll_call(year)
if minRC > maxRC:
print "Max is less than min"
return
for rollcall in range(minRC, maxRC+1):
voteData.clear()
rollcallstr = str(rollcall)
url = 'http://clerk.house.gov/evs/' + str(year) + '/roll' + rollcallstr.zfill(3) + '.xml'
page = urllib2.urlopen(url)
soup = BeautifulStoneSoup(page)
votes = soup.findAll('recorded-vote')
numVotes = len(votes)
for i in range(0, numVotes):
name = votes[i].contents[0].contents[0]
rawVote = votes[i].contents[1].contents[0]
if 'Yes' in rawVote or 'Aye' in rawVote or 'Yea' in rawVote:
vote = 'Yes'
elif 'Not Voting' in rawVote:
vote = 'Not Voting'
elif 'Present' in rawVote:
vote = 'Present'
elif 'No' in rawVote or 'Nay' in rawVote:
vote = 'No'
else:
vote = rawVote
if name not in nameList:
nameList.append(name)
if name not in voteData:
voteData[name] = vote
if rollcall not in congressData:
congressData[rollcall] = voteData.copy()
print 'Added voteData for rollcall ' , rollcall
nameList.sort()
#Data received, now time for excel
wb = Workbook()
ew = ExcelWriter(workbook = wb)
dest_filename = str(get_congress(year)) +'_Congress_Roll_Call_Data.xlsx'
ws = wb.worksheets[0]
ws.title = str(get_congress(year)) + "Congress Roll Call Data"
colcount = len(congressData)
rowcount = len(nameList)
#Write roll call numbers as headers
for col in range(0, colcount):
ws.cell(row= 0, column= col + 1).value = minRC + col
#Write names in first column
for rw in range(rowcount):
ws.cell( row = rw + 1, column = 0 ).value = nameList[rw]
#Write the vote data for each roll call
i = 0
for col in range(minRC, minRC + colcount): #iterate through each column
vD = congressData[col] #get the vote data for this rollcall
print col
i = i + 1
for rw in range(0, rowcount): #iterate through the rows
if nameList[rw] in vD.keys():
ws.cell( row = rw + 1, column = i ).value = vD[nameList[rw]]
ew.save( filename = dest_filename )
return
print "bad file name"
exit()
#check for .xlsx extention
if '.xlsx' not in destFileName:
destFileName = destFileName + ".xlsx"
# make new workbook
wbnew = Workbook()
ew = ExcelWriter(workbook = wbnew)
wsnew = wbnew.worksheets[0]
wsnew.title = "UUtranRelation"
# copy headers from wsdump to wbnbr
rownum = 1
colnum = 1
for row in wsdump.iter_rows():
for cell in row:
colletter = get_column_letter(colnum)
#print colletter, rownum, cell.value
#print '%s%s'%(colletter,rownum)
wsnew.cell('%s%s'%(colletter,rownum)).value = cell.value
colnum = colnum + 1
colnum = 1
rownum = rownum + 1
if rownum > 5:
break
#save file to destFileName
ew.save(filename = destFileName)
#process each row from this subject's data file
inputRow = 2
dataEnded=False
for condN, cond in enumerate(subj.trialList):
#about this condition
thisSetSize=cond['setSize']
thisPresent= cond['present']
thisCondition = "set%i_%s" %(thisSetSize, thisPresent) #e.g. set6_y
#about this response
thisRT= subj.data['resp.rt'][condN]
thisAcc= subj.data['resp.corr'][condN]
#add this RT only if correct ans given
if thisAcc==1 and thisRT<2.0:
RTs[thisCondition].append(thisRT)
name = subj.extraInfo['participant']
print 'analysed %s' %(name)
#now calculate means for each condition and insert into sheet
meanRTs={}
for condition in RTs.keys():
thisMean = mean(RTs[condition])
col=outCols[condition]
xlSheet.cell(col+str(outRow)).value=str(thisMean)
if not anonymous:
xlSheet.cell('A'+str(row)).value = str(name)
outRow=outRow+1#goto next row for next subject
# except:
# print 'problem with', fileName
xlsxWriter.save(filename = outputFilename)
print(str(FinalResult[i])+"是水仙花数")
end = time.time()
TestTime[j]=end-start
print("第"+str(j+1)+"次结束于:%s" %datetime.datetime.now().strftime("%Y/%d/%m %H:%M:%S")+"\n"+"本次耗时："+str(TestTime[j]))
TotalTime = 0
for i in range(TestCount):
print("第" + str(i+1) + "次耗时",TestTime[i])
TotalTime = TotalTime + TestTime[i]
print("平均耗时",TotalTime/TestCount)
if CheckFileExist(ExcelFile):
wb = load_workbook(filename=ExcelFile, read_only=False)#获取一个已经存在的excel文件wb
#print("Worksheet name(s):",wb.get_sheet_names()[0])
ws=wb.get_sheet_by_name(wb.get_sheet_names()[0])#打开该文件wb需要用到的worksheet即ws
else:
message = "文件：%s\n不存在"
wb = Workbook()
ws = wb.active
ws.title = "Sheet1"
FreeColNum=GetEmptyCol()
ewb1=ExcelWriter(workbook=wb)#新建一个ExcelWriter，用来写wb
ws.cell(row=1,column=FreeColNum).value=FreeColNum-1 #写入列表头
for j in range(1,TestCount+1): #空列写入10个数据
ws.cell(row=j+1,column=FreeColNum).value=round(TestTime[j-1],3)
ewb1.save(filename=ExcelFile)#保存一定要有，否则不会有结果
print("成功写入Excel文件第"+chr(64+FreeColNum)+"列")
print("文件路径："+ExcelFile)
input("")
wb = Workbook()
# 新建一个excelWriter
ew = ExcelWriter(workbook=wb)
# 第一个sheet是ws
ws = wb.worksheets[0]
# 设置ws的名称
ws.title = "range names"
# 向某个单元格中写入数据
ws.cell("C1").value = u'哈哈'
# 最后保存文件
ew.save(filename=r'empty_book.xlsx')
"""
向某个单元格内写文件时要先知道它对应的行数和列数，这里注意行数是从1开始计数的，而列则是从字母A开始，因此第一行第一列是A1，
这实际上是采用坐标方式操作Excel。例如，想向表格的第三行第一列插入一个数值1.2，
用xlwt写就是table.write(2, 0, 1.2), 因为xlwt中行列索引都从0开始；
而如果用openpyxl写就是ws.cell(“A3”).value = 1.2。
一般对于一个较大的列数，需要通过get_column_letter函数得到相应的字符，然后再调用cell函数写入。
下面是我之前写的一个代码的一部分，可以用来演示将多位数组保存到Excel文件中。为了体现多维数组，这里用到了numpy，另外这里为了简化过程，没有用ExcelWriter。代码如下：
"""
from openpyxl import Workbook
from openpyxl.cell import get_column_letter
import numpy as np
# 生成一个对角阵
conn.select_db('DW_VTMetrics')
cur=conn.cursor()
get_all = "select * from video_trackingMeta"
print get_all
data = cur.execute(get_all)
data_list = cur.fetchall()
cur.close()
conn.close()
wb = Workbook()
ws = wb.worksheets[0]
ws.title = 'video_trackingMeta'
#ws.column_dimensions["A"].width = 35.0
ew = ExcelWriter(workbook = wb)
file_name = r'/Job/datawarehouse/opertaion/mailsender/data/video_trackingMeta.xlsx'
rows = len(data_list)
cols = len(data_list[0])
for rx in range(rows):
for cx in range(cols):
ws.cell(row = rx + 1, column = cx + 1).value = data_list[rx][cx]
ew.save(filename = file_name)
from openpyxl.cell import get_column_letter
# make new workbook
wb = Workbook()
ew = ExcelWriter(workbook = wb)
dest_filename = r'empty_book.xlsx'
ws = wb.worksheets[0]
ws.title = "range names"
# run through 1 - 40 columns and 1 - 600 rows and fill them with row/column name
for col_idx in xrange(1, 40):
col = get_column_letter(col_idx)
for row in xrange(1, 600):
ws.cell('%s%s'%(col, row)).value = '%s%s' % (col, row)
#create a new sheet and call it 'Pi'
ws = wb.create_sheet()
ws.title = 'Pi'
#assign a value to cell F5 on new sheet
ws.cell('F5').value = 3.14
#save file to 'empty_book.xlsx'
ew.save(filename = dest_filename)
import pandas as pd
# use row 2 as column labels
df = pd.read_csv("kcsj_2016.txt")
print df
print df.columns
# select DF by index(columns)
# df = df[[u'合同备案编号', u'项目名称', u'企业名称', u'合同价格（万元）', u'总投资（万元）', u'规模及等级']]
# print df
# second way to select DF by index
df = df.loc[:, [u'合同备案编号', u'项目名称', u'企业名称', u'合同价格（万元）', u'总投资（万元）', u'规模及等级']]
# df[[u'合同价格（万元）', u'总投资（万元）']] = df[[u'合同价格（万元）', u'总投资（万元）']].astype(float)
# print df
# print df[u"合同价格（万元）"].sum()
# ignore rows by specific column value
df = df[df[u'企业名称'] != u'江苏国泰新点软件有限公司']
# print df
# df = df[df[u'合同备案编号'] == '']
# print df
print df[u"合同价格（万元）"].sum()
print pd.isnull(df)
# see http://stackoverflow.com/questions/13413590/how-to-drop-rows-of-pandas-dataframe-whose-value-of-certain-column-is-nan
# drop all rows that have any NaN values
# print df.dropna(how='any')
# drop rows only if NaN in specific column
df = df.dropna(subset=[u'合同备案编号'])
print df
writer = ExcelWriter('output.xlsx')
df.to_excel(writer, u'工程勘察设计项目合同备案汇总表')
writer.save()