SpringBoot集成EasyPOI实现Excel表格的导入导出功能

导入

Vue

<div class="ruleSetBox">
  <div class="label"><span>限定用户白名单</span></div>
  <div class="switch">
    <el-switch
      v-model="ruleData.whiteFlag"
      :active-value="1"
      active-color="#409EFF"
      :inactive-value="0"
      inactive-color="#ff4949"
      :disabled="onlyread?true:false">
    </el-switch>
  </div>
  <div class="input" v-if="ruleData.whiteFlag">
    <span class="clickSpan">
      设置白名单用户
      <input type="file" id="inputFile" class="inputFile" @change="importWhiteInfo()" v-if="!onlyread">
    </span>
    (
    <a href="./whiteModel.xlsx" download="白名单模板.xlsx" >模板下载</a>
    )
  </div>
</div>

export default {
	methods:{
		importWhiteInfo() { // 导入白名单
	      let files = document.getElementById('inputFile').files
	      let data = new FormData();
	      data.append('file', files[0]);
	      this.$http.post('/xxx/userWhiteInfo?id=' + this.id, data).then(res => {
	        if (res.data.code == 0) {
	          this.$message(res.data.msg)
	        } else {
	          this.$message.error(res.data.msg);
	        }
	      })
	    }
	}
}

<style lang="scss">
.ruleSet {
  padding: 30px;

  .ruleSetBox {
    height: 50px;

    display: flex;
    align-items: center;

    .label {
      width: 180px;
    }

    .switch {
      margin-right: 50px;
    }

    .input {
      width: 350px;
      .el-date-editor {
        width: 350px;
      }

      .el-input {
        width: 205px;
      }
      .el-select {
        width: 200px;
      }

      span.clickSpan {
        color: #409EFF;
        font-size: 0.9rem;
        display: inline-block;
        position: relative;
        cursor: pointer;
        &:hover {opacity: 0.8;}

        .inputFile {
          position: absolute;
          top: 0;
          left: 0;
          width: 100%;
          height: 100%;
          opacity: 0;
          cursor: pointer;
        }
      }
    }
  }
}
</style>

pom.xml

<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-spring-boot-starter</artifactId>
    <version>4.3.0</version>
</dependency>
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-base</artifactId>
    <version>4.3.0</version>
</dependency>
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-web</artifactId>
    <version>4.3.0</version>
</dependency>
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-annotation</artifactId>
    <version>4.3.0</version>
</dependency>

UserWhiteInfoDTO

import cn.afterturn.easypoi.excel.annotation.Excel;
import com.fasterxml.jackson.databind.annotation.JsonSerialize;
import com.fasterxml.jackson.databind.ser.std.ToStringSerializer;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

import java.io.Serializable;

@Data
@ApiModel(value = "白名单信息表")
public class UserWhiteInfoDTO implements Serializable {
    private static final long serialVersionUID = 1L;

	@ApiModelProperty(value = "白名单物理主键")
	@JsonSerialize(using= ToStringSerializer.class)
	private Long id;

	@ApiModelProperty(value = "问卷ID")
	@JsonSerialize(using= ToStringSerializer.class)
	private Long questionnaireId;

	@Excel(name = "姓名")
	@ApiModelProperty(value = "姓名")
	private String playerName;

	@Excel(name = "联系方式")
	@ApiModelProperty(value = "联系方式")
	private String phone;

}

UserWhiteInfoEntity

import cn.afterturn.easypoi.excel.annotation.Excel;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;
import lombok.EqualsAndHashCode;

@Data
@EqualsAndHashCode(callSuper=false)
@TableName("user_white_info")
public class UserWhiteInfoEntity {

	private static final long serialVersionUID = 1L;
	
	/**
	 * id
	 */
	@Excel(name = "序号")
	private Long id;
	
    /**
     * 问卷ID
     */
	@Excel(name = "问卷ID")
	private Long questionnaireId;
	
    /**
     * 姓名
     */
	@Excel(name = "姓名")
	private String playerName;
	
    /**
     * 联系方式
     */
	@Excel(name = "联系方式")
	private String phone;
	
}

Controller

import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ImportParams;

	@PostMapping(value = "/UserWhiteInfo")
    @ApiOperation(value = "导入白名单Excel", notes = "导入")
    @LogOperation(value = "导入白名单Excel")
    public Result<String> importWhiteInfo(MultipartFile file, @RequestParam Long questionnaireId) throws Exception {
    	// 携带questionnaireId用于判断此问卷白名单是否已经导入
        if (!ObjectUtils.isEmpty(questionnaireId)){
            Map<String,Object> param = new HashMap<>();
            param.put("questionnaireId", questionnaireId);
            userWhiteInfoService.deleteById(param);
        }
        List<UserWhiteInfoDTO>  userWhiteInfoDTOList = ExcelImportUtil.importExcel(file.getInputStream()
                , UserWhiteInfoDTO.class, new ImportParams());
        userWhiteInfoDTOList.forEach(e -> e.setQuestionnaireId(questionnaireId));
	    userWhiteInfoService.insertBatch(ConvertUtils.sourceToTarget(userWhiteInfoDTOList, UserWhiteInfoEntity.class));
        Result<String> result = new Result();
        result.setMsg("导入成功");
        return result;
    }

导出

Vue

<div class="queryBox">
  <div class="left"></div>
  <div class="right">
    <el-button type="primary" size="small" @click="export()">导出数据</el-button>
  </div>
</div>

export default {
	methods:{
	  export() { // 导出数据
      let anchor = document.createElement("a");
      anchor.href = window.SITE_CONFIG['apiURL'] + '/xxx/userSignRecord/export?questionnaireId=' + this.questionnaireId;
      anchor.setAttribute("target", '_blank');
      anchor.innerHTML = "downloading...";
      anchor.style.display = "none";
      document.body.appendChild(anchor);
      setTimeout(() => {
          anchor.click();
          document.body.removeChild(anchor);
          setTimeout(() => { self.URL.revokeObjectURL(anchor.href); }, 250);
      }, 66);
    }
	}
}

<style lang="scss">
.reportSignin {

  .queryBox {
    display: flex;
    justify-content: flex-end;
  }

  .tableBox {
    margin-top: 20px;
    
    .el-pagination {
      text-align: center;
    }
  }

}
</style>

UserSignRecord

import com.fasterxml.jackson.databind.annotation.JsonSerialize;
import com.fasterxml.jackson.databind.ser.std.ToStringSerializer;
import lombok.Data;

import java.io.Serializable;

@Data
public class UserSignRecord implements Serializable {

    @JsonSerialize(using= ToStringSerializer.class)
    private long id;
    private String username;
    private String realName;
    private String phoneNum;
    private String createDate;
    
}

UseSignExcel

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

@Data
public class UseSignExcel{

    @Excel(name = "序号")
    private Long id;
    @Excel(name = "用户昵称")
    private String username;
    @Excel(name = "真实姓名")
    private String realName;
    @Excel(name = "联系方式")
    private String phoneNum;
    @Excel(name = "签到日期")
    private String createDate;

}

Controller

	@GetMapping("/UserSignRecord/export")
    @ApiOperation("导出签到记录")
    @LogOperation("导出签到记录")
    public Result<String> exportSignRecord(@ApiIgnore @RequestParam Map<String, Object> params) throws Exception{
        // 参数校验
        Long questionnaireId= MapUtils.getLong(params, "questionnaireId");
        if (ObjectUtils.isEmpty(questionnaireId)) {
            return new Result().error("导出失败");
        }
        List<UserSignRecord> list = userSignRecordService.getUserSignRecordList(params);
        ExcelExportUtil.exportExcel(new ExportParams("用户签到记录","表格"), UseSignExcel.class, list);
        Result<String> result = new Result();
        result.setMsg("导出成功");
        return result;
    }

或者使用自定义导出工具类
ExcelUtils

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;

import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.beans.BeanUtils;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.List;

/**
 * Excel导出工具类
 *
 */
public class ExcelUtils {

    /**
     * Excel导出
     *
     * @param response      response
     * @param fileName      文件名
     * @param list          数据List
     * @param pojoClass     对象Class
     */
    public static void exportExcel(HttpServletResponse response, String fileName, Collection<?> list,
                                     Class<?> pojoClass) throws IOException {
        if(StringUtils.isBlank(fileName)){
            //当前日期
            fileName = DateUtils.format(new Date());
        }
        Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams(), pojoClass, list);
        response.setCharacterEncoding("UTF-8");
        response.setHeader("content-Type", "application/vnd.ms-excel");
        response.setHeader("Content-Disposition",
                "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xls");
        ServletOutputStream out = response.getOutputStream();
        workbook.write(out);
        out.flush();
    }

    /**
     * Excel导出：先将sourceList转换成List<targetClass>再导出
     *
     * @param response      response
     * @param fileName      文件名
     * @param sourceList    原数据List
     * @param targetClass   目标对象Class
     */
    public static void exportExcelToTarget(HttpServletResponse response, String fileName, Collection<?> sourceList,
                                     Class<?> targetClass) throws Exception {
        List targetList = new ArrayList<>(sourceList.size());
        for(Object source : sourceList){
            Object target = targetClass.newInstance();
            BeanUtils.copyProperties(source, target);
            targetList.add(target);
        }
        exportExcel(response, fileName, targetList, targetClass);
    }
}

Controller

	@GetMapping("/UserSignRecord/export")
    @ApiOperation("导出签到记录")
    @LogOperation("导出签到记录")
    public Result<String> exportSignRecord(@ApiIgnore @RequestParam Map<String, Object> params, HttpServletResponse response) throws Exception {
        // 参数校验
        Long questionnaireId= MapUtils.getLong(params, "questionnaireId");
        if (ObjectUtils.isEmpty(questionnaireId)) {
            return new Result().error("导出失败");
        }
        List<UserSignRecord> list = userSignRecordService.getUserSignRecordList(params);
        ExcelUtils.exportExcelToTarget(response, "用户签到情况", list, UseSignExcel.class);
        Result<String> result = new Result();
        result.setMsg("导出成功");
        return result;
    }