调用高德API 实现自动计算两个地点之间驾车距离使用POI操作回写Excel

前言

项目上有个一次需要查询两个地方之间的驾车距离，项目经理一开始让我们每个人查询100个地点，手动查询然后手动写Excel，只有一百个，手动查询高德地图，估计就半个小时，所以我没想过使用代码来解决这个问题，因为调试参数需要时间，所以我不想加班，手动查询了一次，可是第二天，项目经理又分配了同样的任务，这次是1000个，这要是手动查询得花两三个小时，这时，我才开始写代码

步骤其实也很简单，先注册高德开发者平台

https://lbs.amap.com/api/javascript-api/guide/geometry/geometry

然后就可以根据实例开始写代码了

package com.caeser.midrug.util;

import com.fasterxml.jackson.databind.JsonNode;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.net.HttpURLConnection;
import java.net.URL;
import java.text.DecimalFormat;
import java.util.HashMap;
import java.util.Map;

/**
 * @author caeser
 * @date 2021-10-22 14:05:57
 * @desc 读取Excel数据
 */
public class ExcelData {
     public XSSFSheet sheet;
     public XSSFWorkbook sheets;
     public FileInputStream fileInputStream;
     public File excelFile ;


    /**
     * 构造函数，初始化excel数据
     * @param filePath  excel路径
     * @param sheetName sheet表名
     */
    ExcelData(String filePath,String sheetName){
         fileInputStream = null;
        try {
            fileInputStream = new FileInputStream(filePath);
            excelFile=new File(filePath);
            sheets = new XSSFWorkbook(fileInputStream);
            //获取sheet
            sheet = sheets.getSheet(sheetName);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 根据行和列的索引获取单元格的数据
     * @param row
     * @param column
     * @return
     */
    public String getExcelDateByIndex(int row,int column){
        XSSFRow row1 = sheet.getRow(row);
        String cell = row1.getCell(column).toString();
        return cell;
    }

    public  XSSFSheet getSheet(){
        return sheet;
    }
    public XSSFWorkbook getSheets(){
        return sheets;
    }
    public File getExcelFile(){
        return excelFile;
    }

    public static void setExcelDateByIndex(int row, int column, String paramValue, ExcelData target){
        XSSFRow row1 = target.getSheet().getRow(row);
        //创建单元格，括号中的数字表示该行的第几列，从0开始
        Cell cella = row1.createCell(column);
        //向单元格中写数据，可写入各种类型的数据（如Boolean,int,double,String等）
        cella.setCellValue(paramValue);
        return ;
    }

    /**
     * 根据某一列值为“******”的这一行，来获取该行第x列的值
     * @param caseName
     * @param currentColumn 当前单元格列的索引
     * @param targetColumn 目标单元格列的索引
     * @return
     */
    public String getCellByCaseName(String caseName,int currentColumn,int targetColumn){
        String operateSteps="";
        //获取行数
        int rows = sheet.getPhysicalNumberOfRows();
        for(int i=0;i<rows;i++){
            XSSFRow row = sheet.getRow(i);
            String cell = row.getCell(currentColumn).toString();
            if(cell.equals(caseName)){
                operateSteps = row.getCell(targetColumn).toString();
                break;
            }
        }
        return operateSteps;
    }

    //打印excel数据
    public void readExcelData(){
        //获取行数
        int rows = sheet.getPhysicalNumberOfRows();
        for(int i=0;i<rows;i++){
            //获取列数
            XSSFRow row = sheet.getRow(i);
            int columns = row.getPhysicalNumberOfCells();
            for(int j=0;j<columns;j++){
                String cell = row.getCell(j).toString();
                System.out.println(cell);
            }
        }
    }

    // HTTP GET请求
    static Map<String,Object> sendGet(String paUrl, String param) throws Exception {

        Map<String,Object> resultMap=new HashMap<>();
        String USER_AGENT = "Mozilla/5.0";
        String url = paUrl+"?"+param;

        URL obj = new URL(url);
        HttpURLConnection con = (HttpURLConnection) obj.openConnection();

        //默认值我GET
        con.setRequestMethod("GET");

        //添加请求头
        con.setRequestProperty("User-Agent", USER_AGENT);

        int responseCode = con.getResponseCode();
        System.out.println("\nSending 'GET' request to URL : " + url);
        System.out.println("Response Code : " + responseCode);
        resultMap.put("responseCode",responseCode);
        BufferedReader in = new BufferedReader(
                new InputStreamReader(con.getInputStream()));
        String inputLine;
        StringBuffer response = new StringBuffer();

        while ((inputLine = in.readLine()) != null) {
            response.append(inputLine);
        }
        in.close();

        //打印结果
        System.out.println(response.toString());
        resultMap.put("responseInfo",response.toString());
        return  resultMap;
    }

    //测试方法
    public static void main(String[] args) throws Exception {
        ExcelData sheet1 = new ExcelData("D:\\反馈医院间距离_all.xlsx", "Sheet1");


        String getLocationAPI_URL="https://restapi.amap.com/v3/geocode/geo";
        String getDrivingAPI_URL="https://restapi.amap.com/v3/direction/driving";
        String amapKey="sfsufosufosuofusodufosudf";
        String addressName="北京西鹤年堂中医医院";
        String cityName="北京";
        String concatParam="key="+amapKey+"&address="+addressName+"&city="+cityName;
        Map<String,Object> reusltMsg= sendGet(getLocationAPI_URL,concatParam);
        String codeStr=reusltMsg.get("responseCode").toString();
        JsonNode locationObj=null;
        if("200".equals(codeStr)){
            // 如果是200
            String rspInfo=reusltMsg.get("responseInfo").toString();
            JsonNode jsonNode= JsonUtil.string2Json(rspInfo);
            JsonNode queryObj=jsonNode.get("geocodes");
            locationObj=queryObj.get(0).get("location");
            String queryStr = locationObj.toString().replaceAll("\"","");
            System.out.println(queryStr);
        }
        String aLocation="";
        String bLocation="";
        for(int a=1799;a<1890;a++){
            String cellN = sheet1.getExcelDateByIndex(a, 0);
            // 根据第一个名称获取经纬坐标
            concatParam="key="+amapKey+"&address="+cellN+"&city="+cityName;
            reusltMsg= sendGet(getLocationAPI_URL,concatParam);
            codeStr=reusltMsg.get("responseCode").toString();
            if("200".equals(codeStr)){
                // 如果是200
                String rspInfo=reusltMsg.get("responseInfo").toString();
                JsonNode jsonNode= JsonUtil.string2Json(rspInfo);
                JsonNode queryObj=jsonNode.get("geocodes");
                if(queryObj.get(0)==null){
                    //
                }else{
                    locationObj=queryObj.get(0).get("location");
                    String queryStr = locationObj.toString().replaceAll("\"","");
                    aLocation=queryStr;
                    System.out.println(queryStr);
                }
            }
            String cellM = sheet1.getExcelDateByIndex(a, 1);
            // 根据第二个名字获取经纬坐标
            concatParam="key="+amapKey+"&address="+cellM+"&city="+cityName;
            reusltMsg= sendGet(getLocationAPI_URL,concatParam);
            codeStr=reusltMsg.get("responseCode").toString();
            if("200".equals(codeStr)){
                // 如果是200
                String rspInfo=reusltMsg.get("responseInfo").toString();
                JsonNode jsonNode= JsonUtil.string2Json(rspInfo);
                JsonNode queryObj=jsonNode.get("geocodes");
                if(queryObj.get(0)==null){
                    //
                }else{
                    locationObj=queryObj.get(0).get("location");
                    String queryStr = locationObj.toString().replaceAll("\"","");
                    bLocation=queryStr;
                    System.out.println(queryStr);
                }

            }
            System.out.println(cellN+"，"+cellM+"\n");
            System.out.println(aLocation+"，"+bLocation+"\n");
            // 根据两个经纬坐标查询 驾车距离 a\b location有一个为空都不去查询
            if("".equals(aLocation)||"".equals(bLocation)){
                // 存在有一个为空
            }else{
                concatParam="key="+amapKey+"&origin="+aLocation+"&destination="+bLocation;
                reusltMsg= sendGet(getDrivingAPI_URL,concatParam);
                codeStr=reusltMsg.get("responseCode").toString();
                if("200".equals(codeStr)){
                    // 如果是200
                    String rspInfo=reusltMsg.get("responseInfo").toString();
                    JsonNode jsonNode= JsonUtil.string2Json(rspInfo);
                    JsonNode queryObj=jsonNode.get("route");
                    JsonNode pathsObj=queryObj.get("paths");
                    if(pathsObj.get(0)==null){
                        //
                    }else{
                        // 获取驾车距离 单位 米，转换为公里
                        String distanceStr=pathsObj.get(0).get("distance").toString().replaceAll("\"","");

                        double value = Double.valueOf(distanceStr)/1000;
                        // #.00 表示两位小数
                        DecimalFormat df = new DecimalFormat("#0.0"); // 保留1位小数，四舍五入
                        String valueDF=df.format(value);
                        // 写入Excel
                        setExcelDateByIndex(a,3,valueDF,sheet1);
                        System.out.println("方法二：" + valueDF);
                        System.out.println("getDIs");
                    }

                }
            }
            aLocation="";
            bLocation="";
        }
        //把Excel工作薄写入到Excel文件
        FileOutputStream os = new FileOutputStream(sheet1.getExcelFile());
        sheet1.getSheets().write(os);
        os.flush();
        os.close();
        System.out.println("success!");
        //获取第二行第4列
        //        String cell2 = sheet1.getExcelDateByIndex(1, 3);
        //根据第3列值为“customer23”的这一行，来获取该行第2列的值
        //        String cell3 = sheet1.getCellByCaseName("customer23", 2,1);
        //        System.out.println(cell2);
        //        System.out.println(cell3);



    }
}

结尾

其实如果数据量很小，花时间写代码实现自动化纯属娱乐学习了，我一开始就是想偷个懒，觉得手动查询也挺快的，后来发现我太天真了，自动化代码并不难，就看你想不想用了。

上面就是我全部的代码，包括了POI和调用高德API，其实就是发送请求，然后处理请求结果，最后操作字符串回写Excel，希望自动化代码可以用到更多地方。