spring boot生成Excel表格 导出/导入

导入代码如下：

1、依赖包:

 <!--easypoi导出excel-->
        <!--easypoi-base 导入 导出的工具包,可以完成Excel导出,导入,Word的导出,Excel的导出功能-->
        <dependency>
            <groupId>org.jeecg</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>2.3.1</version>
        </dependency>
        <!--easypoi-web  耦合了spring-mvc 基于AbstractView,极大的简化spring-mvc下的导出功能-->
        <dependency>
            <groupId>org.jeecg</groupId>
            <artifactId>easypoi-web</artifactId>
            <version>2.3.1</version>
        </dependency>
        <!--easypoi-annotation 基础注解包,作用与实体对象上,拆分后方便maven多工程的依赖管理-->
        <dependency>
            <groupId>org.jeecg</groupId>
            <artifactId>easypoi-annotation</artifactId>
            <version>2.3.1</version>
        </dependency>

2、建立表格字段

@Data
@ExcelTarget("UserExcelImport")
public class UserExcelImport Serializable{
    private static final long serialVersionUID = 7820944112359402531L;
    @Excel(name = "姓名", orderNum = "1", isImportField = "name", width = 20)
    private String name;

    @Excel(name = "学号",orderNum = "2",isImportField = "number",width = 20)
    private String facilityNumber;

    @Excel(name = "年龄",orderNum = "3",isImportField = "age")
    private String age;
}

3、service

 /**
     * 以Excel模板表导入学生信息
     *
     * @param list 数据集合
     */
    Result importStudent(Integer peopleId, List<Student> list);

4、impl

  @Autowired
    private StudentRepository studentRepository;

    /**
     * 以Excel模板表导入学生系统信息
     *
     * @param list 数据集合
     */
    @Override
    public Result importEquipmentSystem(Integer peopleId, List<Student> list) {
        try {
            list.forEach(li -> {
                Student student = new Student();
                //创建时间 
                student.setCreationTime(new Date());
                //学号 
                student.setNumber(li.getNumber());
                //姓名 
                student.setName(li.getName());
                //年龄
                student.setAge(li.getAge());
                //建档人 
                student.setPeople(li.getPeople());
                //保存数据 
                StudentRepository.save(student);
            });
            //自定义返回枚举
            return ResultUtil.success(ReturnStateEnum.IMPORT_SUCCESS.getDescript());
        } catch (Exception e) {
            e.printStackTrace();
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            return ResultUtil.error(ReturnCode.ERROR, e.getMessage());
        }
    }

 

5、controller

 @Autowired
 private StudentService studentService;

 @PostMapping(value = "/v1/import")
 public Result importStudent(MultipartHttpServletRequest request) {
     MultipartFile file = request.getFile("file");
     //操作人ID
     Integer peopleId = Integer.parseInt(request.getParameter("creator"));
     List<Student> list = ExcelUtils.importExcel(file, 1, 1, UserExcelImport .class);
     return studentService.importStudent(peopleId, list);
 }

导出代码如下：

1、controller

   /**
     * Excel模板表下载
     */
    @GetMapping(value = "/v1/template")
    public void downExcelTemplate(HttpServletResponse response) throws ExcelExportException {
        String title = "XXXX表格";
        String sheet = "XXXX表格";
        // 生成文件
        ExcelUtils.downLoadExcel("模版.xls", response, EquipmentExcelImport.class, title, sheet);
    }

导入/导出工具类:

 /**
     * excel导入
     *
     * @param file       excel文件
     * @param titleRows  头部属于第几行文件
     * @param headerRows 头部有几
     * @param pojoClass  导入实体类型
     */
    public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass) {
        if (file!=null) {
            // 参数设置
            ImportParams params = new ImportParams();
            params.setTitleRows(titleRows);
            params.setHeadRows(headerRows);
            params.setStartRows(1);
            List<T> list;
            try {
                // excel导入
                list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
            } catch (NoSuchElementException e) {
                throw new ExcelExportException("Excel 文件不能为空");
            } catch (Exception e) {
                throw new ExcelExportException(e.getMessage());
            }
            return list;
        }
        return null;
    }

    /**
     * excel模板下载
     *
     * @param fileName  文件名称(测试.xls/.xlsx)
     * @param response  HttpServletResponse 请求返回
     * @param pojoClass 类名.class 泛型
     * @param title     生成表名称
     * @param sheet     生成表底标签名称
     */
    public static void downLoadExcel(String fileName, HttpServletResponse response, Class<?> pojoClass,String title,String sheet) {
        try {
            // 告诉浏览器用什么软件可以打开此文件
            response.setHeader("content-Type", "application/vnd.ms-excel");
            // 下载文件的默认名称
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
            // excel做成
            Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams(title,sheet), pojoClass, new ArrayList<>());
            workbook.write(response.getOutputStream());
        } catch (IOException e) {
            // 异常处理
            throw new ExcelExportException(e.getMessage());
        }
    }

Swagger测试：

    /**
     * Swagger测试
     *
     * @param file Excel表文件
     * @return 返回操作结果
     */
    @PostMapping(value = "/v1/import/equipment")
    @ApiOperation(value = "以Excel表导入设备台账数据", notes = "参数FileName")
    public Result importEquipment(MultipartFile file) {
     //creator 创建人id
        Integer peopleId = Integer.parseInt(request.getParameter("creator"));
     //操作人部门id
        Integer deptId = Integer.parseInt(request.getParameter("department"));
     //excel导入 file文件 titleRows：表头位置  titleHeader：表头行数 第一列，第二行
        List<EquipmentExcelImport> list = ExcelUtils.importExcel(file, 1, 1, EquipmentExcelImport.class);
     导入数据库
        return excelImportService.importEquipment(peopleId, deptId, list);
    }