package com.suyun.utils;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.google.common.collect.Sets;
import com.suyun.common.result.RestResponse;
import com.suyun.common.result.Result;
import com.suyun.modules.vehicle.constants.ExcelInfoConstants;
import com.suyun.modules.vehicle.constants.StringConstants;
import com.suyun.modules.vehicle.form.ExcelInfo;
import com.suyun.modules.vehicle.form.ImportFailMessage;
import com.suyun.utils.json.JSONMapToBean;
import com.suyun.utils.json.ListToJSONArray;
import com.suyun.utils.json.SimpleJsonFormat;
import com.suyun.vehicle.utils.DatetimeUtil;
import com.suyun.vehicle.utils.ObjectUtil;
import com.thinkgem.jeesite.common.utils.excel.ExportExcel;
import com.thinkgem.jeesite.common.utils.excel.ImportExcel;
import com.thinkgem.jeesite.modules.sys.utils.DictUtils;
import com.thinkgem.jeesite.modules.sys.utils.UserUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.CollectionUtils;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.math.BigDecimal;
import java.util.*;
import java.util.concurrent.atomic.AtomicInteger;
/**
* Description：
* <p>
* EXCEL 导出/导入/JAVA执行Excel公式
* </p>
*
* @Author： leo.xiong
* @CreateDate： 2022/11/24 10:46
* @Email： leo.xiong@suyun360.com
* @Since：
*/
public class ExcelUtil {
private static final Logger LOGGER = LoggerFactory.getLogger(ExcelUtil.class);
/**
* 每次计算列个数最大为50
*/
private static final int MAX_CACULATE_COLUMN = 50;
/**
* 公式计算
*
* @param formulaList
* @return
*/
public static Map<String, BigDecimal> caculateFormula(List<String> formulaList) {
if (CollectionUtils.isEmpty(formulaList)) {
return Collections.EMPTY_MAP;
}
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet();
HSSFFormulaEvaluator hssfFormulaEvaluator = new HSSFFormulaEvaluator(workbook);
int rows = (formulaList.size() / MAX_CACULATE_COLUMN) + 1;
AtomicInteger dataIndexAtomicInteger = new AtomicInteger(0);
Map<String, BigDecimal> formulaBigDecimalMap = Maps.newConcurrentMap();
int len = formulaList.size();
for (int row = 0; row < rows; row++) {
HSSFRow hssfRow = sheet.createRow(row);
for (int column = 0; column < MAX_CACULATE_COLUMN; column++) {
int index = dataIndexAtomicInteger.getAndIncrement();
//考虑是否需要多线程并行计算
if (index >= len) {
break;
}
HSSFCell hssfCell = hssfRow.createCell(column);
hssfCell.setCellType(Cell.CELL_TYPE_FORMULA);
String formula = formulaList.get(index);
hssfCell.setCellFormula(formula);
BigDecimal value = null;
try {
value = new BigDecimal(hssfFormulaEvaluator.evaluate(hssfCell).getNumberValue())
.setScale(10, BigDecimal.ROUND_HALF_UP)
.stripTrailingZeros();
} catch (Exception e) {
LOGGER.warn("计算错误 formula: {}", formula, e);
return null;
}
formulaBigDecimalMap.put(formula, value);
}
}
return formulaBigDecimalMap;
}
/**
* 将数据写入指定EXCEL，并用response直接写出到前端
*
* @param excel
* @param filePath
* @param props
* @param dataList
* @param request
* @param response
* @param flag 是否需要生成序号
* @return
*/
public static RestResponse exportExcelWithResponse(ExportExcel excel, String filePath, String[] props, List dataList,
HttpServletRequest request, HttpServletResponse response, boolean flag) {
int index = 0;
for (Object item : dataList) {
Row row = excel.addRow();
index++;
for (int j = 0; j < props.length; j++) {
Object cellValue;
// 以“-”开头的属性，表示是固定值
if (flag && j == 0) {
cellValue = index;
} else if (props[j].startsWith(StringConstants.STRIPING_LINE)) {
cellValue = props[j].substring(1);
//以“:”分割的属性，表示需要字典转换
} else if (props[j].contains(StringConstants.DEFAULT_COLON)) {
String[] propArrays = props[j].split(StringConstants.DEFAULT_COLON);
cellValue = ObjectUtil.getObjectProp(item, propArrays[0]);
cellValue = DictUtils.getDictLabel((String) cellValue, propArrays[1], "");
} else {
cellValue = ObjectUtil.getObjectProp(item, props[j]);
}
excel.addCell(row, j, cellValue);
}
}
try {
excel.uploadWrite(request, response, filePath).dispose();
} catch (IOException e) {
e.printStackTrace();
return new Result("uploadFile " + filePath + " error, Exception:" + e).newMessageTips(false, "-1", "导出失败");
}
return null;
}
public static RestResponse exportExcelWithNumber(String title, String filePath, Map<String, String> map, List list, String fomate, int deep, HttpServletRequest request, HttpServletResponse response, boolean flag) {
ExcelTitle excelTitle = buildExcelTitle(map, flag);
ExportExcel excel = new ExportExcel(title, excelTitle.getNames());
int len = list.size() % ExcelInfoConstants.EXPORT_WRITE_ROW == 0 ? list.size() / ExcelInfoConstants.EXPORT_WRITE_ROW : (list.size() / ExcelInfoConstants.EXPORT_WRITE_ROW) + 1;
List newList = new ArrayList();
for (int i = 0; i < len; i++) {
//遍历次数
int size = i == len - 1 ? list.size() - i * ExcelInfoConstants.EXPORT_WRITE_ROW : ExcelInfoConstants.EXPORT_WRITE_ROW;
for (int j = i * ExcelInfoConstants.EXPORT_WRITE_ROW, time = i * ExcelInfoConstants.EXPORT_WRITE_ROW + size; j < time; j++) {
newList.add(list.get(j));
}
excel = exportExcel(excel, excelTitle.getCodes(), ListToJSONArray.getJSONArrayByList(newList, fomate, deep), i * ExcelInfoConstants.EXPORT_WRITE_ROW, flag);
newList.clear();
}
return exportExcel(excel, filePath, request, response);
}
/**
* 下载
*
* @param title
* @param filePath
* @param map
* @param jsonArray
* @param request
* @param response
* @param flag
* @return
*/
public static RestResponse exportExcelWithJSONArray(String title, String filePath, Map<String, String> map, JSONArray jsonArray,
HttpServletRequest request, HttpServletResponse response, boolean flag) {
ExcelTitle excelTitle = buildExcelTitle(map, flag);
ExportExcel excel = new ExportExcel(title, excelTitle.getNames());
excel = exportExcel(excel, excelTitle.getCodes(), jsonArray, 0, flag);
return exportExcel(excel, filePath, request, response);
}
/**
* 写入数据信息
*
* @param excel
* @param codes
* @param jsonArray
* @param index
* @param flag
* @return
*/
public static ExportExcel exportExcel(ExportExcel excel, String[] codes, JSONArray jsonArray, Integer index, Boolean flag) {
JSONObject jsonObject;
int firstIndex = index;
for (; index < firstIndex + jsonArray.size(); ) {
jsonObject = jsonArray.getJSONObject(index - firstIndex);
Row row = excel.addRow();
Object cellValue;
for (int i = 0, len = codes.length; i < len; i++) {
Integer importDefault = null;
if (codes[i].startsWith(ExcelInfoConstants.EXPORT_NO_DEFAULT)) {
String[] codeArray = codes[i].split(StringConstants.STRIPING_LINE);
codes[i] = codeArray[4];
importDefault = Integer.parseInt(codeArray[3]);
}
if (flag && "index".equals(codes[i])) {
cellValue = index + 1;
} else if (codes[i].startsWith(StringConstants.STRIPING_LINE)) {
cellValue = codes[i].substring(1);
//以“:”分割的属性，表示需要字典转换
} else if (codes[i].contains(StringConstants.DICT)) {
String[] propArrays = codes[i].split(StringConstants.DEFAULT_COLON);
cellValue = jsonObject.get(propArrays[1]);
if (cellValue != null) {
cellValue = DictUtils.getDictLabel(cellValue.toString(), propArrays[2], propArrays.length > 3 ? propArrays[3] : StringConstants.DEFAULT_EMPTY);
} else {
cellValue = propArrays.length > 3 ? propArrays[3] : StringConstants.DEFAULT_EMPTY;
}
//json多层嵌套
} else if (codes[i].contains(StringConstants.DEFAULT_DOT)) {
cellValue = ListToJSONArray.getNesting(jsonObject, codes[i].split(StringConstants.SPLIT_DOT), 0);
} else {
cellValue = jsonObject.get(codes[i]);
}
excel.addCell(row, i, cellValue, importDefault);
}
index += 1;
}
return excel;
}
public static ExportExcel exportExcel(ExportExcel excel, String[] codes, JSONArray jsonArray, int cloumn) {
int index = 0;
JSONObject jsonObject;
for (; index < jsonArray.size(); ) {
jsonObject = jsonArray.getJSONObject(index);
Row row = excel.addRow();
excel.addCell(row, 0, jsonObject.get(codes[0]));
index += 1;
}
return excel;
}
public static ExportExcel exportExcel(ExportExcel excel, Map linkedHashMap, List<Map<String, Object>> maps) {
linkedHashMap.forEach((k, v) -> {
Row row = excel.addRow();
excel.addCell(row, 0, v);
Map map;
for (int i = 0, len = maps.size(); i < len; i++) {
map = maps.get(i);
excel.addCell(row, i + 1, map.get(k));
}
});
return excel;
}
public static ExportExcel exportExcel(ExportExcel excel, String[] cloumns) {
Row row = excel.addRow();
for (int i = 0; i < cloumns.length; i++) {
excel.addCell(row, i, cloumns[i]);
}
return excel;
}
/**
* 写出Excel
*
* @param excel
* @param filePath
* @param request
* @param response
* @return
*/
public static RestResponse exportExcel(ExportExcel excel, String filePath, HttpServletRequest request, HttpServletResponse response) {
try {
excel.uploadWrite(request, response, filePath).dispose();
} catch (IOException e) {
LOGGER.error("写出数据异常，filePath:{},error:{}", e);
return new Result("uploadFile " + filePath + " error, Exception:" + e).newMessageTips(false, "-1", "导出失败");
}
return null;
}
/**
* 组装excel的头部信息
*
* @param map
* @param flag
* @return
*/
public static ExcelTitle buildExcelTitle(Map<String, String> map, Boolean flag) {
try {
String[] codes, names;
if (flag) {
codes = new String[map.size() + 1];
names = new String[map.size() + 1];
codes[0] = "index";
names[0] = UserUtils.isEn() ? "NO." : "序号";
} else {
codes = new String[map.size()];
names = new String[map.size()];
}
String language = UserUtils.getLanguage();
List<Map.Entry<String, String>> entryList = Lists.newArrayList(map.entrySet());
for (int i = 0, len = entryList.size(); i < len; i++) {
Map.Entry<String, String> entryValue = entryList.get(i);
if (flag) {
codes[i + 1] = entryValue.getKey();
names[i + 1] = UserUtils.getValueByLanguage(entryValue.getValue(), language);
} else {
codes[i] = entryValue.getKey();
names[i] = UserUtils.getValueByLanguage(entryValue.getValue(), language);
}
}
return new ExcelTitle(codes, names);
} catch (Exception e) {
LOGGER.error(e.getMessage());
return null;
}
}
/**
* 添加错误信息进入Map中
*
* @param map
* @param rowNumber 使用excel遍历的i
* @param message
* @return
*/
public static Map<Integer, List<String>> importFailInfo(Map<Integer, List<String>> map, Integer rowNumber, String message) {
if (map == null) {
map = new HashMap<>(16);
}
if (map.get(rowNumber + ExcelInfoConstants.EXCEL_ADD_CELL_NUMBER) == null) {
map.put(rowNumber + ExcelInfoConstants.EXCEL_ADD_CELL_NUMBER, new ArrayList<>());
}
map.get(rowNumber + ExcelInfoConstants.EXCEL_ADD_CELL_NUMBER).add(message);
return map;
}
public static boolean isSuccess(Map<Integer, List<String>> map, Integer rowNumber) {
return !CollectionUtils.isEmpty(map) && map.get(rowNumber + 1) != null;
}
/**
* 通过ExcelFile获取列信息
* 一次导入数据量不用超过500条
*
* @param map 导入失败的存储信息，key第几行失败，value，失败的原因集合
* @param file 需要导入的文件流
* @param cells 定义的列信息，参照ImportColumnName接口规范
* @return
*/
public static ExcelInfo getRowInfosByFile(Map<Integer, List<String>> map, MultipartFile file, Map<Integer, String[]> cells) {
boolean isEn = UserUtils.isEn();
if (file.isEmpty()) {
ExcelUtil.importFailInfo(map, -3, isEn ? "file does not exist" : "文件不存在");
return null;
}
ImportExcel ei;
try {
ei = new ImportExcel(file, 0, 0);
} catch (InvalidFormatException | IOException e) {
ExcelUtil.importFailInfo(map, -1, isEn ? "excel import failed" : "excel导入失败" + e);
return null;
}
Row r = ei.getRow(0);
if (isEmptyRow(r)) {
ExcelUtil.importFailInfo(map, -5, isEn ? "excel is empty" : "excel为空");
return null;
}
//校验列名是否都正确，判断是否使用模版
AtomicInteger atomicInteger = new AtomicInteger(-1);
Row finalR = r;
try {
cells.forEach((index, columnName) -> {
String excelColumnName = finalR.getCell(index).getStringCellValue();
if (excelColumnName.indexOf("\n") > -1) {
excelColumnName = excelColumnName.replace("\n", " ");
}
String columnNameValue = UserUtils.getCurrentValue(columnName[0]);
if (!columnNameValue.equalsIgnoreCase(excelColumnName)) {
if (isEn) {
ExcelUtil.importFailInfo(map, -2, "The " + (index + 1) + " column name of the column is abnormal, the template is" + columnNameValue + ", the template is used incorrectly, please download the template and import it again");
} else {
ExcelUtil.importFailInfo(map, -2, "第" + (index + 1) + "列的列名异常，模版为" + columnNameValue + "，模板使用错误，请下载模板重新导入");
}
atomicInteger.set(index);
}
});
} catch (Exception e) {
}
if (atomicInteger.get() != -1) {
return null;
}
List<Integer> columns = new ArrayList(cells.keySet());
ExcelInfo excelInfo = new ExcelInfo(ei.getLastDataRowNum(), columns);
for (int i = ei.getDataRowNum(), len = ei.getLastDataRowNum(); i <= len; i++) {
r = ei.getRow(i);
if (isEmptyRow(r)) {
continue;
}
insertColumnInfoToExcelInfo(map, cells, excelInfo, r, columns, i);
}
validateNoRepeaterColumn(map, cells, excelInfo);
validateDoubleRepeate(map, cells, excelInfo);
return excelInfo;
}
/**
* 判断当前行是否为空
*
* @param row
* @return
*/
public static boolean isEmptyRow(Row row) {
if (row == null) {
return true;
}
for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) {
Cell cell = row.getCell(c);
if (cell != null && !StringConstants.DEFAULT_EMPTY.equals(cell.toString())) {
return false;
}
}
return true;
}
/**
* 添加excle数据进入ExcelInfo对象中
*
* @param map
* @param cells
* @param excelInfo
* @param r
* @param columns
* @param i : EXCEL行号
*/
private static void insertColumnInfoToExcelInfo(Map<Integer, List<String>> map, Map<Integer, String[]> cells, ExcelInfo excelInfo, Row r, List<Integer> columns, int i) {
boolean isEn = UserUtils.isEn();
cells.forEach((index, columnType) -> {
String columnName = UserUtils.getCurrentValue(columnType[0]);
Cell cell = r.getCell(columns.get(index));
Object obj = null;
if (cell != null && StringUtils.isNotEmpty(cell.toString().trim())) {
try {
switch (columnType[1]) {
case ExcelInfoConstants.INTEGER:
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
obj = Double.valueOf(cell.getNumericCellValue()).intValue();
break;
case ExcelInfoConstants.DOUBLE:
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
obj = Double.valueOf(cell.getNumericCellValue());
break;
case ExcelInfoConstants.DATE:
if (DateUtil.isCellDateFormatted(cell)) {
String pattern = columnType.length > 4 ? columnType[4] : DatetimeUtil.TIME_FORMAT_Y_M_D;
obj = DatetimeUtil.changeToDate(DatetimeUtil.formatDate(cell.getDateCellValue(), pattern), pattern);
} else {
ExcelUtil.importFailInfo(map, (i - 1), columnName + (isEn ? "time format exception" : "时间格式异常"));
}
break;
case ExcelInfoConstants.BOOLEAN:
cell.setCellType(Cell.CELL_TYPE_BOOLEAN);
obj = cell.getBooleanCellValue();
break;
default:
/**
* 常规类型不做处理，特殊情况需要设置cell属性，否则报错
*/
try {
obj = cell.getStringCellValue().trim();
} catch (Exception e) {
}
if (obj == null || StringConstants.DEFAULT_EMPTY.equals(obj)) {
cell.setCellType(Cell.CELL_TYPE_STRING);
obj = cell.getStringCellValue().trim();
if (StringConstants.DEFAULT_EMPTY.equals(obj)) {
obj = null;
}
}
if (columnType.length > 5) {
String val = obj.toString();
if (columnType[5].contains(StringConstants.VERTICAL_LINE)) {
String[] strValueList = columnType[5].split("\\" + StringConstants.VERTICAL_LINE);
for (String str : strValueList) {
String[] strValues = str.split(StringConstants.DEFAULT_EQUAL_SIGN);
str = UserUtils.getCurrentValue(strValues[0]);
if (str.contains(val)) {
obj = strValues[1];
break;
}
}
} else if (columnType[5].contains(StringConstants.DICT)) {
String[] labels = columnType[5].split(StringConstants.DEFAULT_COLON);
obj = DictUtils.getDictValue(val, labels[1], labels.length > 2 ? labels[2] : StringConstants.DEFAULT_EMPTY);
}
if (StringUtils.isEmpty(obj.toString()) && ExcelInfoConstants.CAN_NOT_NULL.equals(columnType[3])) {
ExcelUtil.importFailInfo(map, (i - 1), columnName + (isEn ? "Data conversion exception, dictionary mapping information does not exist" : "数据转换异常，字典映射信息不存在"));
}
}
break;
}
} catch (Exception e) {
ExcelUtil.importFailInfo(map, (i - 1), columnName + (isEn ? "type" : "类型") + columnType[1] + (isEn ? "Abnormal data format" : "数据格式异常"));
}
} else if (ExcelInfoConstants.CAN_NOT_NULL.equals(columnType[3])) {
ExcelUtil.importFailInfo(map, (i - 1), columnName + (isEn ? "Can not be empty" : "不能为空"));
}
if (excelInfo.getRowsMap().get(i) == null) {
excelInfo.getRowsMap().put(i, new HashMap<>(16));
}
if (columnType[2].indexOf(StringConstants.DEFAULT_DOT) > -1) {
String[] columnsName = columnType[2].split(StringConstants.SPLIT_DOT);
if (excelInfo.getRowsMap().get(i).get(columnsName[0]) == null) {
excelInfo.getRowsMap().get(i).put(columnsName[0], new HashMap<>());
}
((Map) (excelInfo.getRowsMap().get(i).get(columnsName[0]))).put(columnsName[1], obj);
} else {
excelInfo.getRowsMap().get(i).put(columnType[2], obj);
}
excelInfo.getColumnsMap().get(index).add(obj);
});
}
/**
* 对标记为不能重复的列进行判断
*
* @param map
* @param cells
* @param excelInfo
*/
private static void validateNoRepeaterColumn(Map<Integer, List<String>> map, Map<Integer, String[]> cells, ExcelInfo excelInfo) {
cells.forEach((index, columnType) -> {
if (columnType.length > 4 && ExcelInfoConstants.NO_REPEAT.equals(columnType[4])) {
validateRepeateInfo(map, UserUtils.getCurrentValue(columnType[0]), excelInfo.getColumnsMap().get(index), columnType);
}
});
}
/**
* 多个字段比较重复
*
* @param map
* @param cells
* @param excelInfo
*/
private static void validateDoubleRepeate(Map<Integer, List<String>> map, Map<Integer, String[]> cells, ExcelInfo excelInfo) {
cells.forEach((index, columnType) -> {
if (columnType.length > 4 && columnType[4].startsWith(ExcelInfoConstants.DOUBLE_REPEAT)) {
String[] doubleIndexs = columnType[4].split("_");
String prefix = "";
for (int j = 2, len = doubleIndexs.length; j < len; j++) {
prefix += UserUtils.getCurrentValue(cells.get(Integer.parseInt(doubleIndexs[j]))[0]) + "、";
}
prefix = prefix.substring(0, prefix.length() - 1);
int size = excelInfo.getRowsMap().size();
Object[] repeatInfos = new Object[size];
for (int j = 2, len = doubleIndexs.length; j < len; j++) {
List<Object> columnsInfos = excelInfo.getColumnsMap().get(Integer.parseInt(doubleIndexs[j]));
for (int i = 0; i < size; i++) {
repeatInfos[i] = repeatInfos[i] == null ? columnsInfos.get(i) : columnsInfos.get(i) + String.valueOf(repeatInfos[i]);
}
}
validateRepeateInfo(map, prefix, Lists.newArrayList(repeatInfos), columnType);
}
});
}
/**
* 判断list是否出现重复
*
* @param map
* @param prefix
* @param columnValues
* @param columnType
*/
private static void validateRepeateInfo(Map<Integer, List<String>> map, String prefix, List<Object> columnValues, String[] columnType) {
Set<Object> columnSet = Sets.newHashSet(columnValues);
boolean isEn = UserUtils.isEn();
if (columnValues.size() != columnSet.size()) {
Map<Object, RepeateValue> repeaterValueMap = new HashMap<>(16);
for (int i = 0; i < columnValues.size(); i++) {
if (ObjectUtil.isEmpty(columnValues.get(i))) {
continue;
}
RepeateValue repeateValue = repeaterValueMap.get(columnValues.get(i));
if (repeateValue == null) {
Set<Integer> setValues = new TreeSet<>();
setValues.add(i);
repeateValue = new RepeateValue((i + 2) + "", setValues);
repeaterValueMap.put(columnValues.get(i), repeateValue);
} else {
repeateValue.setMsg(repeateValue.getMsg() + "、" + (i + 2));
repeateValue.getRepeateValues().add(i);
}
}
List<RepeateValue> list = new ArrayList<>(repeaterValueMap.values());
list.removeIf(repeaterValue -> repeaterValue.getRepeateValues().size() < 2);
list.forEach(repeaterValue -> {
repeaterValue.getRepeateValues().forEach(columnIndex -> {
ExcelUtil.importFailInfo(map, columnIndex, prefix + repeaterValue.getMsg() + (isEn ? " repeat" : "重复"));
});
});
}
}
/**
* 把行信息转换为对象信息。
*
* @param clazz 需要转换的对象
* @param rowsMap 行信息
* @return
*/
public static <T> List<T> addObjectToData(Class<T> clazz, Map<Integer, Map<String, Object>> rowsMap) {
List<T> data = new ArrayList<>(rowsMap.size());
//获取所有的行数
rowsMap.forEach((index, value) -> {
data.add(JSONMapToBean.caseToJavaBean(JSONObject.parseObject(new SimpleJsonFormat(value).serialize()), clazz));
});
return data;
}
/**
* Excel数据不为空
*
* @param excelInfo
* @param importFailMessage
* @return
*/
public static boolean isNotEmpty(ExcelInfo excelInfo, ImportFailMessage importFailMessage) {
boolean flag = false;
int total = 0, fail = 0;
if (excelInfo != null && excelInfo.getColumnsMap() != null) {
if (excelInfo.getColumnsMap().size() <= 0) {
ExcelUtil.importFailInfo(importFailMessage.getMap(), -6, UserUtils.isEn() ? "Import data is empty" : "导入数据为空");
} else {
total = excelInfo.getRowsMap().size();
flag = true;
}
}
importFailMessage.setTotal(total);
importFailMessage.setFail(fail);
return flag;
}
/**
* Excel 数据为空
*
* @param excelInfo
* @param importFailMessage
* @return
*/
public static boolean isEmpty(ExcelInfo excelInfo, ImportFailMessage importFailMessage) {
return !isNotEmpty(excelInfo, importFailMessage);
}
/**
* 校验后，不存在错误数据
*
* @param importFailMessage
* @return
*/
public static boolean isEmptyFail(ImportFailMessage importFailMessage) {
boolean flag = true;
if (!CollectionUtils.isEmpty(importFailMessage.getMap())) {
importFailMessage.setFail(importFailMessage.getMap().size());
flag = false;
}
return flag;
}
/**
* 校验后，存在错误数据，并设置错误数据条数
*
* @param importFailMessage
* @return
*/
public static boolean isNotEmptyFail(ImportFailMessage importFailMessage) {
return !isEmptyFail(importFailMessage);
}
}
class ExcelTitle {
private String[] codes;
private String[] names;
public String[] getCodes() {
return codes;
}
public void setCodes(String[] codes) {
this.codes = codes;
}
public String[] getNames() {
return names;
}
public void setNames(String[] names) {
this.names = names;
}
public ExcelTitle(String[] codes, String[] names) {
this.codes = codes;
this.names = names;
}
}
class RepeateValue {
private String msg;
private Set<Integer> repeateValues;
public String getMsg() {
return msg;
}
public void setMsg(String msg) {
this.msg = msg;
}
public Set<Integer> getRepeateValues() {
return repeateValues;
}
public void setRepeateValues(Set<Integer> repeateValues) {
this.repeateValues = repeateValues;
}
public RepeateValue(String msg, Set<Integer> repeateValues) {
this.msg = msg;
this.repeateValues = repeateValues;
}
}package com.suyun.modules.vehicle.constants;
import java.util.LinkedHashMap;
import java.util.Map;
/**
* @Description：导出配置信息 (导出和返回类型的字段信息映射，建议直接放在Entity里面)
* @Author： leo.xiong
* @CreateDate： 2019/1/8 15:02
* @Email： leo.xiong@suyun360.com
* @Version：
*/
public interface ExportProperty {
/**
* 导出规则
* 1、保留小数位数信息
* INT,LONG,默认保留0位小数
* Double默认保留2位小数
* Float默认保留1位小数
* BigDecimal默认保留2位小数
* 修改默认保留小数位数可以使用ExcelInfoConstants.EXPORT_NO_DEFAULT开头，中间是保留位数，后面是取值的属性
* ExcelInfoConstants.EXPORT_NO_DEFAULT + "_1_driverMileage"
* 2、支持数据字典获取值，格式 dict:属性名称:对应字典的type名称，如dict:chargeAndDischargeStatus:hydBottleStatus:未知
* 前面表示获取氢瓶存放状态在数据库里面的值，后面表示根据hydBottleStatus和对应值查询字典对应的中文信息
* 3、已"-"开头，表示直接取值"-"后面的内容，不支持一个数据多条横杠信息
* 4、flag参数表示是否需要添加默认的索引信息
* 5、支持多层子查询取值，只查询用"."，如:busInfo.busModel.name
*/
/**
* OTS存储生命周求
*/
Map<String, String> OTS_SAVE_LIFE_CYCLE = new LinkedHashMap() {{
put("busInfo.busModel.name", "{\"zh\":\"车型\",\"en\":\"Plate No\"}");
put("busInfo.plateNo", "{\"zh\":\"车牌号\",\"en\":\"Plate No\"}");
put("enableTime", "{\"zh\":\"配置生效日期\",\"en\":\"start time\"}");
put("unableTime", "{\"zh\":\"配置失效日期\",\"en\":\"End Time\"}");
put("dict:active:isActive:", "{\"zh\":\"生效状态\",\"en\":\"Start purge flow\"}");
put("dict:otsLifeCycle:ots_table_life_cycle:", "{\"zh\":\"生命周期\",\"en\":\"Driving Total Mileage\"}");
put("companyName", "{\"zh\":\"公司\",\"en\":\"End purge flow\"}");
put("createName", "{\"zh\":\"配置人\",\"en\":\"Driving Total Time\"}");
}};
}在这里插入图片描述
package com.suyun.modules.vehicle.constants;
/**
* @Description：EXCEL辅助常量信息
* @Author： leo.xiong
* @CreateDate： 2019/1/8 15:02
* @Email： leo.xiong@suyun360.com
* @Version：
*/
public interface ExcelInfoConstants {
/**
* 初始读取位置
*/
Integer FIRST_INDEX = 0;
/**
* 每次读取增加一个单元格
*/
Integer EXCEL_ADD_CELL_NUMBER = 1;
/**
* 支持的数据格式
*/
String INTEGER = "INTEGER";
String DOUBLE = "DOUBLE";
String DATE = "DATE";
String BOOLEAN = "BOOLEAN";
String STRING = "STRING";
/**
* 该单元格是否可以为空
*/
String CAN_NULL = "NULL";
String CAN_NOT_NULL = "NOT NULL";
/**
* 该单元格是否可以重复
*/
String CAN_REPEAT = "CAN_REPEAT";
String NO_REPEAT = "NO_REPEAT";
/**
* 出现多个字段共同判断重复使用
*/
String DOUBLE_REPEAT = "DOUBLE_REPEAT";
/**
* 修改默认的导出保留小数位数使用
*/
String EXPORT_NO_DEFAULT = "EXPORT_NO_DEFAULT";
/**
* 导出每次落盘的数据条数
*/
Integer EXPORT_WRITE_ROW = 500;
}
package com.suyun.modules.vehicle.constants;
import java.util.HashMap;
import java.util.Map;
/**
* @Description：导入配置信息 (导入信息和实体字段的配置信息，建议直接放在Entity里面)
* @Author： leo.xiong
* @CreateDate： 2019/1/8 15:03
* @Email： leo.xiong@suyun360.com
* @Version：
*/
public interface ImportColumnName {
/**
* INTEGER,DOUBLE,STRING,BOOLEAN,DATE,TIMESTAMP
* 子对象目前只支持2层处理；
* 第一个参数表示模版的列名称；
* 第二个参数表示获取的数据类型；
* 第三个参数表示对应的对象取值属性；
* 第四个参数表示是否需要校验为空；
* 属性为DATE第五个参数表示时间类型的格式化规则；
* 属性为非DATE第五个参数表示是否可以重复；
* 多个字段联合不能重复为第五个参数，用StatusType.DOUBLE_REPEATE 开头，下划线分开，后面接索引(包含当前字段索引)
* 属性为String第六个参数表示转换的值，支持数据字典格式 dict:province_location_code:默认值；
* 注意：阿里接口的属性是取得set方法，所以属性第一个字母为大写，set方法会变为小写。模版存在“\n”会替换为“ ”；
*/
/**
* 车型计算公式
*/
Map<Integer, String[]> BUS_MODEL_FORMULA_MAP = new HashMap() {{
put(0, new String[]{"{\"en\":\"*Business name\",\"zh\":\"*业务分析名称\"}", ExcelInfoConstants.STRING, "caculateBusiness", ExcelInfoConstants.CAN_NULL, ExcelInfoConstants.CAN_REPEAT});
put(1, new String[]{"{\"en\":\"*X name\",\"zh\":\"*横轴名称\"}", ExcelInfoConstants.STRING, "xName", ExcelInfoConstants.CAN_NULL, ExcelInfoConstants.CAN_REPEAT});
put(2, new String[]{"{\"en\":\"*Y name\",\"zh\":\"*纵轴名称\"}", ExcelInfoConstants.STRING, "yName", ExcelInfoConstants.CAN_NULL, ExcelInfoConstants.CAN_REPEAT});
put(3, new String[]{"{\"en\":\"*Bus model\",\"zh\":\"*车型\"}", ExcelInfoConstants.STRING, "modelName", ExcelInfoConstants.CAN_NULL, ExcelInfoConstants.CAN_REPEAT});
put(4, new String[]{"{\"en\":\"*Signal name\",\"zh\":\"*信号名称\"}", ExcelInfoConstants.STRING, "signalName", ExcelInfoConstants.CAN_NULL, ExcelInfoConstants.CAN_REPEAT});
put(5, new String[]{"{\"en\":\"Delay num\",\"zh\":\"Delay行数\"}", ExcelInfoConstants.STRING, "delayNum", ExcelInfoConstants.CAN_NULL, ExcelInfoConstants.CAN_REPEAT});
put(6, new String[]{"{\"en\":\"Delay formula\",\"zh\":\"Delay计算公式\"}", ExcelInfoConstants.STRING, "delayFormula", ExcelInfoConstants.CAN_NULL, ExcelInfoConstants.CAN_REPEAT});
put(7, new String[]{"{\"en\":\"Delay select\",\"zh\":\"Delay结果筛选\"}", ExcelInfoConstants.STRING, "delaySelect", ExcelInfoConstants.CAN_NULL, ExcelInfoConstants.CAN_REPEAT});
put(8, new String[]{"{\"en\":\"Before select\",\"zh\":\"前置筛选\"}", ExcelInfoConstants.STRING, "processBefore", ExcelInfoConstants.CAN_NULL, ExcelInfoConstants.CAN_REPEAT});
put(9, new String[]{"{\"en\":\"*X excel formula\",\"zh\":\"*横坐标计算公式\"}", ExcelInfoConstants.STRING, "xExcelFormula", ExcelInfoConstants.CAN_NULL, ExcelInfoConstants.CAN_REPEAT});
put(10, new String[]{"{\"en\":\"*Y excel formula\",\"zh\":\"*纵坐标计算公式\"}", ExcelInfoConstants.STRING, "yExcelFormula", ExcelInfoConstants.CAN_NULL, ExcelInfoConstants.CAN_REPEAT});
put(11, new String[]{"{\"en\":\"X after select\",\"zh\":\"横坐标后置筛选\"}", ExcelInfoConstants.STRING, "xProcessAfter", ExcelInfoConstants.CAN_NULL, ExcelInfoConstants.CAN_REPEAT});
put(12, new String[]{"{\"en\":\"Y after select\",\"zh\":\"纵坐标后置筛选\"}", ExcelInfoConstants.STRING, "yProcessAfter", ExcelInfoConstants.CAN_NULL, ExcelInfoConstants.CAN_REPEAT});
put(13, new String[]{"{\"en\":\"X min value\",\"zh\":\"横轴最小值\"}", ExcelInfoConstants.STRING, "xMinValue", ExcelInfoConstants.CAN_NULL, ExcelInfoConstants.CAN_REPEAT});
put(14, new String[]{"{\"en\":\"X max value\",\"zh\":\"横轴最大值\"}", ExcelInfoConstants.STRING, "xMaxValue", ExcelInfoConstants.CAN_NULL, ExcelInfoConstants.CAN_REPEAT});
put(15, new String[]{"{\"en\":\"X number tick\",\"zh\":\"横轴分割值\"}", ExcelInfoConstants.STRING, "xNumberTick", ExcelInfoConstants.CAN_NULL, ExcelInfoConstants.CAN_REPEAT});
put(16, new String[]{"{\"en\":\"Y min value\",\"zh\":\"纵轴最小值\"}", ExcelInfoConstants.STRING, "yMinValue", ExcelInfoConstants.CAN_NULL, ExcelInfoConstants.CAN_REPEAT});
put(17, new String[]{"{\"en\":\"Y max value\",\"zh\":\"纵轴最大值\"}", ExcelInfoConstants.STRING, "yMaxValue", ExcelInfoConstants.CAN_NULL, ExcelInfoConstants.CAN_REPEAT});
put(18, new String[]{"{\"en\":\"Y number tick\",\"zh\":\"纵轴分割值\"}", ExcelInfoConstants.STRING, "yNumberTick", ExcelInfoConstants.CAN_NULL, ExcelInfoConstants.CAN_REPEAT});
}};
}
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
