package com.ld.igds.grain.manager;
|
|
import com.ld.igds.constant.DepotType;
|
import com.ld.igds.grain.*;
|
import com.ld.igds.grain.dto.*;
|
import com.ld.igds.models.Depot;
|
import com.ld.igds.util.ContextUtil;
|
import com.ld.igds.util.FilesUtil;
|
import com.ld.igds.util.NumberUtil;
|
import lombok.extern.slf4j.Slf4j;
|
import org.apache.commons.lang3.StringUtils;
|
import org.apache.commons.lang3.time.DateFormatUtils;
|
import org.apache.poi.hssf.usermodel.*;
|
import org.apache.poi.ss.usermodel.*;
|
import org.apache.poi.ss.util.CellRangeAddress;
|
import org.springframework.stereotype.Component;
|
|
import javax.annotation.Resource;
|
import java.io.FileInputStream;
|
import java.io.FileOutputStream;
|
import java.util.Date;
|
import java.util.HashMap;
|
import java.util.List;
|
import java.util.Map;
|
|
|
/**
|
* @Desc: 粮情页面批量导出EXCEL
|
* @author: Andy
|
* @update-time: 2022/12/8
|
*/
|
@Slf4j
|
@Component
|
public class GrainExportBuilder {
|
|
|
@Resource
|
private FilesUtil filesUtil;
|
|
/**
|
* 生成生成
|
*
|
* @param dataMap
|
* @return 如果返回信息以：ERROR:开头表示执行出错，正常返回文件名称
|
*/
|
public String exportByMapData(Map<String, GrainData> dataMap, List<Depot> listDepot) {
|
|
String result;
|
try {
|
//创建一个新的EXCEL
|
HSSFWorkbook toWorkbook = createWorkBook();
|
HSSFWorkbook fromWorkBook;
|
|
Map<String, HSSFWorkbook> mapWorkBook = new HashMap<>();
|
|
HSSFSheet newSheet, oldSheet;
|
|
Depot depot = null;
|
String cableKey = "";
|
for (GrainData data : dataMap.values()) {
|
depot = this.getDepot(data.getDepotId(), listDepot);
|
|
if (null == depot) {
|
log.info("--------粮情导出EXCEL没有获取到仓库信息，取消导出----{}", data.getDepotId());
|
continue;
|
}
|
if (StringUtils.isEmpty(data.getCableCir())) {
|
cableKey = data.getCable();
|
} else {
|
cableKey = data.getCable() + "_" + data.getCableCir();
|
}
|
|
fromWorkBook = mapWorkBook.get(cableKey);
|
if (null == fromWorkBook) {
|
fromWorkBook = this.getFromWorkBook(data, depot);
|
mapWorkBook.put(cableKey, fromWorkBook);
|
}
|
|
newSheet = toWorkbook.createSheet(depot.getName());
|
|
oldSheet = fromWorkBook.getSheetAt(0);
|
|
this.copySheet(newSheet, oldSheet, toWorkbook, data, depot);
|
|
Thread.sleep(300);
|
}
|
|
result = ContextUtil.getTimeId() + ".xls";
|
String tempPath = filesUtil.getTempPath(depot == null ? ContextUtil.getDefaultCompanyId() : depot.getCompanyId());
|
FileOutputStream fos = new FileOutputStream(tempPath + "/" + result);
|
toWorkbook.write(fos);
|
if (null != fos) fos.close();
|
|
} catch (Exception e) {
|
log.info("---------------导出异常信息----{}", e);
|
result = "ERROR:导出错误：" + e.getMessage();
|
}
|
|
return result;
|
}
|
|
private HSSFWorkbook getFromWorkBook(GrainData data, Depot depot) throws Exception {
|
FileInputStream fis = null;
|
|
String filePath = filesUtil.getConfPath();
|
try {
|
//获取模版的名称，注意筒仓名称配置规则
|
if (StringUtils.isEmpty(data.getCableCir())) {
|
filePath = filePath + "/" + data.getCable() + ".xls";
|
} else {
|
filePath = filePath + "/" + data.getCable() + "_" + data.getCableCir() + ".xls";
|
}
|
|
fis = new FileInputStream(filePath);
|
HSSFWorkbook wb = new HSSFWorkbook(fis);
|
|
return wb;
|
} catch (Exception e) {
|
log.error("------根据粮情导出EXCEL执行异常：{}", e);
|
return null;
|
} finally {
|
if (null != fis) fis.close();
|
}
|
}
|
|
private Depot getDepot(String depotId, List<Depot> listDepot) {
|
if (listDepot == null || listDepot.isEmpty()) return null;
|
|
for (Depot depot : listDepot) {
|
if (depot.getId().equals(depotId)) return depot;
|
}
|
return null;
|
}
|
|
|
private HSSFWorkbook createWorkBook() {
|
return new HSSFWorkbook();
|
}
|
|
|
/**
|
* 复制sheet
|
*
|
* @param newSheet
|
* @param oldSheet
|
* @return
|
*/
|
public Sheet copySheet(HSSFSheet newSheet, HSSFSheet oldSheet, Workbook workbook, GrainData data, Depot depot) {
|
|
//合并单元格
|
int numMergedRegions = oldSheet.getNumMergedRegions();
|
for (int i = 0; i < numMergedRegions; i++) {
|
CellRangeAddress mergedRegion = oldSheet.getMergedRegion(i);
|
newSheet.addMergedRegion(mergedRegion);
|
}
|
|
|
// 设置列宽
|
int physicalNumberOfCells = oldSheet.getRow(0).getPhysicalNumberOfCells();
|
for (int i = 0; i < physicalNumberOfCells; i++) {
|
HSSFRow fromRow = oldSheet.getRow(i);
|
if (null == fromRow) continue;
|
for (int j = fromRow.getLastCellNum(); j >= fromRow.getFirstCellNum(); j--) {
|
newSheet.setColumnWidth(j, oldSheet.getColumnWidth(j));
|
newSheet.setColumnHidden(j, false);
|
}
|
}
|
|
|
//此处需要将 cellStyle 定义在遍历行的地方,定义在外面可能出现样式渲染错误
|
CellStyle cellStyle = workbook.createCellStyle();
|
cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
|
cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
|
cellStyle.setBorderRight(CellStyle.BORDER_THIN);
|
cellStyle.setBorderTop(CellStyle.BORDER_THIN);
|
cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
|
cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
|
cellStyle.setWrapText(true);
|
|
|
//每次完成一个删除一个
|
List<GrainPoint> listPoints = data.getListPoints();
|
|
//最大获取行数
|
int maxRowSize = oldSheet.getPhysicalNumberOfRows() - 1;
|
for (int i = 0; i < maxRowSize; i++) {
|
Row newRow = newSheet.createRow(i);
|
Row oldRow = oldSheet.getRow(i);
|
newRow.setHeight((short) (20 * 20));
|
|
int maxColSize = oldRow.getPhysicalNumberOfCells();
|
|
String oldCellValue;
|
for (int j = 0; j < maxColSize; j++) {
|
Cell newCell = newRow.createCell(j);
|
Cell oldCell = oldRow.getCell(j);
|
if (oldCell == null) {
|
continue;
|
}
|
oldCellValue = oldCell.getStringCellValue();
|
|
newCell.setCellValue(getNewCellValue(oldCellValue, listPoints, data, depot));
|
|
newCell.setCellStyle(cellStyle);
|
}
|
}
|
return newSheet;
|
}
|
|
|
/**
|
* 根据单元格标签替换值，需要替换的标记为c_
|
*
|
* @param oldCellValue
|
* @param data
|
* @return
|
*/
|
public String getNewCellValue(String oldCellValue, List<GrainPoint> listPoints, GrainData data, Depot depot) {
|
|
try {
|
|
if (null == oldCellValue) return null;
|
|
if (!oldCellValue.startsWith("c_")) return oldCellValue;
|
|
if ("c_title".equals(oldCellValue)) return "粮情报表";
|
if ("c_depot".equals(oldCellValue)) return depot.getName() + "粮情报表";
|
if ("c_weather".equals(oldCellValue)) return data.getWeather();
|
if ("c_checkDate".equals(oldCellValue))
|
return DateFormatUtils.format(data.getReceiveDate(), "yyyy-MM-dd HH:mm");
|
if ("c_ori".equals(oldCellValue)) return "";
|
if ("c_tIn".equals(oldCellValue)) return getStrValue(data.getTempIn());
|
if ("c_tOut".equals(oldCellValue)) return getStrValue(data.getTempOut());
|
if ("c_hIn".equals(oldCellValue)) return getStrValue(data.getHumidityIn());
|
if ("c_hOut".equals(oldCellValue)) return getStrValue(data.getHumidityOut());
|
|
if ("c_tempMax".equals(oldCellValue)) return getStrValue(data.getTempMax());
|
if ("c_tempMin".equals(oldCellValue)) return getStrValue(data.getTempMin());
|
if ("c_tempAve".equals(oldCellValue)) return getStrValue(data.getTempAve());
|
if ("c_depotType".equals(oldCellValue)) return depot.getDepotTypeName();
|
if ("c_storeDate".equals(oldCellValue))
|
return null == depot.getStoreDate() ? null : DateFormatUtils.format(depot.getStoreDate(), "yyyy-MM-dd");
|
if ("c_foodVariety".equals(oldCellValue)) return depot.getFoodVarietyName();
|
if ("c_storageReal".equals(oldCellValue)) return getStrValue(NumberUtil.keepPrecision(depot.getStorageReal()/1000, 2));
|
if ("c_foodLocation".equals(oldCellValue)) return depot.getFoodLocation();
|
if ("c_bulkWeight".equals(oldCellValue)) return getStrValue(depot.getBulkWeight());
|
if ("c_checkUser".equals(oldCellValue)) return data.getCheckUser();
|
if ("c_storeKeeper".equals(oldCellValue)) return depot.getStoreKeeperName();
|
if ("c_createDate".equals(oldCellValue)) return DateFormatUtils.format(new Date(), "yyyy-MM-dd HH:mm");
|
|
if (oldCellValue.startsWith("c_lay_")) {
|
String[] tempAttr = oldCellValue.split("_");
|
int layNum = Integer.valueOf(tempAttr[3]);
|
for (GrainLay lay : data.getListLays()) {
|
if (lay.getFz() == layNum) {
|
if (oldCellValue.indexOf("max") > 0) return getStrValue(lay.getTempMax());
|
if (oldCellValue.indexOf("min") > 0) return getStrValue(lay.getTempMin());
|
if (oldCellValue.indexOf("ave") > 0) return getStrValue(lay.getTempAve());
|
}
|
}
|
return oldCellValue;
|
}
|
|
|
//平房仓的获取方式
|
|
if (DepotType.TYPE_01.getCode().equals(depot.getDepotType())) {
|
return getNewCellValue01(data, oldCellValue, listPoints);
|
}
|
|
if (DepotType.TYPE_02.getCode().equals(depot.getDepotType())) {
|
return getNewCellValue02(data, oldCellValue, listPoints);
|
}
|
|
if (DepotType.TYPE_03.getCode().equals(depot.getDepotType())) {
|
return getNewCellValue02(data, oldCellValue, listPoints);
|
}
|
|
if (DepotType.TYPE_04.getCode().equals(depot.getDepotType())) {
|
return getNewCellValue02(data, oldCellValue, listPoints);
|
}
|
|
} catch (Exception e) {
|
log.error("---------批量导出粮情EXCEL---赋值出错---{}", e);
|
return oldCellValue;
|
}
|
|
return null;
|
}
|
|
|
/**
|
* @Desc: 筒仓的粮温点规则c_x_y_z 其中x = 表示圈数，y=总根数，z= 当前层
|
* @author: Andy
|
* @update-time: 2022/12/12
|
*/
|
private String getNewCellValue02(GrainData data, String oldCellValue, List<GrainPoint> listPoints) {
|
//单元格赋值 c_x_y_z 其中x = 表示圈数，y=总根数，z= 当前层
|
String[] attr = oldCellValue.split("_");
|
int x = Integer.valueOf(attr[1]), y = Integer.valueOf(attr[2]), z = Integer.valueOf(attr[3]);
|
|
x = x - 1;
|
y = y - 1;
|
GrainPoint point;
|
for (int i = 0; i < listPoints.size(); i++) {
|
point = listPoints.get(i);
|
|
if (point.getX() == x && point.getY() == y && point.getFz() == z) {
|
listPoints.remove(i);
|
return GrainDataBuilder.renderTempValue(point.getTemp());
|
}
|
}
|
|
return "--";
|
}
|
|
|
private String getNewCellValue01(GrainData data, String oldCellValue, List<GrainPoint> listPoints) {
|
String cable = data.getCable();
|
String[] attrCable = cable.split("-");
|
int numX = Integer.valueOf(attrCable[2]);
|
|
|
//单元格赋值 c_层_行_列
|
String[] attr = oldCellValue.split("_");
|
int lay = Integer.valueOf(attr[1]), row = Integer.valueOf(attr[2]), col = Integer.valueOf(attr[3]);
|
|
row = row - 1;
|
col = numX - col;
|
|
GrainPoint point;
|
for (int i = 0; i < listPoints.size(); i++) {
|
point = listPoints.get(i);
|
if (point.getFz() == lay && point.getY() == row && point.getX() == col) {
|
listPoints.remove(i);
|
return GrainDataBuilder.renderTempValue(point.getTemp());
|
}
|
}
|
|
return "--";
|
}
|
|
private static String getStrValue(Double tempIn) {
|
if (null == tempIn) return null;
|
return tempIn + "";
|
}
|
}
|
