高德地图工具类
package com.dpx.base.modular.ex;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Component;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.math.BigDecimal;
import java.net.MalformedURLException;
import java.net.URL;
import java.net.URLConnection;
/**
* @Description: 高德地图工具类
*/
@Component
@Slf4j
public class GaoDeMapUtil {
/**
* 功能描述: 高德地图Key
*
* @param null
* @return
* @author 周兆宇
* @date 2022-01-26 09:13:40
*/
private static final String GAO_DE_KEY = "45dc04XXXa056a0a34b";
//申请的账户Key
/**
* 功能描述: 根据地址名称得到两个地址间的距离
*
* @param start 起始位置
* @param end 结束位置
* @return long 两个地址间的距离
* @author isymikasan
* @date 2022-01-26 09:16:04
*/
public static Long getDistanceByAddress(String start, String end) {
String startLonLat = getLonLat(start);
String endLonLat = getLonLat(end);
Long distance = Long.valueOf(getDistance(startLonLat, endLonLat));
return distance;
}
/**
* 功能描述: 地址转换为经纬度
*
* @param address 地址
* @return java.lang.String 经纬度
* @author isymikasan
* @date 2022-01-26 09:17:13
*/
public static String getLonLat(String address) {
try {
// 返回输入地址address的经纬度信息, 格式是 经度,纬度
String queryUrl = "http://restapi.amap.com/v3/geocode/geo?key=" + GAO_DE_KEY + "&address=" + address;
// 高德接口返回的是JSON格式的字符串
String queryResult = getResponse(queryUrl);
JSONObject job = JSONObject.parseObject(queryResult);
JSONObject jobJSON = JSONObject
.parseObject(
job.get("geocodes").toString().substring(1, job.get("geocodes").toString().length() - 1));
String LngAndLat = jobJSON.get("location").toString();
return LngAndLat;
} catch (Exception e) {
return e.toString();
}
}
/**
* 将经纬度 转换为 地址
*
* @param longitude 经度
* @param latitude 纬度
* @return 地址名称
* @throws Exception
*/
public String getAddress(String longitude, String latitude) throws Exception {
String url;
try {
url = "http://restapi.amap.com/v3/geocode/regeo?output=JSON&location=" + longitude + "," + latitude
+ "&key=" + GAO_DE_KEY + "&radius=0&extensions=base";
log.info("经度" + longitude);
log.info("纬度：" + latitude);
log.info("url:" + url);
// 高德接口返回的是JSON格式的字符串
String queryResult = getResponse(url);
if (queryResult==null||"".equals(queryResult)||"null".equals(queryResult)) {
return "查询结果为空";
}
// 将获取结果转为json 数据
JSONObject obj = JSONObject.parseObject(queryResult);
if (obj.get(GaoDeEnum.STATUS.getCode()).toString().equals(GaoDeEnum.INT_ONE.getCode())) {
// 如果没有返回-1
JSONObject reGeoCode = obj.getJSONObject(GaoDeEnum.RE_GEO_CODE.getCode());
if (reGeoCode.size() > 0) {
// 在regeocode中拿到 formatted_address 具体位置
String formatted = reGeoCode.get("formatted_address").toString();
return formatted;
} else {
return "未找到相匹配的地址！";
}
} else {
return "请求错误！";
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
return "系统未知异常，请稍后再试";
}
}
/**
* 功能描述: 根据两个定位点的经纬度算出两点间的距离
*
* @param startLonLat 起始经纬度
* @param endLonLat 结束经纬度（目标经纬度）
* @return java.lang.Long 两个定位点之间的距离
* @author isymikasan
* @date 2022-01-26 09:47:42
*/
public static Long getDistance(String startLonLat, String endLonLat) {
try {
// 返回起始地startAddr与目的地endAddr之间的距离，单位：米
Long result = new Long(0);
String queryUrl =
"http://restapi.amap.com/v3/distance?key=" + GAO_DE_KEY + "&origins=" + startLonLat
+ "&destination="
+ endLonLat;
String queryResult = getResponse(queryUrl);
JSONObject job = JSONObject.parseObject(queryResult);
JSONArray ja = job.getJSONArray("results");
JSONObject jobO = JSONObject.parseObject(ja.getString(0));
result = Long.parseLong(jobO.get("distance").toString());
//log.info("距离：" + result);
return result;
} catch (Exception e) {
return null;
}
}
/**
* 功能描述: 发送请求
*
* @param serverUrl 请求地址
* @return java.lang.String
* @author isymikasan
* @date 2022-01-26 09:15:01
*/
private static String getResponse(String serverUrl) {
// 用JAVA发起http请求，并返回json格式的结果
StringBuffer result = new StringBuffer();
try {
URL url = new URL(serverUrl);
URLConnection conn = url.openConnection();
BufferedReader in = new BufferedReader(new InputStreamReader(conn.getInputStream()));
String line;
while ((line = in.readLine()) != null) {
result.append(line);
}
in.close();
} catch (MalformedURLException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return result.toString();
}
/**
* java 通过经纬度算两点直线距离
*/
private static final double EARTH_RADIUS_KM = 6371.393;
/**
* lat1 纬度1
* lon1 经度1
* lat2 纬度2
* lon2 经度2
*/
public static double calculateDistance(double lat1, double lon1, double lat2, double lon2) {
// 将经纬度转换为弧度
double lat1Rad = Math.toRadians(lat1);
double lon1Rad = Math.toRadians(lon1);
double lat2Rad = Math.toRadians(lat2);
double lon2Rad = Math.toRadians(lon2);
// 计算经纬度的差值
double deltaLat = lat2Rad - lat1Rad;
double deltaLon = lon2Rad - lon1Rad;
// 应用Haversine公式
double a = Math.pow(Math.sin(deltaLat / 2), 2) +
Math.cos(lat1Rad) * Math.cos(lat2Rad) *
Math.pow(Math.sin(deltaLon / 2), 2);
double c = 2 * Math.atan2(Math.sqrt(a), Math.sqrt(1 - a));
double distance = EARTH_RADIUS_KM * c;
BigDecimal two11 = new BigDecimal(distance);
double three11 = two11.setScale(3,BigDecimal.ROUND_HALF_UP).doubleValue();
return three11;
}
}
业务代码
public void queryBy() {
System.err.println("------进入解析-----");
//循环读取Excel
Jedis jedis = new Jedis("localhost",6379);
int num = 0;
int numjc = 0;
int geshu = 0;
try {
Ssgszgs ssgszgs = new Ssgszgs();
List<Map<String, Object>> sheet1 = ssgszgsDao.queryAllByLimitMap(ssgszgs);
System.err.println(sheet1.size());
for (Map<String, Object> map : sheet1) {
geshu++;
System.err.println("-"+geshu+"-");
if (map == null || map.isEmpty()) {
continue;
}
Double zxjl = Double.valueOf(0);
Long distance =0L;
//1，读取Excel一行
String qi = (String) map.get("ssgscs");
String zhong = (String) map.get("zgsdz");
System.err.println(qi+" -- "+zhong);
if("0".equals(zhong)){
//是的话直接填写到当前格
System.err.println("终点为0");
//写入------
map.put("kjjl",0.0);
map.put("ljjl",0.0);
continue;
}
//判断是否市辖区
//判断是否是统一地址
if(qi.equals(zhong)||"市辖区".equals(zhong)){
//是的话直接填写到当前格
System.err.println("同一城市");
//写入------
map.put("kjjl",0.0);
map.put("ljjl",0.0);
continue;
}
//判断起点终点是否查询过 redis
String qzQuery= jedis.get(qi+zhong+"shuang");
if(qzQuery!=null){
//System.err.println("redis-------"+qzQuery);
String[] split = qzQuery.split(",");
String s0 = split[0];
String s1 = split[1];
System.err.println("判断相同Redis匹配----直线距离："+s0+"----驾车距离"+s1);
//写入------
map.put("kjjl",s0);
map.put("ljjl",s1);
continue;
}
String qilonLat = "0";
String zhonglonLat ="0";
//判断redis中是否存储过
String qit = jedis.get(qi+"dan");
String zhongt = jedis.get(zhong+"dan");
if(qit!=null){
qilonLat = qit;
System.err.println("起点--Redis匹配--"+qit);
}
if(zhongt!=null){
zhonglonLat = zhongt;
System.err.println("终点--Redis匹配--"+zhongt);
}
if("0".equals(qilonLat)){
num++;
qilonLat = GaoDeMapUtil.getLonLat(qi);
if(qilonLat=="java.lang.NullPointerException"||"java.lang.NullPointerException".equals(qilonLat)){
System.err.println("无法识别起点");
map.put("kjjl",0.0);
map.put("ljjl",0.0);
continue;
}
}
if("0".equals(zhonglonLat)){
num++;
zhonglonLat = GaoDeMapUtil.getLonLat(zhong);
if(zhonglonLat=="java.lang.NullPointerException"||"java.lang.NullPointerException".equals(zhonglonLat)){
System.err.println("无法识别终点");
map.put("kjjl",0.0);
map.put("ljjl",0.0);
continue;
}
}
//地点放入redis
String qkey = qi+"dan";
String qvalue = qilonLat;
byte[] qbkey = qkey.getBytes("UTF-8");
byte[] qbvalue = qvalue.getBytes("UTF-8");
jedis.set(qbkey, qbvalue);
String zkey = zhong+"dan";
String zvalue = zhonglonLat;
byte[] zbkey = zkey.getBytes("UTF-8");
byte[] zbvalue = zvalue.getBytes("UTF-8");
jedis.set(zbkey, zbvalue);
//3，获取两点间直线距离 ？
String[] split = qilonLat.split(",");
double j = Double.parseDouble(split[0]);
double v = Double.parseDouble(split[1]);
String[] split2 = zhonglonLat.split(",");
double j2 = Double.parseDouble(split2[0]);
double v2= Double.parseDouble(split2[1]);
zxjl = GaoDeMapUtil.calculateDistance(j, v, j2, v2);
//System.err.println("直线距离： "+zxjl);
map.put("kjjl",zxjl);
try{
numjc++;
//4，获取两点间驾车距离 高德API
distance = GaoDeMapUtil.getDistance(qilonLat, zhonglonLat);
}catch (Exception e){
System.err.println(e);
continue;
}
double l = 0.0;
if(distance!=null&&distance>1){
double distance1 = distance;
l = distance1 / 1000;
}
map.put("ljjl",l);
//6.将两点名称：直线距离/驾车距离放到 redis 中，下次循环
String qzkey = qi+zhong+"shuang";
String qzvalue = zxjl+","+l+"";
byte[] qzbkey = qzkey.getBytes("UTF-8");
byte[] qzbvalue = qzvalue.getBytes("UTF-8");
jedis.set(qzbkey, qzbvalue);
}
ExcelUtilText.createExcel(sheet1,"D:\\wjcl\\270000-314536.xlsx");
System.err.println("查询高德地址： "+ num);
System.err.println("查询高德驾驶距离： "+ numjc);
} catch (Exception e) {
throw new RuntimeException(e);
}
}
高德相关字段枚举
package com.dpx.base.modular.ex;
import lombok.AllArgsConstructor;
import lombok.Getter;
@AllArgsConstructor
@Getter
public enum GaoDeEnum {
// 高德地图固定字段
STATUS("status"),
INT_ONE("1"),
RE_GEO_CODE("regeocode"),
GEO_CODES("geocodes"),
LOCATION("location"),
FORMATTED_ADDRESS("formatted_address"),
RESULTS("results"),
DISTANCE("distance");
private String code;
}
package com.dpx.base.modular.ex;
public enum CodeEnum {
SUCCESS(0),
ERROR(1);
private Integer code;
private CodeEnum(Integer code) {
this.code = code;
}
public Integer getCode() {
return this.code;
}
}
excel导入导出
package com.dpx.base.util;
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.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.*;
import static org.apache.poi.ss.usermodel.CellType.STRING;
/**
* excel工具类
*/
public class ExcelUtilText {
/**
* 将Excel内容转换list
*
* @param file
* @param name
* @return
* @throws Exception
*/
public static List<Map<String, Object>> excelToList(MultipartFile file, String name) throws Exception {
Workbook workbook = WorkbookFactory.create(file.getInputStream());
Sheet sheet = workbook.getSheet(name);
//行数
int num = sheet.getLastRowNum();
//列数
int col = sheet.getRow(0).getLastCellNum();
List<Map<String, Object>> list = new ArrayList<>();
String[] colName = new String[col];
//获取列名
Row row = sheet.getRow(0);
for (int i = 0; i < col; i++) {
String[] s = row.getCell(i).getStringCellValue().split("-");
colName[i] = s[0];
}
//将一行中每列数据放入一个map中,然后把map放入list
for (int i = 1; i <= num; i++) {
Map<String, Object> map = new HashMap<>();
Row row1 = sheet.getRow(i);
if (row1 != null) {
for (int j = 0; j < col; j++) {
Cell cell = row1.getCell(j);
if (cell != null) {
cell.setCellType(STRING);
map.put(colName[j], cell.getStringCellValue());
}
}
}
list.add(map);
}
return list;
}
//获取首行标题
public static String[] getLieMiang(MultipartFile file, String name) throws Exception {
Workbook workbook = WorkbookFactory.create(file.getInputStream());
Sheet sheet = workbook.getSheet(name);
//行数
int num = sheet.getLastRowNum();
//列数
int col = sheet.getRow(0).getLastCellNum();
List<Map<String, Object>> list = new ArrayList<>();
String[] colName = new String[col];
//获取列名
Row row = sheet.getRow(0);
for (int i = 0; i < col; i++) {
String[] s = row.getCell(i).getStringCellValue().split("-");
colName[i] = s[0];
}
return colName;
}
/***
* 解析Excel日期格式
* @param strDate
* @return
*/
public static Date ExcelDoubleToDate(String strDate) {
Date tDate = new Date();
if (strDate.length() == 5) {
try {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
tDate = DoubleToDate(Double.parseDouble(strDate));
} catch (Exception e) {
e.printStackTrace();
}
}
return tDate;
}
/**
* 解析Excel日期格式
*
* @param dVal
* @return
*/
public static Date DoubleToDate(Double dVal) {
Date tDate = new Date();
//系统时区偏移 1900/1/1 到 1970/1/1 的 25569 天
long localOffset = tDate.getTimezoneOffset() * 60000;
tDate.setTime((long) ((dVal - 25569) * 24 * 3600 * 1000 + localOffset));
return tDate;
}
/**
* 导出集合到excel
*
* @param response
* @param name
* @param list
*/
public static void exportToExcel(HttpServletResponse response, String name, List<LinkedHashMap<String, Object>> list) {
try {
//文件名称
String fileName = name + ".xls";
HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
HSSFSheet hssfSheet = hssfWorkbook.createSheet(name);
int rowNum = 0;
//新建行
HSSFRow hssfRow = hssfSheet.createRow(rowNum++);
//列
int j = 0;
if (list.size() > 0) {
for (String i : list.get(0).keySet()) {
//新建第一行
hssfRow.createCell(j++).setCellValue(i);
}
//将数据放入表中
for (int i = 0; i < list.size(); i++) {
//新建一行
HSSFRow row = hssfSheet.createRow(rowNum++);
Map map = list.get(i);
System.out.println(map);
j = 0;
for (Object obj : map.values()) {
if (obj != null) {
row.createCell(j++).setCellValue(obj.toString());
} else {
row.createCell(j++);
}
}
}
}
// 告诉浏览器用什么软件可以打开此文件
response.setHeader("content-Type", "application/vnd.ms-excel");
// 下载文件的默认名称
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8"));
hssfWorkbook.write(response.getOutputStream());
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* src:定义下载的文件路径
*
* @param mapArrayList
* @param src
*/
public static void createExcel(List<Map<String, Object>> mapArrayList, String src) {
System.out.println("数据转成Excel...");
//获取数据源的 key, 用于获取列数及设置标题
Map<String, Object> map = mapArrayList.get(0);
Set<String> stringSet = map.keySet();
ArrayList<String> headList = new ArrayList<>(stringSet);
// 定义一个新的工作簿
XSSFWorkbook wb = new XSSFWorkbook();
// 创建一个Sheet页
XSSFSheet sheet = wb.createSheet("First sheet");
//设置行高
sheet.setDefaultRowHeight((short) (2 * 256));
//设置列宽
sheet.setColumnWidth(0, 4000);
sheet.setColumnWidth(1, 4000);
sheet.setColumnWidth(2, 4000);
XSSFFont font = wb.createFont();
font.setFontName("宋体");
font.setFontHeightInPoints((short) 16);
//获得表格第一行
XSSFRow row = sheet.createRow(0);
//chen 优化标题获取 根据数据源信息给第一行每一列设置标题
for (int i = 0; i < headList.size(); i++) {
XSSFCell cell = row.createCell(i);
cell.setCellValue(headList.get(i));
//System.err.println("=========="+headList.get(i));
}
XSSFRow rows;
XSSFCell cells;
//循环拿到的数据给所有行每一列设置对应的值
for (int i = 0; i < mapArrayList.size(); i++) {
// 在这个sheet页里创建一行
rows = sheet.createRow(i + 1);
//chen 优化值获取 给该行数据赋值
for (int j = 0; j < headList.size(); j++) {
String value;
if (mapArrayList.get(i).get(headList.get(j)) != null) {
value = mapArrayList.get(i).get(headList.get(j)).toString();
} else {
value = "";
}
cells = rows.createCell(j);
cells.setCellValue(value);
}
}
try {
File file = new File(src);
FileOutputStream fileOutputStream = new FileOutputStream(file);
wb.write(fileOutputStream);
wb.close();
fileOutputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
