Beispiel #1
0
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")
Beispiel #2
0
    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)
Beispiel #3
0
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))
Beispiel #4
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'
Beispiel #5
0
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()
Beispiel #6
0
 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')
Beispiel #7
0
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)
Beispiel #8
0
    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)
Beispiel #9
0
 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
Beispiel #10
0
 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)
Beispiel #11
0
 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
Beispiel #12
0
 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)
Beispiel #13
0
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()
Beispiel #14
0
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
Beispiel #15
0
 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')
Beispiel #16
0
 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()
Beispiel #17
0
    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文件创建
Beispiel #18
0
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
Beispiel #19
0
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
Beispiel #20
0
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
Beispiel #21
0
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 "数据保存成功..."
Beispiel #22
0
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)
Beispiel #23
0
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
Beispiel #25
0
        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)
Beispiel #26
0
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')
Beispiel #27
0
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'
Beispiel #29
0
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')
Beispiel #31
0
 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()
```
Beispiel #33
0
 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("-")]
     #产地,城市 如果没有城市,就匹配产地,都没有默认中国大陆其格式为 &#20013;&#22269;&#22823;&#38470;
     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"
)
Beispiel #35
0
                # 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)
Beispiel #37
0
###################################################################
## 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()