KprAlamBizPlugin/src/main/java/com/shkpr/service/alambizplugin/commtools/ExcelUtils.java

package com.shkpr.service.alambizplugin.commtools;

import com.global.base.log.LogLevelFlag;
import com.global.base.log.LogPrintMgr;
import com.shkpr.service.alambizplugin.annotation.ExcelMapping;
import com.shkpr.service.alambizplugin.constants.ExcelEnum;
import com.shkpr.service.alambizplugin.constants.LogFlagBusiType;
import org.apache.commons.collections4.map.CaseInsensitiveMap;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.math.BigDecimal;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.function.Function;
import java.util.stream.Collectors;
import java.util.stream.IntStream;

/**
 * excel工具类
 *
 * @author 欧阳劲驰
 * @serial 1.0.0
 */
public class ExcelUtils {
    /**
     * log
     */
    private static final String mStrClassName = "ExcelUtils";
    private static final String mBizType = LogFlagBusiType.BUSI_GIS_SURVEY.toStrValue();
    /**
     * 默认页名
     */
    private static final String DEFAULT_SHEET_NAME = "页1";

    /**
     * 解析excel文件
     *
     * @param path         文件地址
     * @param headerRowNum 表头行
     * @param dataRowNum   数据行
     * @return 数据(key : 页名, v : 数据)
     */
    public static Map<String, List<Map<String, String>>> parseExcelFile(String path, Integer headerRowNum, Integer dataRowNum) throws InterruptedException {
        //excel枚举
        ExcelEnum excelEnum = null;
        //文件后缀
        String ext = path.substring(path.lastIndexOf("."));
        //根据后缀名指定枚举
        if (".xls".equalsIgnoreCase(ext)) excelEnum = ExcelEnum.XLS;
        else if (".xlsx".equalsIgnoreCase(ext)) excelEnum = ExcelEnum.XLS;
        if (excelEnum == null) return null;
        //解析文件
        try {
            return parseExcelFile(Files.newInputStream(Paths.get(path)), excelEnum, headerRowNum, dataRowNum);
        } catch (IOException e) {
            LogPrintMgr.getInstance().printLogMsg(LogLevelFlag.LOG_ERROR, mBizType, mStrClassName
                    , String.format("excel文件解释失败 msg:%s", e.getMessage())
            );
            return null;
        }
    }

    /**
     * 解析excel文件
     *
     * @param inputStream  输入流
     * @param excelEnum    excel类型
     * @param headerRowNum 表头行
     * @param dataRowNum   数据行
     * @return 数据(key : 页名, v : 数据)
     */
    public static Map<String, List<Map<String, String>>> parseExcelFile(InputStream inputStream, ExcelEnum excelEnum
            , Integer headerRowNum, Integer dataRowNum) throws InterruptedException {
        if (inputStream == null || excelEnum == null || excelEnum == ExcelEnum.CSV) return null;
        //读取输入流
        try (Workbook workbook = excelEnum == ExcelEnum.XLSX ? new XSSFWorkbook(inputStream) : new HSSFWorkbook(inputStream)) {
            if (workbook.getNumberOfSheets() <= 0) return null;
            //结果
            Map<String, List<Map<String, String>>> results = new HashMap<>(workbook.getNumberOfSheets());
            //遍历页,并解析
            for (Sheet sheet : workbook) results.put(sheet.getSheetName(), parseSheet(sheet, headerRowNum, dataRowNum));
            return results;
        } catch (IOException e) {
            LogPrintMgr.getInstance().printLogMsg(LogLevelFlag.LOG_ERROR, mBizType, mStrClassName
                    , String.format("excel文件解释失败 msg:%s", e.getMessage())
            );
            return null;
        }
    }

    /**
     * 解析页
     *
     * @param sheet        页
     * @param headerRowNum 表头行
     * @param dataRowNum   数据行
     * @return 数据
     */
    private static List<Map<String, String>> parseSheet(Sheet sheet, Integer headerRowNum, Integer dataRowNum) throws InterruptedException {
        //数据集合
        List<Map<String, String>> dataList = new ArrayList<>();
        if (sheet == null) return dataList;
        //行数
        int rowsNum = sheet.getPhysicalNumberOfRows();
        if (rowsNum <= 1) return dataList;
        //页头行
        Row headerRow = sheet.getRow(Math.max(headerRowNum - 1, 0));
        //字段映射
        Map<Integer, String> fieldMap = new HashMap<>();
        for (Cell cell : headerRow) {
            //检查线程中断,并响应
            if (Thread.interrupted()) throw new InterruptedException();
            //获取值,如不为空,则存入索引关系
            String cellValue = getCellValue(cell);
            if (!StringUtils.isEmpty(cellValue)) {
                fieldMap.put(cell.getColumnIndex(), cellValue);
            }
        }
        //遍历行
        for (Row row : sheet) {
            //检查线程中断,并响应
            if (Thread.interrupted()) throw new InterruptedException();
            //跳过非数据行
            if (row == null || row.getRowNum() < (dataRowNum - 1)) continue;
            //跳过空行
            if (IntStream.range(row.getFirstCellNum(), row.getLastCellNum())
                    .mapToObj(row::getCell)
                    .allMatch(cell -> cell == null || cell.getCellTypeEnum() == CellType.BLANK)) continue;
            //数据
            Map<String, String> data = new CaseInsensitiveMap<>();
            //遍历字段映射
            for (Map.Entry<Integer, String> entry : fieldMap.entrySet()) {
                //获取单元格
                Cell cell = row.getCell(entry.getKey());
                //设置值
                data.put(entry.getValue(), getCellValue(cell));
            }
            //添加数据
            dataList.add(data);
        }

        return dataList;
    }

    /**
     * excel文件转数据集合
     *
     * @param path         文件地址
     * @param clazz        数据类型
     * @param headerRowNum 表头行
     * @param dataRowNum   数据行
     * @param <E>          数据泛形
     * @return 数据集合
     */
    public static <E> Map<String, List<E>> parseExcelFile(String path, Class<E> clazz, Integer headerRowNum, Integer dataRowNum) {
        //excel枚举
        ExcelEnum excelEnum = null;
        //文件后缀
        String ext = path.substring(path.lastIndexOf("."));
        //根据后缀名指定枚举
        if (".xls".equalsIgnoreCase(ext)) excelEnum = ExcelEnum.XLS;
        else if (".xlsx".equalsIgnoreCase(ext)) excelEnum = ExcelEnum.XLS;
        if (excelEnum == null) return null;
        //解析文件
        try {
            return parseExcelFile(Files.newInputStream(Paths.get(path)), clazz, excelEnum, headerRowNum, dataRowNum);
        } catch (IOException e) {
            LogPrintMgr.getInstance().printLogMsg(LogLevelFlag.LOG_ERROR, mBizType, mStrClassName
                    , String.format("excel文件解释失败 msg:%s", e.getMessage())
            );
            return null;
        }
    }

    /**
     * excel文件转数据集合
     *
     * @param inputStream  input流
     * @param clazz        数据类型
     * @param excelEnum    excel枚举
     * @param headerRowNum 表头行
     * @param dataRowNum   数据行
     * @param <E>          数据泛形
     * @return 数据集合
     */
    public static <E> Map<String, List<E>> parseExcelFile(InputStream inputStream, Class<E> clazz, ExcelEnum excelEnum
            , Integer headerRowNum, Integer dataRowNum) {
        if (inputStream == null || excelEnum == null || excelEnum == ExcelEnum.CSV) return null;
        //读取表
        try (Workbook workbook = excelEnum == ExcelEnum.XLSX ? new XSSFWorkbook(inputStream) : new HSSFWorkbook(inputStream)) {
            if (workbook.getNumberOfSheets() <= 0) return null;
            //结果
            Map<String, List<E>> results = new HashMap<>(workbook.getNumberOfSheets());
            //遍历页,并解析
            for (Sheet sheet : workbook)
                results.put(sheet.getSheetName(), parseSheet(sheet, clazz, headerRowNum, dataRowNum));
            return results;
        } catch (IOException e) {
            LogPrintMgr.getInstance().printLogMsg(LogLevelFlag.LOG_ERROR, mBizType, mStrClassName
                    , String.format("excel文件解释失败 msg:%s", e.getMessage())
            );
            return null;
        }
    }

    /**
     * 解析页
     *
     * @param sheet        页
     * @param clazz        类型
     * @param headerRowNum 表头行
     * @param dataRowNum   数据行
     * @param <E>          数据泛形
     * @return 数据
     */
    private static <E> List<E> parseSheet(Sheet sheet, Class<E> clazz, Integer headerRowNum, Integer dataRowNum) {
        try {
            //数据集合
            List<E> dataList = new ArrayList<>();
            if (sheet == null) return dataList;
            //行数
            int rowsNum = sheet.getPhysicalNumberOfRows();
            if (rowsNum <= 1) return dataList;
            //页头行
            Row headerRow = sheet.getRow(Math.max(headerRowNum - 1, 0));
            //字段映射
            Map<Integer, Field> fieldMap = Arrays.stream(clazz.getDeclaredFields())
                    //过滤需要导出的字段
                    .filter(f -> f.isAnnotationPresent(ExcelMapping.class))
                    //设置字段公开
                    .peek(f -> f.setAccessible(true))
                    .collect(Collectors.toMap(
                            f -> {
                                //获取excel映射值
                                String excelMappingValue = f.getAnnotation(ExcelMapping.class).value();
                                //获取对应的索引
                                return IntStream.range(0, headerRow.getLastCellNum())
                                        //过滤相同的值
                                        .filter(index -> headerRow.getCell(index).getStringCellValue().equals(excelMappingValue))
                                        .findFirst().orElse(-1);
                            }, Function.identity(),
                            (it1, it2) -> it2,
                            CaseInsensitiveMap::new
                    ));
            //遍历行
            for (Row row : sheet) {
                //跳过非数据
                if (row == null || row.getRowNum() < (dataRowNum - 1)) continue;
                //跳过空行
                if (IntStream.range(row.getFirstCellNum(), row.getLastCellNum())
                        .mapToObj(row::getCell)
                        .allMatch(cell -> cell == null || cell.getCellTypeEnum() == CellType.BLANK)) continue;
                //实列化数据
                E data = clazz.getDeclaredConstructor().newInstance();
                //遍历字段映射
                for (Map.Entry<Integer, Field> fieldEntry : fieldMap.entrySet()) {
                    //跳过未找到的字段
                    if (fieldEntry.getKey() == -1) continue;
                    //获取单元格
                    Cell cell = row.getCell(fieldEntry.getKey());
                    //设置值
                    setObjValue(data, fieldEntry.getValue(), getCellValue(cell));
                }
                //添加数据
                dataList.add(data);
            }
            return dataList;
        } catch (NoSuchMethodException | InvocationTargetException | InstantiationException |
                 IllegalAccessException e) {
            LogPrintMgr.getInstance().printLogMsg(LogLevelFlag.LOG_ERROR, mBizType, mStrClassName
                    , String.format("excel页解释失败 msg:%s", e.getMessage())
            );
            return null;
        }
    }

    /**
     * 写入文件
     *
     * @param headers      表头信息
     * @param datas        数据
     * @param outputStream 输出流
     * @param excelEnum    excel枚举
     * @param headerRowNum 表头行
     * @param dataRowNum   数据行
     */
    public static void writeFile(Map<String, Map<String, String>> headers, Map<String, List<Map<String, Object>>> datas
            , OutputStream outputStream, ExcelEnum excelEnum, Integer headerRowNum, Integer dataRowNum) {
        //创建表
        try (Workbook workbook = excelEnum == ExcelEnum.XLSX ? new SXSSFWorkbook() : new HSSFWorkbook()) {
            //表头样式
            CellStyle headerStyle = workbook.createCellStyle();
            headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
            headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

            Font headerFont = workbook.createFont();
            headerFont.setBold(true);

            headerStyle.setFont(headerFont);

            //遍历页
            for (Map.Entry<String, Map<String, String>> headersEntry : headers.entrySet()) {
                //创建页
                Sheet sheet = workbook.createSheet(headersEntry.getKey());
                if (!datas.containsKey(headersEntry.getKey())) continue;
                //写入页
                writeSheet(headersEntry.getValue(), datas.get(headersEntry.getKey()), sheet, headerStyle, headerRowNum, dataRowNum);
            }
            //写入文件
            workbook.write(outputStream);
            //清理临时文件
            if (workbook instanceof SXSSFWorkbook) ((SXSSFWorkbook) workbook).dispose();
        } catch (IOException e) {
            LogPrintMgr.getInstance().printLogMsg(LogLevelFlag.LOG_ERROR, mBizType, mStrClassName
                    , String.format("excel文件写入失败 msg:%s", e.getMessage())
            );
        }

    }

    /**
     * 写入文件
     *
     * @param header       表头信息
     * @param data         数据
     * @param outputStream 输出流
     * @param excelEnum    excel枚举
     * @param headerRowNum 表头行
     * @param dataRowNum   数据行
     */
    public static void writeFile(Map<String, String> header, List<Map<String, Object>> data
            , OutputStream outputStream, ExcelEnum excelEnum, Integer headerRowNum, Integer dataRowNum) {
        //创建表
        try (Workbook workbook = excelEnum == ExcelEnum.XLSX ? new XSSFWorkbook() : new HSSFWorkbook()) {
            //表头样式
            CellStyle headerStyle = workbook.createCellStyle();
            headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
            headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

            Font headerFont = workbook.createFont();
            headerFont.setBold(true);

            headerStyle.setFont(headerFont);

            //创建页
            Sheet sheet = workbook.createSheet(DEFAULT_SHEET_NAME);
            //写入页
            writeSheet(header, data, sheet, headerStyle, headerRowNum, dataRowNum);
            //写入文件
            workbook.write(outputStream);
        } catch (IOException e) {
            LogPrintMgr.getInstance().printLogMsg(LogLevelFlag.LOG_ERROR, mBizType, mStrClassName
                    , String.format("excel文件写入失败 msg:%s", e.getMessage())
            );
        }

    }

    /**
     * 写入页
     *
     * @param header       表头
     * @param data         数据
     * @param sheet        页
     * @param headerStyle  头样式
     * @param headerRowNum 表头行
     * @param dataRowNum   数据行
     */
    public static void writeSheet(Map<String, String> header, List<Map<String, Object>> data
            , Sheet sheet, CellStyle headerStyle, Integer headerRowNum, Integer dataRowNum) {
        //表头键
        List<String> headerKeys = new ArrayList<>();
        //表头行
        Row headRow = sheet.createRow(Math.max(headerRowNum - 1, 0));
        //遍历表头
        for (Map.Entry<String, String> headerEntry : header.entrySet()) {
            //缓存键
            headerKeys.add(headerEntry.getKey());
            //设置值和样式
            Cell cell = headRow.createCell(headerKeys.size() - 1);
            setCellValue(cell, headerEntry.getValue());
            cell.setCellStyle(headerStyle);
        }
        //遍历数据
        for (int i = 0; i < data.size(); i++) {
            Map<String, Object> item = data.get(i);
            Row row = sheet.createRow(i + dataRowNum - 1);
            //遍历表头键
            for (int j = 0, headerKeysSize = headerKeys.size(); j < headerKeysSize; j++) {
                //根据表头键,设置值
                String headerKey = headerKeys.get(j);
                Cell cell = row.createCell(j);
                setCellValue(cell, item.get(headerKey));
            }
        }
    }

    /**
     * 获取单元格值
     *
     * @param cell 单元格
     * @return 值
     */
    private static String getCellValue(Cell cell) {
        if (cell == null) return null;
        //值
        String value = null;
        try {
            switch (cell.getCellTypeEnum()) {
                case NUMERIC: {
                    if (DateUtil.isCellDateFormatted(cell)) {
                        value = TimeTool.convertDateObj2DateStr(cell.getDateCellValue(), cell.getCellStyle().getDataFormatString());
                    } else if (String.valueOf(cell.getNumericCellValue()).contains(".")) {
                        value = BigDecimal.valueOf(cell.getNumericCellValue()).stripTrailingZeros().toPlainString();
                    } else
                        value = String.valueOf(cell.getNumericCellValue());
                }
                break;
                case STRING:
                    value = cell.getRichStringCellValue().toString();
                    break;
                case BOOLEAN:
                    value = String.valueOf(cell.getBooleanCellValue());
                    break;
                case FORMULA: {
                    //根据结果类型设置值
                    CellType cellType = cell.getCachedFormulaResultTypeEnum();
                    if (cellType == CellType.NUMERIC) value = String.valueOf(cell.getNumericCellValue());
                    else if (cellType == CellType.STRING) value = cell.getRichStringCellValue().toString();
                    else value = cell.getCellFormula();
                }
                break;
                default:
                    break;
            }
        } catch (Exception e) {
            LogPrintMgr.getInstance().printLogMsg(LogLevelFlag.LOG_ERROR, mBizType, mStrClassName
                    , String.format("excel单元格解释失败 error:%s", e)
            );
        }
        return value == null ? "" : value.trim();
    }

    /**
     * 设置单元格值
     *
     * @param cell  单元格
     * @param value 值
     */
    private static void setCellValue(Cell cell, Object value) {
        if (value == null) cell.setCellValue("");
        else if (value instanceof String) cell.setCellValue((String) value);
        else if (value instanceof Integer) cell.setCellValue((Integer) value);
        else if (value instanceof Double) cell.setCellValue((Double) value);
        else if (value instanceof Boolean) cell.setCellValue((Boolean) value);
        else if (value instanceof Long) cell.setCellValue((Long) value);
        else if (value instanceof Float) cell.setCellValue((Float) value);
        else if (value instanceof Short) cell.setCellValue((Short) value);
        else if (value instanceof Byte) cell.setCellValue((Byte) value);
        else if (value instanceof Character) cell.setCellValue(value.toString());
        else if (value instanceof Collection<?>)
            cell.setCellValue(((Collection<?>) value).stream().map(Objects::toString).collect(Collectors.joining(",")));
        else cell.setCellValue(value.toString());
    }

    /**
     * 设置数据值
     *
     * @param data  数据
     * @param field 字段
     * @param value 值
     * @param <E>   数据泛形
     * @throws IllegalAccessException 非法访问异常
     */
    private static <E> void setObjValue(E data, Field field, Object value) throws IllegalAccessException {
        //值判断空
        if (value != null) {
            //判断数据类型是否相同,如相同直接设置值,如不同,则转换值
            if (field.getType().isAssignableFrom(value.getClass())) {
                //设置值
                field.set(data, value);
            } else {
                //如值为double类型
                if (value instanceof Double) {
                    //对double类型兼容的类型处理
                    if (field.getType() == short.class || field.getType() == Short.class)
                        field.set(data, ((Double) value).shortValue());
                    else if (field.getType() == int.class || field.getType() == Integer.class)
                        field.set(data, ((Double) value).intValue());
                    else if (field.getType() == long.class || field.getType() == Long.class)
                        field.set(data, ((Double) value).longValue());
                    else if (field.getType() == float.class || field.getType() == Float.class)
                        field.set(data, ((Double) value).floatValue());
                }
            }
        }
    }
}