由于需要计算2000-3000行物流报价，总体报价数量超过7500条，人工太慢就想起用AI来进行。
选择了前段捕获的老兵版的chatgpt，它让我通过编写脚本使用python来计算我所需要的公里数：
我是一家上海的集装箱物流公司的负责人，这个表格是一个集装箱卡车报价表格，我想让你帮我整理一下这个表格内F列（门点，具体地址按当地的人民政府位置，一般为禁区，这里的禁区只做门点距离参考，不是车辆实际抵达）到上海市港建路1号的集装箱卡车五轴货运车辆行驶距离，然后把这个数据M列（外四公里数）里面
相关代码如下：
import requests
import time
from openpyxl import load_workbook
AMAP_KEY = “307eab613981673a2ccd4964e4cebe99”
INPUT_EXCEL = “亚马逊报价2026.xlsx”
OUTPUT_EXCEL = “亚马逊报价2026_小车公里数.xlsx”
ORIGIN_ADDR = “上海市港建路1号”
ADDRESS_COL = 6
RESULT_COL = 13
START_ROW = 3
END_ROW = 2068
def normalize(addr):
return “”.join(
[p.strip() for p in str(addr).replace(“\\”, “/”).split(“/”) if p.strip()]
)
def geocode(addr):
url = “https://restapi.amap.com/v3/geocode/geo”
params = {“key”: AMAP_KEY, “address”: addr}
try:
r = requests.get(url, params=params, timeout=10)
d = r.json()
except:
return None
if d.get(“status”) != “1”:
return None
loc = d[“geocodes”][0][“location”].split(“,”)
return float(loc[0]), float(loc[1])
def driving(origin, dest):
url = “https://restapi.amap.com/v5/direction/driving”
params = {
“key”: AMAP_KEY,
“origin”: f”{origin[0]},{origin[1]}”,
“destination”: f”{dest[0]},{dest[1]}”,
“strategy”: 10,
“extensions”: “base”,
}
try:
r = requests.get(url, params=params, timeout=10)
d = r.json()
except:
return None
if d.get(“status”) != “1”:
return None
paths = d.get(“route”, {}).get(“paths”, [])
if not paths:
return None
km = float(paths[0][“distance”]) / 1000.0
return km
def main():
print(“获取起点坐标:”, ORIGIN_ADDR)
origin = geocode(ORIGIN_ADDR)
if not origin:
print(“起点失败，退出”)
return
print(“打开 Excel:”, INPUT_EXCEL)
wb = load_workbook(INPUT_EXCEL)
ws = wb.active
if not ws.cell(2, RESULT_COL).value:
ws.cell(2, RESULT_COL).value = “外四公里数(小车)”
cache_geo = {}
cache_dist = {}
for row in range(START_ROW, END_ROW + 1):
val = ws.cell(row, ADDRESS_COL).value
if not val:
continue
addr = str(val).strip()
if not addr:
continue
if ws.cell(row, RESULT_COL).value not in (None, “”):
continue
print(f”\n第 {row} 行:”, addr)
if addr in cache_dist:
km = cache_dist[addr]
else:
if addr in cache_geo:
dest = cache_geo[addr]
else:
dest = geocode(normalize(addr))
if not dest:
print(“地理编码失败，跳过”)
continue
cache_geo[addr] = dest
time.sleep(0.2)
km = driving(origin, dest)
if km is None:
print(“路线失败，跳过”)
continue
cache_dist[addr] = km
time.sleep(0.2)
r = round(km)
ws.cell(row, RESULT_COL).value = r
print(“写入:”, r, “km”)
wb.save(OUTPUT_EXCEL)
print(“\n完成，已保存:”, OUTPUT_EXCEL)
if __name__ == “__main__”:
main()
