excel--easypoi

package com.a.utils.common;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
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.result.ExcelImportResult;
import com.a.utils.common.function.FileVerityFunction;
import com.a.utils.enums.ExcelTypeEnum;
import lombok.extern.slf4j.Slf4j;
import org.apache.logging.log4j.util.Strings;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.stereotype.Component;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;
import java.util.Objects;

/**
 * 
 * @description: Excel导入导出工具类
 */
@Component
@Slf4j
public class ExcelUtils {

    private static final String XLS = "^.+\\.(?i)(xls)$";
    private static final String XLSX = "^.+\\.(?i)(xlsx)$";

    /**

     * 导出excel
     * @param list  泛型数据
     * @param pojoClass 需要导出的对象
     * @param title 标题
     * @param sheetName sheet的名称
     * @param fileName  文件名称
     * @param isCreateHeader    是否创建表头
     */

    public static void exportExcel(List<?> list, Class<?> pojoClass, String title,
                            String sheetName, String fileName, boolean isCreateHeader,HttpServletResponse response) throws IOException {

        final ExportParams exportParams = new ExportParams(title, sheetName, ExcelType.XSSF);
        exportParams.setCreateHeadRows(isCreateHeader);
        baseExport(list, pojoClass, fileName, exportParams,response);
    }

    /**

     * 导出Excel
     * @param list  泛型数据
     * @param pojoClass 需要导出的对象
     * @param title 标题
     * @param sheetName sheet的名称
     * @param fileName  文件名称
     */

    public static void exportExcel(List<?> list, Class<?> pojoClass, String title,
                            String sheetName, String fileName,HttpServletResponse response) throws IOException {
        baseExport(list, pojoClass, fileName, new ExportParams(title, sheetName, ExcelType.XSSF),response);
    }

    /**
     * 导出Excel
     * @param list  泛型数据
     * @param pojoClass 需要导出的对象
     * @param fileName  文件名称
     * @param exportParams  导出文件属性
     */
    public static void exportExcel(List<?> list, Class<?> pojoClass, String fileName,
                            ExportParams exportParams,HttpServletResponse response) throws IOException {
        baseExport(list, pojoClass, fileName, exportParams,response);
    }

    /**
     * 多个sheet导出
     * @param list
     * @param fileName
     */
    public static void exportExcel(List<Map<String, Object>> list,
                            String fileName,HttpServletResponse response) throws IOException {
        baseExport(list, fileName,response);
    }

    /**
     * 最基础的对象导出
     * @param list  数据列表
     * @param pojoClass 导出对象
     * @param fileName  文件名称
     * @param exportParams  导出文件属性
     */
    private static void baseExport(List<?> list, Class<?> pojoClass,
                            String fileName, ExportParams exportParams,HttpServletResponse response) throws IOException {
        final Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
        downLoadExcel(fileName, workbook,response);
    }

    /**
     * 最基础的多sheet导出
     * @param list  多个不同数据对象的列表
     * @param fileName  文件名称
     */
    private static void baseExport(List<Map<String, Object>> list, String fileName,HttpServletResponse response) throws IOException {
        final Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
        downLoadExcel(fileName, workbook,response);
    }

    /**
     * 文件下载
     * @param fileName  文件名称
     * @param workbook  exce对象
     */
    private static void downLoadExcel(String fileName, Workbook workbook,HttpServletResponse response) throws IOException {
        ServletOutputStream output = null;
        try {
            final String downloadName = URLEncoder.encode(fileName + "." + ExcelTypeEnum.XLSX.getValue(), "UTF-8");
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename=" + downloadName);
            output = response.getOutputStream();
            workbook.write(output);
        } catch (final Exception e) {
            throw new IOException(e.getMessage());
        } finally {
            if (output != null) {
                output.flush();
                output.close();
            }
        }
    }

    /**
     * 从指定位置获取文件后进行导入
     * @param filePath
     * @param titleRows
     * @param headerRows
     * @param pojoClass
     * @param <T>
     * @return
     */
    public static <T> List<T> importExcel(String filePath, Integer titleRows, Integer headerRows, Class<?> pojoClass) {
        if (Strings.isEmpty(filePath) || "".equals(filePath)) {
            return null;
        } else {
            final ImportParams params = new ImportParams();
            //表格标题行数，默认0
            params.setTitleRows(titleRows);
            //表头行数，默认1
            params.setHeadRows(headerRows);
            //是否需要保存上传的Excel
            params.setNeedSave(true);
            //保存上传的Excel目录
            params.setSaveUrl("/excel/");
            return ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
        }
    }

    /**
     * 上传文件导入
     * @param file
     * @param pojoClass 导入的对象
     * @param <T>
     * @return
     * @throws Exception
     */
    public static <T> List<T> importExcel(MultipartFile file, Class<T> pojoClass,String savePath) throws Exception {
    try{
        if (file != null) {
             //获取上传文件的名称
            String fileName = file.getOriginalFilename();
            if (!Objects.requireNonNull(fileName).matches(XLS) && !fileName.matches(XLSX)) {
                log.info("上传文件格式不正确");
            }else{
                return baseImport(file.getInputStream(), pojoClass,savePath);
            }
        }
    }catch (Exception e){
        log.error("导入Excel文件异常，异常原因："+e);
        throw new IOException(e);
    }
    return  null;
    }

    private static <T> List<T> baseImport(InputStream inputStream, Class<T> pojoClass,String savePath) throws Exception {
        if (inputStream == null) {
            return null;
        } else {
            final ImportParams params = new ImportParams();
            params.setSaveUrl(savePath);
            params.setNeedSave(true);
            return ExcelImportUtil.importExcel(inputStream, pojoClass, params);
        }
    }


    /**
     * 解析excel
     * @param file 文件流
     * @param pojoClass 导入的对象
     * @param <T>
     * @param filterStrList 过滤
     * @return
     * @throws Exception
     */
    public static <T> ExcelImportResult<T> readExcelGetObjList(MultipartFile file, Class<T> pojoClass,
                                                         List<String> filterStrList) throws Exception {
        try{
            if (file != null) {
                //获取上传文件的名称
                String fileName = file.getOriginalFilename();
                if (!Objects.requireNonNull(fileName).matches(XLS) && !fileName.matches(XLSX)) {
                    log.info("上传文件格式不正确");
                    return null;
                }
                if (file.getInputStream() == null) {
                    log.info("文件不存在");
                    return null;
                }
                final ImportParams params = new ImportParams();
                FileVerityFunction fileVerityFunction =new FileVerityFunction();
                fileVerityFunction.setFilterStrList(filterStrList);
                params.setNeedVerify(true);
                params.setVerifyHandler(fileVerityFunction);
                ExcelImportResult result= ExcelImportUtil.importExcelMore(file.getInputStream(), pojoClass, params);
                return result;
            }
        }catch (Exception e){
            log.error("导入Excel文件异常，异常原因："+e);
            throw e;
        }
        return null;
    }


}

easypoi: POI 工具类,Excel的快速导入导出,Excel模板导出,Word模板导出,可以仅仅5行代码就可以完成Excel的导入导出,修改导出格式简单粗暴,快速有效,easypoi值得你尝试