POI从数据库查询数据写入Excel工具类

项目目录截图：

效果图：

POM.xml(只关注最后一个POI)

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.3.4.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com</groupId>
    <artifactId>exceldemo</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>exceldemo</name>
    <description>Demo project for Spring Boot</description>

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

    <dependencies>
        <!--springboot启动 web依赖 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <!-- SPRINGBOOT DRUID -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.10</version>
        </dependency>
        <!-- SPRINGBOOT JDBC -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <!-- MYSQL -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>${mysql-connector-java.version}</scope>
        </dependency>
        <!--mybatis-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.2</version>
        </dependency>
        <!--  mybatis -->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.1</version>
        </dependency>
        <!--devtools热部署-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <optional>true</optional>
            <scope>true</scope>
        </dependency>
        <!--lombok-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.16.18</version>
            <scope>provided</scope>
        </dependency>
        <!--POI-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.8</version>
        </dependency>
    </dependencies>
</project>

application.yml(项目配置)

#不需要的完全可以省略
server:
  port: 8888
spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    driver-class-name: com.mysql.jdbc.Driver
    url: jdbc:mysql://数据库连接:端口/表
    username: root
    password: qwer1234!@#$
    druid:
      initial-size: 5
      min-idle: 5
      max-active: 20
      max-wait: 60000
      min-evictable-idle-time-millis: 300000
      stat-view-servlet:
        login-password: 123
        login-username: root
  mvc:
    static-path-pattern: /**
  devtools:
    restart:
      enabled: true
      additional-paths: src/main/java
      exclude: WEB-INF/**
  resources:
    static-locations: classpath:/templates/
mybatis:
  mapper-locations: calsspath:mapper/*.xml
  type-aliases-package: com.exceldemo.entity

实体类：

package com.exceldemo.entity;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.experimental.Accessors;

import java.io.Serializable;

/**
 * @ClassName UserEntity
 * @Description
 * @Author oliver
 * @Date 2020/10/20 18:14
 */
@Data
@Accessors(chain = true)
@AllArgsConstructor
@NoArgsConstructor
public class UserEntity implements Serializable {

    private String id;
    private String username;
    private String password;
    private String salt;
}

Mapper：

package com.exceldemo.mapper;

import com.exceldemo.entity.UserEntity;
import org.apache.ibatis.annotations.Mapper;

import java.util.List;

@Mapper
public interface ExcelMpper {
    List<UserEntity> findAll();
}

<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.exceldemo.mapper.ExcelMpper">
    <select id="findAll" resultType="com.exceldemo.entity.UserEntity">
        select * from user
    </select>
</mapper>

Service：

package com.exceldemo.service;

import com.exceldemo.entity.UserEntity;

import java.util.List;

/**
 * @ClassName ExcelService
 * @Description TODO
 * @Author oliver
 * @Date 2020/10/20 17:59
 */

public interface ExcelService {

    List<UserEntity> findAll();
}
//=================================分割线==================================
package com.exceldemo.service.impl;

import com.exceldemo.entity.UserEntity;
import com.exceldemo.mapper.ExcelMpper;
import com.exceldemo.service.ExcelService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

/**
 * @ClassName ExcelServiceImpl
 * @Description TODO
 * @Author oliver
 * @Date 2020/10/20 17:59
 */
@Service
public class ExcelServiceImpl implements ExcelService {

    @Autowired
    private ExcelMpper excelMpper;

    @Override
    public List<UserEntity> findAll() {
        return excelMpper.findAll();
    }
}

Controller：

package com.exceldemo.controller;

import com.exceldemo.entity.UserEntity;
import com.exceldemo.service.ExcelService;
import com.exceldemo.utils.ExcelUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;

import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

/**
 * @ClassName ExcelController
 * @Description TODO
 * @Author oliver
 * @Date 2020/10/20 17:53
 */
@Controller
public class ExcelController {

    @Autowired
    private ExcelService excelService;

    @RequestMapping("/getExcel")
    public String excelDemo() {
        //这个主要用来装查询过来的数据
        List<List<Object>> list = new ArrayList<>();
        //数据库查询的数据
        List<UserEntity> userList = excelService.findAll();
        //遍历列表
        userList.stream().forEach(e->{
            ArrayList<Object> objects = new ArrayList<>();
            objects.add(e.getId());
            objects.add(e.getUsername());
            objects.add(e.getPassword());
            objects.add(e.getSalt());
            list.add(objects);
        });
        try {
            ExcelUtil.writeExcel(new UserEntity(),"某某列表",list);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return "hello";
    }
}

Util：

package com.exceldemo.utils;

import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.util.List;

/**
 * @ClassName ExcelUtil
 * @Description TODO
 * @Author oliver
 * @Date 2020/10/20 18:30
 */
public class ExcelUtil {

    public static void writeExcel(Object o, String tableName, List<List<Object>> entityList) throws IOException {
        //创建工作簿 HSSF 03 XSSF 07
        XSSFWorkbook hssfWorkbook = new XSSFWorkbook();
        //创建新的sheet
        XSSFSheet sheet = hssfWorkbook.createSheet("sheet1");
        //选中行一对象
        XSSFRow row = sheet.createRow(0);
        //getDeclaredFields获取所有包括私有，getFields只获取公有
        Field[] fields = o.getClass().getDeclaredFields();
        for (int i = 0; i < fields.length; i++) {
            row.createCell(i).setCellValue(fields[i].getName());
        }
        //遍历对应数据
        for (int i = 0; i < entityList.size(); i++) {
            XSSFRow row1 = sheet.createRow(i + 1);
            int col = 0;
            for (Object o1 : entityList.get(i)) {
                row1.createCell(col++).setCellValue(o1.toString());
            }
        }
        try {
            FileOutputStream fileOutputStream = new FileOutputStream("D:\\" + tableName +".xlsx");
            hssfWorkbook.write(fileOutputStream);
            fileOutputStream.close();
        } catch (FileNotFoundException e) {
            throw new FileNotFoundException("文件未找到");
        } catch (IOException e) {
            throw new IOException("系统异常");
        }
    }

    public static void readExcel(){
		//TODO
    }
}

读取excel暂时没写，希望对大家有帮助！！！