EasyPoi导入导出工具类

工具类：

package com.hisense.ioc.qrcode.management.util;

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 lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.beans.PropertyDescriptor;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * 描述：easyPoi导入导出类
 * 作者：海的那边
 * 时间：10:32 2019/11/6
 */
@Slf4j
public class ExcelUtil {
    private ExcelUtil() {
    }

    /**
     * 得到Workbook对象
     *
     * @param file
     * @return
     * @throws IOException
     */
    public static Workbook getWorkBook(MultipartFile file) throws IOException {
        //这样写  excel 能兼容03和07
        InputStream is = file.getInputStream();
        Workbook hssfWorkbook = null;
        try {
            hssfWorkbook = new HSSFWorkbook(is);
        } catch (Exception ex) {
            is = file.getInputStream();
            hssfWorkbook = new XSSFWorkbook(is);
        }
        return hssfWorkbook;
    }

    /**
     * 得到错误信息
     *
     * @param sb
     * @param list
     * @param i
     * @param obj
     * @param name 用哪个属性名去表明不和规定的数据
     * @param msg
     * @throws Exception
     */
    public static void getWrongInfo(StringBuilder sb, List list, int i, Object obj, String name, String msg) {
        Class clazz = obj.getClass();
        Object str = null;
        //得到属性名数组
        Field[] fields = clazz.getDeclaredFields();
        try{
            for (Field f : fields) {
                if (f.getName().equals(name)) {
                    //用来得到属性的get和set方法
                    PropertyDescriptor pd = new PropertyDescriptor(f.getName(), clazz);
                    //得到get方法
                    Method getMethod = pd.getReadMethod();
                    str = getMethod.invoke(obj);
                }
            }
            if (i == 0) {
                sb.append(msg + str + ";");
            } else if (i == (list.size() - 1)) {
                sb.append(str + "</br>");
            } else {
                sb.append(str + ";");
            }
        }catch (Exception e){
            log.error("程序异常:{}",e);
        }
    }


    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, boolean isCreateHeader, HttpServletResponse response) {
        ExportParams exportParams = new ExportParams(title, sheetName);
        exportParams.setCreateHeadRows(isCreateHeader);
        defaultExport(list, pojoClass, fileName, response, exportParams);

    }

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

    public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {
        defaultExport(list, fileName, response);
    }

    private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams) {
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
        if (workbook != null) {
            downLoadExcel(fileName, response, workbook);
        }

    }

    public static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {
        try {
            response.reset();
            // 告诉浏览器用什么软件可以打开此文件
            response.setHeader("Cache-Control", "no-store");
            response.setCharacterEncoding("UTF-8");
            //设置浏览器响应头对应的Content-disposition
            response.setHeader("Access-Control-Allow-Origin", "*");
            String zipFileName = URLEncoder.encode(fileName, "UTF-8");
            response.addHeader("Content-Disposition", "attachment; filename=" + zipFileName);
            response.setContentType("application/octet-stream");
            response.flushBuffer();
            //编码
            workbook.write(response.getOutputStream());
        } catch (IOException e) {
            log.error("程序异常",e);
        } finally {
            try {
                workbook.close();
            } catch (IOException e) {
                log.error("程序异常",e);
            }
        }
    }

    private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {
        Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.XSSF);
        if (workbook != null) {
            downLoadExcel(fileName, response, workbook);
        }

    }

    public static <T> List<T> importExcel(String filePath, Integer titleRows, Integer headerRows, Class<T> pojoClass) {
        if (StringUtils.isBlank(filePath)) {
            return new ArrayList<>();
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        List<T> list = null;
        try {
            list = ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
        }catch (Exception e) {
            log.error("程序异常",e);
        }
        return list;
    }

    public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass) {
        if (file == null) {
            return new ArrayList<>();
        }
        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) {
           log.error("程序异常",e);
        }
        return list;
    }
}

一   导出：

controllor层：

 @ApiOperation(value = "自然地理导出", notes = "自然地理导出")
    @PostMapping(value = "/exportData")
    public ObjectRestResponse exportInformation(HttpServletResponse response, @RequestBody List<Integer> list) {
        return excelService.export(response, list);
    }

service层：（部分代码，具体根据自己业务逻辑取出数据）

/**
     * 导出列表
     *
     * @param response
     */
    public ObjectRestResponse export(HttpServletResponse response, List<Integer> list) {
        ObjectRestResponse ob = new ObjectRestResponse();
        try {
            // 从数据库获取需要导出的数据
            List<PhysicalGeography> list1 = physicalMapper.getForInformations(list);
            String type = list1.get(0).getNatureType();
            if ("1".equals(type)) {
                MountainDTO mountainDTO;
                List<MountainDTO> mList = new ArrayList<>();
                for (PhysicalGeography p : list1) {
                    mountainDTO = new MountainDTO();
                    mountainDTO.setName(p.getName());
                    mountainDTO.setAltitude(p.getAltitude());
                    mountainDTO.setLocation(p.getLocation());
                    mountainDTO.setMainPeak(p.getMainPeak());
                    mountainDTO.setMarks(p.getMarks());
                    mountainDTO.setApprovalStatus(p.getApprovalStatus());
                    mountainDTO.setRunningStatus(p.getRunningStatus());
                    mountainDTO.setLatitudeLongitudeRange(p.getLatitudeLongitudeRange());
                    mountainDTO.setTrend(p.getTrend());
                    mList.add(mountainDTO);
                }
                ExcelUtil.exportExcel(mList, null, GlobalConstant.TABLE_PHYSICAL, MountainDTO.class, GlobalConstant.TABLE_PHYSICAL + ".xlsx", response);
                ob.okResponse();
                log.info("山川导出成功");
            }

DTO：

package com.hisense.ioc.qrcode.management.dto;

import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelTarget;
import com.hisense.ioc.qrcode.management.entity.PhysicalGeography;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.io.Serializable;

/**
 * 描述：对应sheet的实体类
 * 作者：huyuan
 * 时间：10:52 2019/11/6
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
@ExcelTarget("userExcel")
public class MountainDTO implements Serializable {
    private Integer id;
    @Excel(name="名称")
    private String name;
    @Excel(name="位置")
    private String location;
    /**
     * 经纬度的范围
     */
    @Excel(name="范围")
    private String latitudeLongitudeRange;
    /**
     * 海拔
     */
    @Excel(name="海拔")
    private String altitude;
    /**
     * 主峰
     */
    @Excel(name="主峰")
    private String mainPeak;
    /**
     * 走向
     */
    @Excel(name="走向")
    private String trend;
    /**
     * 详情备注
     */
    @Excel(name="备注")
    private String marks;
    /**
     * 审批状态
     */
    @Excel(name="审批状态")
    private String approvalStatus;
    /**
     * 运行状态
     */
    @Excel(name="运行状态")
    private String runningStatus;
    private String  natureType;
}

pom.xml(用于poi的支持，其他的请自行添加)：

 <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>4.1.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-annotation</artifactId>
            <version>4.1.0</version>
        </dependency>

二  导入：

controllor层：

public ObjectRestResponse importInformation(@RequestParam("file") MultipartFile file) {
        ObjectRestResponse ob = new ObjectRestResponse();
        int m = excelService.importSheets(file);
        if (m > 0) {
            ob.setMessage(GlobalConstant.IMPORT_OK);
        } else {
            ob.failResponse(GlobalConstant.IMPORT_FAILD_NO_TABLE);
        }
        return ob;
    }

service层（部分，请根据自身情况调整）：

 public int importSheets(MultipartFile file) {
        int k = 0;
        try {
            // 根据file得到Workbook,主要是要根据这个对象获取,传过来的excel有几个sheet页
            Workbook workBook = ExcelUtil.getWorkBook(file);
            StringBuilder sb = new StringBuilder();
            ImportParams params = new ImportParams();

            // 循环工作表Sheet
            for (int numSheet = 0; numSheet < workBook.getNumberOfSheets(); numSheet++) {
                // 表头在第几行
                params.setTitleRows(0);
                // 距离表头中间有几行不要的数据
                params.setStartRows(0);
                // 第几个sheet页
                params.setStartSheetIndex(numSheet);
                // 验证数据
                params.setNeedVerify(true);
                if (numSheet == 0) {
                    k = mountainData(params, sb, file);
                }

mountainData封装方法：

private int mountainData(ImportParams params, StringBuilder sb, MultipartFile file) {
        ObjectRestResponse ob = new ObjectRestResponse();
        int n = 0;
        try {
            ExcelImportResult<MountainDTO> result = ExcelImportUtil.importExcelMore(file.getInputStream(),
                    MountainDTO.class, params);
            // 校验是否合格
            if (result.isVerfiyFail()) {
                // 不合格的数据
                List<MountainDTO> errorList = result.getList();
                // 拼凑错误信息,自定义
                for (int i = 0; i < errorList.size(); i++) {
                    ExcelUtil.getWrongInfo(sb, errorList, i, errorList.get(i), "name", "山川信息不合法");
                }
            }
            // 合格的数据
            List<MountainDTO> successList = result.getList();
            if (!successList.isEmpty()) {
                n++;
                PhysicalGeography phy;
                for (MountainDTO mountainDTO : successList) {
                    phy = new PhysicalGeography();
                    phy.setAltitude(mountainDTO.getAltitude());
                    phy.setLocation(mountainDTO.getLocation());
                    phy.setApprovalStatus(mountainDTO.getApprovalStatus());
                    phy.setRunningStatus(mountainDTO.getRunningStatus());
                    phy.setTrend(mountainDTO.getTrend());
                    phy.setMarks(mountainDTO.getMarks());
                    phy.setMainPeak(mountainDTO.getMainPeak());
                    phy.setLatitudeLongitudeRange(mountainDTO.getLatitudeLongitudeRange());
                    phy.setName(mountainDTO.getName());
                    phy.setNatureType(GlobalConstant.ONE);
                    physicalMapper.insertGeo(phy);
                    ob.setMessage("导入成功");
                }
            } else {
                ob.setMessage("导入失败：表格内容不能为空");
            }
        } catch (Exception e) {
            log.error(GlobalConstant.ERROR_IMPORT, e);
        }
        return n;
    }

至此，EasyPoi的导入导出就全部完成了！简单易懂！