Java 开发实习任务总结

前言

文章主要记录本人到公司实习的工作任务，解决任务思路以及错误排查以此来达到分享和开发过程总结的目的。

一、需求任务

1.1 明确需求

从数据库表中导出 excel 表形式的数据，导出的数据如下：

用户信息表

用户记录表

1.2 确定方向

使用 Springboot 框架结合JAVA解析Excel工具 EasyExcel 来实现。
EasyExcel官网：https://github.com/alibaba/easyexcel
Java解析、生成Excel比较有名的框架有Apache poi、jxl。但他们都存在一个严重的问题就是非常的耗内存，poi有一套SAX模式的API可以一定程度的解决一些内存溢出的问题，但POI还是有一些缺陷，比如07版Excel解压缩以及解压后存储都是在内存中完成的，内存消耗依然很大。easyexcel重写了poi对07版Excel的解析，一个3M的excel用POI sax解析依然需要100M左右内存，改用easyexcel可以降低到几M，并且再大的excel也不会出现内存溢出；03版依赖POI的sax模式，在上层做了模型转换的封装，让使用者更加简单方便。

二、编写代码

2.1 导入依赖

<!--EasyExcel相关依赖-->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.0.5</version>
</dependency>

2.2 编写实体类

@Data
public class ReportExcelBean {

    @ExcelProperty(value = "年龄", index = 0)
    private Integer age;

    @ExcelProperty(value = "性别", index = 1)
    private Integer sex;

    @ExcelProperty(value = "设备型号", index = 2)
    private String deviceVersion;

    @ExcelProperty(value = "手机号码", index = 3)
    private String phone;
    @ExcelProperty(value = "累计访问次数", index = 4)
    private Integer reportVisitNum;

    @ExcelProperty(value = "累计生成报告数", index = 5)
    private Integer reportNum;

    @ExcelProperty(value = "首次访问日期", index = 6)
    @JsonFormat(pattern = "yyyy-MM-dd", timezone = "GMT+8")
    private Date dateCreated;

    @ExcelProperty(value = "最后访问日期", index = 7)
    @JsonFormat(pattern = "yyyy-MM-dd", timezone = "GMT+8")
    private Date dateUpdated;

    @ExcelProperty(value = "7日留存", index = 8)
    private String isSaveSevenDay;

    @ExcelProperty(value = "30日留存", index = 9)
    private String isSaveThirtyDay;

    @ExcelProperty(value = "90日留存", index = 10)
    private String isSaveNinetyDay;

    @ExcelProperty(value = "左眼视力", index = 11)
    private String leftEye;

    @ExcelProperty(value = "右眼视力", index = 12)
    private String rightEye;

    @ExcelProperty(value = "左眼视力变化", index = 13)
    private String leftEyeChange;

    @ExcelProperty(value = "右眼视力变化", index = 14)
    private String rightEyeChange;
}

导出 Excel 时，若需要表头在属性上@ExcelProperty(value = “id”, index = 0) 注解。其中 value 代表在导出 Excel 时，该字段对应的表头名称；index 代表该字段对应的表头位置（从0开始）。如下图：

2.3 接口上添加抽象方法

public interface ReportManager extends IService<Report> {
    //导出用户数据
    void export(HttpServletResponse response) throws IOException;
    
}

2.4 实现类实现抽象方法

@Service
public class ReportManagerImpl extends ServiceImpl<ReportMapper, Report> implements ReportManager {

    @Autowired
    ReportMapper reportMapper;
    
    @Override
    public void export(HttpServletResponse response) throws IOException {
        List<ReportExcelBean> reportExcelBeanList = reportMapper.userOtherInfo();

        //导出
        String fileName = "report数据";
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-Disposition", "attachment;filename=" + new String( fileName.getBytes("gb2312"), "ISO8859-1" ) + ".xlsx");
        ServletOutputStream out = response.getOutputStream();
        EasyExcel.write(response.getOutputStream())
                .head(ReportExcelBean.class)
                .excelType(ExcelTypeEnum.XLSX)
                .sheet("report数据")
                .doWrite(reportExcelBeanList);
        out.flush();
        response.getOutputStream().close();
        out.close();
    }
}

2.5 ReportMapper添加方法

在 ReportMapper.java 中 添加 userOtherInfo() 方法返回 ReportExcelBean 的List集合。

@Mapper
public interface ReportMapper extends BaseMapper<Report> {
     List<ReportExcelBean> userOtherInfo();
}

2.6 ReportMapper.xml中编写Sql

查询符合条件的用户信息

<select id="userOtherInfo" resultType="com.boe.visual.chart.service.bean.ReportExcelBean">
   SELECT
   user.age as age,
   user.sex as sex,
   user.device_version as deviceVersion,
   user.phone as phone,
   IFNULL(a.report_visitNum, 0) AS reportVisitNum,
   IFNULL(a.report_num, 0) AS reportNum,
   user.date_created as dateCreated,
   IFNULL(user.date_updated, '') AS dateUpdated,
   IF(IFNULL(TIMESTAMPDIFF(DAY,user.date_created,user.date_updated),'')&lt;=7 AND IFNULL(TIMESTAMPDIFF(DAY,user.date_created,user.date_updated),'') &gt; 0,"是","否") as isSaveSevenDay,
   IF(IFNULL(TIMESTAMPDIFF(DAY,user.date_created,user.date_updated),'')&lt;=30 AND IFNULL(TIMESTAMPDIFF(DAY,user.date_created,user.date_updated),'') &gt; 7,"是","否") as isSaveThirtyDay,
   IF(IFNULL(TIMESTAMPDIFF(DAY,user.date_created,user.date_updated),'')&lt;=90 AND IFNULL(TIMESTAMPDIFF(DAY,user.date_created,user.date_updated),'') &gt;30,"是","否") as isSaveNinetyDay,
   IFNULL(a.last_left_eye, '') AS leftEye,
   IFNULL(a.last_right_eye, '') AS rightEye,
   IFNULL(
           CONCAT(
                   a.last_left_eye, '-', b.first_left_eye
               ),
           ''
       ) AS leftEyeChange,
   IFNULL(
           CONCAT(
                   a.last_right_eye, '-', b.first_right_eye
               ),
           ''
       ) AS rightEyeChange
   FROM
   t_electronic_vision_user `user`
   LEFT JOIN (
   SELECT
   ANY_VALUE(rr.left_eye) AS last_left_eye,
   ANY_VALUE(rr.right_eye) AS last_right_eye,
   re.report_visitNum,
   re.report_num,
   re.user_id,
   rr.date_created
   FROM
   t_electronic_vision_report rr,
   (
   SELECT
   user_id,
   COUNT(is_visitor) AS report_visitNum,
   COUNT(id) AS report_num,
   MAX(date_created) AS last_test_time
   FROM
   t_electronic_vision_report
   GROUP BY
   user_id
   ) re
   WHERE
   rr.user_id = re.user_id
   AND re.last_test_time = rr.date_created
   GROUP BY
   rr.user_id,
   rr.date_created
   ) a ON user.user_id = a.user_id
   LEFT JOIN (
   SELECT
   ANY_VALUE(rr.left_eye) AS first_left_eye,
   ANY_VALUE(rr.right_eye) AS first_right_eye,
   re.report_visitNum,
   re.report_num,
   re.user_id,
   rr.date_created
   FROM
   t_electronic_vision_report rr,
   (
   SELECT
   user_id,
   COUNT(is_visitor) AS report_visitNum,
   COUNT(id) AS report_num,
   MIN(date_created) AS first_test_time
   FROM
   t_electronic_vision_report
   GROUP BY
   user_id
   ) re
   WHERE
   rr.user_id = re.user_id
   AND re.first_test_time = rr.date_created
   GROUP BY
   rr.user_id,
   rr.date_created
   ) b ON user.user_id = b.user_id
   ORDER BY
   user.device_version ASC,
   user.user_id ASC
</select>

2.7 编写Controller层代码

@RestController
@Api(tags = "EasyExcelController" , value = "EasyExcel导出测试")
@RequestMapping("/admin")
public class EasyExcelController{
    @Autowired
    ReportManager reportManager;

    @GetMapping("userOtherInfo/excel")
    public void excelExportOther(HttpServletResponse response) throws IOException {
        reportManager.export(response);
    }

}

2.8 测试

启动项目输入请求路径，出现下载好的report数据(10).xlsx

打开report数据(10).xlsx

总结

刚开始做时有的导出的列为空值，没有数据可以通过debug调试，查看要查出的列是否为null。如果是的话大概率是在2.6 ReportMapper.xml中编写Sql中查询的列名和实体类ReportExcelBean的属性名不一致导致的。如下要一一对应：