package com.zy.asrs.utils;
|
|
import com.alibaba.fastjson.JSON;
|
import com.alibaba.fastjson.JSONObject;
|
import com.alibaba.fastjson.serializer.SerializerFeature;
|
import com.core.common.Cools;
|
import com.zy.asrs.domain.enums.MapExcelNodeType;
|
import com.zy.common.model.MapNode;
|
import com.zy.core.enums.MapNodeType;
|
import org.apache.poi.ss.usermodel.*;
|
import org.apache.poi.xssf.usermodel.XSSFColor;
|
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
|
import org.springframework.stereotype.Component;
|
|
import java.io.File;
|
import java.io.FileInputStream;
|
import java.io.IOException;
|
import java.util.ArrayList;
|
import java.util.HashMap;
|
import java.util.List;
|
import java.util.Map;
|
|
@Component
|
public class MapExcelUtils {
|
|
public HashMap<Integer, List<List<MapNode>>> processExcelData(HashMap<Integer, List<List<HashMap<String, Object>>>> dataMap) {
|
HashMap<Integer, List<List<MapNode>>> levMap = new HashMap<>();
|
for (Map.Entry<Integer, List<List<HashMap<String, Object>>>> entry : dataMap.entrySet()) {
|
Integer lev = entry.getKey();
|
List<List<HashMap<String, Object>>> excelData = entry.getValue();
|
|
HashMap<Integer, JSONObject> rowMap = new HashMap<>();
|
HashMap<Integer, JSONObject> bayMap = new HashMap<>();
|
|
int finalRow = -1;
|
int finalBay = -1;
|
|
//获取列数据
|
List<HashMap<String, Object>> tmpList0 = excelData.get(0);
|
List<HashMap<String, Object>> tmpList1 = excelData.get(1);
|
for (int i = 2; i < tmpList1.size(); i++) {
|
HashMap<String, Object> tmpBayMap = tmpList1.get(i);
|
if (Cools.isEmpty(tmpBayMap.get("value"))) {
|
continue;
|
}
|
HashMap<String, Object> tmpDistanceMap = tmpList0.get(i);
|
int bay = (int) Double.parseDouble(tmpBayMap.get("value").toString());
|
JSONObject distanceData = JSON.parseObject(tmpDistanceMap.get("value").toString());
|
|
bayMap.put(bay, distanceData);
|
finalBay = bay;
|
}
|
|
//获取排数据
|
for (int i = 2; i < excelData.size(); i++) {
|
List<HashMap<String, Object>> rowData = excelData.get(i);
|
HashMap<String, Object> tmpRowMap = rowData.get(1);
|
if (Cools.isEmpty(tmpRowMap.get("value"))) {
|
continue;
|
}
|
HashMap<String, Object> tmpDistanceMap = rowData.get(0);
|
int row = (int) Double.parseDouble(tmpRowMap.get("value").toString());
|
JSONObject distanceData = JSON.parseObject(tmpDistanceMap.get("value").toString());
|
rowMap.put(row, distanceData);
|
finalRow = row;
|
}
|
|
List<List<MapNode>> mapList = new ArrayList<>();
|
//生成二维坐标
|
for (int row = 1; row <= finalRow; row++) {
|
List<MapNode> rows = new ArrayList<>();
|
for (int bay = 1; bay <= finalBay; bay++) {
|
MapNode node = new MapNode();
|
node.setId(row + "-" + bay);
|
|
HashMap<String, Object> nodeData = excelData.get(row + 1).get(bay + 1);
|
String nodeType = nodeData.get("bgColor").toString();
|
String data = "";
|
if (nodeType.equals(MapExcelNodeType.MAIN_PATH.flag)) {
|
node.setValue(MapNodeType.MAIN_PATH.id);
|
} else if (nodeType.equals(MapExcelNodeType.NORMAL_PATH.flag)) {
|
node.setValue(MapNodeType.NORMAL_PATH.id);
|
} else if (nodeType.equals(MapExcelNodeType.LIFT.flag)) {
|
node.setValue(MapNodeType.LIFT.id);
|
JSONObject jsonData = JSON.parseObject(nodeData.get("value").toString());
|
data = jsonData.getString("value");
|
} else if (nodeType.equals(MapExcelNodeType.CHARGE.flag)) {
|
node.setValue(MapNodeType.CHARGE.id);
|
JSONObject jsonData = JSON.parseObject(nodeData.get("value").toString());
|
data = jsonData.getString("value");
|
} else if (nodeType.equals(MapExcelNodeType.STATION.flag)) {
|
node.setValue(MapNodeType.CONVEYOR.id);
|
JSONObject jsonData = JSON.parseObject(nodeData.get("value").toString());
|
data = jsonData.getString("value");
|
}else {
|
node.setValue(MapNodeType.DISABLE.id);
|
}
|
|
node.setData(data);
|
rows.add(node);
|
}
|
mapList.add(rows);
|
}
|
|
//生成小车地图坐标间距数据
|
for (int row = 1; row <= finalRow; row++) {
|
for (int bay = 1; bay <= finalBay; bay++) {
|
MapNode mapNode = mapList.get(row - 1).get(bay - 1);
|
JSONObject rowData = rowMap.get(row);
|
JSONObject bayData = bayMap.get(bay);
|
|
Integer top = rowData.getInteger("top");
|
Integer bottom = rowData.getInteger("bottom");
|
Integer left = bayData.getInteger("left");
|
Integer right = bayData.getInteger("right");
|
|
mapNode.setTop(top);
|
mapNode.setBottom(bottom);
|
mapNode.setLeft(left);
|
mapNode.setRight(right);
|
}
|
}
|
|
//生成牛眼Y坐标基准数据
|
for (int bay = 1; bay <= finalBay; bay++) {
|
int yBase = 10000;
|
for (int row = 1; row <= finalRow; row++) {
|
MapNode mapNode = mapList.get(row - 1).get(bay - 1);
|
HashMap<String, Object> nodeData = excelData.get(row + 1).get(bay + 1);
|
|
JSONObject rowData = rowMap.get(row);
|
Integer bottom = rowData.getInteger("bottom");
|
String nodeValue = nodeData.get("value").toString();
|
if (Utils.isJSON(nodeValue)) {
|
JSONObject jsonData = JSON.parseObject(nodeValue);
|
if (jsonData.containsKey("bottom")) {
|
bottom = jsonData.getInteger("bottom");
|
}
|
}
|
|
if (row != 1) {
|
yBase += bottom;
|
}
|
mapNode.setYBase(yBase);
|
}
|
}
|
|
//生成牛眼X坐标基准数据
|
for (int row = 1; row <= finalRow; row++) {
|
int xBase = 10000;
|
for (int bay = 1; bay <= finalBay; bay++) {
|
MapNode mapNode = mapList.get(row - 1).get(bay - 1);
|
HashMap<String, Object> nodeData = excelData.get(row + 1).get(bay + 1);
|
|
JSONObject bayData = bayMap.get(bay);
|
Integer right = bayData.getInteger("right");
|
String nodeValue = nodeData.get("value").toString();
|
if (Utils.isJSON(nodeValue)) {
|
JSONObject jsonData = JSON.parseObject(nodeValue);
|
if (jsonData.containsKey("right")) {
|
right = jsonData.getInteger("right");
|
}
|
}
|
|
if (bay != 1) {
|
xBase += right;
|
}
|
mapNode.setXBase(xBase);
|
}
|
}
|
|
//生成四向车地图所需格式
|
MapNode disableNode = new MapNode();
|
disableNode.setId("0-0");
|
disableNode.setValue(MapNodeType.DISABLE.id);
|
disableNode.setLeft(0);
|
disableNode.setRight(0);
|
disableNode.setTop(0);
|
disableNode.setBottom(0);
|
|
List<MapNode> disableRow = new ArrayList<>();
|
for (int bay = 1; bay <= finalBay; bay++) {
|
disableRow.add(disableNode);
|
}
|
disableRow.add(0, disableNode);
|
disableRow.add(disableNode);
|
for (List<MapNode> mapNodes : mapList) {
|
mapNodes.add(0, disableNode);
|
mapNodes.add(disableNode);
|
}
|
mapList.add(0, disableRow);
|
mapList.add(disableRow);
|
|
System.out.println(JSON.toJSONString(mapList, SerializerFeature.DisableCircularReferenceDetect));
|
levMap.put(lev, mapList);
|
}
|
|
return levMap;
|
}
|
|
public HashMap<Integer, List<List<HashMap<String, Object>>>> readExcel(String filePath) throws IOException {
|
HashMap<Integer, List<List<HashMap<String, Object>>>> dataMap = new HashMap<>();
|
|
FileInputStream inputStream = new FileInputStream(new File(filePath));
|
Workbook workbook;
|
|
if (filePath.endsWith(".xlsx")) {
|
workbook = new XSSFWorkbook(inputStream);
|
} else if (filePath.endsWith(".xls")) {
|
throw new IllegalArgumentException("文件格式不支持");
|
} else {
|
throw new IllegalArgumentException("文件格式不支持");
|
}
|
|
int numberOfSheets = workbook.getNumberOfSheets();
|
System.out.println("总共有 " + numberOfSheets + " 个Sheet");
|
|
// 遍历所有Sheet
|
for (int i = 0; i < numberOfSheets; i++) {
|
Sheet sheet = workbook.getSheetAt(i);
|
String sheetName = sheet.getSheetName();
|
|
List<List<HashMap<String, Object>>> data = new ArrayList<>();
|
if (sheetName.contains("F")) {
|
String[] split = sheetName.split("F");
|
String levStr = split[0];
|
Integer lev = Integer.parseInt(levStr);
|
|
for (Row row : sheet) {
|
List<HashMap<String, Object>> rowData = new ArrayList<>();
|
for (Cell cell : row) {
|
rowData.add(getCellValue(cell));
|
}
|
data.add(rowData);
|
}
|
|
dataMap.put(lev, data);
|
}
|
}
|
|
workbook.close();
|
inputStream.close();
|
return dataMap;
|
}
|
|
private HashMap<String, Object> getCellValue(Cell cell) {
|
if (cell == null) {
|
return null;
|
}
|
|
HashMap<String, Object> map = new HashMap<>();
|
|
String bgColor = getCellBackgroundColor(cell);
|
map.put("bgColor", bgColor);
|
|
String value = "";
|
switch (cell.getCellType()) {
|
case Cell.CELL_TYPE_STRING:
|
value = cell.getStringCellValue();
|
break;
|
case Cell.CELL_TYPE_NUMERIC:
|
value = String.valueOf(cell.getNumericCellValue());
|
break;
|
case Cell.CELL_TYPE_BOOLEAN:
|
value = String.valueOf(cell.getBooleanCellValue());
|
break;
|
case Cell.CELL_TYPE_FORMULA:
|
value = cell.getCellFormula();
|
break;
|
case Cell.CELL_TYPE_BLANK:
|
value = "";
|
break;
|
default:
|
value = "";
|
}
|
|
map.put("value", value);
|
return map;
|
}
|
|
|
public String getCellBackgroundColor(Cell cell) {
|
if (cell == null) {
|
return "无单元格";
|
}
|
|
CellStyle style = cell.getCellStyle();
|
short bgColorIndex = style.getFillForegroundColor(); // 获取前景色(实际是底色)
|
|
return getXSSFBackgroundColor(style);
|
}
|
|
// 处理XSSF (.xlsx) 颜色
|
private String getXSSFBackgroundColor(CellStyle style) {
|
XSSFColor color = (XSSFColor) style.getFillForegroundColorColor();
|
if (color == null) {
|
return "default";
|
}
|
|
byte[] rgb = color.getRGB();
|
if (rgb == null) {
|
return "default";
|
}
|
|
return String.format("RGB(%d,%d,%d)",
|
rgb[0] & 0xFF,
|
rgb[1] & 0xFF,
|
rgb[2] & 0xFF);
|
}
|
|
}
|
