package jnpf.file.util;
|
|
import cn.afterturn.easypoi.excel.ExcelExportUtil;
|
import cn.afterturn.easypoi.excel.entity.ExportParams;
|
import cn.afterturn.easypoi.excel.entity.ImportParams;
|
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
|
import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
|
import feign.Response;
|
import jnpf.base.ActionResult;
|
import jnpf.base.vo.DownloadVO;
|
import jnpf.config.ConfigValueUtil;
|
import jnpf.constant.FileTypeConstant;
|
import jnpf.constant.MsgCode;
|
import jnpf.excel.ExcelExportStyler;
|
import jnpf.excel.ExcelHelper;
|
import jnpf.exception.DataException;
|
import jnpf.file.FileApi;
|
import jnpf.file.FileUploadApi;
|
import jnpf.model.ExcelModel;
|
import jnpf.model.ExcelViewFieldModel;
|
import jnpf.model.upload.UploadFileModel;
|
import jnpf.util.DateUtil;
|
import jnpf.util.*;
|
import jnpf.util.context.SpringContext;
|
import lombok.Cleanup;
|
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
|
import org.apache.poi.ss.usermodel.*;
|
import org.apache.poi.ss.util.CellRangeAddress;
|
import org.apache.poi.ss.util.CellRangeAddressList;
|
import org.apache.poi.xssf.usermodel.*;
|
import org.dromara.x.file.storage.core.FileInfo;
|
import org.springframework.web.multipart.MultipartFile;
|
|
import java.io.File;
|
import java.io.IOException;
|
import java.io.InputStream;
|
import java.nio.file.StandardCopyOption;
|
import java.util.ArrayList;
|
import java.util.HashMap;
|
import java.util.List;
|
import java.util.Map;
|
|
/**
|
* 系统模块得导入导出公共方法
|
*
|
* @author JNPF开发平台组
|
* @version v5.0.0
|
* @copyright 引迈信息技术有限公司
|
* @date 2024/5/31 13:50:52
|
*/
|
public class ExcelTool {
|
|
private static ConfigValueUtil configValueUtil = SpringContext.getBean(ConfigValueUtil.class);
|
private static FileUploadApi fileUploadApi = SpringContext.getBean(FileUploadApi.class);
|
private static FileApi fileApi = SpringContext.getBean(FileApi.class);
|
|
public static ActionResult<Object> uploader() {
|
List<MultipartFile> list = UpUtil.getFileAll();
|
MultipartFile file = list.get(0);
|
if (file.getOriginalFilename().endsWith(".xlsx") || file.getOriginalFilename().endsWith(".xls")) {
|
String filePath = XSSEscape.escape(configValueUtil.getTemporaryFilePath());
|
String fileName = XSSEscape.escape(RandomUtil.uuId() + "." + UpUtil.getFileType(file));
|
//上传文件
|
FileInfo fileInfo = fileUploadApi.uploadFile(file, filePath, fileName);
|
DownloadVO vo = DownloadVO.builder().build();
|
vo.setName(fileInfo.getFilename());
|
return ActionResult.success(vo);
|
} else {
|
return ActionResult.fail(MsgCode.ETD110.get());
|
}
|
}
|
|
public static DownloadVO getImportTemplate(String temporaryFilePath, String templateName, Map<String, String> keyMap, List<Map<String, Object>> list, ExcelModel excelModel) {
|
DownloadVO vo = DownloadVO.builder().build();
|
//主表对象
|
List<ExcelExportEntity> entitys = new ArrayList<>();
|
//以下添加字段
|
for (String key : keyMap.keySet()) {
|
String name = keyMap.get(key);
|
entitys.add(new ExcelExportEntity(name + "(" + key + ")", key));
|
}
|
|
ExportParams exportParams = new ExportParams(null, templateName);
|
exportParams.setType(ExcelType.XSSF);
|
try {
|
@Cleanup Workbook workbook = new HSSFWorkbook();
|
if (entitys.size() > 0) {
|
if (list.size() == 0) {
|
list.add(new HashMap<>());
|
}
|
ExcelHelper helper = new ExcelHelper();
|
exportParams.setStyle(ExcelExportStyler.class);
|
workbook = ExcelExportUtil.exportExcel(exportParams, entitys, list);
|
helper.init(workbook, exportParams, entitys, excelModel);
|
helper.doPreHandle();
|
helper.doPostHandle();
|
}
|
|
String fileName = templateName + "导入模板" + ".xls";
|
MultipartFile multipartFile = ExcelUtil.workbookToCommonsMultipartFile(workbook, fileName);
|
multipartFile = setTopTitle(excelModel, multipartFile, fileName);
|
FileInfo fileInfo = fileUploadApi.uploadFile(multipartFile, temporaryFilePath, fileName);
|
vo.setName(fileInfo.getFilename());
|
vo.setUrl(UploaderUtil.uploaderFile(fileInfo.getFilename() + "#" + FileTypeConstant.TEMPORARY) + "&name=" + fileName);
|
} catch (Exception e) {
|
e.printStackTrace();
|
}
|
return vo;
|
}
|
|
/**
|
* 设置表头（翻译标记用。）
|
*
|
* @param excelModel
|
* @param multipartFile
|
* @param fileName
|
* @return
|
* @throws IOException
|
*/
|
private static MultipartFile setTopTitle(ExcelModel excelModel, MultipartFile multipartFile, String fileName) throws IOException {
|
if (excelModel.isHasHeader()) {
|
InputStream inputStream = multipartFile.getInputStream();
|
@Cleanup XSSFWorkbook workbook2 = new XSSFWorkbook(inputStream);
|
XSSFSheet sheetAt = workbook2.getSheetAt(0);
|
short lastCellNum = sheetAt.getRow(0).getLastCellNum();
|
sheetAt.shiftRows(0, 1, 1);
|
XSSFRow row = sheetAt.createRow(0);
|
XSSFCell cell = row.createCell(0);
|
//样式设置
|
CellStyle style = workbook2.createCellStyle();
|
style.setAlignment(HorizontalAlignment.LEFT);
|
style.setVerticalAlignment(VerticalAlignment.TOP);
|
style.setWrapText(true);
|
cell.setCellStyle(style);
|
//行高设置
|
row.setHeightInPoints(54);
|
|
Font font = workbook2.createFont();
|
font.setColor(IndexedColors.BLACK.getIndex());
|
font.setBold(true);
|
XSSFRichTextString textString = new XSSFRichTextString("填写说明:\n" +
|
"（1）翻译标记命名规则：只能输入字母、数字、点、横线和下划线，且以字母开头；\n" +
|
"（2）翻译标记全局唯一，不可重复；\n" +
|
"（3）翻译语言必须填写一项；");
|
|
textString.applyFont(0, 5, font);
|
cell.setCellValue(textString);
|
|
//合并单元格
|
sheetAt.addMergedRegionUnsafe(new CellRangeAddress(0, 0, 0, lastCellNum - 1));
|
|
//冻结行下移
|
sheetAt.createFreezePane(0, 2);
|
//校验规则下移
|
List<XSSFDataValidation> dataValidations = sheetAt.getDataValidations();
|
List<DataValidation> dvNew = new ArrayList<>();
|
for (DataValidation dataValidation : dataValidations) {
|
DataValidationConstraint constraint = dataValidation.getValidationConstraint();
|
CellRangeAddressList regions = dataValidation.getRegions();
|
CellRangeAddress crd = regions.getCellRangeAddresses()[0];
|
CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(crd.getFirstRow() + 1, crd.getLastRow() + 1, crd.getFirstColumn(), crd.getLastColumn());
|
DataValidationHelper helper = sheetAt.getDataValidationHelper();
|
dvNew.add(helper.createValidation(constraint, cellRangeAddressList));
|
}
|
sheetAt.getCTWorksheet().unsetDataValidations();
|
for (DataValidation item : dvNew) {
|
sheetAt.addValidationData(item);
|
}
|
multipartFile = ExcelUtil.workbookToCommonsMultipartFile(workbook2, fileName);
|
}
|
return multipartFile;
|
}
|
|
public static List<ExcelExportEntity> getImportExcelExportEntityList(boolean isError, Map<String, String> keyMap) {
|
List<ExcelExportEntity> entitys = new ArrayList<>();
|
if (isError) {
|
entitys.add(new ExcelExportEntity("异常原因(errorsInfo)", "errorsInfo"));
|
}
|
for (String key : keyMap.keySet()) {
|
String name = keyMap.get(key);
|
entitys.add(new ExcelExportEntity(name + "(" + key + ")", key));
|
}
|
return entitys;
|
}
|
|
|
/**
|
* 导出表格方法
|
*
|
* @param temporaryFilePath
|
* @param sheetName excel名称
|
* @param keyMap 字段key-name
|
* @param list 数据
|
* @param excelModel 表格参数
|
* @return
|
*/
|
public static DownloadVO creatModelExcel(String temporaryFilePath, String sheetName, Map<String, String> keyMap, List<Map<String, Object>> list, ExcelModel excelModel) {
|
List<String> keys = excelModel.getSelectKey();
|
DownloadVO vo = DownloadVO.builder().build();
|
List<ExcelExportEntity> entitys = new ArrayList<>();
|
for (String key : keys) {
|
String name = keyMap.get(key);
|
entitys.add(new ExcelExportEntity(name, key));
|
}
|
ExportParams exportParams = new ExportParams(null, "表单信息");
|
exportParams.setStyle(ExcelExportStyler.class);
|
exportParams.setType(ExcelType.XSSF);
|
try {
|
@Cleanup Workbook workbook = new HSSFWorkbook();
|
if (entitys.size() > 0) {
|
//去除空数据
|
List<Map<String, Object>> dataList = new ArrayList<>();
|
for (Map<String, Object> map : list) {
|
int i = 0;
|
for (String key : keys) {
|
Object o = map.get(key);
|
if (o != null) {
|
i++;
|
}
|
}
|
if (i > 0) {
|
dataList.add(map);
|
}
|
}
|
if (dataList.size() == 0) {
|
dataList.add(new HashMap<>());
|
}
|
workbook = ExcelExportUtil.exportExcel(exportParams, entitys, dataList);
|
ExcelHelper helper = new ExcelHelper();
|
helper.init(workbook, exportParams, entitys, excelModel);
|
helper.doPreHandle();
|
helper.doPostHandle();
|
}
|
String fileName = sheetName + "_" + DateUtil.dateNow("yyyyMMddHHmmss") + ".xls";
|
MultipartFile multipartFile = ExcelUtil.workbookToCommonsMultipartFile(workbook, fileName);
|
//上传文件
|
FileInfo fileInfo = fileUploadApi.uploadFile(multipartFile, temporaryFilePath, fileName);
|
|
vo.setName(fileInfo.getFilename());
|
vo.setUrl(UploaderUtil.uploaderFile(fileInfo.getFilename() + "#" + FileTypeConstant.TEMPORARY) + "&name=" + fileName);
|
} catch (Exception e) {
|
e.printStackTrace();
|
}
|
return vo;
|
}
|
|
|
public static DownloadVO exportExceptionReport(String temporaryFilePath, String menuFullName, Map<String, String> keyMap, List<Map<String, Object>> dataList, ExcelModel excelModel) {
|
DownloadVO vo = DownloadVO.builder().build();
|
try {
|
List<ExcelExportEntity> entitys = ExcelTool.getImportExcelExportEntityList(true, keyMap);
|
@Cleanup Workbook workbook = new HSSFWorkbook();
|
ExportParams exportParams = new ExportParams(null, "错误报告");
|
exportParams.setFreezeCol(1);
|
exportParams.setType(ExcelType.XSSF);
|
exportParams.setStyle(ExcelExportStyler.class);
|
workbook = ExcelExportUtil.exportExcel(exportParams, entitys, dataList);
|
ExcelHelper helper = new ExcelHelper();
|
helper.init(workbook, exportParams, entitys, excelModel);
|
helper.doPreHandle();
|
helper.doPostHandle();
|
|
String fileName = menuFullName + "导入模板错误报告_" + DateUtil.dateNow("yyyyMMddHHmmss") + ".xls";
|
MultipartFile multipartFile = ExcelUtil.workbookToCommonsMultipartFile(workbook, fileName);
|
//上传文件
|
FileInfo fileInfo = fileUploadApi.uploadFile(multipartFile, temporaryFilePath, fileName);
|
|
vo.setName(fileInfo.getFilename());
|
vo.setUrl(UploaderUtil.uploaderFile(fileInfo.getFilename() + "#" + FileTypeConstant.TEMPORARY) + "&name=" + fileName);
|
} catch (Exception e) {
|
e.printStackTrace();
|
}
|
return vo;
|
}
|
|
public static Map<String, Object> importPreview(String temporaryFilePath, String fileName, Map<String, String> keyMap) {
|
return importPreview(temporaryFilePath, fileName, keyMap, 0, 1);
|
}
|
|
public static Map<String, Object> importPreview(String temporaryFilePath, String fileName, Map<String, String> keyMap, Integer titleIndex, Integer headerRows) {
|
Map<String, Object> headAndDataMap = new HashMap<>(2);
|
String filePath = fileApi.getLocalBasePath() + temporaryFilePath;
|
try(Response response = fileUploadApi.downFile(new UploadFileModel(filePath, configValueUtil.getTemporaryFilePath(), fileName));
|
InputStream inputStream = response.body().asInputStream()){
|
cn.hutool.core.io.FileUtil.copyFile(inputStream, new File(filePath + fileName), StandardCopyOption.REPLACE_EXISTING);
|
} catch (IOException e) {
|
throw new RuntimeException(e);
|
}
|
|
File temporary = new File(XSSEscape.escapePath(filePath + fileName));
|
ImportParams params = new ImportParams();
|
params.setTitleRows(titleIndex);
|
params.setHeadRows(headerRows);
|
params.setNeedVerify(true);
|
List<ExcelViewFieldModel> columns = new ArrayList<>();
|
for (String key : keyMap.keySet()) {
|
columns.add(new ExcelViewFieldModel(key, keyMap.get(key)));
|
}
|
List<Map<String, Object>> jsonToList;
|
List<Map> excelDataList;
|
try {
|
jsonToList = JsonUtil.getJsonToList(JsonUtil.getListToJsonArray(columns));
|
InputStream inputStream = ExcelUtil.solveOrginTitle(temporary, titleIndex, headerRows);
|
excelDataList = ExcelUtil.importExcelByInputStream(inputStream, titleIndex, headerRows, Map.class);
|
} catch (Exception e) {
|
e.printStackTrace();
|
throw new DataException(MsgCode.VS407.get());
|
}
|
List<Map<String, Object>> resultList = getResultList(excelDataList, new ArrayList<>(keyMap.keySet()));
|
if (resultList.size() > 1000) {
|
throw new DataException(MsgCode.ETD117.get());
|
}
|
headAndDataMap.put("dataRow", resultList);
|
headAndDataMap.put("headerRow", jsonToList);
|
return headAndDataMap;
|
}
|
|
/**
|
* key字段处理
|
*
|
* @param excelDataList
|
* @param selectKey
|
* @return
|
*/
|
public static List<Map<String, Object>> getResultList(List<Map> excelDataList, List<String> selectKey) {
|
List<Map<String, Object>> allDataList = new ArrayList<>();
|
for (int z = 0; z < excelDataList.size(); z++) {
|
Map<String, Object> dataMap = new HashMap<>(16);
|
Map m = excelDataList.get(z);
|
List excelEntrySet = new ArrayList<>(m.entrySet());
|
//取出的数据最后一行 不带行标签
|
int resultsize = z == excelDataList.size() - 1 ? excelEntrySet.size() : m.containsKey("excelRowNum") ? excelEntrySet.size() - 1 : excelEntrySet.size();
|
if (resultsize < selectKey.size()) {
|
throw new DataException(MsgCode.VS407.get());
|
}
|
for (int e = 0; e < resultsize; e++) {
|
Map.Entry o = (Map.Entry) excelEntrySet.get(e);
|
String entryKey = o.getKey().toString();
|
String substring = entryKey.substring(entryKey.lastIndexOf("(") + 1, entryKey.lastIndexOf(")"));
|
boolean contains = selectKey.contains(substring);
|
if (!contains) {
|
throw new DataException(MsgCode.VS407.get());
|
}
|
dataMap.put(substring, o.getValue());
|
}
|
allDataList.add(dataMap);
|
}
|
return allDataList;
|
}
|
}
|
