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'机油滤清器', 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 goods_format in goods_set:
goods_size = self.goods_dict[goods_format]
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']), goods_format, goods_size))
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_sku_excel(self):
wb = Workbook()
ew = ExcelWriter(workbook=wb)
the_sheet = wb.create_sheet(u"sku适配车型", 0)
insert_list = (u'机油滤清器', u'滤清器尺寸', u'car_id', u'品牌', u'车系', u'车型',
u'排量', u'年款', u'名称')
self.write_excel_cell(the_sheet, 1, insert_list)
is_sheet_num = 2
for goods_format, car_id_set in self.goods_car_dict.iteritems():
goods_size = self.goods_dict[goods_format]
if len(car_id_set) == 0:
self.write_excel_cell(
the_sheet, is_sheet_num,
(goods_format, goods_size, 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,
(goods_format, goods_size, 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')
cell_value2 = ws.cell("B1").value
"""
写文件，只有一种操作方式，就是通过坐标。例如要向单元格C1写数据，就要用类似ws.cell(“C1”).value = something这样的方式。
一般推荐的方式是用openpyxl中的Writer类来实现。
"""
from openpyxl.workbook import Workbook # 这个类用来生成一个excel文件
# ExcelWriter,里面封装好了对Excel的写操作
from openpyxl.writer.excel import ExcelWriter
# 新建一个workbook
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，
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)
def SaveExcel(tWorkHanle, tFileName):
try:
ew = ExcelWriter(workbook=tWorkHanle)
ew.save(filename=tFileName)
except Exception, exinfo:
print exinfo
def Start():
#reload(sys)
#sys.setdefaultencoding('utf-8')
create_folder()
ScriptPath = script_path()
BK_Path = ScriptPath + "/Bai_Ke/"
QMJK_Path = ScriptPath + "/Quan_Min_Jian_Kang/"
XYWY_Path = ScriptPath + "/Xun_Yi_Wen_Yao/"
YPT_Path = ScriptPath + "/Yao_Pin_Tong/"
baidu_address = "http://www.baidu.com/s"
######################################################################
#---------打开原文件--------
fname = ScriptPath + "/Drugs_Database_WD_20140323_v1.xlsx"
bk = xlrd.open_workbook(fname)
try:
sh = bk.sheet_by_name("Sheet1")
except:
print "no sheet in %s named Sheet1" % fname
nrows = sh.nrows #获取行数
ncols = sh.ncols #获取列数
######################################################################
#---------新建一个文件--------
wb1 = Workbook()
ewb1 = ExcelWriter(workbook=wb1)
new_filename = ScriptPath + '/Drugs_Database_WD_20140323_v1_new.xlsx'
ws1 = wb1.worksheets[0]
ws1.title = "Sheet1"
######################################################################
address = "http://www.baidu.com/s"
for col in range(0, ncols):
ws1.cell(row=0, column=col).value = sh.cell_value(0, col)
ws1.cell(row=0, column=8).value = "百度药品链接"
for i in xrange(1, nrows):
#for i in xrange(23101,nrows):
for j in range(0, ncols):
ws1.cell(row=i, column=j).value = sh.cell_value(i, j)
key = sh.cell_value(i, 0).encode('utf8')
print key
keyword = {'wd': key, 'rn': '1'}
ex = ExtractorWebsite(baidu_address, keyword, 0)
website = ex.extractor_website()
if website:
print website
ws1.cell(row=i, column=8).value = website
else:
print "Can't find"
ws1.cell(row=i, column=8).value = "Can't find"
if i % Save_Freq == 0:
ewb1.save(filename=new_filename)
##############################################
drug_name = sh.cell_value(i, 1)
drug_name_gbk = drug_name.encode('gbk')
drug_name_url = urllib2.quote(drug_name_gbk)
bk_address = "http://baike.baidu.com/search/word?word="
bk_address += drug_name
copy1 = ExtractorWebsite(bk_address, '', 0)
new_address = copy1.extractor_baike()
print new_address
copy11 = ExtractorWebsite(new_address, '', 1)
spath = BK_Path + "BK_" + '%d' % (i + 1) + ".html"
f = open(spath, "w")
f.write(copy11.text.encode('utf8'))
f.close()
qm_120_address = "http://yp.qm120.com/search.aspx?keys="
qm_120_address += drug_name_url
#print qm_120_address
copy2 = ExtractorWebsite(qm_120_address, '', 0)
spath = QMJK_Path + "QMJK_" + '%d' % (i + 1) + ".html"
f = open(spath, "w")
f.write(copy2.text.encode('utf8'))
f.close()
xywy_address = "http://yao.xywy.com/so/?q="
xywy_address += drug_name
#print xywy_address
copy3 = ExtractorWebsite(xywy_address, '', 0)
spath = XYWY_Path + "XYWY_" + '%d' % (i + 1) + ".html"
f = open(spath, "w")
f.write(copy3.text.encode('utf8'))
f.close()
ypt_address = "http://ypk.39.net/search/all?k="
ypt_address += drug_name_url
#print ypt_address
copy4 = ExtractorWebsite(ypt_address, '', 0)
spath = YPT_Path + "YPT_" + '%d' % (i + 1) + ".html"
f = open(spath, "w")
f.write(copy4.text.encode('utf8'))
f.close()
ewb1.save(filename=new_filename)
except urllib2.URLError, e:
print "Error Reason:", e.reason
except:
print traceback.format_exc()
with open('work', 'r') as cityfile:
# citys=json.loads(cityfile)
cityjs = cityfile.readline().strip()
# print type(cityjs)
# print cityjs
citys = json.loads(cityjs, encoding="GB2312")
# print type(cityjs)
objs = citys["obj"]
wb = Workbook() # 创建工作薄
ew = ExcelWriter(workbook=wb) # 写入工作薄对象
ws = wb.worksheets[0] # 默认第一个表格
ws.title = "data"
for cellnum, obj in enumerate(objs):
active_id = obj["active_id"]
# print active_id,type(active_id)
begin_date = obj["begin_date"]
end_date = obj["end_date"]
active_name = obj["active_name"]
url = 'http://creditcard.ccb.com/cn/creditcard/acitivity/' + active_id + '.html'
respose = get_request(url)
if respose:
soup = bs(respose, "lxml")
content = soup.find_all("div", "content")[1]
cont = content.find_all("p")
person = cont[0].get_text()[6:]
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("")
def Start():
create_folder()
ScriptPath = script_path()
XYWY_Path = ScriptPath+"/Xun_Yi_Wen_Yao/"
HDF_Path = ScriptPath+"/Hao_Dai_Fu/"
#---------打开原文件--------
fname = XYWY_Path+"/Hospital_Names.xlsx"
bk = xlrd.open_workbook(fname)
try:
sh = bk.sheet_by_name("Sheet1")
except:
print "no sheet in %s named Sheet1" % fname
nrows = sh.nrows #获取行数
#---------新建一个文件--------
wb1 = Workbook()
ewb1 = ExcelWriter(workbook=wb1)
new_filename=HDF_Path+'/Hospitals.xlsx'
ws1=wb1.worksheets[0]
ws1.title="Sheet1"
ws1.cell(row=0,column=0).value = "医院名称"
ws1.cell(row=0,column=1).value = "医院链接"
HDF_address = "http://so.haodf.com/index/search?type=hospital&kw="
row_num = 0
for i in xrange(1,nrows):
key = sh.cell_value(i,2)
print key
copy = ExtractorWebsite(HDF_address+key.encode('gbk'))
#print copy.text
if copy.extractor_website() == 0:
temp = copy.extractor_name()
hospital_name = temp[0].text.encode('utf8')
row_num += 1
ws1.cell(row=row_num,column=0).value = hospital_name
print hospital_name
for href in temp[0].items():
if href[0] == 'href':
hospital_address = href[1]
print hospital_address + '\n'
ws1.cell(row=row_num,column=1).value = hospital_address
if row_num%100==0:
ewb1.save(filename=new_filename)
else:
print "Can't find\n"
ewb1.save(filename=new_filename)
def Start():
create_folder()
ScriptPath = script_path()
BKMY_Path = ScriptPath+"/Bai_Ke_Ming_Yi/"
#---------新建一个文件--------
wb1 = Workbook()
ewb1 = ExcelWriter(workbook=wb1)
new_filename=BKMY_Path+'/Disease_Names.xlsx'
ws1=wb1.worksheets[0]
ws1.title="Sheet1"
ws1.cell(row=0,column=0).value = "所属科类别"
ws1.cell(row=0,column=1).value = "疾病名称"
ws1.cell(row=0,column=2).value = "百科名医链接"
ws1.cell(row=0,column=3).value = "百度百科链接"
ws1.cell(row=0,column=4).value = "全球医院链接"
BKMY_address = "http://www.baikemy.com"
department_name = []
department_address = []
page_cnt = []
copy = ExtractorWebsite(BKMY_address+"/disease/list/0/0")
href_list = copy.extractor_website()
count = len(href_list)
row_num = 0
for cnt in xrange(0,count):
#for cnt in xrange(0,1):
department_name.append(href_list[cnt].text.encode('utf8'))
print department_name[cnt]
for href in href_list[cnt].items():
if href[0] == 'href':
department_address.append(BKMY_address+href[1])
print department_address[cnt]
########################################################################
copy = ExtractorWebsite(department_address[cnt])
temp = copy.get_diseases()
if temp:
page_cnt.append(len(temp)-1)
else:
page_cnt.append(1)
print page_cnt[cnt]
########################################################################
names = copy.disease_names()
if names:
name_cnt = len(names)
for namecnt in range(0,name_cnt):
name = names[namecnt].text
print name
for href in names[namecnt].items():
if href[0] == 'href':
disease_address = BKMY_address+href[1]
print disease_address
bk_address = "http://baike.baidu.com/search/word?word="
bk_address += name
copy11 = ExtractorWebsite(bk_address)
baike_address = copy11.extractor_baike()
print baike_address
##---------------------------------------------------##
qqyy_address = "http://so.qqyy.com/j?wd="
qqyy_address += name
print qqyy_address
##---------------------------------------------------##
row_num+=1
ws1.cell(row=row_num,column=0).value = department_name[cnt]
ws1.cell(row=row_num,column=1).value = name
ws1.cell(row=row_num,column=2).value = disease_address
ws1.cell(row=row_num,column=3).value = baike_address
ws1.cell(row=row_num,column=4).value = qqyy_address
for page_num in xrange(2,page_cnt[cnt]+1):
copy1 = ExtractorWebsite(department_address[cnt]+"?pageIndex=%d"%page_num)
names = copy1.disease_names()
name_cnt += len(names)
for namecnt in range(0,len(names)):
name = names[namecnt].text
print name
for href in names[namecnt].items():
if href[0] == 'href':
disease_address = BKMY_address+href[1]
print disease_address
bk_address = "http://baike.baidu.com/search/word?word="
bk_address += name
copy11 = ExtractorWebsite(bk_address)
baike_address = copy11.extractor_baike()
print baike_address
##---------------------------------------------------##
qqyy_address = "http://so.qqyy.com/j?wd="
qqyy_address += name
##---------------------------------------------------##
row_num+=1
ws1.cell(row=row_num,column=0).value = department_name[cnt]
ws1.cell(row=row_num,column=1).value = name
ws1.cell(row=row_num,column=2).value = disease_address
ws1.cell(row=row_num,column=3).value = baike_address
ws1.cell(row=row_num,column=4).value = qqyy_address
#print name_cnt
ewb1.save(filename=new_filename)
else:
print "没有相关疾病"
#print 0
row_num+=1
ws1.cell(row=row_num,column=0).value = department_name[cnt]
ws1.cell(row=row_num,column=1).value = "没有相关疾病"
ws1.cell(row=row_num,column=2).value = "None"
ws1.cell(row=row_num,column=3).value = "None"
ws1.cell(row=row_num,column=4).value = "None"
ewb1.save(filename=new_filename)
def save():
excel_writer = ExcelWriter(excel.work_book)
excel_writer.save(filename=excel.file_name)
def store_xlsx(self, data):
accout = getpass.getuser()
excel = Workbook() #optimized_write = True)
excel_writer = ExcelWriter(workbook=excel)
excel_outpath = r'/home/%s/mapdata/cellmap.xlsx' % accout
sheet = excel.create_sheet()
sheet = excel.worksheets[0]
sheet.title = 'cellmap_%d' % self.num
wb = load_workbook(filename=excel_outpath)
sheetnames = wb.get_sheet_names()
ws = wb.get_sheet_by_name('cellmap_%d' % self.num)
n = len(data.data)
data.data += ('last', )
print 'data length:', n, 'data,length with ending mark:', len(
data.data), self.num
sheet.cell('A1').value = 'mapdata'
marker = 0
sheet.column_dimensions['A'].width = 1.89
sheet.row_dimensions[1].height = 14.15
for col in range(2, data.info.width + 2):
column = get_column_letter(col)
sheet.cell('%s%s' % (column, 1)).value = '%d' % marker
sheet.column_dimensions['%s' % column].width = 1.89
marker += 1
marker = 0
for row in range(2, data.info.height + 2):
sheet.cell('%s%s' %
('A', data.info.height + 3 - row)).value = '%d' % marker
sheet.row_dimensions[(data.info.height + 3 - row)].height = 14.15
marker += 1
i = 0
thread = ''
for row in range(2, (data.info.height + 2)):
for col in range(2, (data.info.width + 2)):
column = get_column_letter(col)
#thread=self.checker(thread,i,data.data,n)
#thread=self.checker(thread,i,data.data,n)
height = (data.info.height + 2) - row + 1
sheet.cell('%s%s' %
(column, height)).value = '%s' % data.data[i]
#self.colormark(column,height,i,data.data,sheet)
i += 1
sheet = excel.create_sheet()
sheet.title = 'parameters'
sheet.cell('A1').value = 'parameters'
sheet.cell(
'A2'
).value = 'This represents a 2-D grid map, in which each cell represents the probability of occupancy.'
sheet.cell('A4').value = 'map_load_time'
sheet.cell('A5').value = '%s' % data.info.map_load_time
sheet.cell('C4').value = 'The map resolution [m/cell]'
sheet.cell('C5').value = '%s' % data.info.resolution
sheet.cell('A7').value = 'Map width [cells]'
sheet.cell('A8').value = '%s' % data.info.width
sheet.cell('C7').value = 'Map height [cells]'
sheet.cell('C8').value = '%s' % data.info.height
sheet.cell('A10').value = 'map centre MAP(0,0)'
sheet.cell(
'A10'
).value = 'map centre MAP(0,0), cell(0,0)=B%s' % ws.get_highest_row()
sheet.cell('A11').value = 'position(x,y,z):'
sheet.cell('C11').value = '%s' % data.info.origin.position.x
sheet.cell('D11').value = '%s' % data.info.origin.position.y
sheet.cell('E11').value = '%s' % data.info.origin.position.z
sheet.cell('A12').value = 'orientation(x,y,z,w):'
sheet.cell('C12').value = '%s' % data.info.origin.orientation.x
sheet.cell('D12').value = '%s' % data.info.origin.orientation.y
sheet.cell('E12').value = '%s' % data.info.origin.orientation.z
sheet.cell('F12').value = '%s' % data.info.origin.orientation.w
sheet.cell('A14').value = 'robot odom'
sheet.cell('A15').value = 'robot position: (x ,y ,z )'
sheet.cell('A16').value = '%s' % self.robot_odom.pose.pose.position.x
sheet.cell('B16').value = '%s' % self.robot_odom.pose.pose.position.y
sheet.cell('C16').value = '%s' % self.robot_odom.pose.pose.position.z
sheet.cell('A17').value = 'robot rotation: (x ,y ,z ,w )'
sheet.cell(
'A18').value = '%s' % self.robot_odom.pose.pose.orientation.x
sheet.cell(
'B18').value = '%s' % self.robot_odom.pose.pose.orientation.y
sheet.cell(
'C18').value = '%s' % self.robot_odom.pose.pose.orientation.z
sheet.cell(
'D18').value = '%s' % self.robot_odom.pose.pose.orientation.w
sheet.cell('A19').value = 'current time:'
sheet.cell('A20').value = '%s' % str(self.robot_odom.header.stamp)
sheet.cell('A21').value = 'robot frame_id:'
sheet.cell('A22').value = '%s' % self.robot_odom.header.frame_id
excel.save(filename=excel_outpath)
print 'written in to excel file cellmap.xlsx'
print "Worksheet range(s):", wb.get_named_ranges()
print "Worksheet name(s):", wb.get_sheet_names()
print "Worksheet title:", ws.title
print 'cols:', ws.get_highest_column(), 'rows:', ws.get_highest_row()
print 'width:', data.info.width, '0~%d' % (
data.info.width -
1), 'height:', data.info.height, '0~%d' % (data.info.height - 1)
def write_excel(excel_name, result_dicts):
from openpyxl.workbook import Workbook
#ExcelWriter,里面封装好了对Excel的写操作
from openpyxl.writer.excel import ExcelWriter
#get_column_letter函数将数字转换为相应的字母，如1-->A,2-->B
from openpyxl.cell import get_column_letter
from openpyxl.reader.excel import load_workbook
if os.path.isfile(excel_name):
# #读取excel2007文件
wb = load_workbook(excel_name)
else:
#新建一个workbook
wb = Workbook()
#新建一个excelWriter
ew = ExcelWriter(workbook=wb)
#设置文件输出路径与名称
dest_filename = excel_name
# # 获取第一个sheet
try:
ws = wb.get_sheet_by_name('sheet1')
except KeyError:
ws = wb.worksheets[0]
ws.title = "sheet1"
#第一个sheet是ws
# ws = wb.worksheets[0]
# #设置ws的名称
# ws.title = "sheet1"
line = 1
print(u'定位写入坐标')
while ws.cell("A%s" % line).value:
# print(ws.cell("A%s" % line).value)
line += 1
print(u'从第%s行开始写入' % line)
if not os.path.isfile(excel_name):
ws.cell("A%s" % line).value = u'期数'
ws.cell("B%s" % line).value = u'定向计划提取'
ws.cell("C%s" % line).value = u'大写'
ws.cell("D%s" % line).value = u'到期时间'
ws.cell("E%s" % line).value = u'交付金额'
line += 1
for i, result in enumerate(result_dicts):
print(u'正在写入第%s条数据到excel' % (i + 1))
ws.cell("A%s" % line).value = result['stage']
ws.cell("B%s" % line).value = result['money']
ws.cell("C%s" % line).value = ''
ws.cell("D%s" % line).value = result['date']
ws.cell("E%s" % line).value = result['money']
line += 1
#最后保存文件
ew.save(filename=excel_name)
def Start():
create_folder()
ScriptPath = script_path()
HDF_Path = ScriptPath + "/Hao_Dai_Fu/"
flag = 0
fname = HDF_Path + "/Hospitals.xlsx"
new_filename = HDF_Path + '/Doctors.xls'
#---------打开原文件--------
bk = xlrd.open_workbook(fname)
try: #已经有文件
bk1 = xlrd.open_workbook(new_filename, formatting_info=True)
except: #还没有文件 说明是第一次运行程序
flag = 1
sh = bk.sheet_by_name("Sheet1")
nrows = sh.nrows #获取行数
if flag == 1:
#---------新建一个文件--------
wb1 = Workbook()
ewb1 = ExcelWriter(workbook=wb1)
ws1 = wb1.worksheets[0]
ws1.title = "Sheet1"
ws1.cell(row=0, column=0).value = "医院名称"
ws1.cell(row=0, column=1).value = "科室名称"
ws1.cell(row=0, column=2).value = "医生链接"
row_num = 0
for i in xrange(1, nrows):
hos_name = sh.cell_value(i, 0)
print hos_name
hos_address = sh.cell_value(i, 1)
copy11 = ExtractorWebsite(hos_address)
department_list = copy11.extractor_website()
count = len(department_list)
print count
for cnt in xrange(0, count):
dep_name = department_list[cnt].text
print dep_name
for href in department_list[cnt].items():
if href[0] == 'href':
dep_address = href[1]
#print dep_address
copy1 = ExtractorWebsite(dep_address)
doctor_list = copy1.extractor_doctor()
if doctor_list == False:
print 1
doctor_address = dep_address.replace(
'.htm', '/menzhen.htm')
print doctor_address
row_num += 1
ws1.cell(row=row_num, column=0).value = hos_name
ws1.cell(row=row_num, column=1).value = dep_name
ws1.cell(row=row_num, column=2).value = doctor_address
else:
page_cnt = len(doctor_list) - 3
print page_cnt
doctor_address = dep_address.replace(
'.htm', '/menzhen.htm')
print doctor_address
row_num += 1
ws1.cell(row=row_num, column=0).value = hos_name
ws1.cell(row=row_num, column=1).value = dep_name
ws1.cell(row=row_num, column=2).value = doctor_address
for page_num in xrange(2, page_cnt + 1):
doctor_address = dep_address.replace(
'.htm', '/menzhen_%d.htm' % page_num)
print doctor_address
row_num += 1
ws1.cell(row=row_num, column=0).value = hos_name
ws1.cell(row=row_num, column=1).value = dep_name
ws1.cell(row=row_num, column=2).value = doctor_address
ws1.cell(row=0, column=100).value = i
ws1.cell(row=1, column=100).value = row_num
ewb1.save(filename=new_filename)
else:
sh1 = bk1.sheet_by_name("Sheet1")
wb = copy(bk1)
ws = wb.get_sheet(0)
start = int(sh1.cell_value(0, 100))
row_num = int(sh1.cell_value(1, 100))
for i in xrange(start + 1, nrows):
hos_name = sh.cell_value(i, 0)
print hos_name
hos_address = sh.cell_value(i, 1)
copy11 = ExtractorWebsite(hos_address)
department_list = copy11.extractor_website()
count = len(department_list)
print count
for cnt in xrange(0, count):
dep_name = department_list[cnt].text
print dep_name
for href in department_list[cnt].items():
if href[0] == 'href':
dep_address = href[1]
#print dep_address
copy1 = ExtractorWebsite(dep_address)
doctor_list = copy1.extractor_doctor()
if doctor_list == False:
print 1
doctor_address = dep_address.replace(
'.htm', '/menzhen.htm')
print doctor_address
row_num += 1
ws.write(row_num, 0, hos_name)
ws.write(row_num, 1, dep_name)
ws.write(row_num, 2, doctor_address)
else:
page_cnt = len(doctor_list) - 3
print page_cnt
doctor_address = dep_address.replace(
'.htm', '/menzhen.htm')
print doctor_address
row_num += 1
ws.write(row_num, 0, hos_name)
ws.write(row_num, 1, dep_name)
ws.write(row_num, 2, doctor_address)
for page_num in xrange(2, page_cnt + 1):
doctor_address = dep_address.replace(
'.htm', '/menzhen_%d.htm' % page_num)
print doctor_address
row_num += 1
ws.write(row_num, 0, hos_name)
ws.write(row_num, 1, dep_name)
ws.write(row_num, 2, doctor_address)
ws.write(0, 100, i)
ws.write(1, 100, row_num)
wb.save(new_filename)
#coding:utf-8
from openpyxl import Workbook
from openpyxl import load_workbook
from openpyxl.writer.excel import ExcelWriter
wb = load_workbook(filename = r'C:\Users\meng4\Desktop\test\test.xlsx')
ws = wb.get_sheet_by_name("Sheet1")
wb1 = Workbook()
ewb1 = ExcelWriter(workbook=wb1)
dest_filename = r"C:\Users\meng4\Desktop\test\test1.xlsx"
ws1 = wb1.worksheets[0]
ws1.title = "test"
for i in range(1,36):
li = []
for row_num in xrange (1,213824):
c2 = ws.cell(row = row_num,column = 2).value
c3 = ws.cell(row = row_num,column = 3).value
if c2 == i:
if c3 in li:
continue
else:
ls.append(c3)
else:
continue
