package com.alibaba.yuntu.me.biz.district.service.impl.MyUtis;
import com.alibaba.fastjson.JSONObject;
import com.alibaba.yuntu.me.common.base.util.HttpUtil;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.concurrent.BasicThreadFactory;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.CollectionUtils;
import java.io.*;
import java.util.*;
import java.util.concurrent.ConcurrentHashMap;
import java.util.concurrent.CountDownLatch;
import java.util.concurrent.ScheduledExecutorService;
import java.util.concurrent.ScheduledThreadPoolExecutor;
@Slf4j
public class distance {
private static Integer PARAM_LOCATION1_COLUMN = 7; //location1参数列
private static Integer PARAM_LOCATION2_COLUMN = 8; //location2参数列
private static Integer START_COLUMN = 9; //回写开始列
private static Integer CURRENT_SHEET = 0;
private static Integer THREAD_NUM = 20; //线程池开启线程数量
private static String FILEPATH = "C:\\Users\\fengzi\\Desktop\\xxx\\xxxx\\用户坐标.xlsx";
public static ScheduledExecutorService THREAD_EXECUTOR = new ScheduledThreadPoolExecutor(THREAD_NUM, new BasicThreadFactory.Builder().namingPattern("batch-data-complate-pool-%d").daemon(true).build());
private static List<HashMap<String,String>> resultList = Collections.synchronizedList(new ArrayList<>());
private static String URL = "https://xxxx/xxxx/distance";
public static String KEY = "xxxxxxxx";
public static String MyDistance(String location1, String location2){
Map<String,String> params = new HashMap<>();
String distance = "";
params.put("key",KEY);
params.put("origins",location1);
params.put("destination",location2);
params.put("type","1");
String s = HttpUtil.sendGet(URL, params, null, "utf-8");
JSONObject jsonObject = JSONObject.parseObject(s);
if (jsonObject.containsKey("results") && jsonObject.containsKey("status") && jsonObject.containsKey("infocode")
&& jsonObject.getString("status").equals("1") && jsonObject.getJSONArray("results").size()>0){
distance = jsonObject.getJSONArray("results").getJSONObject(0).getString("distance");
}
return distance;
}
public static List<List<String>> analysisSheet(String filePath, int currentSheet) {
Workbook wb = null;
Sheet sheet = null;
Row row = null;
List<List<String>> list = null;
String cellData = null;
wb = readExcel(filePath);
if (wb != null) {
//用来存放表中数据
list = new ArrayList<List<String>>();
//获取第一个sheet
sheet = wb.getSheetAt(currentSheet);
//获取最大行数
int rownum = sheet.getPhysicalNumberOfRows();
//获取第一行
row = sheet.getRow(0);
//获取最大列数
int colnum = 0;
/* 获取最大列数 */
for(int i = 1; i < rownum; i++){
row = sheet.getRow(i);
if(row.getPhysicalNumberOfCells()>colnum){
colnum = row.getPhysicalNumberOfCells();
}
}
for (int i = 1; i < rownum; i++) {
Map<String, String> map = new LinkedHashMap<String, String>();
row = sheet.getRow(i);
if (row != null) {
List<String> rowData = new ArrayList<>();
for (int j = 0; j < colnum; j++) {
cellData = (String) getCellFormatValue(row.getCell(j));
rowData.add(cellData);
}
list.add(rowData);
} else {
break;
}
}
}
return list;
}
//读取excel文件
public static Workbook readExcel(String filePath) {
Workbook wb = null;
if (filePath == null) {
return null;
}
String extString = filePath.substring(filePath.lastIndexOf("."));
InputStream is = null;
try {
is = new FileInputStream(filePath);
if (".xls".equals(extString)) {
return wb = new HSSFWorkbook(is);
} else if (".xlsx".equals(extString)) {
return wb = new XSSFWorkbook(is);
} else {
return wb = null;
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return wb;
}
public static Object getCellFormatValue(Cell cell) {
Object cellValue = null;
if (cell != null) {
//判断cell类型
switch (cell.getCellType()) {
case Cell.CELL_TYPE_NUMERIC: {
cellValue = String.valueOf(cell.getNumericCellValue());
break;
}
case Cell.CELL_TYPE_FORMULA: {
//判断cell是否为日期格式
if (DateUtil.isCellDateFormatted(cell)) {
//转换为日期格式YYYY-mm-dd
cellValue = cell.getDateCellValue();
} else {
//数字
cellValue = String.valueOf(cell.getNumericCellValue());
}
break;
}
case Cell.CELL_TYPE_STRING: {
cellValue = cell.getRichStringCellValue().getString();
break;
}
default:
cellValue = "";
}
} else {
cellValue = "";
}
return cellValue;
}
public static void writeExcel(ConcurrentHashMap<Integer, String> toExcelMap, int currentSheet, String filePath) {
OutputStream out = null;
try {
// 读取Excel文档
Workbook workBook = readExcel(filePath);
// sheet 对应一个工作页
Sheet sheet = workBook.getSheetAt(currentSheet);
/**
* 往Excel中写新数据
*/
for (int i = 1; i <= sheet.getLastRowNum(); i++) {
Row row = sheet.getRow(i);
if(toExcelMap.containsKey(i)){
String s = toExcelMap.get(i);
row.createCell(START_COLUMN).setCellValue(s);
}
}
// 创建文件输出流，准备输出电子表格：这个必须有，否则你在sheet上做的任何操作都不会有效
out = new FileOutputStream(filePath);
workBook.write(out);
System.out.println("------- 数据导出成功(filePath"+filePath+") -------");
} catch (Exception e) {
e.printStackTrace();
System.out.println("------- 数据导出失败 -------");
} finally {
try {
if (out != null) {
out.flush();
out.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
@SneakyThrows
public static void main(String[] args) {
log.info("------- 当前处理文件：{} -------", FILEPATH);
long start = System.currentTimeMillis();
List<List<String>> lists = analysisSheet(FILEPATH, CURRENT_SHEET);
final ConcurrentHashMap<Integer, String> toExcelMap = new ConcurrentHashMap<>(lists.size());
int i = 1;
ConcurrentHashMap<String, Integer> countMap = new ConcurrentHashMap<>(lists.size());
for (List<String> lineList : lists) {
if (StringUtils.isBlank(lineList.get(PARAM_LOCATION1_COLUMN)) || StringUtils.isBlank(lineList.get(PARAM_LOCATION2_COLUMN))) {
i++;
continue;
}
if(lineList.size()<=9){
countMap.put(lineList.get(PARAM_LOCATION1_COLUMN) + "XXXXXXX"+ lineList.get(PARAM_LOCATION2_COLUMN) +"XXXXXXX" + i, i);
}else if(lineList.size()>9 && StringUtils.isBlank(lineList.get(9))){
countMap.put(lineList.get(PARAM_LOCATION1_COLUMN) + "XXXXXXX"+ lineList.get(PARAM_LOCATION2_COLUMN) +"XXXXXXX" + i, i);
}
i++;
}
final CountDownLatch countDownLatch = new CountDownLatch(countMap.entrySet().size());
for (final Map.Entry<String, Integer> entry : countMap.entrySet()) {
//开启多线程处理模式
THREAD_EXECUTOR.submit(new Runnable() {
@Override
public void run() {
log.info("****************** 当前处理文件：{}, 第 {} 条 {}**********************", FILEPATH, countDownLatch.getCount(), Thread.currentThread().getName());
String location1 = entry.getKey().split("XXXXXXX")[0];
String location2 = entry.getKey().split("XXXXXXX")[1];
System.out.println(location1);
System.out.println(location2);
String row = entry.getKey().split("XXXXXXX")[2];
String distance = MyDistance(location1, location2);
if(StringUtils.isNotBlank(distance)){
toExcelMap.put(Integer.valueOf(row),distance);
}
countDownLatch.countDown();
}
});
}
log.info("main thread wait.");
try {
countDownLatch.await();
} catch (InterruptedException e) {
e.printStackTrace();
}
log.info("main thread end...");
if (toExcelMap != null && !CollectionUtils.isEmpty(toExcelMap.keySet())) {
writeExcel(toExcelMap, CURRENT_SHEET, FILEPATH);
}
log.info("------- 文件处理完成：{}, cost time : {} ms ------- ", FILEPATH, System.currentTimeMillis() - start);
System.out.println(toExcelMap);
Thread.sleep(2000);
}
}
