在excel 宏中vb 实现 高德与天地图(gps)坐标的互相转换
1
2右键插入模块
3实现代码
代码如下,之前没接触过vb可能实现过程有点繁琐,不过能实现功能
Function 天地图坐标转高德Lng(wgLon, wgLat)
x = 0
y = 0
Pi = 3.14159265358979
a = 6378245#
ee = 6.69342162296594E-03
If 是否国外(wgLon, wgLat) Then
x = wgLon
y = wgLat
Else
dLat = transformLat(wgLon - 105#, wgLat - 35#)
dLon = transformLon(wgLon - 105#, wgLat - 35#)
radLat = wgLat / 180# * Pi
magic = Sin(radLat)
magic = 1 - ee * magic * magic
sqrtMagic = Sqr(magic)
dLat = (dLat * 180#) / ((a * (1 - ee)) / (magic * sqrtMagic) * Pi)
dLon = (dLon * 180#) / (a / sqrtMagic * Cos(radLat) * Pi)
y = wgLat + dLat
x = wgLon + dLon
End If
天地图坐标转高德Lng = x
End Function
Function 天地图坐标转高德Lat(wgLon, wgLat)
x = 0
y = 0
Pi = 3.14159265358979
a = 6378245#
ee = 6.69342162296594E-03
If 是否国外(wgLon, wgLat) Then
x = wgLon
y = wgLat
Else
dLat = transformLat(wgLon - 105#, wgLat - 35#)
dLon = transformLon(wgLon - 105#, wgLat - 35#)
radLat = wgLat / 180# * Pi
magic = Sin(radLat)
magic = 1 - ee * magic * magic
sqrtMagic = Sqr(magic)
dLat = (dLat * 180#) / ((a * (1 - ee)) / (magic * sqrtMagic) * Pi)
dLon = (dLon * 180#) / (a / sqrtMagic * Cos(radLat) * Pi)
y = wgLat + dLat
x = wgLon + dLon
End If
天地图坐标转高德Lat = y
End Function
Function 高德转天地图Lng(wgLon, wgLat)
x = 0
y = 0
Pi = 3.14159265358979
a = 6378245#
ee = 6.69342162296594E-03
If 是否国外(wgLon, wgLat) Then
x = wgLon
y = wgLat
Else
dLat = transformLat(wgLon - 105#, wgLat - 35#)
dLon = transformLon(wgLon - 105#, wgLat - 35#)
radLat = wgLat / 180# * Pi
magic = Sin(radLat)
magic = 1 - ee * magic * magic
sqrtMagic = Sqr(magic)
dLat = (dLat * 180#) / ((a * (1 - ee)) / (magic * sqrtMagic) * Pi)
dLon = (dLon * 180#) / (a / sqrtMagic * Cos(radLat) * Pi)
y = wgLat - dLat
x = wgLon - dLon
End If
高德转天地图Lng = x
End Function
Function 高德转天地图Lat(wgLon, wgLat)
x = 0
y = 0
Pi = 3.14159265358979
a = 6378245#
ee = 6.69342162296594E-03
If 是否国外(wgLon, wgLat) Then
x = wgLon
y = wgLat
Else
dLat = transformLat(wgLon - 105#, wgLat - 35#)
dLon = transformLon(wgLon - 105#, wgLat - 35#)
radLat = wgLat / 180# * Pi
magic = Sin(radLat)
magic = 1 - ee * magic * magic
sqrtMagic = Sqr(magic)
dLat = (dLat * 180#) / ((a * (1 - ee)) / (magic * sqrtMagic) * Pi)
dLon = (dLon * 180#) / (a / sqrtMagic * Cos(radLat) * Pi)
y = wgLat - dLat
x = wgLon - dLon
End If
高德转天地图Lat = y
End Function
Function 是否国外(lon, lat)
ss = False
If lon < 72.004 Or lon > 137.8347 Then
ss = True
ElseIf lat < 0.8293 Or lat > 55.8271 Then
ss = True
End If
是否国外 = ss
End Function
Function transformLat(x, y)
Pi = 3.14159265358979
a = 6378245#
ee = 6.69342162296594E-03
ret = -100# + 2# * x + 3# * y + 0.2 * y * y + 0.1 * x * y + 0.2 * Sqr(Abs(x))
ret = ret + (20# * Sin(6# * x * Pi) + 20# * Sin(2# * x * Pi)) * 2# / 3#
ret = ret + (20# * Sin(y * Pi) + 40# * Sin(y / 3# * Pi)) * 2# / 3#
ret = ret + (160# * Sin(y / 12# * Pi) + 320 * Sin(y * Pi / 30#)) * 2# / 3#
transformLat = ret
End Function
Function transformLon(x, y)
Pi = 3.14159265358979
a = 6378245#
ee = 6.69342162296594E-03
ret = 300# + x + 2# * y + 0.1 * x * x + 0.1 * x * y + 0.1 * Sqr(Abs(x))
ret = ret + (20# * Sin(6# * x * Pi) + 20# * Sin(2# * x * Pi)) * 2# / 3#
ret = ret + (20# * Sin(x * Pi) + 40# * Sin(x / 3# * Pi)) * 2# / 3#
ret = ret + (150# * Sin(x / 12# * Pi) + 300# * Sin(x / 30# * Pi)) * 2# / 3#
transformLon = ret
End Function
4,使用excel函数,效果如下
上述有问题也可直接下载excel使用,最好使用office excel打开,并启用宏