准备工作:
- 创建表和数据
-- ----------------------------
-- 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
文件了。