SpringBoot整合Easyexcel实现将数据导出为Excel表格的功能

前言

本文主要介绍基于SpringBoot +MyBatis-Plus+Easyexcel+Vue实现缺陷跟踪系统中导出缺陷数据的功能，实现效果如下图：

后端实现

EasyExcel是一个基于Java的、快速、简洁、解决大文件内存溢出的Excel处理工具。他能让你在不用考虑性能、内存的等因素的情况下，快速完成Excel的读、写等功能。

本文使用springboot整合easyexcel对excel文件进行操作，来实现数据以excel形式导出的功能。

1.数据表设计

主要涉及的数据表有缺陷表、用户表、功能模块表，此处只展示bug表的相关内容。

CREATE TABLE `tb_bug` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT 'id',
  `bug_name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '缺陷名称',
  `bug_kind` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '缺陷类型',
  `confirm` tinyint(1) NOT NULL DEFAULT '0' COMMENT '0待确认1已确认2已拒绝',
  `priority` tinyint(1) DEFAULT NULL COMMENT '优先级，1、2、3、4',
  `status` tinyint(1) DEFAULT NULL COMMENT '0未解决1已解决',
  `creator_id` int(11) NULL DEFAULT NULL COMMENT '创建者id',
  `function_id` int(11) NULL DEFAULT NULL COMMENT '功能模块id',
  `update_time` date NULL DEFAULT NULL COMMENT '更新时间',
  `designee_id` int(11) NULL DEFAULT NULL COMMENT '被指派者id',
  `solve_time` date NULL DEFAULT NULL COMMENT '解决时间',  
  `bug_remark` varchar(999) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '缺陷描述',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC;

2.添加依赖

<!--spring-boot启动依赖-->
<dependency>
	<groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-starter</artifactId>
</dependency>
<!--spring-boot web依赖-->
<dependency>
	<groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!--mysql依赖-->
<dependency>
	<groupId>mysql</groupId>
	<artifactId>mysql-connector-java</artifactId>
	<version>8.0.27</version>
</dependency>
<!--mybatis-plus启动依赖-->
<dependency>
	<groupId>com.baomidou</groupId>
	<artifactId>mybatis-plus-boot-starter</artifactId>
	<version>3.5.1</version>
</dependency>
<dependency>
	<groupId>com.baomidou</groupId>
	<artifactId>mybatis-plus-extension</artifactId>
	<version>3.5.1</version>
</dependency>
<!--操作Excel依赖-->
<dependency>
	<groupId>com.alibaba</groupId>
	<artifactId>easyexcel</artifactId>
	<version>3.0.5</version>
</dependency>

3.实体类

@Getter
@Setter
@TableName("tb_bug")
public class Bug implements Serializable {
     private static final long serialVersionUID = 1L;
     //id
     @TableId(value = "id", type = IdType.AUTO)
     private Integer id;
     //缺陷名称
     private String bugName;
     //缺陷类型
     private String bugKind;
     //优先级，1、2、3、4
     private Integer priority;
     //0未解决1已解决2已关闭3激活
     private Integer status;
     //0待确认1已确认2已拒绝
     private Integer confirm;
     //创建者id
     private Integer creatorId;
     //功能模块id
     private Integer functionId;
     //更新时间
     private Date updateTime;
     //被指派者id
     private Integer designeeId;
     //解决时间
     private Date solveTime;
     //缺陷描述
     private String bugRemark;
}

4.model类

采用了@ExcelProperty的注解，其中value表示列名，index表示列名的索引值。

此外，还可以使用@ExcelIgnore注解，表示忽略这个字段，不导出这个字段的数据。

其他表格样式注解：

@HeadRowHeight(30)  表头行高
@ContentRowHeight(15)  //内容行高
@ColumnWidth(18)  //列宽
@ContentFontStyle(fontHeightInPoints = (short) 12)  //字体大小

@Data
@ColumnWidth(22)
@EqualsAndHashCode
public class BugOutputExcelModel {
    @ExcelProperty("缺陷id")
    private Integer id;

    @ExcelProperty(value = "缺陷名称")
    private String bugName;

    @ExcelProperty(value = "缺陷类型")
    private String bugKind;

    @ExcelProperty(value = "确认")
    private String confirm;

    @ExcelProperty(value = "优先级")
    private Integer priority;

    @ExcelProperty(value = "缺陷状态")
    private String status;

    @ExcelProperty(value = "创建者")
    private String creatorName;

    @ExcelProperty(value = "所属项目")
    private String productName;

    @ExcelProperty(value = "所属模块")
    private String functionName;

    @ExcelProperty(value = "更新时间")
    private Date updateTime;

    @ExcelProperty(value = "被指派者")
    private String designeeName;

    @ExcelProperty(value = "解决时间")
    private Date solveTime;

    @ExcelProperty(value = "缺陷描述")
    private String bugRemark;

}

5.工具类

public class ExcelUtil<T> {

    public static void outputExcel(HttpServletResponse response, List list, Class cla, String sheetName) throws IOException {
        response.setContentType("application/vnd.ms-excel");
        String fileName = URLEncoder.encode(sheetName, "UTF-8");
        response.setHeader("Content-Disposition","attachment;filename="+fileName);
        ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();
        WriteSheet sheet = EasyExcel.writerSheet(0, sheetName).head(cla).build();
        excelWriter.write(list, sheet);
        excelWriter.finish();
    }
}

6.Service类

public interface IBugService extends IService<Bug> {
    void outputBugByExcel() throws IOException;
}

@Service
public class BugServiceImpl extends ServiceImpl<BugMapper, Bug> implements IBugService {
    @Autowired
    private BugMapper bugMapper;
    @Autowired
    private UserMapper userMapper;
    @Autowired
    private ProductMapper productMapper;
    @Autowired
    private FunctionMapper functionMapper;
    @Autowired
    private ProductFunctionMapper productFunctionMapper;    
    @Autowired
    private HttpServletResponse response;
    @Override
    public void outputBugByExcel() throws IOException {
        List<BugOutputExcelModel> list = new ArrayList<>();
        QueryWrapper<Bug> wrapper = new QueryWrapper<Bug>();
        List<Bug> bug = bugMapper.selectList(wrapper);
        for (int i=0;i<bug.size();i++){
            User creator = userMapper.selectById(bug.get(i).getCreatorId());
            User designee = userMapper.selectById(bug.get(i).getDesigneeId());
            Function function = functionMapper.selectById(bug.get(i).getFunctionId());
            QueryWrapper<ProductFunction> queryWrapper = new QueryWrapper<ProductFunction>();
            queryWrapper.eq("function_id",bug.get(i).getFunctionId());
            ProductFunction productFunction = productFunctionMapper.selectOne(queryWrapper);
            Product product = productMapper.selectById(productFunction.getProductId());
            BugOutputExcelModel model = new BugOutputExcelModel();
            //将两个字段相同的对象进行属性值的复制
            BeanUtils.copyProperties(bug.get(i), model);
            String status = "";
            if (bug.get(i).getStatus()==0) status="未解决";
            else if(bug.get(i).getStatus()==1) status="已解决";
            else if(bug.get(i).getStatus()==2) status="已关闭";
            else status="激活";
            model.setStatus(status);
            String confirm="";
            if (bug.get(i).getConfirm()==0) confirm="待确认";
            else if(bug.get(i).getConfirm()==1) confirm="已确认";
            else confirm="已拒绝";
            model.setConfirm(confirm);
            model.setCreatorName(creator == null ? "" : creator.getRealname());
            model.setProductName(product.getProductName());
            model.setFunctionName(function.getFunctionName());
            model.setDesigneeName(designee == null ? "" : designee.getRealname());
            list.add(model);
        }
        ExcelUtil.outputExcel(response, list, BugOutputExcelModel.class, "缺陷信息");
    }
}

7.Controller类

@RestController
@RequestMapping("/bug")
public class BugController {
    @Autowired
    private IBugService bugService;

    @ApiOperation("bug管理-导出全部数据excel")
    @GetMapping("/outputBugByExcel")
    public void outputBugByExcel() throws IOException {
        bugService.outputBugByExcel();
    }
}

前端实现

vue+springboot实现导出excel文件_weixin_53952829的博客-CSDN博客

结束语

感谢大家的观看，希望对大家有帮助，有问题可以指出！