ucard-back/ucard-backend/src/main/java/com/crm/rely/backend/util/EasyExcelUti.java

package com.crm.rely.backend.util;

import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.metadata.BaseRowModel;
import com.alibaba.excel.metadata.Sheet;
import com.crm.rely.backend.core.constant.Constants;
import com.crm.rely.backend.core.entity.base.PageEntity;
import com.crm.rely.backend.exception.ServiceException;
import org.springframework.beans.BeanUtils;
import org.springframework.web.multipart.MultipartFile;

import java.io.BufferedInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

/**
 * 导入工具
 *
 * @Author: houn
 * @Date: 2020/12/16 21:36
 * @Description:
 */
public class EasyExcelUti {

    /**
     * 读取某个 sheet 的 Excel
     *
     * @param excel    文件
     * @param rowModel 实体类映射，继承 BaseRowModel 类
     * @return Excel 数据 list
     */
    public static List<Object> readExcel(MultipartFile excel, BaseRowModel rowModel) throws IOException {
        return readExcel(excel, rowModel, 1, 1);
    }

    /**
     * 读取某个 sheet 的 Excel
     *
     * @param excel    文件
     * @param rowModel 实体类映射，继承 BaseRowModel 类
     * @param sheetNo  sheet 的序号 从1开始
     * @return Excel 数据 list
     */
    public static <T extends BaseRowModel> List<T> readExcel(MultipartFile excel, T rowModel, int sheetNo) throws IOException {

        ExcelListener excelListener=  getExcelListener(excel,rowModel,sheetNo);

        return excelListener.getDatas();
    }

    public static <T> List<T> readExcel(MultipartFile excel, BaseRowModel rowModel, int sheetNo, Class<T> clazz) throws IOException, ServiceException, IllegalAccessException, InstantiationException {

        ExcelListener excelListener=  getExcelListener(excel,rowModel,sheetNo);

        if (!excelListener.getImportHeads().equals(excelListener.getModelHeads())) {
            throw ServiceException.exception(Constants.UPLOAD_EXCEL_ERROR);
        }
        if (excelListener.getDatas() != null && excelListener.getDatas().size() > 0) {
            List<T> list = new LinkedList<>();
            for (Object o : excelListener.getDatas()) {
                BaseRowModel model = (BaseRowModel) o;
                T t = clazz.newInstance();
                BeanUtils.copyProperties(model, t);
                list.add(t);

            }
            return list;
        } else {
            throw ServiceException.exception(Constants.UPLOAD_EXCEL_ERROR);
        }
    }

    private static <T extends BaseRowModel>ExcelListener getExcelListener(MultipartFile excel, T rowModel, int sheetNo) throws IOException {
        ExcelListener excelListener = new ExcelListener();
        ExcelReader reader = getReader(excel, excelListener);
        if (reader == null) {
            return null;
        }
        reader.read(new Sheet(sheetNo, 0, rowModel.getClass()));
        return excelListener;
    }
    /**
     * 读取某个 sheet 的 Excel
     *
     * @param excel       文件
     * @param rowModel    实体类映射，继承 BaseRowModel 类
     * @param sheetNo     sheet 的序号 从1开始
     * @param headLineNum 表头行数，默认为1
     * @return Excel 数据 list
     */
    public static List<Object> readExcel(MultipartFile excel, BaseRowModel rowModel, int sheetNo, int headLineNum) throws IOException {
        ExcelListener excelListener = new ExcelListener();
        ExcelReader reader = getReader(excel, excelListener);
        if (reader == null) {
            return null;
        }
        reader.read(new Sheet(sheetNo, headLineNum, rowModel.getClass()));
        return excelListener.getDatas();
    }

    /**
     * 读取指定sheetName的Excel(多个 sheet)
     *
     * @param excel    文件
     * @param rowModel 实体类映射，继承 BaseRowModel 类
     * @return Excel 数据 list
     * @throws IOException
     */
    public static List<Object> readExcel(MultipartFile excel, BaseRowModel rowModel, String sheetName) throws IOException {
        ExcelListener excelListener = new ExcelListener();
        ExcelReader reader = getReader(excel, excelListener);
        if (reader == null) {
            return null;
        }
        for (Sheet sheet : reader.getSheets()) {
            if (rowModel != null) {
                sheet.setClazz(rowModel.getClass());
            }
            //读取指定名称的sheet
            if (sheet.getSheetName().contains(sheetName)) {
                reader.read(sheet);
                break;
            }
        }
        return excelListener.getDatas();
    }

    /**
     * 返回 ExcelReader
     *
     * @param excel         需要解析的 Excel 文件
     * @param excelListener new ExcelListener()
     * @throws IOException
     */
    private static ExcelReader getReader(MultipartFile excel, ExcelListener excelListener) throws IOException {
        String filename = excel.getOriginalFilename();
        if (filename != null && (filename.toLowerCase().endsWith(".xls") || filename.toLowerCase().endsWith(".xlsx"))) {
            InputStream is = new BufferedInputStream(excel.getInputStream());
            return new ExcelReader(is, null, excelListener, false);
        } else {
            return null;
        }
    }

}