使用POI将数据库中的数据导入到Excel中

准备工作：

• 创建表和数据

-- ----------------------------
-- Table structure for sys_role
-- ----------------------------
DROP TABLE IF EXISTS `sys_role`;
CREATE TABLE `sys_role`  (
  `ID` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '角色ID',
  `NAME` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '角色名称',
  `COMMENT` varchar(2048) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '角色说明',
  `CREATOR_ID` int(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT '角色创建者ID',
  `CREATE_TIME` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '角色创建日期',
  PRIMARY KEY (`ID`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '角色表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of sys_role
-- ----------------------------
INSERT INTO `sys_role` VALUES (1, 'ADMIN', '管理员', 10, '2020-12-25 09:58:14');
INSERT INTO `sys_role` VALUES (2, 'SALES', '销售员', 30, '2020-12-25 09:58:40');

---

创建一个Maven项目

<?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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>fun.gosuncn</groupId>
    <artifactId>poi</artifactId>
    <version>1.0</version>

    <dependencies>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.21</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.2</version>
        </dependency>
    </dependencies>

</project>

主要核心的代码：

package fun.gosuncn;

import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.xssf.usermodel.*;

import java.io.FileOutputStream;
import java.io.OutputStream;
import java.sql.*;

public class Test {

    private static final String url = "jdbc:mysql://gosuncn.fun:3306/gosuncn?serverTimezone=Asia/Shanghai&characterEncoding=utf8";

    private static final String user = "gosuncn";

    private static final String password = "gosuncn";

    private static final String sql = "SELECT `ID`, `NAME`, `COMMENT`, `CREATOR_ID`, `CREATE_TIME` FROM sys_role";

    public static void main(String[] args) {

        /**
         * 创建一个Excel文件对象
         */
        XSSFWorkbook workbook = new XSSFWorkbook();
        /**
         * 创建单元格样式
         */
        XSSFCellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        /**
         * 创建一个表单命名为<角色表>
         */
        XSSFSheet sheet = workbook.createSheet("角色表");
        /**
         * 设置列宽
         */
        sheet.setColumnWidth(0, 10 * 256);
        sheet.setColumnWidth(1, 10 * 256);
        sheet.setColumnWidth(2, 15 * 256);
        sheet.setColumnWidth(3, 15 * 256);
        sheet.setColumnWidth(4, 20 * 256);
        /**
         * 创建第一行
         */
        XSSFRow row = sheet.createRow(0);
        /**
         * 设置每列的标题
         */
        XSSFCell cell = row.createCell(0);
        cell.setCellStyle(style);
        cell.setCellValue("ID");
        cell = row.createCell(1);
        cell.setCellStyle(style);
        cell.setCellValue("NAME");
        cell = row.createCell(2);
        cell.setCellStyle(style);
        cell.setCellValue("COMMENT");
        cell = row.createCell(3);
        cell.setCellStyle(style);
        cell.setCellValue("CREATOR_ID");

        try (Connection connection = DriverManager.getConnection(url, user, password);
             PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
            ResultSet resultSet = preparedStatement.executeQuery();
            int rowNum = 1;
            while (resultSet.next()) {
                row = sheet.createRow(rowNum);
                int ID = resultSet.getInt("ID");
                String NAME = resultSet.getString("NAME");
                String COMMENT = resultSet.getString("COMMENT");
                int CREATOR_ID = resultSet.getInt("CREATOR_ID");
                Date CREATE_TIME = resultSet.getDate("CREATE_TIME");
                cell = row.createCell(0);
                cell.setCellStyle(style);
                cell.setCellValue(ID);
                cell = row.createCell(1);
                cell.setCellStyle(style);
                cell.setCellValue(NAME);
                cell = row.createCell(2);
                cell.setCellStyle(style);
                cell.setCellValue(COMMENT);
                cell = row.createCell(3);
                cell.setCellStyle(style);
                cell.setCellValue(CREATOR_ID);
                rowNum++;
            }
        } catch (SQLException exception) {
            System.out.println("SQLException Happening ...");
        }

        try (OutputStream outputStream = new FileOutputStream("poi.xlsx")) {
            workbook.write(outputStream);
        } catch (Exception exception) {
            System.out.println("SQLException Happening ...");
        }

    }
}

然后就可以在项目目录下打开poi.xlsx文件了。