SpringBoot+Mybatis-plus实现excel文件导出下载

目录

整体结构

加入依赖

实体类

Controller

DictTypeMapper

DictTypeMapper.xml

application.yml（配置文件）

运行

---

整体结构

加入依赖

在pom.xml中加入此依赖

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.1.7</version>
        </dependency>

实体类

在类上面加入一下注解，可以根据自己需求修改列宽等数据。

@ColumnWidth(20) //列宽
@ContentRowHeight(20)//数据行高
@HeadRowHeight(30)//表头高
@Data
@ExcelIgnoreUnannotated   //解决不加ExcelProperty注解的，也会出现在excel中

在想要展现在Excel表中属性加上注解 

@ExcelProperty(value = "字典名称",index = 1)  //value：在excel中列名，index：次序

 具体表现为下图：

package com.example.ideaworkplace.pojo;

import com.alibaba.excel.annotation.ExcelIgnoreUnannotated;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import com.baomidou.mybatisplus.annotation.FieldFill;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import lombok.Data;

import java.util.Date;
@ColumnWidth(20) //列宽
@ContentRowHeight(20)//数据行高
@HeadRowHeight(30)//表头高
@Data
@ExcelIgnoreUnannotated   //解决不加ExcelProperty注解的，也会出现在excel中
public class DictType {


    /** 字典主键 */
    @ExcelProperty(value = "主键id",index = 0)
    @TableId(type = IdType.AUTO)
    private Long id;

    /** 字典名称 */
    @ExcelProperty(value = "字典名称",index = 1)
    private String dictName;

    /** 字典类型 */
    @ExcelProperty(value = "字典类型",index = 2)
    private String dictType;

    /** 状态（0停用 1正常） */
    @ExcelProperty(value = "字典状态",index = 3)
    private String status;

    /** 创建者*/
    private String createBy;

    /**创建时间*/
    @TableField(fill = FieldFill.INSERT) //插入时自动填充
    private Date createTime;

    /**更新者*/
    private String updateBy;

    /**更新时间*/
    @TableField(fill = FieldFill.UPDATE) //更新时自动填充
    private Date updateTime;

    /**备注*/
    @ExcelProperty(value = "备注",index = 4)
    private String remark;


}

Controller

package com.example.ideaworkplace.controller;

import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.alibaba.excel.EasyExcel;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.example.ideaworkplace.mapper.DictTypeMapper;
import com.example.ideaworkplace.pojo.DictType;
import io.swagger.v3.oas.annotations.Operation;
import io.swagger.v3.oas.annotations.tags.Tag;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

@Tag(name = "字典类型操作")
@RestController
@RequestMapping("/dictType/page")
public class DictTypeController {
    
    @Resource
    private DictTypeMapper dictTypeMapper;

    @RequestMapping("/typeExport")
    @Operation(summary = "导出字典类型",description = "export dict type")
    public void typeExport(HttpServletRequest request, HttpServletResponse response){
        try {
            String filename = "字典类型";
            String userAgent = request.getHeader("User-Agent");
            if(userAgent.contains("MSIE")||userAgent.contains("Trident")){
                filename = URLEncoder.encode(filename,"UTF-8");            }else {
                filename = new String(filename.getBytes("UTF-8"),"ISO-8859-1");
            }
            response.setContentType("application/json.ms-exce");
            response.setCharacterEncoding("utf-8");
            response.addHeader("Content-Disposition","filename = " + filename + ".xlsx");
            QueryWrapper<DictType> queryWrapper = new QueryWrapper<>();
            queryWrapper.select("*");
            List<DictType> dictTypeList = dictTypeMapper.selectList(queryWrapper);
            EasyExcel.write(response.getOutputStream(),DictType.class).sheet("sheet").doWrite(dictTypeList);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    
}

DictTypeMapper

package com.example.ideaworkplace.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.ideaworkplace.pojo.DictType;

public interface DictTypeMapper extends BaseMapper<DictType> {
}

DictTypeMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.ideaworkplace.mapper.DictTypeMapper">
</mapper>

application.yml（配置文件）

spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/mysql?serverTimezone=Asia/Shanghai
    username: root
    password: root

mybatis-plus:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl #开启SQL语句打印
  mapper-locations: /mapper/*Mapper.xml

  global-config:
    db-config:
      id-type: auto   #全局自增主键

运行

运行之后在网页根据controller中的@RequestMapping注解输入网址，例如我的是：

loclahost:8080/dictType/page/typeExport

运行之后如下

 下载之后打开excel如下

 如果本篇文章对您有所帮助，来个一键三连吧好兄弟，笔芯！

注：本文可能会涉及一些其他依赖包引入，如有问题可以私信或者评论，已读必回。