1 #!/usr/bin/python 2 #coding:utf-8 3   4 import xlrd 5 import xlwt 6 import requests 7 import urllib 8 import math 9 import re10  11 pattern_x=re.compile(r‘"x":(".+?")‘)12 pattern_y=re.compile(r‘"y":(".+?")‘)13  14 def mercator2wgs84(mercator):15     #key1=mercator.keys()[0]16     #key2=mercator.keys()[1]17     point_x=mercator[0]18     point_y=mercator[1]19     x=point_x/20037508.3427892*18020     y=point_y/20037508.3427892*18021     y=180/math.pi*(2*math.atan(math.exp(y*math.pi/180))-math.pi/2)22     return (x,y)23  24 def get_mercator(addr):25     quote_addr=urllib.quote(addr.encode(‘utf8‘))26     city=urllib.quote(u‘齐齐哈尔市龙‘.encode(‘utf8‘))27     province=urllib.quote(u‘黑龙江省‘.encode(‘utf8‘))28     if quote_addr.startswith(city) or quote_addr.startswith(province):29         pass30     else:31         quote_addr=city+quote_addr32     s=urllib.quote(u‘北京市‘.encode(‘utf8‘))33     api_addr="http://api.map.baidu.com/?qt=gc&wd=%s&cn=%s&ie=utf-8&oue=1&fromproduct=jsapi&res=api&callback=BMap._rd._cbk62300"%(quote_addr34 ,s)35     req=requests.get(api_addr)36     content=req.content37     x=re.findall(pattern_x,content)38     y=re.findall(pattern_y,content)39     if x:40         x=x[0]41         y=y[0] 42         x=x[1:-1]43         y=y[1:-1]44         x=float(x)45         y=float(y)46         location=(x,y)47     else:48         location=()49     return location50  51 def run():52     data=http://www.mamicode.com/xlrd.open_workbook(‘Book2.xls‘)53     rtable=data.sheets()[0]54     nrows=rtable.nrows55     values=rtable.col_values(0)56      57     workbook=xlwt.Workbook()58     wtable=workbook.add_sheet(‘data‘,cell_overwrite_ok=True)59     row=060     for value in values:61         mercator=get_mercator(value)62         if mercator:63             wgs=mercator2wgs84(mercator)64         else:65             wgs=(‘NotFound‘,‘NotFound‘)66         print "%s,%s,%s"%(value,wgs[0],wgs[1])67         wtable.write(row,0,value)68         wtable.write(row,1,wgs[0])69         wtable.write(row,2,wgs[1])70         row=row+171  72     workbook.save(‘data.xls‘)73  74 if __name__==‘__main__‘:75     run()