from excel_xlrd_xlwt import read_to_json
import os
import math
import xlrd,xlwt
from xlutils.copy import copy

'''
该脚本功能是读取Excel文件里面的经纬度信息，计算出距离并且填到表中
经纬度距离计算公式是根据
https://www.cnblogs.com/softfair/p/distance_of_two_latitude_and_longitude_points.html  
这个链接的地址计算的

'''

EARTH_RADIUS = 6378.137 #地球半径 km
ROOT = os.path.split(__file__)[0]
#要读取的Excel文件路径
excelUrl = os.path.join(ROOT,'data','lon-lat-data.xls')

def haverSin(theta):
    v = math.sin(theta / 2)
    return v * v

#计算距离
def distance(excelUrl):
    '''
        计算xls文件的里面的经纬度距离
    '''
    jsonData = read_to_json(excelUrl)
    sheetDatas = jsonData['Sheet2']
    dataLen = len(sheetDatas)
    for i in range(dataLen):
        obj =   sheetDatas[i]
        #把角度转换成弧度
        lon1 = math.radians(obj['Lon1'])
        lon2 = math.radians(obj['Lon2'])
        lat1 = math.radians(obj['Lat1'])
        lat2 = math.radians(obj['Lat2'])
        #计算绝对值
        vlon = abs(lon1-lon2)
        vlat = abs(lat1-lat2)
        #计算两点间距离
        h = haverSin(vlat) + math.cos(lat1)*math.cos(lat2)*haverSin(vlon)
        #计算实际距离
        dis = 2*EARTH_RADIUS*math.asin(math.sqrt(h))    
        obj['Dis'] = float(('%0.7f'%(dis*1000)))
        
    return  sheetDatas

def writeDisToXls(excelUrl,sheetDatas):
    '''
        把计算结果填充进原Excel
    '''
    wb = xlrd.open_workbook(excelUrl,formatting_info=True)
    sheetName = 'Sheet2'
    oldSheet = getSheetByName(wb,sheetName)
    lastCol = oldSheet.ncols
    
    copyWb = copy(wb)    
    sheet = copyWb.get_sheet(sheetName) #根据xls里面的表名获取具体某个表   
    title = 'Dis'
    sheet.write(0,lastCol,title) #先写入标题行的字段名
    dataLen = len(sheetDatas)
    for i in range(dataLen):
        obj = sheetDatas[i]
        sheet.write(i+1,lastCol,obj[title])

    dirSet = os.path.splitext(excelUrl)
    excelUrl = dirSet[0] + '_result.xls' #设置保存的路径   
    if os.path.isfile(excelUrl) and os.path.exists(excelUrl):
        os.remove(excelUrl) #删除旧的
    copyWb.save(excelUrl)
    print("处理后的Excel数据保存在 %s 中"%excelUrl)

def getSheetByName(wb,sheetName):
    tables = wb.sheets() #读取所有表    
    for sheet in tables:
        if sheet.name == sheetName:
            return sheet
            

sheetDatas = distance(excelUrl)
writeDisToXls(excelUrl,sheetDatas)