Springboot实现内容向Excel的导入导出

1、Excel导入导出

1.1 用户信息列表导入导出

1.1.1 参考文档

https://blog.csdn.net/qq_35387940/article/details/88967572

1.1.2 实际演示

（1）、pom.xml

<!-- 导入和导出-->
<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>

（2）、实体类

@Data
@AllArgsConstructor
@NoArgsConstructor
public class PersonDeptVo {
    @Excel(name="用户ID",orderNum = "0")
    private int personId;
    @Excel(name="用户名",orderNum = "1")
    private String personName;
    @Excel(name="性别",orderNum = "2")
    private int gender;
    @Excel(name="身份证号",orderNum = "3")
    private String IDNumber;
    @Excel(name="电话号码",orderNum = "4")
    private String telephoneNumber;
    @Excel(name="部门",orderNum = "5")
    private int departmentName;
    @Excel(name="密级",orderNum = "6")
    private int secretLevel;
}

（3）、工具类

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 org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;

import java.util.Map;
import java.util.NoSuchElementException;

//excel文件导入导出工具类
public class ExcelUtil {

    /**
     * 文件导出
     * @param list
     * @param title
     * @param sheetName
     * @param pojoClass
     * @param fileName
     * @param response
     */
    //用户信息导出xls文件（包含表格标题，sheet名）
    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));
    }

    //默认导出格式，按照实际的pojo对象来（包含导出格式）
    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);
    }

    //下载导出文件
    private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {
        try {
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition",
                    "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
            workbook.write(response.getOutputStream());
        } catch (IOException e) {
            throw new RuntimeException(e.getMessage());
        }
    }

    /**
     * 文件导入
     * @param multipartFile
     * @param titleRows
     * @param headerRows
     * @param pojoClass
     * @param <T>
     * @return
     */
    //导入
    public static <T> List<T> importExcel(MultipartFile multipartFile,Integer titleRows,Integer headerRows, Class<T> pojoClass) throws IOException {
        if (multipartFile.isEmpty()){
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        File file = MultipartFileToFile(multipartFile);
        List<T> list = null;
        try {
            list = ExcelImportUtil.importExcel(file, pojoClass, params);//importExcel方法参数只能是File或流文件
        }catch (NoSuchElementException e){
            throw new RuntimeException("模板不能为空");
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e.getMessage());
        }
        return list;
    }
    /**
     * 将MultipartFile转换为File
     * @param multiFile
     * @return
     */
    public static File MultipartFileToFile(MultipartFile multiFile) {
        // 获取文件名
        String fileName = multiFile.getOriginalFilename();
        // 获取文件后缀
        String prefix = fileName.substring(fileName.lastIndexOf("."));
        // 若须要防止生成的临时文件重复,能够在文件名后添加随机码

        try {
            File file = File.createTempFile(fileName, prefix);
            multiFile.transferTo(file);
            return file;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
}

（4）、Mapper

//用户角色查看
List<RoleInfo> queryPersonRole();
//用户信息批量导入
boolean userBatchAdd(List<SecretRelatedPersonInfo> list);

（5）、xml

<select id="queryUserListAll" resultType="com.zallxk.rfid_aoc.domain.vo.PersonDeptVo" parameterType="list">
    select
        person_id,
        person_name,
        gender,
        ID_number,
        telephone_number,
        department_name,
        secret_level
    from secret_related_person_info srpi,department_info di
    where srpi.department_id = di.department_id
</select>

<insert id="userBatchAdd" parameterType="list">
    insert into rfid_aoc.secret_related_person_info
    (
    person_name,
    gender,
    ID_number,
    telephone_number,
    department_id,
    secret_level,
    create_time
    )values
    <foreach collection="list" item="item" index="index" separator=",">
        (
        #{item.personName},
        #{item.gender},
        #{item.IDNumber},
        #{item.telephoneNumber},
        #{item.departmentId},
        #{item.secretLevel},
        #{item.createTime}
        )
    </foreach>
</insert>

（6）、Service

//用户角色查看
List<RoleInfo> queryPersonRole();
//用户信息批量导入
boolean userBatchAdd(List<SecretRelatedPersonInfo> list);

（7）、Serviceimpl

//获取用户列表
@Override
public List<PersonDeptVo> queryUserListAll() {
    return secretRelatedPersonMapper.queryUserListAll();
}

//用户信息批量导入
@Override
public boolean userBatchAdd(List<SecretRelatedPersonInfo> list) {
    return secretRelatedPersonMapper.userBatchAdd(list);
}

（8）、controller

@RequestMapping("/exportExcel")
public void export(HttpServletResponse response){

    List<PersonDeptVo> userList = secretRelatedPersonService.queryUserListAll();
    System.out.println(userList);
    //导出操作
    ExcelUtil.exportExcel(userList,"用户信息","sheet1",PersonDeptVo.class,"testDATA.xls",response);
}

/**
     * 用户信息批量导入（文件形式）
     * @return
     */
@RequestMapping("/importExcel")
public ResponseEntity importExcel(@RequestParam("file")MultipartFile file) throws IOException {

    //解析excel
    List<SecretRelatedPersonInfo> userList = ExcelUtil.importExcel(file,1,1,SecretRelatedPersonInfo.class);
    System.out.println("导入数据一共【"+userList.size()+"】行");

    if (secretRelatedPersonService.userBatchAdd(userList)) {
        return ResponseEntity.ok(ResultObject.success("导入成功",secretRelatedPersonService.queryUserListAll()));
    }else return ResponseEntity.ok(ResultObject.failed(null));
}

1.2 模板下载

1.2.1 固定模板文件下载

/**
 * 用户信息导入模板下载
 * @param request
 * @param response
 */
@GetMapping("/downloadPFile")
public void downloadFile(HttpServletRequest request,HttpServletResponse response){
    try {
        // 以流的形式下载文件这种方法，打成jar包之后，下载的文件，会被损坏
        InputStream fis =this.getClass().getClassLoader().getResourceAsStream("importfile/用户信息导入模板.xls");
        response.setHeader("Content-Disposition", "attachment;filename="+new String("用户信息导入模板.xls".getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1));
        response.setContentType("application/msword;charset=UTF-8");
        ServletOutputStream out = response.getOutputStream();
        byte[] buffer = new byte[1024];
        int len;
        while ((len = fis.read(buffer)) != -1) {
            out.write(buffer, 0, len);
        }
        out.flush();
        fis.close();
        out.close();
    } catch (IOException e) {
        e.printStackTrace();
    }
}

1.2.2 导入模板下载中针对个别字段提供下拉框

• 提供依赖

  <!--Excel导出,导入-->
  <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>
  

• 实体类

  @Data
  @AllArgsConstructor
  @NoArgsConstructor
  public class SecretRelatedPersonInfo {
      // 姓名
      @Excel(name = "姓名", orderNum = "0")
      private String personName;
  
      //性别
      @Excel(name = "性别", orderNum = "1")
      private Integer gender;
  
      // 身份证号
      @Excel(name = "身份证号", orderNum = "2")
      private String idnumber;
  
      //电话号码
      @Excel(name = "电话号码", orderNum = "3")
      private String telephoneNumber;
  
      // 部门ID
      @Excel(name = "部门", orderNum = "4")
      private Integer departmentId;
  
      // 用户名
      @Excel(name = "用户名", orderNum = "8")
      private String userName;
  }
  
  

• Controller

  /**
       * 用户信息导入模板下载
       *
       * @param response
       */
  @RequestMapping("/downloadFile")
  public void downloadFile(HttpServletResponse response) {
      ServletOutputStream os = null;
      try {
          //设置表格第一行的标题
          String title = "用户信息";
          //设置导出文件名
          String fileName = "用户信息导入模板.xls";
          //定义导出参数
          ExportParams exportParams = new ExportParams(title, title);
          //设置Workbook工作簿（导出的是空列表，所以最后一个参数new一个即可,如果导出所有信息换成查询后的列表即可）
          Workbook workbook = ExcelExportUtil.exportExcel(exportParams,SecretRelatedPersonInfo.class,new ArrayList<SecretRelatedPersonInfo>());
          //调用Serviceimpl中的download方法
          secretRelatedPersonService.download(workbook);
          response.setContentType("application/vnd.ms-excel;charset=UTF-8");
          response.setCharacterEncoding("UTF-8");
          response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName, "UTF-8"));
          os = response.getOutputStream();
          workbook.write(os);
      }catch (Exception e){
          e.printStackTrace();
      }
  }
  

• ServiceImpl配置

  @Override
  public void download(Workbook workbook) {
      //性别列表
      List<String> gender = new ArrayList<>();
      gender.add("男");
      gender.add("女");
      //获取部门信息
      List<String> deptName =departmentManagerMapper.getDepartmentName();
  
      //生成下拉列表，列表信息未list的值，中间两个数对应的是字段orderNum
      FileUtil.selectList(workbook, 1, 1, gender.toArray(new String[gender.size()]));
      FileUtil.selectList(workbook, 4, 4, deptName.toArray(new String[deptName.size()]));
  }
  

• 工具类

  /**建议先加到工具类再查看说明
       * @firstRow 开始行号(下标0开始)
       * @lastRow  结束行号，最大65535
       * @firstCol 区域中第一个单元格的列号 (下标0开始)
       * @lastCol 区域中最后一个单元格的列号
       * @dataArray 下拉内容
       * @sheetHidden 隐藏的sheet编号（例如1,2,3），多个下拉数据不能使用同一个
       * */
  public static void selectList(Workbook workbook,int firstCol,int lastCol,String[] strings ) {
      Sheet sheet = workbook.getSheetAt(0);
      //  生成下拉列表
      //  只对(x，x)单元格有效
      CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(2, 65535, firstCol, lastCol);
      //  生成下拉框内容
      DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(strings);
      HSSFDataValidation dataValidation = new HSSFDataValidation(cellRangeAddressList, dvConstraint);
      //  对sheet页生效
      sheet.addValidationData(dataValidation);
  }
  

1.2.3 模板某列进行数据校验

• controller中如下

  • 第一种设置某一列输入信息为文本格式

    @UserLoginToken(methodName = "用户信息导入模板下载")
    @RequestMapping("/downloadFile")
    public void downloadFile(HttpServletResponse response) {
        ServletOutputStream os = null;
        try {
            String title = "用户信息";
            String fileName = "用户信息导入模板.xls";
            ExportParams exportParams = new ExportParams(title, title);
            Workbook workbook = ExcelExportUtil.exportExcel(exportParams, SecretRelatedPersonInfo.class, new ArrayList<SecretRelatedPersonInfo>());
            
            //先得到工作簿的第一个Sheet---------------
            Sheet sheet = workbook.getSheetAt(0);
            // 设置单元格格式为文本格式----------------
            CellStyle cellStyle = workbook.createCellStyle();
            DataFormat dataFormat = workbook.createDataFormat();
            cellStyle.setDataFormat(dataFormat.getFormat("@"));
            //设置单元格格式为"文本"（第一个参数是对应的列标）--------------------
            sheet.setDefaultColumnStyle(2, cellStyle);
            sheet.setDefaultColumnStyle(3, cellStyle);
            
            secretRelatedPersonService.download(workbook);
            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            response.setCharacterEncoding("UTF-8");
            response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName, "UTF-8"));
            os = response.getOutputStream();
            workbook.write(os);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    

  • 第二种设置单元格只能输入数字（数字校验）

    @UserLoginToken(methodName = "台账模板下载")
    @GetMapping("/downloadFile")
    public void downloadFile(HttpServletResponse response) {
        ServletOutputStream os = null;
        try {
            String title = "台账信息";
            String fileName = "台账信息列表模板.xls";
            ExportParams exportParams = new ExportParams(title, title);
            Workbook workbook = ExcelExportUtil.exportExcel(exportParams, SecretRelatedCarrierAccountInfo.class, new ArrayList<SecretRelatedCarrierAccountInfo>());
            Sheet sheet = workbook.getSheetAt(0);
            //查看download方法的使用（方法中说明了数字校验相关内容，看下面的内容介绍）
            carrierAccountService.download(workbook, sheet);
            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            response.setCharacterEncoding("UTF-8");
            response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName, "UTF-8"));
            os = response.getOutputStream();
            workbook.write(os);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    

• 数字校验的download

  @Override
  public void download(Workbook workbook, Sheet sheet) {
      //载体密级列表
      List<String> level = secretRelatedPersonMapper.getName(Constant.CARRIER_LEVEL);
      //载体列表
      List<String> type = dictionariesMapper.getName();
      //负责人列表
      List<String> personName = secretRelatedPersonMapper.getPersonName();
  
      FileUtil.selectList(workbook, 1, 1, level.toArray(new String[level.size()]));
      FileUtil.selectList(workbook, 6, 6, type.toArray(new String[type.size()]));
      FileUtil.selectList(workbook, 8, 8, personName.toArray(new String[personName.size()]));
  
      //数据校验,第二、三个参数指定的时候某一列
      FileUtil.validate(sheet, 3, 3);
      FileUtil.validate(sheet, 4, 4);
      FileUtil.validate(sheet, 5, 5);
  }
  

• FileUtils中方法如下

  public static void validate(Sheet sheet, int firstCol, int lastCol){
      //数据校验
      
      //获取一个数据校验的帮助类
      DataValidationHelper dvHelper = sheet.getDataValidationHelper();
      //通过帮助类做出一些限制
      DataValidationConstraint constraint = dvHelper.createNumericConstraint(
          //限制类型为十进制数字
          DataValidationConstraint.ValidationType.DECIMAL,
          //操作类型是在min和max之间
          DataValidationConstraint.OperatorType.BETWEEN,
          //double的最小值
          String.valueOf(Double.MIN_VALUE),
          //double的最大值
          String.valueOf(Double.MAX_VALUE));
      //只是范围列表（第二行到65535行，firstCol--lastCol列）
      CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(2, 65535, firstCol, lastCol);
      //创建校验
      DataValidation validation = dvHelper.createValidation(constraint, cellRangeAddressList);
      //设置提示信息
      validation.createErrorBox("提示","请输入数字");
      //添加校验到sheet中
      sheet.addValidationData(validation);
  }