调用高德逆地理编码解析数据存入EXCEL

package weaver.bjwj.wx.action;

import cn.hutool.http.HttpUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.api.system.createDB.util.excelutil.ExcelUtils;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.commons.lang.StringUtils;

import java.io.;
import java.net.HttpURLConnection;
import java.net.MalformedURLException;
import java.net.SocketTimeoutException;
import java.net.URL;
import java.util.;

import static com.baidu.ueditor.define.FileType.getSuffix;

/**

• POI解析Excel
  */
  public class test {

  private static XSSFWorkbook workbook;
  private static XSSFSheet sheet;
  private static XSSFRow row;
  private static XSSFCell cell;
  private static File file;

  public static void main(String[] args) throws Exception {
  List<Map<String, Object>> maps = inputExcelMap();
  for (int i = 0; i < maps.size(); i++) {
  //读取经纬度，站点写入 新表
  //获得省市区，地址，写入 新表
  String name = maps.get(i).get(“name”).toString();
  System.out.println(“name：” + name);
  System.out.println(“有没有:”+maps.get(i).get(“Y”));

       if (maps.get(i).get("Y")!= null) {
           String x = maps.get(i).get("Y").toString();
           String y = maps.get(i).get("X").toString();
           System.out.println("x：" + x);
           System.out.println("y：" + y);
           if (!x.equals("")) {
               String address = x + "," + y;
               HashMap<String, Object> map1 = new HashMap<>();
  
              //kay是高德申请的
               map1.put("key", "xxxxxxxx");
               map1.put("location", address);
               JSONObject jsonObject = httpJSONGD(map1);
               int z = 0;
               try {
                   z = readColumn();
               } catch (Exception e) {
                   e.printStackTrace();
               }
               System.out.println("获取到行数:" + z);
               //表头测试数据
               List<String> headList = new ArrayList<>();
               headList.add("原地址");
               headList.add("原始数据X");
               headList.add("原始数据Y");
               headList.add("高德精度X");
               headList.add("高德维度Y");
               headList.add("定位");
               headList.add("省");
               headList.add("市");
               headList.add("区");
               headList.add("街道");
               headList.add("地址");
               headList.add("门牌地址到请求坐标的距离");
      /        List<List<String>> contentList = Processing_data(jsonObject, x, y, name);//内容测试数据
           //    List<List<String>> contentList = Processing_data_add(name);
               //    setSheet("WorkSheet");                        //创建sheet页i
               try {
                   createHead(headList);                         //设置表头
               } catch (Exception e) {
                   e.printStackTrace();
               }
               createContent(contentList, z);                 //设置内容
               writeToFile("C:/Users/wanji/Desktop/work2.xls");         //写入文件*/
           }
       }
   }
  

  }

  //创建sheet页
  public static void setSheet(String sheetName) {
  sheet = workbook.createSheet(sheetName);
  }

  //创建表头
  public static void createHead(List headList) throws IOException {
  //创建表头，也就是第一行
  FileInputStream fis = new FileInputStream(“C:/Users/wanji/Desktop/work2.xls”);
  workbook = new XSSFWorkbook(fis);
  sheet = workbook.getSheetAt(0);
  row = sheet.createRow(0);
  for (int i = 0; i < headList.size(); i++) {
  cell = row.createCell(i);
  cell.setCellValue(headList.get(i));
  }

  }

  //创建表内容
  public static void createContent(List<List> contentList, int indexNum) {
  //创建表内容，从第二行开始
  for (int i = 0; i < contentList.size(); i++) {
  row = sheet.createRow(i + indexNum + 1);
  for (int j = 0; j < contentList.get(i).size(); j++) {
  row.createCell(j).setCellValue(contentList.get(i).get(j));
  }
  }
  }

  //写入文件
  public static void writeToFile(String filePath) {
  file = new File(filePath);
  //将文件保存到指定的位置
  try {
  FileOutputStream out = new FileOutputStream(file);
  workbook.write(out);
  System.out.println(“写入成功”);
  workbook.close();
  } catch (IOException e) {
  e.printStackTrace();
  }
  }

  // 内容测试数据 要追加的内容
  protected static List<List> getContent() {
  List<List> contentList = new ArrayList<>();
  List content1 = new ArrayList<>();
  content1.add(“张三”);
  content1.add(“18”);
  List content2 = new ArrayList<>();
  content2.add(“李四”);
  content2.add(“20”);
  contentList.add(content1);
  contentList.add(content2);
  return contentList;
  }

  public static int readColumn() throws Exception {
  int lastRowNum = 0;
  try {
  FileInputStream fis = new FileInputStream(“C:/Users/wanji/Desktop/work2.xls”);
  XSSFWorkbook workbook = new XSSFWorkbook(fis);
  XSSFSheet sheet = workbook.getSheetAt(0);
  int firstRowNum = sheet.getFirstRowNum();
  lastRowNum = sheet.getLastRowNum();
  Row firstRow = sheet.getRow(firstRowNum);
  /int firstCellNum = firstRow.getFirstCellNum();
  int lastCellNum = firstRow.getLastCellNum();/
  System.out.println(“第一行行号：” + firstRowNum);
  System.out.println(“最后一行行号：” + lastRowNum);
  /* System.out.println(“第一列列号：” + firstCellNum);
  System.out.println(“最后一列列号：” + lastCellNum);*/
  } catch (FileNotFoundException e) {
  // TODO Auto-generated catch block
  e.printStackTrace();
  }
  return lastRowNum;
  }

  public static String Http_Get(String GorP, String strurl, String body) {
  HttpURLConnection connection = null;
  InputStream is = null;
  BufferedReader br = null;
  String result = null;// 返回结果字符串
  Date d1 = new Date();
  try {
  // 创建远程url连接对象
  URL url = new URL(strurl);
  // 通过远程url连接对象打开一个连接，强转成httpURLConnection类
  connection = (HttpURLConnection) url.openConnection();
  //connection.setSSLSocketFactory(new TLSSocketConnectionFactory());
  connection.setDoInput(true);
  connection.setDoOutput(true);
  // 设置连接方式：GET,POST
  connection.setRequestMethod(GorP);
  // 设置连接主机服务器的超时时间：15000毫秒
  connection.setConnectTimeout(15000);
  // 设置读取远程返回的数据时间：60000毫秒
  connection.setReadTimeout(60000);
  // 发送请求
  connection.connect();
  OutputStream out = connection.getOutputStream(); // 获取输出流对象
  connection.getOutputStream().write(body.getBytes(“UTF-8”)); // 将要提交服务器的SOAP请求字符流写入输出流
  out.flush();
  out.close();
  //System.out.println(connection.getResponseCode());
  // 通过connection连接，获取输入流
  if (connection.getResponseCode() == 200) {
  is = connection.getInputStream();
  // 封装输入流is，并指定字符集
  br = new BufferedReader(new InputStreamReader(is, “UTF-8”));
  // 存放数据
  StringBuffer sbf = new StringBuffer();
  String temp = null;
  while ((temp = br.readLine()) != null) {
  sbf.append(temp);
  sbf.append(“”);
  }
  result = sbf.toString();
  }
  } catch (SocketTimeoutException e) {
  System.out.println(“****** SocketTimeoutException ********”);
  e.printStackTrace();
  } catch (MalformedURLException e) {
  e.printStackTrace();
  } catch (IOException e) {
  e.printStackTrace();
  } finally {
  // 关闭资源
  if (null != br) {
  try {
  br.close();
  } catch (IOException e) {
  e.printStackTrace();
  }
  }
  if (null != is) {
  try {
  is.close();
  } catch (IOException e) {
  e.printStackTrace();
  }
  }
  connection.disconnect();// 关闭远程连接
  }
  Date d2 = new Date();
  return result;
  }

  public static List<Map<String, Object>> inputExcelMap() {
  ExcelReader reader = ExcelUtil.getReader(“C:/Users/wanji/Desktop/bbb.xlsx”);
  List<Map<String, Object>> readAll = reader.readAll();
  reader.close();
  return readAll;
  }

  public static JSONObject httpJSONGD(HashMap<String, Object> map) {
  String result3 = HttpUtil.get(“https://restapi.amap.com/v3/geocode/regeo”, map);
  JSONObject jsonObject = JSONObject.parseObject(result3);
  System.out.println(“返回的JSON：” + jsonObject);
  return jsonObject;
  }

  public static List<List> Processing_data(JSONObject jsonObject, String x, String y, String name) {
  System.out.println(“执行的结果：” + jsonObject);
  //高德回传所有数据
  JSONObject regeocode = jsonObject.getJSONObject(“regeocode”);
  String formatted_address = regeocode.getString(“formatted_address”);
  List<List> contentList = new ArrayList<>();
  List content = new ArrayList<>();
  content.add(name);
  content.add(x);
  content.add(y);
  //高德大部分数据
  JSONObject addressComponent = regeocode.getJSONObject(“addressComponent”);
  //经纬度
  JSONObject streetNumber = addressComponent.getJSONObject(“streetNumber”);
  if (streetNumber.containsKey(“location”)) {
  String locationXY = streetNumber.getString(“location”);
  System.out.println(“locationXY:” + locationXY);
  String x1 = locationXY.substring(0, locationXY.indexOf(“,”));
  String y1 = locationXY.substring(x1.length() + 1, locationXY.length());
  System.out.println(“X1” + x1);
  System.out.println(“Y1” + y1);
  content.add(x1);
  content.add(y1);
  //定位
  String dw=x1+“|”;
  dw=dw+y1+“|”;
  if (!formatted_address.equals(“”)) {
  dw=dw+formatted_address;
  }
  content.add(dw);
  } else {
  content.add(x);
  content.add(y);
  String dw=x+“|”;
  dw=dw+y+“|”;
  if (!formatted_address.equals(“”)) {
  dw=dw+formatted_address;
  }
  content.add(dw);
  }

   //省
   String province = addressComponent.getString("province");
   content.add(province);
   //市  如果没有城市就按省结算
   String city = addressComponent.getString("city");
   if (!city.equals("[]")) {
       content.add(city);
   } else {
       content.add(province);
   }
   //区
   String district = addressComponent.getString("district");
   content.add(district);
   //街道
   String street = streetNumber.getString("street");
   content.add(street);
   //地址
   if (!formatted_address.equals("")) {
       content.add(formatted_address);
   } else {
       content.add("无");
   }
  
   //门牌地址到请求坐标的距离
   String distance = streetNumber.getString("distance");
   content.add(distance);
   contentList.add(content);
   return contentList;
  

  }

  public static List<List> Processing_data_add(String name) {
  //高德回传所有数据
  List<List> contentList = new ArrayList<>();
  List content = new ArrayList<>();
  name=name.replaceAll(“~”,“-”);
  content.add(name);

   contentList.add(content);
   return contentList;
  

  }
  }