Join the 1-day Testing & QA Summit featuring 15+ Expert Speakers.Register for FREE! Join TestMu Conference

How to use sheetsR method in pyatom

Best Python code snippet using pyatom_python

getroadshape.py

Source:getroadshape.py

1"""2ç¬åä¸æµ·å¸ææéè·¯çå½¢ç¶åæ ï¼å¹¶ä»¥jsonæä»¶æ ¼å¼åå¨3"""4from urllib.parse import quote5from urllib import request6import json7import xlrd8import ssl9ssl._create_default_https_context = ssl._create_unverified_context1011amap_web_key = 'ä½ çkey'12poi_search_url = "http://restapi.amap.com/v3/place/text"13poi_boundary_url = "https://www.amap.com/service/poiInfo?query_type=IDQ&qii=true&need_utd=true&utd_sceneid=1000&addr_poi_merge=true&is_classify=true"14cityname = "ä¸æµ·"15classfiled = "éè·¯å"16read_file_dir = "éè·¯å.xls"17save_file_dir = "road.json"18#no_sheet:sheetçç¼å·,no_cell_value:åç¼å·19no_sheet = 020no_cell_value = 02122# æ ¹æ®idè·åè¾¹çæ°æ®23def getBounById(id):24    req_url = poi_boundary_url + "&key=" + amap_web_key + "&id=" + id25    with request.urlopen(req_url) as f:26        data = f.read()27        data = data.decode('utf-8')28        dataList = []29        datajson = json.loads(data)  # å°åç¬¦ä¸²è½¬æ¢ä¸ºjson30        #print(datajson)31        if len(datajson) < 1:32            return dataList33        datajson = datajson['data']34        datajson = datajson['poi_list'][0]35        datajson = datajson['domain_list'][3]36        if datajson.get('value') != None:37            datajson = datajson['value']38            dataArr = [x.split('|') for x in datajson.split('_')]39            for i in dataArr:40                innerList = []41                #æ¯ä¸ªinnerListåå¨ä¸å¯¹æ°æ®42                f = i[0].split(',')4344                innerList.append(float(f[0]))45                innerList.append(float(f[1]))46                #print(innerList)47                dataList.append(innerList)48        return dataList4950def readname():51    myWordbookr = xlrd.open_workbook(read_file_dir)52    mySheetsr = myWordbookr.sheets()53    mySheetr = mySheetsr[no_sheet]54    # è·ååæ°55    nrows = mySheetr.nrows56    with open(save_file_dir, "w") as fp:57        for i in range(1, nrows):58            # delay = 80000059            # while delay > 0:60            #     delay -= 161            id = mySheetr.cell_value(i, no_cell_value)62            roadname = mySheetr.cell_value(i, 1)63            address = mySheetr.cell_value(i, 2)64            boundarydata = getBounById(id)65            tmp = {66                "features": [67                    { "attributes": {68                            "FID": i,69                            "åç§°": roadname,70                            "åº": address71                        },72                        "geometry": {73                            "paths": [74                               boundarydata75                            ]76                        }77                    }78                ]79            }80            print(tmp)81            fp.write(json.dumps(tmp, indent=4, ensure_ascii=False))82            if i % 100 == 0:83                print("sleep")84                delay = 80000000085                while delay > 0:86                    delay -= 187                print("stopsleep")888990    print('åå¥æå')9192readname()939495# æ ¹æ®è·åå°çpoiæ°æ®çidè·åè¾¹çæ°æ®96#dataList = getBounById('B0FFGQ7PQK')97#print(dataList)
...

time-bus.py

Source:time-bus.py

1import xlrd2import urllib.request3from urllib.parse import quote4import json5import pandas as pd6import time7import os8910def rgrid(read_file_dir):11    myWordbookr = xlrd.open_workbook(read_file_dir)12    mySheetsr = myWordbookr.sheets()13    mySheetr = mySheetsr[no_sheet]14    # è·ååæ°15    nrows = mySheetr.nrows16    grid = []17    for i in range(1, nrows):18        lon = mySheetr.cell_value(i, 1)19        lat = mySheetr.cell_value(i, 2)20        grid.append([round(lon, 6), round(lat, 6)])21    return grid222324read_file_dir = "sfq-grid.xls"  # è¾å¥æµç®èå´æ æ ¼åæ æä»¶ï¼ä½ä¸ºèµ·ç¹25no_sheet = 026grid = rgrid(read_file_dir)27key = ''  # è¾å¥ä½ çkey28des = [120.802392,31.061964]  # ç»ç¹åæ ï¼åä¸ï¼éè¿é«å¾·å°å¾åæ æ¾åå¨æ¥è¯¢29uri = 'https://restapi.amap.com/v3/direction/transit/integrated?origin='30city = '0512'  # æ¯æå¸åå¬äº¤æ¢ä¹/è·¨åå¬äº¤çèµ·ç¹åå¸ï¼è§åï¼åå¸åç§°/citycode31data_csv = {}32times, lons, lats, distances = [], [], [], []33for i in range(len(grid)):34    url = uri + str(grid[i][0]) + ',' + str(grid[i][1]) + '&destination=' + str(des[0]) + ',' + str(35        des[1]) + '&city=' + str(city) + '&cityd=' + str(city)+ '&key=' + key36    print(url)37    temp = urllib.request.urlopen(url)38    temp = json.loads(temp.read())39    data = temp["route"]40    lons.append(data["origin"].split(",")[0])41    lats.append(data["origin"].split(",")[1])42    try:43        distances.append(data["transits"][0]["distance"])44        times.append(data["transits"][0]["duration"])45        print("å·²å®æï¼", i, "/", len(grid))46    except:47        print(1)48        distances.append(no_sheet)49        times.append(no_sheet)50data_csv['lon'], data_csv['lat'], data_csv['distance'], data_csv['time'] = lons, lats, distances, times51df = pd.DataFrame(data_csv)52file_name = 'time_bus' + str(des[0]) + '_' + str(des[1]) + ".csv"53file_path = "c:" + os.sep + "test" + os.sep + file_name54df.to_csv(file_path, index=False, encoding='utf_8_sig')
...

getbusline.py

Source:getbusline.py

1import time2import xlrd3import xlwt4start_time = time.time()5no_sheet = 06read_file_dir = "poi-320509-150700-04131106-1.xls"  # poiæä»¶7myWordbookr = xlrd.open_workbook(read_file_dir)8mySheetsr = myWordbookr.sheets()9mySheetr = mySheetsr[no_sheet]10nrows = mySheetr.nrows11busline=[]12name = "å¬äº¤çº¿è·¯" + time.strftime("%m%d%H%M", time.localtime())13book = xlwt.Workbook(encoding='utf-8', style_compression=0)14sheet = book.add_sheet("1", cell_overwrite_ok=True)15sheet.write(0, 0, 'busline')16for i in range(1, nrows):17    cityname = mySheetr.cell_value(i, 5)18    bus = mySheetr.cell_value(i, 3)19    print("bus", bus)20    for j in range(len(bus.split(';'))):21        bus1 = bus.split(';')[j]22        busline.append(bus1)232425print("bl",busline)26for k in range(len(busline)):27    sheet.write(k+1, 0, busline[k])28end_time = time.time()29book.save(r'' + name + '.xls')
...

Automation Testing Tutorials

Learn to execute automation testing from scratch with LambdaTest Learning Hub. Right from setting up the prerequisites to run your first automation test, to following best practices and diving deeper into advanced test scenarios. LambdaTest Learning Hubs compile a list of step-by-step guides to help you be proficient with different test automation frameworks i.e. Selenium, Cypress, TestNG etc.