excel学习库

下面将Springboot中利用Mybatis查询后将结果导出的主要代码总结如下：

一、依赖

使用easyexcel实现导出excel功能

<!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.1.6</version>
</dependency>

二、mybatis的xml文件

//查询条件类pers.gl.vo.EmpQuery
package pers.gl.vo;
import java.util.Date;
import org.springframework.format.annotation.DateTimeFormat;
import lombok.Data;
@Data
public class EmpQuery {
    private String xm;
    @DateTimeFormat(pattern = "yyyy-MM-dd")
    private Date startDate;
    @DateTimeFormat(pattern = "yyyy-MM-dd")
    private Date endDate;
}

<!-- 带有条件的查询 -->
<select id="getEmpList" resultType="pers.gl.model.Emp" parameterType="pers.gl.vo.EmpQuery">
    select * from emp
    <include refid="getEmpListWhere"></include>
    order by id desc
</select>
<sql id="getEmpListWhere">
    <where>
        <if test="xm != null">
            xm like '%' #{xm} '%'
        </if>
        <if test="startDate != null">
            <![CDATA[
                and csrq >= #{startDate}
            ]]>
        </if>
        <if test="endDate != null">
            <![CDATA[
                and csrq<= #{endDate}
            ]]>
        </if>
    </where>
</sql>

三、Mapper

// 按照条件查询
public List<Emp> getEmpList(EmpQuery empQuery);

四、Service

// excel下载
public void downloadExcel(HttpServletResponse response, EmpQuery empQuery) {
    // 设置响应内容
    response.setContentType("application/vnd.ms-excel");
    response.setCharacterEncoding("UTF-8");// 防止下载的文件名字乱码
    try {
        // 文件以附件形式下载
        response.setHeader("Content-disposition",
                "attachment;filename=down_" + System.currentTimeMillis() + ".xlsx");
        // System.out.print(empQuery.toString());
        EasyExcel.write(response.getOutputStream(), Emp.class).sheet("文件下载")
                .doWrite(empMapper.getEmpList(empQuery));
    } catch (Exception e) {
        throw new RuntimeException(e);
    }
}

五、Controller

@GetMapping("/download2")
public void excelExport02(HttpServletResponse response, EmpQuery empQuery) {
     empService.downloadExcel(response, empQuery);
}

六、前端

 <a onclick="easyExcelExport2()" target="_blank" class="layui-btn layui-btn-sm">EasyExcel导出测试2</a>

function easyExcelExport2() {
    window.location.href=getContextPath() + '/emp/download2?'+$("#form-search").serialize();
}

感谢一位年轻人对我的指点。