jnpf-common-v6x/jnpf-boot-common/jnpf-common-office/target/classes/jnpf/util/ExcelUtil.java

package jnpf.util;

import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import jakarta.servlet.http.HttpServletResponse;
import jnpf.exception.ImportException;
import jnpf.support.MyStandardMultipartFile;
import lombok.Cleanup;
import org.apache.catalina.core.ApplicationPart;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.tomcat.util.http.fileupload.FileItem;
import org.apache.tomcat.util.http.fileupload.FileItemFactory;
import org.apache.tomcat.util.http.fileupload.disk.DiskFileItemFactory;
import org.springframework.web.multipart.MultipartFile;

import java.io.*;
import java.net.URLEncoder;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @author JNPF开发平台组
 * @version V3.1.0
 * @copyright 引迈信息技术有限公司
 * @date 2021/3/16 10:51
 */
public class ExcelUtil {

    /**
     * Workbook 转 MultipartFile
     *
     * @param workbook excel文档
     * @param fileName 文件名
     * @return
     */
    public static MultipartFile workbookToCommonsMultipartFile(Workbook workbook, String fileName) {
        //Workbook转FileItem
        FileItemFactory factory = new DiskFileItemFactory(16, null);
        FileItem fileItem = factory.createItem("textField", "text/plain", true, fileName);
        try {
            OutputStream os = fileItem.getOutputStream();
            workbook.write(os);
            os.close();
            //FileItem转MultipartFile
            MultipartFile multipartFile = new MyStandardMultipartFile(new ApplicationPart(fileItem, null), fileName);
            return multipartFile;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 下载excel
     *
     * @param fileName excel名称
     * @param workbook
     */
    public static void dowloadExcel(Workbook workbook, String fileName) {
        try {
            HttpServletResponse response = ServletUtil.getResponse();
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition",
                    "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
            workbook.write(response.getOutputStream());
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * excel转成实体
     *
     * @param filePath   路径
     * @param titleRows  行
     * @param headerRows 列
     * @param pojoClass  实体
     * @param <T>
     * @return
     */
    public static <T> List<T> importExcel(String filePath, Integer titleRows, Integer headerRows, Class<T> pojoClass) {
        if (StringUtils.isBlank(filePath)) {
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        List<T> list = null;
        try {
            list = ExcelImportUtil.importExcel(new File(jnpf.util.XSSEscape.escapePath(filePath)), pojoClass, params);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * excel转成实体
     *
     * @param file       文件
     * @param titleRows  行
     * @param headerRows 列
     * @param pojoClass  实体
     * @param <T>
     * @return
     */
    public static <T> List<T> importExcel(File file, Integer titleRows, Integer headerRows, Class<T> pojoClass) throws ImportException {
        if (file == null) {
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        List<T> list = null;
        try {
            list = ExcelImportUtil.importExcel(file, pojoClass, params);
        } catch (Exception e) {
            throw new ImportException(e.getMessage());
        }
        return list;
    }

    /**
     * excel转成实体
     *
     * @param inputStream 文件流
     * @param titleRows   行
     * @param headerRows  列
     * @param pojoClass   实体
     * @param <T>
     * @return
     */
    public static <T> List<T> importExcelByInputStream(InputStream inputStream, Integer titleRows, Integer headerRows, Class<T> pojoClass) throws Exception {
        if (inputStream == null) {
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        List<T> list = null;
        list = ExcelImportUtil.importExcel(inputStream, pojoClass, params);
        return list;
    }

    /**
     * excel转成实体
     *
     * @param file       文件
     * @param titleRows  行
     * @param headerRows 列
     * @param pojoClass  实体
     * @param <T>
     * @return
     */
    public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass) {
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        List<T> list = null;
        try {
            list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 备用方案，读取不到时间暂用此方法
     * 通过基础poi读取NUMERIC转换成时间格式
     *
     * @param
     * @return
     * @copyright 引迈信息技术有限公司
     * @date 2023/2/3
     */
    public static void imoportExcelToMap(File file, Integer titleIndex, List<Map> excelDataList) {
        List<Map<String, Object>> mapList = new ArrayList<>();
        FileInputStream inputStream = null;
        try {
            String fileName = file.getName();
            @Cleanup Workbook workbook = null;
            inputStream = new FileInputStream(file);
            try {
                workbook = new HSSFWorkbook(inputStream);
            } catch (Exception e) {
                inputStream = new FileInputStream(file);
                workbook = new XSSFWorkbook(inputStream);
            }

            Sheet sheet = workbook.getSheetAt(0);
            Row titleRow = sheet.getRow(titleIndex - 1);

            for (int i = titleIndex; i < sheet.getPhysicalNumberOfRows(); i++) {
                Row row = sheet.getRow(i);
                Map<String, Object> map = new HashMap<>();
                for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) {
                    Cell cell = row.getCell(j);
                    Cell titleCell = titleRow.getCell(j);
                    if (cell != null && org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
                        short format = cell.getCellStyle().getDataFormat();
                        if (cell.getDateCellValue() != null && format > 0) {
                            //表头数据
                            String titleName = titleCell.getStringCellValue();
                            if (StringUtil.isEmpty(titleName)) {
                                titleName = sheet.getRow(titleIndex - 2).getCell(j).getStringCellValue();
                            }
                            //单元格内容
                            Date dateCellValue = cell.getDateCellValue();
                            String valueName = DateUtil.daFormat(dateCellValue);
                            map.put(titleName, valueName);
                        }
                    }
                }
                mapList.add(map);
            }
            //基础poi读取到时间同步到easypoi读取到的数据中去
            if (!CollectionUtils.sizeIsEmpty(mapList)) {
                for (int n = 0; n < mapList.size(); n++) {
                    Map<String, Object> a = mapList.get(n);
                    Map b = excelDataList.get(n);
                    if (a != null) {
                        for (String key : a.keySet()) {
                            if (b.containsKey(key)) b.put(key, a.get(key));
                        }
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                inputStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    public static InputStream solveOrginTitle(File file, Integer rowLen) throws IOException {
        return solveOrginTitle(file, 0, rowLen);
    }

    public static InputStream solveOrginTitle(File file, Integer titleIndex, Integer rowLen) throws IOException {
        @Cleanup Workbook workbook = WorkbookFactory.create(file);
        Sheet sheet = workbook.getSheetAt(0);

        for (int i = titleIndex; i < titleIndex + rowLen; i++) {
            Row headerRow = sheet.getRow(i);
            int lastCellNum = headerRow.getLastCellNum();
            for (int j = 0; j < lastCellNum; j++) {
                Cell cell = headerRow.getCell(j);
                if (cell == null) {
                    continue;
                }
                String name = cell.getStringCellValue();
                Comment cellComment = cell.getCellComment();
                //都没有标题就跳过
                if (StringUtils.isEmpty(name) && cellComment == null) {
                    continue;
                }
                //有标题没批注，说明表头有问题
                if (Objects.isNull(cellComment)) {
                    throw new RuntimeException();
                }
                name = name + "(" + cellComment.getString().getString() + ")";
                cell.setCellValue(name);
            }
        }
        MultipartFile multipartFile = ExcelUtil.workbookToCommonsMultipartFile(workbook, file.getName());
        InputStream inputStream = multipartFile.getInputStream();
        return inputStream;
    }

    /**
     * 判断字符串内有没有括号
     *
     * @param str
     * @return
     */
    public static boolean matcherFind(String str) {
        Pattern pattern = Pattern.compile("(.*)\\((.*?)\\)");
        Matcher matcher = pattern.matcher(str);
        return matcher.find();
    }

    /**
     * 获取标题括号内得id
     *
     * @param cellValue
     * @return
     */
    public static String getIdFromCellValue(String cellValue) {
        String id = "";
        if (matcherFind(cellValue)) {
            id = cellValue.substring(cellValue.lastIndexOf("(") + 1, cellValue.lastIndexOf(")"));
        }
        return id;
    }

}