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.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
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.nio.file.Files;
import java.nio.file.Paths;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
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();

    /**
     * 解析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) {
        //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) {
        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) {
        //数据集合
        List<Map<String, String>> dataList = new ArrayList<>();
        if (sheet == null) return dataList;
        //行数
        int rowsNum = sheet.getPhysicalNumberOfRows();
        if (rowsNum <= 1) return dataList;
        //页头行
        Row rowHead = sheet.getRow(headerRowNum);
        //字段映射
        Map<Integer, String> fieldMap = new HashMap<>();
        for (Cell cell : rowHead) {
            //获取值,如不为空,则存入索引关系
            String cellValue = getCellValue(cell);
            if (!StringUtils.isEmpty(cellValue)) {
                fieldMap.put(cell.getColumnIndex(), cellValue);
            }
        }
        //遍历行
        for (Row row : sheet) {
            //跳过非数据行
            if (row == null || row.getRowNum() < dataRowNum) 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 HashMap<>();
            //遍历字段映射
            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(headerRowNum);
            //字段映射
            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()
                    ));
            //遍历行
            for (Row row : sheet) {
                //跳过第非数据
                if (row == null || row.getRowNum() < dataRowNum) 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 rowsWithColsData
     * @param outputStream
     */
    public static void writeExcel(List<List<Object>> rowsWithColsData, OutputStream outputStream, boolean hasHeader) {
        if (rowsWithColsData == null || rowsWithColsData.size() <= 0 || outputStream == null)
            return;
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook();
        XSSFSheet xssfSheet = xssfWorkbook.createSheet("sheet1");

        XSSFCellStyle colHeaderStyle = null;
        if (hasHeader) {
            colHeaderStyle = xssfWorkbook.createCellStyle();
            XSSFFont colHeaderFont = xssfWorkbook.createFont();
            colHeaderFont.setBold(true);
            colHeaderStyle.setFont(colHeaderFont);
        }

        for (int i = 0, j = rowsWithColsData.size(); i < j; i++) {
            XSSFRow xssfRow = xssfSheet.createRow(i);
            List<Object> columnData = rowsWithColsData.get(i);
            if (columnData == null)
                continue;

            for (int m = 0, n = columnData.size(); m < n; m++) {
                XSSFCell xssfCell = xssfRow.createCell(m);
                Object col = columnData.get(m);
                if (i == 0 && colHeaderStyle != null)
                    xssfCell.setCellStyle(colHeaderStyle);

                if (col == null) {
                    xssfCell.setCellValue("");
                } else {
                    if (col instanceof String) {
                        xssfCell.setCellValue(new XSSFRichTextString((String) col));
                    } else if (col instanceof Date) {
                        xssfCell.setCellValue((Date) col);
                    } else if (col instanceof Boolean) {
                        xssfCell.setCellValue((Boolean) col);
                    } else if (col instanceof Double) {
                        xssfCell.setCellValue((Double) col);
                    } else
                        xssfCell.setCellValue(new XSSFRichTextString(String.valueOf(col)));
                }

                if (i != 0 && i == j - 1)
                    xssfSheet.autoSizeColumn(m);
            }
        }

        try {
            xssfWorkbook.write(outputStream);
            outputStream.flush();
            outputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取单元格值
     *
     * @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(".")) {
                        DecimalFormat df = new DecimalFormat("#.####");
                        value = df.format(cell.getNumericCellValue());
                    } else
                        value = String.valueOf(cell.getNumericCellValue());
                }
                break;
                case STRING:
                    value = cell.getRichStringCellValue().toString();
                    break;
                case BOOLEAN:
                    value = String.valueOf(cell.getBooleanCellValue());
                    break;
                case FORMULA:
                    value = String.valueOf(cell.getNumericCellValue());
                    break;
                default:
                    break;
            }
        } catch (Exception ignored) {
        }
        return value == null ? "" : value.trim();
    }

    /**
     * 设置数据值
     *
     * @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());
                }
            }
        }
    }
}