Excel导入导出封装框架EasyPoi—spring boot+dubbo架构项目demo

EasyPoi Introduction

easypoi功能如同名字easy,主打的功能就是容易,让一个没见接触过poi的人员 就可以方便的写出Excel导出,Excel模板导出,Excel导入,Word模板导出,PDF导出,Excel转HTML,Excel charts导出通过简单的注解和模板 语言(熟悉的表达式语法),完成以前复杂的写法。

需求分析

由于项目需要做excel导入导出功能，对比poi相关的技术框架最终选择easypoi，主要因为高度封装应用及其简单。

SpringBoot + dubbo架构的Demo

依赖：

EasyPOI工具类：

//
// Source code recreated from a .class file by IntelliJ IDEA
// (powered by Fernflower decompiler)
//

package cn.afterturn.easypoi.excel;

import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.result.ExcelImportResult;
import cn.afterturn.easypoi.excel.imports.ExcelImportServer;
import cn.afterturn.easypoi.excel.imports.sax.SaxReadExcel;
import cn.afterturn.easypoi.excel.imports.sax.parse.ISaxRowRead;
import cn.afterturn.easypoi.exception.excel.ExcelImportException;
import cn.afterturn.easypoi.handler.inter.IExcelReadRowHanlder;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.List;
import org.apache.poi.util.IOUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class ExcelImportUtil {
    private static final Logger LOGGER = LoggerFactory.getLogger(ExcelImportUtil.class);

    private ExcelImportUtil() {
    }

    public static <T> List<T> importExcel(File file, Class<?> pojoClass, ImportParams params) {
        FileInputStream in = null;

        List var4;
        try {
            in = new FileInputStream(file);
            var4 = (new ExcelImportServer()).importExcelByIs(in, pojoClass, params).getList();
        } catch (ExcelImportException var9) {
            throw new ExcelImportException(var9.getType(), var9);
        } catch (Exception var10) {
            LOGGER.error(var10.getMessage(), var10);
            throw new ExcelImportException(var10.getMessage(), var10);
        } finally {
            IOUtils.closeQuietly(in);
        }

        return var4;
    }

    public static <T> List<T> importExcel(InputStream inputstream, Class<?> pojoClass, ImportParams params) throws Exception {
        return (new ExcelImportServer()).importExcelByIs(inputstream, pojoClass, params).getList();
    }

    public static <T> ExcelImportResult<T> importExcelMore(InputStream inputstream, Class<?> pojoClass, ImportParams params) throws Exception {
        return (new ExcelImportServer()).importExcelByIs(inputstream, pojoClass, params);
    }

    public static <T> ExcelImportResult<T> importExcelMore(File file, Class<?> pojoClass, ImportParams params) {
        FileInputStream in = null;

        ExcelImportResult var4;
        try {
            in = new FileInputStream(file);
            var4 = (new ExcelImportServer()).importExcelByIs(in, pojoClass, params);
        } catch (ExcelImportException var9) {
            throw new ExcelImportException(var9.getType(), var9);
        } catch (Exception var10) {
            LOGGER.error(var10.getMessage(), var10);
            throw new ExcelImportException(var10.getMessage(), var10);
        } finally {
            IOUtils.closeQuietly(in);
        }

        return var4;
    }

    public static <T> List<T> importExcelBySax(InputStream inputstream, Class<?> pojoClass, ImportParams params) {
        return (new SaxReadExcel()).readExcel(inputstream, pojoClass, params, (ISaxRowRead)null, (IExcelReadRowHanlder)null);
    }

    public static void importExcelBySax(InputStream inputstream, Class<?> pojoClass, ImportParams params, IExcelReadRowHanlder hanlder) {
        (new SaxReadExcel()).readExcel(inputstream, pojoClass, params, (ISaxRowRead)null, hanlder);
    }

    public static <T> List<T> importExcelBySax(InputStream inputstream, ISaxRowRead rowRead) {
        return (new SaxReadExcel()).readExcel(inputstream, (Class)null, (ImportParams)null, rowRead, (IExcelReadRowHanlder)null);
    }
}

依赖包：

      <dependency>
            <groupId>commons-fileupload</groupId>
            <artifactId>commons-fileupload</artifactId>
            <version>1.3.3</version>
        </dependency>
     <!--EasyPoi导入导出-->
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>3.0.3</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-web</artifactId>
            <version>3.0.3</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-annotation</artifactId>
            <version>3.0.3</version>
        </dependency>

controller层

  @ApiOperation(value = "导入", notes = "导入")
    @PostMapping("/importExcel")
    public ReturnData<Integer> importExcel(@RequestParam("file") MultipartFile file) {
        ImportParams importParams = new ImportParams();
        // 数据处理
        importParams.setHeadRows(1);
        importParams.setTitleRows(1);
        int temp = 0;
        // 需要验证
        importParams.setNeedVerfiy(true);
        try {
            ExcelImportResult<ManagerUnlawMaintainEXCELVO> result = ExcelImportUtil.importExcelMore(file.getInputStream(), ManagerUnlawMaintainEXCELVO.class,
                    importParams);
            List<ManagerUnlawMaintainEXCELVO> managerUnlawMaintainEXCELVOS = result.getList();
            temp = managerUnlawMaintainService.batchAdd(managerUnlawMaintainEXCELVOS);
        }  catch (Exception e) {
            return ReturnUtils.fail(ReturnCode.SYS_FAILED);
        }
        return ReturnUtils.success(temp);

    }


    @ApiOperation(value = "导出", notes = "导出")
    @GetMapping("/exportExcel/{behaviorNo}")
    public void exportExcel(@PathVariable String behaviorNo,HttpServletResponse response){
        List<ManagerUnlawMaintainEXCELVO> ManagerUnlawMaintainEXCELVOs = managerUnlawMaintainService.searchEXCELVO(behaviorNo);
        log.info("[ManagerUnlawMaintainEXCELVOs={}]",ManagerUnlawMaintainEXCELVOs);
        ExcelUtiles.exportExcel(ManagerUnlawMaintainEXCELVOs, "违法行为代码", "违法行为代码", ManagerUnlawMaintainEXCELVO.class, "违法行为代码.xls", response);
    }

Excel里对应参数：

import cn.afterturn.easypoi.excel.annotation.Excel;
import lombok.Data;

import java.io.Serializable;

/**
 * @author li-jc
 * @version 1.0.0
 * @className ManagerUnlawMaintainEXCELVO
 * @description TODO
 * @date 2018/11/21 16:34
 * @since JDK 1.8
 */
@Data
public class ManagerUnlawMaintainEXCELVO  implements Serializable {

    private static final long serialVersionUID = -7246242312232344322L;

    @Excel(name = "行为代码" ,orderNum = "0")
    private String behaviorNo;

    @Excel(name = "违法行为" ,orderNum = "1")
    private String illegalBehavior;

    @Excel(name = "违则" ,orderNum = "2")
    private String illrgalRule;

    @Excel(name = "罚则" ,orderNum = "3")
    private String penalty;

    @Excel(name = "处罚内容" ,orderNum = "4")
    private String punishment;

    @Excel(name = "权利事项编码" ,orderNum = "5")
    private String powerNo;
}

Service层

public interface ManagerUnlawMaintainService {
    /**
     * 批量新增
     * @param managerUnlawMaintainEXCELVOS
     * @return
     */
    int  batchAdd(List<ManagerUnlawMaintainEXCELVO> managerUnlawMaintainEXCELVOS);

    /**
     * 查询导出数据
     * @param behaviorNo
     * @return
     */
    List<ManagerUnlawMaintainEXCELVO>  searchEXCELVO(String behaviorNo);
}

serviceImpl :

import com.alibaba.dubbo.config.annotation.Service;

import com.citycloud.umale.commons.util.PageUtils;
import com.citycloud.umale.commons.vo.PageVO;
import com.citycloud.umale.maintenance.bo.ManagerUnlawMaintainBO;
import com.citycloud.umale.maintenance.bo.ManagerUnlawMaintainBatchDelBO;
import com.citycloud.umale.maintenance.bo.ManagerUnlawMaintainSearchBO;
import com.citycloud.umale.maintenance.dao.ManagerUnlawMaintainMapper;
import com.citycloud.umale.maintenance.po.ManagerUnlawMaintain;
import com.citycloud.umale.maintenance.service.ManagerUnlawMaintainService;
import com.citycloud.umale.maintenance.service.impl.assemble.ManagerFdmAssemble;
import com.citycloud.umale.maintenance.service.impl.assemble.ManagerUnlawMaintainAssemble;
import com.citycloud.umale.maintenance.vo.ManagerFdmVO;
import com.citycloud.umale.maintenance.vo.ManagerUnlawMaintainEXCELVO;
import com.citycloud.umale.maintenance.vo.ManagerUnlawMaintainVO;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import java.util.List;
/**
 * @author li-jc
 * @version 1.0.0
 * @className ManagerUnlawMaintainServiceImpl
 * @description 违法行为ServiceImpl
 * @date 2018/11/21 9:57
 * @since JDK 1.8
 */
@Slf4j
@Service(interfaceClass=ManagerUnlawMaintainService.class)
public class ManagerUnlawMaintainServiceImpl implements ManagerUnlawMaintainService {

    @Autowired
    ManagerUnlawMaintainMapper managerUnlawMaintainMapper;
    @Override
    public int batchAdd(List<ManagerUnlawMaintainEXCELVO> managerUnlawMaintainEXCELVOS) {
        return managerUnlawMaintainMapper.batchAdd(ManagerUnlawMaintainAssemble.assembleManagerUnlawMaintainEXCEL(managerUnlawMaintainEXCELVOS));
    }

    @Override
    public List<ManagerUnlawMaintainEXCELVO> searchEXCELVO(String behaviorNo) {
        List<ManagerUnlawMaintain> managerUnlawMaintains = managerUnlawMaintainMapper.search(behaviorNo);
        return ManagerUnlawMaintainAssemble.assembleManagerUnlawMaintainEXCELVO(managerUnlawMaintains);
    }
}

Mapper:

 */
@Mapper
public interface ManagerUnlawMaintainMapper {
    /**
     * 搜索
     * @param behaviorNo
     * @return
     */
    List<ManagerUnlawMaintain> search(@Param("behaviorNo") String behaviorNo);

    /**
     * 批量新增
     * @param managerUnlawMaintains
     */
    int batchAdd(List<ManagerUnlawMaintain> managerUnlawMaintains);

}

Mybatis层：

 <!--批量新增-->
  <insert id="batchAdd"  parameterType="java.util.List">
      INSERT INTO manager_unlaw_maintain (behavior_no, illegal_behavior, illrgal_rule,penalty,punishment,power_no,create_time)
      VALUES
      <foreach collection ="list" item="managerUnlawMaintain" separator =",">
        (#{managerUnlawMaintain.behaviorNo}, #{managerUnlawMaintain.illegalBehavior}, #{managerUnlawMaintain.illrgalRule},
        #{managerUnlawMaintain.penalty},#{managerUnlawMaintain.punishment},#{managerUnlawMaintain.powerNo},now())
</foreach >
  </insert>
  <!--查询-->
  <select id="search" resultType="com.citycloud.umale.maintenance.po.ManagerUnlawMaintain">
    SELECT  * from manager_unlaw_maintain where status  = '0'
    <if test="behaviorNo !=null and behaviorNo != ''">
      AND behavior_no =#{behaviorNo}
    </if>
  </select>

总结： 让excel的导入导出变得可配置可插拔，使用起来更方便，要是对导入导出的样式有特别要求还需要参考官方文档。对异常处理根据项目需求可自定义excel运行时异常。如对本demo有问题请留言，感谢您的阅读。