From 34981c30a78e8bbd7791131059a9210f9928b62c Mon Sep 17 00:00:00 2001
From: 刘光辉 <347230014@qq.com>
Date: 星期四, 17 九月 2026 09:24:09 +0800
Subject: [PATCH] Merge remote-tracking branch 'origin/master' into master
---
jnpf-file/jnpf-file-api/src/main/java/jnpf/file/util/ExcelTool.java | 358 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1 files changed, 358 insertions(+), 0 deletions(-)
diff --git a/jnpf-file/jnpf-file-api/src/main/java/jnpf/file/util/ExcelTool.java b/jnpf-file/jnpf-file-api/src/main/java/jnpf/file/util/ExcelTool.java
new file mode 100644
index 0000000..8a06d9a
--- /dev/null
+++ b/jnpf-file/jnpf-file-api/src/main/java/jnpf/file/util/ExcelTool.java
@@ -0,0 +1,358 @@
+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.entity.FileParameter;
+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.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.*;
+
+/**
+ * 绯荤粺妯″潡寰楀鍏ュ鍑哄叕鍏辨柟娉�
+ *
+ * @author JNPF寮�鍙戝钩鍙扮粍
+ * @version v5.0.0
+ * @copyright 寮曡繄淇℃伅鎶�鏈湁闄愬叕鍙�
+ * @date 2024/5/31 13:50:52
+ */
+public class ExcelTool {
+
+ ExcelTool(){
+
+ }
+ public static final String NAME="&name=";
+
+ 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);
+ String originalFilename = file.getOriginalFilename();
+ if (null != originalFilename && (originalFilename.endsWith(".xlsx") || originalFilename.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 (Map.Entry<String, String> entry : keyMap.entrySet()) {
+ String key = entry.getKey();
+ String name = keyMap.get(key);
+ entitys.add(new ExcelExportEntity(name + "(" + key + ")", key));
+ }
+
+ ExportParams exportParams = new ExportParams(null, templateName);
+ exportParams.setType(ExcelType.XSSF);
+ exportParams.setStyle(ExcelExportStyler.class);
+ if (list.isEmpty()) {
+ list.add(new HashMap<>());
+ }
+
+ try (Workbook workbook = ExcelExportUtil.exportExcel(exportParams, entitys, list)) {
+ ExcelHelper helper = new ExcelHelper();
+ 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 (Map.Entry<String, String> entry : keyMap.entrySet()) {
+ String key = entry.getKey();
+ 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);
+ List<Map<String, Object>> dataList = new ArrayList<>();
+ if (!entitys.isEmpty()) {
+ 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.isEmpty()) {
+ dataList.add(new HashMap<>());
+ }
+ }
+
+ try (Workbook 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() + "#" + "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();
+ List<ExcelExportEntity> entitys = ExcelTool.getImportExcelExportEntityList(true, keyMap);
+ ExportParams exportParams = new ExportParams(null, "閿欒鎶ュ憡");
+ exportParams.setFreezeCol(1);
+ exportParams.setType(ExcelType.XSSF);
+ exportParams.setStyle(ExcelExportStyler.class);
+ try(Workbook 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 IllegalArgumentException(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 (Map.Entry<String, String> entry : keyMap.entrySet()) {
+ String key = entry.getKey();
+ columns.add(new ExcelViewFieldModel(key, keyMap.get(key)));
+ }
+ List<Map<String, Object>> jsonToList;
+ List<Map<String, Object>> excelDataList;
+ try {
+ jsonToList = JsonUtil.getJsonToList(JsonUtil.getListToJsonArray(columns));
+ InputStream inputStream = ExcelUtil.solveOrginTitle(temporary, titleIndex, headerRows);
+ excelDataList = ExcelUtil.getMapByInputStream(inputStream, titleIndex, headerRows);
+ } 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<String, Object>> 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<String, Object> m = excelDataList.get(z);
+ Set<String> keySet = m.keySet();
+ //鍙栧嚭鐨勬暟鎹渶鍚庝竴琛� 涓嶅甫琛屾爣绛�
+ int i = m.containsKey("excelRowNum") ? keySet.size() - 1 : keySet.size();
+ int resultsize = z == excelDataList.size() - 1 ? keySet.size() : i;
+ if (resultsize < selectKey.size()) {
+ throw new DataException(MsgCode.VS407.get());
+ }
+
+ for (Map.Entry<String, Object> item : m.entrySet()) {
+ String entryKey = item.getKey();
+ Object o = item.getValue();
+ if (entryKey.contains("excelRowNum")) {
+ continue;
+ }
+ 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);
+ }
+ allDataList.add(dataMap);
+ }
+ return allDataList;
+ }
+}
--
Gitblit v1.8.0