一、使用python将excel表中的经纬度换算成小数格式。

在文件上看到的经纬度是东经 116°5′27.78″，北纬 23°10′57.18″，要转化为116.09105,23.182550000000003 格式。如果要用vba编写函数，可能比较麻烦，为此我使用python来转化

import sys# 获取命令行参数
arguments = sys.argv
zh = arguments[1]zh= zh.replace(",", "，")
zhdj=zh.split('，')[0]
zhbw=zh.split('，')[1]if '″' not in zh:if len(zhdj.split("′")[1]) > 0:zhdj0=int(zhdj.split("东经")[1].split('°')[0]) + float(zhdj.split("°")[1].split('′')[0])/60 + float(zhdj.split("′")[1])/3600zhbw0=int(zhbw.split("北纬")[1].split('°')[0]) + float(zhbw.split("°")[1].split('′')[0])/60 + float(zhbw.split("′")[1])/3600else:zhdj0=int(zhdj.split("东经")[1].split('°')[0]) + float(zhdj.split("°")[1].split('′')[0])/60zhbw0=int(zhbw.split("北纬")[1].split('°')[0]) + float(zhbw.split("°")[1].split('′')[0])/60       
else:zhdj0=int(zhdj.split("东经")[1].split('°')[0]) + float(zhdj.split("°")[1].split('′')[0])/60 + float(zhdj.split("′")[1].split('″')[0])/3600zhbw0=int(zhbw.split("北纬")[1].split('°')[0]) + float(zhbw.split("°")[1].split('′')[0])/60 + float(zhbw.split("′")[1].split('″')[0])/3600
print(str(zhdj0) + "," + str(zhbw0))

上面为exzb.py源程序，用于vba传递参数并返回结果。

单元格的公式：=zh(SUBSTITUTE(SUBSTITUTE(R98,CHAR(34),"")," ",""))

用于去除字符串中的空格。

打开vba编制器：

Function zh(param1 As String) As StringDim objShell As ObjectDim pythonExePath As StringDim scriptPath As StringDim command As String' 创建WScript.Shell对象Set objShell = VBA.CreateObject("WScript.Shell")' 指定Python解释器的路径pythonExePath = "C:/Users/YBK/.conda/envs/python38/python.exe"' 指定Python脚本的路径scriptPath = "E:\.spyder-py3\exzb.py"' 构建命令command = pythonExePath & " " & scriptPath & " " & param1 & " "' 运行命令并等待完成result = objShell.Exec(command).StdOut.ReadAllzh = result
End Function

其他经纬度可以用公式转换。

导出文件内容：

{"Version":"V9.7.1","Type":1,"ObjItems":[{"Type":7,"ObjID":2130112827,"ParentID":2024832056,"tmModify":"2024/09/16 22:47:55","Object":{"Name":"测试的点","Type":7,"Comment":"","ObjectDetail":{"Lat":23.18255000,"Lng":116.09105000,"Gcj02":0,"Altitude":0,"EditMode":0,"OverlayIdx":0,"TxtType":1,"ShowLevel":1,"ShowLevelMax":0,"TimeUncertain":0,"SignEvent":{"Radius":0,"ShowClr":0},"SignPic":{"SignPic":1,"AlignFlag":0,"SignClr":0,"PicScale":0,"SignPicNum":0,"SignPicNumOffx":0,"SignPicNumOffy":0,"SignPicNumClr":0,"SignPicNumSize":0},"TxtShowSta":0,"TxtShowStaSet":0}}}]
}

使用python转化成kml格式：（自己编写的）

import json
from simplekml import Kml# 读取.ovjsn文件
with open(r'C:\Users\YBK\Documents\测试的点.ovjsn', 'r' ,encoding='utf-8-sig') as f:ovjsn_data = json.load(f)
objitems = ovjsn_data['ObjItems']
zbdict = objitems[0]
objectdetail = zbdict['Object']
objname = objectdetail['Name']
objdetail = objectdetail['ObjectDetail']
if 'ObjChildren' in objdetail:objchildren = objdetail['ObjChildren']
else:objchildren = objitems
# 创建KML对象
kml = Kml()
for obj in objchildren:print(obj['Object']['Name'])print(obj['Object']['ObjectDetail']['Lng'],obj['Object']['ObjectDetail']['Lat'])# 创建一个地点标记place = kml.newpoint(name=obj['Object']['Name'], coords=[(obj['Object']['ObjectDetail']['Lng'],obj['Object']['ObjectDetail']['Lat'],0)])place.description = objname  # 可以添加描述# 将标记添加到KML文件中# 将KML文件保存到文件
kml.save(fr"C:\Users\YBK\Documents\{objname}.kml")

生成的kml:

<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://www.opengis.net/kml/2.2" xmlns:gx="http://www.google.com/kml/ext/2.2"><Document id="9"><Placemark id="11"><name>测试的点</name><description>测试的点</description><Point id="10"><coordinates>116.09105,23.18255,0</coordinates></Point></Placemark></Document>
</kml>