#!/usr/bin/env python
# -*- coding: utf-8 -*- 
import urllib.request
import urllib.parse
import xlrd
from xlrd import open_workbook
import xlwt
import xlutils
import datetime
from xlutils.copy import copy
import sys
import json
import time

def testXlrd(filename):
    book = xlrd.open_workbook(filename)
    sh = book.sheet_by_index(0)
    print(sh.nrows, sh.ncols)
    for rownum in range(2, 759):
        row = sh.row_values(rownum)[0]
        print(row)
        requestLBS(filename, row, rownum)

def requestLBS(filename, name, rownum):
    time.sleep(2)
    url = "http://if.xqwy.cn/community/search"
    postdata = urllib.parse.urlencode({
        "deviceId":"00000000-04e6-7d39-28fe-a7f40033c587",
        "appVersion":"4.6",
        "longitude":"121.538163",
        "version":"1.0.0",
        "pageSize":"10",
        "keyword":name,
        "city":"苏州",
        "latitude":"31.296207",
        "page":"1"
    }).encode("utf-8")
    req = urllib.request.Request(url,postdata)
    req.add_header("User-Agent", "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_12_5) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/59.0.3071.115 Safari/537.36")
    data = urllib.request.urlopen(req).read()
    jsonstr = json.loads(data)
    msg = jsonstr['message']
    if msg == None:
        communities = jsonstr['data']['communities']
        for i in range(len(communities)):
            for key,value in communities[i].items():
                #print(key,value)
                if key == "longitude":
                    print("longitude: "+ str(value))
                    testXlwt(filename,str(value),rownum,8)
                if key == "latitude":
                    print("latitude: " + str(value))
                    testXlwt(filename,str(value),rownum,9)
 #    longitudestr = json.loads(communities)
    # longitude = longitudestr['longitude']
    # print(longitude)

    # latitude = jsonstr['data']['communities']['latitude']
    # fhandle = open("/Users/xunwang/Desktop/ftx.txt", "wb")
    # fhandle.write(longitude + ","+ latitude)
    # fhandle.close()

def testXlwt(filename, strs, rownum, index):
    book = xlrd.open_workbook(filename)
    sh = book.sheet_by_index(0)
    wsh = copy(book)
    wsh2 = wsh.get_sheet(0)

    wsh2.write(rownum, index, strs)
    wsh.save(filename)
    print(" 写入表成功" + str(rownum))

if __name__=='__main__':
    testXlrd("/Users/xunwang/Desktop/yuanqu.xls")