def update(u_id):
global indx
wb_retail = xlrd.open_workbook('retail.xls')
s1 = wb_retail.sheet_by_index(0)
wx_retail = copy(wb_retail)
wr_retail = wx_retail.get_sheet(0)
p = s1.nrows
for i in range(len(e)):
count.append(int(e[i].get()))
if (count[i] != 0):
wr_retail.write(p, 0, u_id)
wr_retail.write(p, 1, ds.cell_value(i + 1, 0))
wr_retail.write(p, 2, count[i])
wr_retail.write(p, 3, ds.cell_value(i + 1, 3))
wr_retail.write(p, 4,
ds.cell_value(i + 1, 3) - ds.cell_value(i + 1, 2))
wr_retail.write(p, 5, t)
wx_retail.save("retail.xls")
purchase[indx] = str(
ds.cell_value(i + 1, 0) + " " + str(count[i]) + " " +
str(ds.cell_value(i + 1, 3)))
indx += 1
#print(purchase)
wr_shop.write(i + 1, 1, int(ds.cell_value(i + 1, 1)) - count[i])
wx_shop.save("shop.xls")
def __init__(self, filename='', type='xls', mode='r'):
"""
constructor
"""
self.lib = OPENPYXL if type == "xlsx" else XLRD
if not filename:
if self.lib == OPENPYXL:
self.wb = openpyxl.Workbook()
sheets = self.wb.worksheets
for sheet in sheets:
self.wb.remove_sheet(sheet)
elif self.lib == XLRD:
self.wb = xlwt.Workbook()
elif mode == "r":
if self.lib == OPENPYXL:
self.wb = openpyxl.load_workbook(filename, read_only=True)
elif self.lib == XLRD:
self.wb = xlrd.open_workbook(filename, on_demand=True)
elif mode == "rw":
if self.lib == OPENPYXL:
self.wb = openpyxl.load_workbook(filename)
elif self.lib == XLRD:
rb = xlrd.open_workbook(filename, on_demand=True)
self.wb = xlutils.copy(rb)
def writeXLS(xlsfile):
book = xlwt.Workbook()
characters = 0
if os.path.exists(xlsfile):
readbook = xlrd.open_workbook(xlsfile)
# FIXME: here it loses the format
book = copy(readbook) # xlutils.copy
for page in loadedpages:
try:
sheet = book.add_sheet(page.title)
except Exception as ex:
print "[WARNING]", ex.message
print "[INFO] Updating strings on the page..."
rb = xlrd.open_workbook(xlsfile)
index = 0
for s in rb.sheets():
if s.name == page.title:
break
index += 1
sheet = book.get_sheet(index)
finally:
characters += page.write_to(sheet)
book.save(xlsfile)
print "[INFO] Export done. Check output file %s (%d characters, %d cartels)" % (
xlsfile, characters, math.ceil(characters / 1375.0))
def submit_post(grade, clas):
result = check(clas, grade, request.form.get('master'),
request.form.get('password'))
if result != 'success':
return result
filename = 'static/' + grade + '/' + clas + '/' + time.strftime(
'%Y%m', time.localtime(time.time())) + '.txt'
file = open(filename, 'w', encoding='utf-8')
data = {
'master': getstring(request.form.get('master')),
'week1': getstring(request.form.get('week1')),
'week2': getstring(request.form.get('week2')),
'week3': getstring(request.form.get('week3')),
'week4': getstring(request.form.get('week4')),
'danger': getstring(request.form.get('danger')),
'action': getstring(request.form.get('action')),
'accident': getstring(request.form.get('accident')),
'password': getstring(request.form.get('password'))
}
file.write(str(data))
file.close()
workbook = xlrd.open_workbook('static/submit.xls')
sheetname = time.strftime('%Y%m', time.localtime(time.time()))
newbook = copy(workbook)
sheet = newbook.get_sheet(get_index(sheetname))
sheet.write(int(clas), int(grade), 1)
newbook.save('static/submit.xls')
return 'success'
def xlutils():
"""xlutils基本代码import xlrd 读取数据
"""
import xlwt #写入数据
import xlutils #操作excel
#----xlrd库
#打开excel文件
workbook = xlrd.open_workbook('myexcel.xls')
#获取表单
worksheet = workbook.sheet_by_index(0)
#读取数据
data = worksheet.cell_value(0, 0)
#----xlwt库
#新建excel
wb = xlwt.Workbook()
#添加工作薄
sh = wb.add_sheet('Sheet1')
#写入数据
sh.write(0, 0, 'data')
#保存文件
wb.save('myexcel.xls')
#----xlutils库
#打开excel文件
book = xlrd.open_workbook('myexcel.xls')
#复制一份
new_book = xlutils.copy(book)
#拿到工作薄
worksheet = new_book.getsheet(0)
#写入数据
worksheet.write(0, 0, 'new data')
#保存
new_book.save()
def write_read(self):
try:
s6 = self.get_html()
s1 = xlrd.open_workbook(self.write_xls)
s2 = copy(s1)
s3 = s2.add_sheet(self.new_xls)
s4 = ['职位', '公司链接', '公司名称', '工作地点', '薪资']
for s in range(5):
s3.write(0, s, s4[s])
for i in range(1, len(s6) + 1):
for j in range(5):
s3.write(i, j, s6[i - 1][j])
s2.save(self.write_xls)
print('ok')
except:
a5 = self.get_html()
a1 = xlwt.Workbook(self.write_xls)
a2 = a1.add_sheet(self.new_xls)
a3 = ['职位', '公司链接', '公司名称', '工作地点', '薪资']
for a in range(5):
a2.write(0, a, a3[a])
for i in range(1, len(a5) + 1):
for j in range(5):
a2.write(i, j, a5[i - 1][j])
a1.save(self.write_xls)
print('ok')
def items(index, u_id):
global count, ds, dw, wr_shop, wx_shop, e
global purchase, indx
indx = 0
purchase = {}
count = []
e = []
#b1=[]
#b2=[]
dw = xlrd.open_workbook('shop.xls')
ds = dw.sheet_by_index(index)
wx_shop = copy(dw)
wr_shop = wx_shop.get_sheet(index)
dr = ds.nrows
fr.tkraise()
Label(fr, text=ds.name, font=('Aerial 50 bold')).grid(row=0, column=1)
for i in range(1, dr):
Label(fr, text=str(ds.cell_value(i, 0)),
font=('Chiller', 40)).grid(row=i, column=1)
e.append(Entry(fr))
e[i - 1].insert(0, '0')
e[i - 1].grid(row=i, column=2)
'''b1.append(Button(fr,text='+',command=lambda: add(str(ds.cell_value(i,0)),i)).grid(row=i,column=2))
#Label(fr,text=str(count[str(ds.cell_value(i,0))]),font=(40)).grid(row=i,column=3)
b2.append(Button(fr,text='-',command=lambda: remove(str(ds.cell_value(b2,0)),i)).grid(row=i,column=4))'''
Button(fr, text='Buy More', command=lambda: buy(u_id)).grid(row=dr,
column=1)
Button(fr, text='Generate Bill',
command=lambda: generate(u_id)).grid(row=dr, column=2)
def writeresult(self, row, value):
print('===========测试结果写入result 表格========')
self.file_path = os.path.join(bases.REPORT_PATH, bases.RESULT_NAME)
self.workbook = xlrd.open_workbook(self.file_path)
self.new_workbook = copy(self.workbook) # 复制新的工作表
self.new_worksheet = self.new_workbook.get_sheet(0)
self.new_worksheet = self.new_worksheet.write(row, 11, value) #重新写入
self.new_workbook.save(self.file_path)
def __init__(self, inm, name): # init 方法套用 多态套用
# Excel.__init__(self, nam, num) # Excel 类init变量
s1 = xlrd.open_workbook(name)
self.s2 = copy(s1)
self.s3 = self.s2.add_sheet(inm)
# self.d = xlwt.Workbook() #新建一个excel文件
# self.table = self.d.add_sheet(inm) #新建一个excel表 add_sheet(工作表的名字)必填的
# self.inm = inm
self.name = name
def write_value(self,row,col,value):
'''
写入excel数据
row,col,value
'''
read_data = xlrd.open_workbook(self.file_name)
write_data = copy(read_data)
sheet_data = write_data.get_sheet(0)
sheet_data.write(row,col,value)
write_data.save(self.file_name)
def _xl_open_workbook(self):
"""Opens a workbook using either xlrd or openpyxl, and monkey patches
the resulting workbook objects to have the same interface for our
purposes.
"""
if self._use_xlsx:
wb = openpyxl.load_workbook(self._file_or_path)
wb.__dict__['sheet_by_name'] = wb.get_sheet_by_name
else:
wb = xlutils.copy(xlrd.open_workbook(self._file_or_path))
wb.__dict__['create_sheet'] = lambda title: wb.add_sheet(title)
return wb
def write_data(self, row, col, data):
"""
在单元格写入数据
:param col:
:param row:
:param data:
:return:
"""
read_value = xlrd.open_workbook(self.path)
write_data = copy(read_value)
write_data.get_sheet(self.index).write(row, col, data)
write_data.save(self.path)
def generate(u_id):
#global purchase,index
#index=0
#purchase={}
update(u_id)
dw_new = xlrd.open_workbook('new1p.xls')
ds_new = dw_new.sheet_by_index(0)
wx_new = copy(dw_new)
wr_new = wx_new.get_sheet(0)
bill = Frame(root)
bill.grid(row=0, column=0, sticky="news")
bill.tkraise()
Label(bill,
text='Thank You!! for shopping with us',
font=('Aerial 50 bold')).grid(row=0, column=1)
Label(bill, text='Product Qty Price',
font=('Chiller 30 bold')).grid(row=1, column=1)
n = 0
total = 0
pur = []
for i in range(len(purchase)):
Label(bill, text=purchase[i], font=('Aerial 25')).grid(row=i + 2,
column=1)
pur = purchase[i].split(' ')
total += int(pur[1]) * float(pur[2])
n = i + 2
Label(bill, text='', font=('Aerial 25')).grid(row=n + 1, column=1)
Label(bill, text='total ' + str(total),
font=('Aerial 25')).grid(row=n + 2, column=1)
temp = 0
for i in range(ds_new.nrows):
if (ds_new.cell_value(i, 0) == str(int(u_id))):
wr_new.write(i, 6, ds_new.cell_value(i, 6) + total)
wr_new.write(i, 7, ds_new.cell_value(i, 7) + total)
wr_new.write(i, 8, ds_new.cell_value(i, 8) + total)
temp = 1
break
if (temp == 0):
wr_new.write(ds_new.nrows, 0, str(int(u_id)))
wr_new.write(ds_new.nrows, 1, 0)
wr_new.write(ds_new.nrows, 2, 0)
wr_new.write(ds_new.nrows, 3, 0)
wr_new.write(ds_new.nrows, 4, 0)
wr_new.write(ds_new.nrows, 5, 0)
wr_new.write(ds_new.nrows, 6, total)
wr_new.write(ds_new.nrows, 7, total * 1.2)
wr_new.write(ds_new.nrows, 8, total)
wr_new.write(ds_new.nrows, 9, 0)
wx_new.save("new1p.xls")
ses.main()
def writeGeneTable(nameList, descriptionList):
'''write table (excel) which contain Gene informations'''
geneTable = open_workbook(geneTableFileName)
geneTableCopy = copy(geneTable)
i=0
while (i < len(nameList)):
print i
geneTableCopy.get_sheet(0).write(i+1, 0, nameList[i])
geneTableCopy.get_sheet(0).write(i+1, 1, descriptionList[i])
i+=1
geneTableCopy.save(geneTableFileNameTest)
return
def login(
self, request
): ## Method overriding: Overriding login method of class Read ##
book = open_workbook(
'/home/ubuntu/PycharmProjects/password_protector/password_protector/polls/password.xlsx'
)
book = copy(book) # creates a writeable copy
sheet = book.get_sheet(0) # get a first sheet
sheet.write(2, Read.u, "Facebook")
sheet.write(3, Read.u, "Gmail")
sheet.write(4, Read.u, "Yahoo")
sheet.write(2, Read.p, self.fb)
sheet.write(3, Read.p, self.gmail)
sheet.write(4, Read.p, self.yahoo)
book.save('password.xlsx')
def write_in_file(cls):
try:
book = open_workbook(
'/home/ubuntu/PycharmProjects/mysite/mysite/polls/password.xlsx'
)
book = copy(book) # creates a writeable copy
sheet = book.get_sheet(0) # get a first sheet
sheet.write(Write.row, Write.clm, Write.name)
sheet.write(Write.row, Write.clm + 1, Write.email)
sheet.write(Write.row, Write.clm + 2, Write.passw)
Write.clm += 3
book.save('password.xlsx')
except FileNotFoundError:
w = Write()
w.initial_write()
def setUpClass(cls):
atp_log.info('=====测试开始=====')
warnings.simplefilter("ignore", ResourceWarning) # 忽略ResourceWarning
#cls.s = requests.session() #session关联，会话保持
#cls.data_dic = opexcel.get_test_data(opexcel.get_param()) #从excel获取的参数，用例继承该父类，可直接使用参数
cls.writeResult = WriteResult()
cls.cell = 1
# 初始化结果excel
cls.file_path = os.path.join(bases.PARAM_PATH, bases.PARAM_NAME)
cls.workbook = xlrd.open_workbook(cls.file_path)
cls.new_workbook = copy(cls.workbook) # 复制新的工作表
#cls.new_worksheet = cls.new_workbook.get_sheet(0)
cls.new_workbook.save(
os.path.join(bases.REPORT_PATH, bases.RESULT_NAME)) #结果xls文件创建
def writeSampleTable():
'''write table (excel) containing sample informations'''
sampleTable = open_workbook(sampleTableFileName)
samplePhenotypeTable = open_workbook(samplePhenotypeTableFileName)
sampleTableCopy = copy(sampleTable)
subjIDPhenList =[]
phenTitleList = []
firstTime = True
for row in samplePhenotypeTable.sheet_by_index(0)._cell_values:
if firstTime:
phenTitleList = row
firstTime = False
subjIDPhen = row [0]
subjIDPhenList.append(subjIDPhen)
sampleTableCopy.get_sheet(0).write(0,61,phenTitleList[0])
sampleTableCopy.get_sheet(0).write(0,62,phenTitleList[1])
sampleTableCopy.get_sheet(0).write(0,63,phenTitleList[2])
sampleTableCopy.get_sheet(0).write(0,64,phenTitleList[3])
j=0
for row in sampleTable.sheet_by_index(0)._cell_values:
print j
i=0
subjIDGen = ''
for c in row[0]:
subjIDGen += c
if c == '-':
i+=1
if i == 2:
subjIDGen = subjIDGen[:-1]
try:
rowContent = samplePhenotypeTable.sheet_by_index(0)._cell_values[subjIDPhenList.index(subjIDGen)]
except ValueError:
print 'SUBJID : ', subjIDGen, ' not find in the phenotype table'
sampleTableCopy.get_sheet(0).write(j,61,rowContent[0])
sampleTableCopy.get_sheet(0).write(j,62,rowContent[1])
sampleTableCopy.get_sheet(0).write(j,63,rowContent[2])
sampleTableCopy.get_sheet(0).write(j,64,rowContent[3])
break
j+=1
sampleTableCopy.save(sampleTableFileName)
return
def updateGeneTable(geneList, chrList, coordList):
'''add information in the Gene table'''
geneTable = open_workbook(geneTableFileName)
geneTableCopy = copy(geneTable)
rowIndex = 0
for row in geneTable.sheet_by_index(0)._cell_values:
print rowIndex
try:
listIndex = geneList.index(row[0])
geneTableCopy.get_sheet(0).write(rowIndex, 2, chrList[listIndex])
geneTableCopy.get_sheet(0).write(rowIndex, 3, coordList[listIndex])
except ValueError:
print 'GeneSymbol: ', row[0], ' not find in the gene table'
rowIndex +=1
geneTableCopy.save(geneTableFileNameTest)
return
def main():
########## Test Variables and Indexers
count = 0
#######################################
previous_dir = os.getcwd()
### Change our working directory to the location of the e-mail bodies we will be parsing.
os.chdir('emailBodies')
### For all of the files in this directory (Should only be .txts in a certain format) iterate through them
for f in os.listdir('.'):
### Where f is the name of our current file
### In case we are still in the wrong directory after an iteration or two change back to correct one
if os.getcwd() == previous_dir:
os.chdir('emailBodies')
### Open the current .txt file for reading
fd = open(f,'r+')
fd.read(498)
machineName = fd.read(10)
machineName = ''.join(machineName.split())
fd.read(300)
#Read the rest of the file in as a string that will be parsed
parseMe = fd.read()
fd.close()
messageInfo = findInfo(parseMe)
#### Now that we have filenames and MD5's lets go back up a directory and write them to our central .xls file
os.chdir(previous_dir)
#### Open our workbook MD5SCAN.xls
rb = open_workbook(myTable)
wb = copy(rb)
##### List of the current sheets in so that we can append if we already have a record of it
writeBook(messageInfo,wb,rb,machineName)
### Clearing our entries for the past machine to make sure that we don't rewrite information
del(messageInfo.foundFiles[:])
del(messageInfo.foundMD5[:])
count+=1
def save_data_to_excel(data_list, filename='./FormatData/财务分析数据.xls'):
"""保存数据
将转化为一维数据表的科目发生额及余额存至Excel文档
参数
------
data_list : list
待保存的数据列表
filename : str
Excel文档的存放位置及文件名，含文件格式后缀
返回值
------
str
如果保存成功则输出“数据保存成功！”，否则提示“保存失败...”
"""
''' 根据数据的类型选择待追加数据的 sheet '''
if data_list[0] == 'TB':
sheet_index = 3
elif data_list[0] == 'DE':
sheet_index = 2
''' 打开并读取原 Excel 文件中的数据 '''
origin_data = xlrd.open_workbook(filename, formatting_info=True)
new_data = xlutils.copy(wb=origin_data)
new_sheet = new_data.get_sheet(sheet_index)
''' 获取原 Excel 文档的最大行数 '''
origin_sheet = origin_data.sheet_by_index(sheet_index)
max_row = origin_sheet.nrows
''' 将数据写入 Excel 文档 '''
for value in data_list[1]:
for i in range(len(value)):
new_sheet.write(max_row, i, value[i])
max_row = max_row + 1
new_data.save(filename)
return "数据保存成功..."
def get():
workbook = xlrd.open_workbook('static/submit.xls')
sheetname = time.strftime('%Y%m', time.localtime(time.time()))
try:
sheet = workbook.sheet_by_name(sheetname)
except:
newbook = copy(workbook)
sheet = newbook.add_sheet(sheetname)
for i in range(1, 4):
for j in range(1, 21):
sheet.write(j, i, 0)
newbook.save('static/submit.xls')
return '[[0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0], ' \
'[0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0], ' \
'[0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0]]'
returndata = []
for i in range(1, 4):
grade = []
for j in range(1, 21):
grade.append(int(sheet.cell(j, i).value))
returndata.append(grade)
return str(returndata)
def vmin_report(vmin_list, fname): workbook = open_workbook(fname,formatting_info = True) append_data = copy(workbook) sheet_name = 'Vmin' nu = 0 while sheet_name in workbook.sheet_names(): nu = nu + 1 sheet_name =sheet_name[:4] + str(nu) else: print 'add sheet:',sheet_name append_data.add_sheet(sheet_name) worksheet = append_data.get_sheet(-1) worksheet.write(0,0,'clock:') worksheet.write(0,1,'vmin:') for i, case in enumerate(vmin_list): if "ddr_vmin" in case.test_cmd_list[0]: cmd,vol,level = case.test_cmd_list[0].split() worksheet.write(i+1,0,ddr_fc[level]) worksheet.write(i+1,1,vol) elif "core_vmin" in case.test_cmd_list[0]: cmd,vol,level,clst = case.test_cmd_list[0].split() worksheet.write(i+1,0,core_fc[clst][level]+'/'+clst) worksheet.write(i+1,1,vol)
def fonction(referenceFile, completeFile):
referenceTable = open_workbook(referenceFile)
completeTable = open_workbook(completeFile)
completeTableCopy = copy(completeTable)
geneList =[]
for row in completeTable.sheet_by_index(0)._cell_values:
geneList.append('\'' + row[0])
i = 0
for row in referenceTable.sheet_by_index(0)._cell_values:
print i
if (i < len(geneList)):
if (row[3] == geneList[i]):
completeTableCopy.get_sheet(0).write(i,1,row[6])
i+=1
completeTableCopy.save(completeFile)
return
def grava_xls(nome_arq,result,aba,tipo):
if tipo==1:
rb = open_workbook(nome_arq)
r_sheet = rb.sheet_by_index(aba)
wb = copy(rb)
w_sheet = wb.get_sheet(aba)
numrows = len(result)
for i in range(numrows):
w_sheet.write(i, 0, result[i])
wb.save(nome_arq)
else:
print (result)
wb = Workbook()
ws = wb.add_sheet('Resultados')
numrows = len(result)
numcols = len(result[0])
for i in range(numrows):
for j in range(numcols):
ws.write(i, j, result[i][j])
wb.save(nome_arq)
def main():
if len(sys.argv) > 1 and sys.argv[1].isdigit():
iCodeNum = sys.argv[1]
else:
iCodeNum = 100
iCodeNum = int(iCodeNum)
if len(sys.argv) > 2 and sys.argv[2].isdigit():
iLastDay = sys.argv[2]
else:
iLastDay = 30
iLastDay = int(iLastDay)
oldExcel = open_workbook('./daobiao/excel/invitecode.xls',
formatting_info=1)
oldSheet = oldExcel.sheet_by_name('invite_code')
nrows = int(oldSheet.nrows) - 1
newExcel = copy(oldExcel)
newSheet = newExcel.get_sheet(0)
now = datetime.now()
mDict = {}
for i in range(nrows):
sCode = oldSheet.cell(i, 0).value
mDict[sCode] = 1
for i in range(iCodeNum):
s = get_random_string()
while s in mDict:
s = get_random_string()
mDict[s] = 1
nrows = nrows + 1
newSheet.write(nrows, INVITE_CODE_INDEX, s)
newSheet.write(nrows, CREATE_TIME_INDEX,
now.strftime('%Y-%m-%d %H:%M:%S'))
newSheet.write(nrows, LASTDAY_INDEX, iLastDay)
newExcel.save('./daobiao/excel/invitecode.xls')
from xlutils.copy import *
from xlrd import *
from xlwt import *
from datetime import *
import ConfigParser
# CONFIG #
config = ConfigParser.ConfigParser()
config.read("config.txt")
# EXCEL #
rb = open_workbook(config.get("general", "filename"), formatting_info=True, on_demand=True)
r_sheet = rb.sheet_by_index(4)
wb = copy(rb)
w_sheet = wb.get_sheet(4)
# VARIABLES #
includedEvents = []
noBackInServiceTime = []
incidentTimes = []
time_value = xldate_as_tuple(r_sheet.cell(570,16).value,rb.datemode)
print(time_value)
time_value = time_value(3) + time_value(4)
# IF PM IS IN THE CELL_VALUE #
import xlrd #读取excel
from xlutils import copy #复制excel用例
from vip_addUser import vip_add_user
from Vip_token import vip_get_token
import json
#1、读取excel测试用例
excelDir = r'C:\Users\xintian\desktop\松勤VIP接口测试用例-自动化-v4.0.xls' #xlsx另存为xls
#1-1 打开excel表
workbook = xlrd.open_workbook(excelDir, formatting_info=True)
workbooknew = copy(workbook)
worksheetnew = workbooknew.get_sheet(1)
worksheet = workbooknew.sheet_by_name('新增客户61个')
#print(cellData)
#print(cellExp)
#1-获取token
token = vip_get_token()
#4、自动化执行测试用例
for one in range(1, 10):
# 读取指定单元格
cellData = worksheet.cell(one, 6).value # 行，列---请求参数
cellExp = json.loads(worksheet.cell(one, 8).value) # ----预期结果
idNum = worksheet.cell(one, 0).value
# 2-新增用户
res = vip_add_user(cellData, token, True)["message"]
# 3、跟预期数据匹配
if res == cellExp["message"]:
print('f{idNum}--->测试用例----成功')
excel_res = 'pass'
import xlrd
from xlutils.copy import *
import xlwt
# 1.打开excel文件，读取某一个位置的内容
book = xlrd.open_workbook("../20180621刷红.xls")
sheet = book.sheet_by_index(0)
# 2.读取道路，案件小类，计量 3列的数据放在3个列表当中
DaoLu = sheet.col_values(4,156,166)
AnJianXiaoLei = sheet.col_values(7,156,166)
JiLiangShu = sheet.col_values(12,156,166)
# 3.读入欲写入表格的内容准备进行比较
book2 = xlrd.open_workbook("./biaoge.xls")
read_sheet = book2.sheet_by_index(0)
<<<<<<< HEAD
# 4.调用xlutils复制要写入的表格
bookwrite = copy(book2)
write_sheet = bookwrite.get_sheet(0)
# 5.进行比较和逻辑运算
# write_sheet.write(3, 0, 123)
#最总把内存中复制的表格保存到新的表格中
bookwrite.save("./456.xls")
=======
jian_cha_xiang = read_sheet.row_values(1,1,45)
jian_cha_jie_dao = read_sheet.col_values(0,2,17)
print(jian_cha_xiang)
print(jian_cha_jie_dao)
i = 0
cishu = 0
while i<10:
if "违法三乱"==AnJianXiaoLei[i] and jian_cha_jie_dao[0]==DaoLu[i]:
#coding:UTF-8
import sys
reload(sys)
sys.setdefaultencoding('utf8')
import MySQLdb
import xlwt
import time
import datetime
import os
import xlrd
from xlutils import copy
from xlutils.copy import copy
sys.path.append('..')
sys.path.append('/tmp/ErrorReportPro/errorReport/')
oldWb = xlrd.open_workbook(r'E:\GH-CDN-E-NGid5206new.xls',
formatting_info=True)
w = copy(oldWb)
append_index = len(w._Workbook__worksheets) - 3
w.set_active_sheet(append_index)
w.save(r'E:\GH-CDN-E-NGid5206newnew.xls')
def write_with_xlwt(self):
workbook = xlrd.open_workbook(self.target)
workbook = xlutils.copy(workbook)
sheet = workbook.get_sheet(0)
sheet.write(0, 0, 'value')
workbook.save()
import xlwt #写入数据
import xlutils #操作excel
#----xlrd库
#打开excel文件
workbook = xlrd.open_workbook('myexcel.xls')
#获取表单
worksheet = workbook.sheet_by_index(0)
#读取数据
data = worksheet.cell_value(0,0)
#----xlwt库
#新建excel
wb = xlwt.Workbook()
#添加工作薄
sh = wb.add_sheet('Sheet1')
#写入数据
sh.write(0,0,'data')
#保存文件
wb.save('myexcel.xls')
#----xlutils库
#打开excel文件
book = xlrd.open_workbook('myexcel.xls')
#复制一份
new_book = xlutils.copy(book)
#拿到工作薄
worksheet = new_book.getsheet(0)
#写入数据
worksheet.write(0,0,'new data')
#保存
new_book.save()
```
def get_data(self, content, startNum, m):
soup = BeautifulSoup(content, 'html.parser')
#找第一个title
#title = soup.find("title")
title = soup.title
_title = title.string[0:title.string.find("-")]
#产地，城市 如果没有城市，就匹配产地，都没有默认中国大陆其格式为 中国大陆
city = soup.find(text=re.compile("城市"))
provience = soup.find(text=re.compile("省份"))
chandi = soup.find(text=re.compile("产地"))
fruit_name = ''
if city is None or provience is None or chandi is None:
fruit_place = '中国大陆'
fruit_name = "中国" + str(m)
elif city is None and provience is None:
fruit_place = 'f' + chandi[chandi.find(':') + 2:]
fruit_name = chandi[chandi.find(':') + 2:] + str(m)
elif city is not None or provience is not None:
fruit_place = provience[provience.find(':') +
2:] + city[city.find(':') + 2:]
fruit_name = city[city.find(':') + 2:] + str(m)
if fruit_name.find("/") > 0:
fruit_name = fruit_name.split("/")[0]
# 售价
fruit_sellprice = round(random.uniform(40, 80), 2)
# 原价
fruit_price = round(random.uniform(fruit_sellprice, 80), 2)
#进价
fruit_enterprice = round(random.uniform(40, fruit_sellprice), 2)
#图片名
fruit_images = startNum
#库存数量
fruit_amount = 100
#上架状态
fruit_states = 0
#描述信息
fruit_info = _title
#保鲜时间
fruit_date = 180
#上架时间（现在为准）
now = datetime.datetime.now()
fruit_time = now.strftime('%Y-%m-%d %H:%M')
#置顶
istop = 0
#水果类别
fruit_type = m
#添加人
addman = '爬虫抓取'
#获取图片地址
picDiv = soup.find(class_="tb-thumb-content")
links = picDiv.find_all("img")
piccount = 1
#10057开始 图片存储
for link in links:
name = str(startNum) + "_ (" + str(piccount) + ").png"
print('图片名.......', name)
#要转化一下为.jpg_430x430q90.jpg的后缀的图片，才能得到高清图
temp = 'http:' + link['src']
targetpicurl = temp[0:temp.find('jpg')] + 'jpg_430x430q90.jpg'
print(targetpicurl)
flag = self.copypic(targetpicurl, name)
#flag ==1 为图片复制成功 0 为成功
if flag == 1:
piccount = piccount + 1
# 图片数量
fruit_imgcount = piccount - 1
#使用execl保存表格数据
rexcel = open_workbook("fruit.xls") # 用wlrd提供的方法读取一个excel文件
rows = rexcel.sheets()[0].nrows # 用wlrd提供的方法获得现在已有的行数
excel = copy(rexcel) #用xlutils提供的copy方法将xlrd的对象转化为xlwt的对象
table = excel.get_sheet(0) # 用xlwt对象的方法获得要操作的sheet
values = [
startNum, fruit_name, fruit_sellprice, fruit_price, fruit_place,
startNum, piccount, fruit_amount, fruit_states, _title, fruit_date,
fruit_time, istop, fruit_type, fruit_enterprice, addman
]
row = rows
num = 0
for value in values:
table.write(row, num, value) # xlwt对象的写方法，参数分别是行、列、值
num = num + 1
excel.save("fruit.xls")
# 2-2新增用户
adduser_url1 = "http://ip:port/api/mgr/sq_mgr/?action=list_course&pagenum=1&pagesize=20"
#请求参数为字符串，字符串转json格式
adduser_data = json.loads(celldata)
print(type(adduser_data))
adduser_headers = {"Content-y=type": "application/json", "X-AUTO_TOKEN": token}
adduser_resp = requests.post(adduser_url1,
data=json.dumps(adduser_data),
headers="adduser_headers")
print(adduser_resp.text)
#加检查点
res = adduser_resp.json()["message"]
if res == "success":
print("新增用户测试成功----成功，耗时为", adduser_resp.elapsed.total_seconds())
excel_res = "pass"
else:
print("新增用户测试成功----失败，")
excel_res = "fail"
#3.测试结果写入excel
import xlutils
# 首先打开文件
# workbooknew=xlrd.open_workbook(excel_dir)
# 复制
workbookWr = xlutils.copy(workbook)
wrsheet = workbook.get_sheet(0)
wrsheet.write(1, 9, excel_res)
workbookWr.save(
r"/Users/mac/PycharmProjects/songqin_test_course/port_test/物流查询接口用例的副本testresult.xlsx"
)
# print(os.path.join(npath,y))
path = "e:/test1/2019.7/"
filename1 = "SUM - 2019.xlsx"
data1 = xlrd.open_workbook(filename1)
full_li = []
for i in range(1,5):
cur_table = data1.sheets(i)
full_li.append(cur_table.col_values(3))
data2 = xlutils.copy(data1)
cplist = []
for i in range(1,5):
cplist.append(data2.get_sheet(i))
def readfile(filename):
data = xlrd.open_workbook(filename)
table1 = data.sheets()[0]
nrow = table1.nrows-3
ncol = table1.ncols-2
key = table1.cell_value(3,1)
value = table1.cell_value(nrow,ncol)
return key,value
for filename in fun1(path):
key,value = readfile(filename)
def wirte_value(self,row,col,value):
read_data = xlrd.open_workbook(self.file_name)
write_data = copy(read_data)
###################################################################
## New imports for all the excel reading and writing functions
from xlrd import open_workbook
from xlwt import *
from xlutils.copy import *
# Open the excel spreadsheet and copy it
rb = open_workbook(
'N:\sw\user\masker\pygram\ipfiles\AM7801 EVB Test Template.xls',
formatting_info=True)
#rb = open_workbook('Y:\Projects\Chiarlo\Shared_Folder\DVT Test Results\AM7801\A4 BOM_E\SN001_008_009_010_samplesboards\Ant_Rx_VSWR\AM7801 EVB Test Template with limits.xls',formatting_info=True)
#rb = open_workbook('X:\\01-Projects\\05-Chiarlo\PCB\AM7802-01_EVB_EEB\Test Plan\AM7801 EVB Test Template.xls',formatting_info=True)
#rb = open_workbook('N:\sw\user\masker\pygram\ipfiles\AM7801 EVB Test Template with limits-09-1112.xls',formatting_info=True)
rs = rb.sheet_by_index(0)
wb = copy(rb)
ws = wb.get_sheet(0)
###################################################################
###################################################################
# start pygram
aa = pygram()
# Load the logfile
#aa.add_logfile( r'N:\sw\user\masker\pygram\ipfiles\001-RX1.s2p')
#aa.add_logfile( r'N:\sw\user\masker\pygram\ipfiles\001-RX2.s2p')
#aa.add_logfile( r'N:\sw\user\masker\pygram\ipfiles\001-RX3.s2p')
#aa.add_logfile( r'N:\sw\user\masker\pygram\ipfiles\001-RX4.s2p')
aa.add_all_logfiles_dialog()
