SpingBoot实现excel文件导出

我们在做项目的时候，经常用到导出功能，比如根据条件检索出有用的信息展示到页面，但是光展示还不行，还必须要以excel文件形式导出来，今天我们来用SpringBoot来实现这个功能，我们今天就以人员信息为列，导出所有检索到的人员信息。

环境搭起来：

先创建人员信息表：

CREATE TABLE `user` (
  `id` varchar(225) NOT NULL,
  `name` varchar(225) DEFAULT NULL,
  `age` int(22) DEFAULT NULL,
  `dept` varchar(225) DEFAULT NULL,
  `education` varchar(225) DEFAULT NULL,
  `competent` varchar(223) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入一些人员信息

然后上代码：框架搭起来：

/**
 * zigao
 */
public class ResponseMessage implements Serializable {
    int status;
    Object message;
    String errmsg;

    public int getStatus() {
        return this.status;
    }

    public void setStatus(int status) {
        this.status = status;
    }

    public Object getMessage() {
        return this.message;
    }

    public void setMessage(Object message) {
        this.message = message;
    }

    public String getErrmsg() {
        return this.errmsg;
    }

    public void setErrmsg(String errmsg) {
        this.errmsg = errmsg;
    }

    public ResponseMessage() {
    }

    public ResponseMessage(int status) {
        this.status = 0;
    }

    public ResponseMessage(Object data) {
        this.status = 0;
        this.message = data;
    }

    public ResponseMessage(int status, Object data) {
        this.status = status;
        this.message = data;
    }

    public ResponseMessage(int status, String err) {
        this.status = status;
        this.errmsg = err;
    }
}

/**
 *
 *
 * @author zigao
 * @date 2020年08月01日 11:58
 */
@RequestMapping("/export")
@RestController
public class UserController {

    //打印日志
    public Logger logger = LoggerFactory.getLogger(this.getClass());

    @Autowired
    private UserService userService;

    @GetMapping("/exportUser")
    public void listUser(HttpServletRequest request,HttpServletResponse response)throws IOException {
        ResponseMessage responseMessage = new ResponseMessage(0);
        try{
            String agent = request.getHeader("USER-AGENT").toLowerCase();
            System.out.println(agent);
            List<User> listUser = userService.getUser();
            userService.exportUserData(listUser,response);
            responseMessage.setMessage(listUser);
        }catch (Exception ex){
            logger.error("导出信息异常", ex);
            throw new RuntimeException(ex.getMessage());
        }
    }
}

@Data
public class User {

    /**
     * 用户编码
     */
    @ExcelProperty("用户编码")
    private String id;

    /**
     * 名字
     */
    @ExcelProperty("名字")
    private String name;

    /**
     * 年龄
     */
    @ExcelProperty("年龄")
    private Integer age;

    /**
     *部门
     */
    @ExcelProperty("部门")
    private String dept;

    /**
     * 学历
     */
    @ExcelProperty("学历")
    private String education;

    /**
     * 职称
     */
    @ExcelProperty("职称")
    private String competent;
}

@Service
public class UserService {


    @Autowired
    private UserMapper userMapper;

    /**
     * 查询用户信息
     * @return
     */
    public List<User> getUser(){
       return userMapper.getUser();
    }

    /**
     * 文件导出实现类
     * @param listUser
     * @param response
     * @throws IOException
     */
    public void exportUserData(List<User> listUser, HttpServletResponse response)throws IOException{
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        String fileName = "统计用户信息" + System.currentTimeMillis() + ".xlsx";
        String file = URLEncoder.encode(fileName, "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + file);
        EasyExcel.write(response.getOutputStream(), User.class).sheet("sheet1").doWrite(listUser);
      }
}

@Repository
@Mapper
public interface UserMapper {
    /**
     * 获取用户信息
     * @param
     * @return
     */
   @Select("select * from user")
   List<User> getUser();

}

@SpringBootApplication
@MapperScan("com.zigao.com")
public class zigaoApplication {
    public static void main(String[] args) {
        SpringApplication.run(zigaoApplication.class, args);
        System.out.println("===============================已启动================================");
    }
}

 

server:
  port: 8888
  servlet:
    # 上下文路径
    context-path: /api
  tomcat:
    uri-encoding: UTF-8

pagehelper:
  helperDialect: mysql
  reasonable: true
  supportMethodsArguments: true
  pageSizeZero: false #pageSize=0

spring:
  application:
    #  项目名称
    name: zigao
  profiles:
    # 环境
    active: dev
  jackson:
    # json 序列化配置
    date-format: yyyy-MM-dd HH:mm:ss
    time-zone: GMT
  http:
    #  编码
    encoding:
      charset: UTF-8
      enabled: true
      force: true

spring:
  datasource:
    driverClassName: com.mysql.jdbc.Driver
    url: jdbc:mysql://127.0.0.1:3306/test3?useUnicode=true&characterEncoding=utf-8
    username: root
    password: 1qaz2WSX
  #设置文件大小
  servlet:
    multipart:
      max-file-size: 50MB
      max-request-size: 50MB
mybatis:
  mapper-locations: classpath:mapper/*Mapper.xml
  type-aliases-package: com.zigao
  configuration:
    call-setters-on-nulls: true
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

 <groupId>zigao</groupId>
    <artifactId>zigao.cnn</artifactId>
    <version>1.0-SNAPSHOT</version>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.2.7.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <name>zigao</name>
    <description>zigao.cnn</description>

    <properties>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>

        <dependency>
            <groupId>commons-codec</groupId>
            <artifactId>commons-codec</artifactId>
            <version>1.13 </version>
        </dependency>

        <!--分页包-->
        <!--阿里json包-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.41</version>
        </dependency>
        <!-- lombok -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.8</version>
        </dependency>
        <!--web起步依赖-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <!--alibaba easyexcel-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.0.5</version>
        </dependency>
        <!--mybatis起步依赖-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.2</version>
        </dependency>
        <!--springboot起步依赖-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>

        <!--mysql连接依赖-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
            <version>5.1.46</version>
        </dependency>
        <!--spring-boot测试工具起步依赖-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.assertj</groupId>
            <artifactId>assertj-core</artifactId>
        </dependency>
    </dependencies>

    <build>
        <plugins>

            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

 

代码完事了，我们来测试下：