前言

本文主要介紹基于SpringBoot +MyBatis-Plus+Easyexcel+Vue實(shí)現(xiàn)缺陷跟蹤系統(tǒng)中導(dǎo)出缺陷數(shù)據(jù)的功能，實(shí)現(xiàn)效果如下圖：

后端實(shí)現(xiàn)

EasyExcel是一個(gè)基于Java的、快速、簡(jiǎn)潔、解決大文件內(nèi)存溢出的Excel處理工具。他能讓你在不用考慮性能、內(nèi)存的等因素的情況下，快速完成Excel的讀、寫(xiě)等功能。

本文使用springboot整合easyexcel對(duì)excel文件進(jìn)行操作，來(lái)實(shí)現(xiàn)數(shù)據(jù)以excel形式導(dǎo)出的功能。

1.數(shù)據(jù)表設(shè)計(jì)

主要涉及的數(shù)據(jù)表有缺陷表、用戶表、功能模塊表，此處只展示bug表的相關(guān)內(nèi)容。

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 '缺陷類(lèi)型',
  `confirm` tinyint(1) NOT NULL DEFAULT '0' COMMENT '0待確認(rèn)1已確認(rèn)2已拒絕',
  `priority` tinyint(1) DEFAULT NULL COMMENT '優(yōu)先級(jí)，1、2、3、4',
  `status` tinyint(1) DEFAULT NULL COMMENT '0未解決1已解決',
  `creator_id` int(11) NULL DEFAULT NULL COMMENT '創(chuàng)建者id',
  `function_id` int(11) NULL DEFAULT NULL COMMENT '功能模塊id',
  `update_time` date NULL DEFAULT NULL COMMENT '更新時(shí)間',
  `designee_id` int(11) NULL DEFAULT NULL COMMENT '被指派者id',
  `solve_time` date NULL DEFAULT NULL COMMENT '解決時(shí)間',  
  `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啟動(dòng)依賴-->
<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啟動(dòng)依賴-->
<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.實(shí)體類(lèi)

@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;
     //缺陷類(lèi)型
     private String bugKind;
     //優(yōu)先級(jí)，1、2、3、4
     private Integer priority;
     //0未解決1已解決2已關(guān)閉3激活
     private Integer status;
     //0待確認(rèn)1已確認(rèn)2已拒絕
     private Integer confirm;
     //創(chuàng)建者id
     private Integer creatorId;
     //功能模塊id
     private Integer functionId;
     //更新時(shí)間
     private Date updateTime;
     //被指派者id
     private Integer designeeId;
     //解決時(shí)間
     private Date solveTime;
     //缺陷描述
     private String bugRemark;
}

4.model類(lèi)

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

此外，還可以使用@ExcelIgnore注解，表示忽略這個(gè)字段，不導(dǎo)出這個(gè)字段的數(shù)據(jù)。

其他表格樣式注解：

@HeadRowHeight(30) ?表頭行高
@ContentRowHeight(15) ?//內(nèi)容行高
@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 = "缺陷類(lèi)型")
    private String bugKind;

    @ExcelProperty(value = "確認(rèn)")
    private String confirm;

    @ExcelProperty(value = "優(yōu)先級(jí)")
    private Integer priority;

    @ExcelProperty(value = "缺陷狀態(tài)")
    private String status;

    @ExcelProperty(value = "創(chuàng)建者")
    private String creatorName;

    @ExcelProperty(value = "所屬項(xiàng)目")
    private String productName;

    @ExcelProperty(value = "所屬模塊")
    private String functionName;

    @ExcelProperty(value = "更新時(shí)間")
    private Date updateTime;

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

    @ExcelProperty(value = "解決時(shí)間")
    private Date solveTime;

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

}

5.工具類(lèi)

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類(lèi)

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();
            //將兩個(gè)字段相同的對(duì)象進(jìn)行屬性值的復(fù)制
            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="已關(guān)閉";
            else status="激活";
            model.setStatus(status);
            String confirm="";
            if (bug.get(i).getConfirm()==0) confirm="待確認(rèn)";
            else if(bug.get(i).getConfirm()==1) confirm="已確認(rèn)";
            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類(lèi)

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

    @ApiOperation("bug管理-導(dǎo)出全部數(shù)據(jù)excel")
    @GetMapping("/outputBugByExcel")
    public void outputBugByExcel() throws IOException {
        bugService.outputBugByExcel();
    }
}

前端實(shí)現(xiàn)

vue+springboot實(shí)現(xiàn)導(dǎo)出excel文件_weixin_53952829的博客-CSDN博客

結(jié)束語(yǔ)

感謝大家的觀看，希望對(duì)大家有幫助，有問(wèn)題可以指出！文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-623158.html

到了這里，關(guān)于SpringBoot整合Easyexcel實(shí)現(xiàn)將數(shù)據(jù)導(dǎo)出為Excel表格的功能的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！