EasyExcel 不使用实体类把 SQL 查询结果动态写入 Excel
一、依赖
commons-collections4-4.4.jar
commons-compress-1.20.jar
commons-logging-1.2.jar
easyexcel-3.0.0-beta3.jar
fastjson-1.2.15.jar
log4j-1.2.17.jar
mysql-connector-java-5.1.26.jar
poi-4.1.2.jar
poi-ooxml-4.1.2.jar
poi-ooxml-schemas-4.1.2.jar
slf4j-api-1.6.4.jar
slf4j-simple-1.6.4.jar
xmlbeans-3.1.0.jar
二、工具类
package com;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.List;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.Map;
public class Utils {
private static Connection conn;
public static Connection getSourceData() {
String dburl = "jdbc:mysql://xxx.xxx.xxx.xxx:3306/test?useUnicode=true&characterEncoding=utf8&useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=GMT%2B8";
String username = "root";
String password = "123456";
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection(dburl, username, password);
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
public static Map<String, List<List<String>>> searchSql(String sql, Connection con) {
Map<String, List<List<String>>> resultMap = new HashMap<String, List<List<String>>>();
List<List<String>> listMaps = new ArrayList<List<String>>();
PreparedStatement pstmt = null;
System.out.println("searchSql_<sql>: " + sql);
List<List<String>> listField = new ArrayList<List<String>>();
List<String> fieldList = new ArrayList<String>();
try {
pstmt = con.prepareStatement(sql);
ResultSet result = pstmt.executeQuery();
ResultSetMetaData resultSetMetaData = result.getMetaData();
for (int i = 1; i <= resultSetMetaData.getColumnCount(); i++) {
String columnLabel = resultSetMetaData.getColumnLabel(i);
listField.add(createHeader(columnLabel));
fieldList.add(columnLabel);
}
while (result.next()) {
ArrayList<String> list = new ArrayList<String>();
for (String string : fieldList) {
list.add(result.getString(string));
}
listMaps.add(list);
}
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
try {
con.close();
} catch (SQLException e1) {
e1.printStackTrace();
}
}
resultMap.put("result", listMaps);
resultMap.put("headers", listField);
return resultMap;
}
public static List<String> createHeader(String headName) {
return new ArrayList<>(Arrays.asList(headName));
}
}
三、测试
package com;
import java.io.File;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import com.alibaba.excel.EasyExcel;
public class Main {
private static File file = new File("1.txt");
private static String excelFilePathString = file.getAbsolutePath().replace("1.txt", "") + "\\excelfile";
private static String EXCEL_FILE_NAME = "text.xlsx";
public static void main(String[] args) {
String sqlString = "SELECT * FROM `test`;";
excelWriteBySqlConnection(sqlString);
}
public static void excelWriteBySqlConnection(String sqlString) {
Connection conn = Utils.getSourceData();
Map<String, List<List<String>>> listMaps = new HashMap<String, List<List<String>>>();
listMaps = Utils.searchSql(sqlString,conn);
String fileName = excelFilePathString + "\\" + EXCEL_FILE_NAME;
Long t1 = System.currentTimeMillis();
System.out.println("数据总行数:" + listMaps.get("result").size());
System.out.println("写入Excel文件开始----------");
EasyExcel.write(fileName).head(listMaps.get("headers"))
.sheet(EXCEL_FILE_NAME.replace(".xlsx", ""))
.doWrite(listMaps.get("result"));
Long t2 = System.currentTimeMillis();
System.out.println("写入Excel文件结束,总用时:" + ((t2 - t1) / 1000.00) + "s");
if (null != conn) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}