github代码地址
POI导出Excel
从数据库导出指定表结构和数据到Excel中,表名为Excel名,字段为标题行,数据为对应字段的列。
根据业务需求可以重写以达到复用!!!(开玩笑的,看看就好,主要为了学习poi)。
一个工具类…搞定,POI,如果对某位同学学习poi或者有其他帮助,随便点个赞就好😆
PIOOutputExcel
package myproject.util;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;
import java.io.*;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
@Component
public final class PIOOutputExcel {
private static final String SELECT = "select";
private static final String FROM = "FROM";
private static String sql = "";
static Logger logger = LoggerFactory.getLogger(PIOOutputExcel.class);
static String table = "";
protected static void OutputExcel(String tablename,String excelVersion,String filePath){
if("".equals(tablename) || null == tablename){
if(tablename.length() <= 0){
logger.info("导出表名为空,导出失败");
return;
}
logger.info("导出表名为空,导出失败");
return;
}
if(!getDBTableName(tablename)){
logger.info("表不存在");
return;
}
List<String> dbTableColumn = getDBTableColumn(table);
if(dbTableColumn.isEmpty()){
logger.info("表中字段为空");
return;
}
boolean xlsx = outPutTitle(dbTableColumn, excelVersion, filePath);
if(!xlsx){
logger.info("标题写入失败,请确认后续数据是否成功写入,若数据未正确写入请确认是否参数正确,且排查错误");
return;
}
Map<List<String>, Integer> stringStringMap = OutputData(dbTableColumn);
if(!OutputDBData(stringStringMap,excelVersion,filePath)){
logger.info("追加写入数据时出现异常");
return;
}
}
protected static boolean getDBTableName(String tablename){
Connection connection = JdbcConnectionUtil.getConnection();
ResultSet resultSet = null;
try {
DatabaseMetaData metaData = connection.getMetaData();
resultSet = metaData.getTables("","","",new String[]{"TABLE"});
while(resultSet.next()){
String tname = resultSet.getString(3);
if(tname.toLowerCase().equals(tablename.toLowerCase())){
table = tname;
return true;
}
}
} catch (SQLException e) {
logger.info("查询数据库所有表名时出现异常");
return false;
}finally {
JdbcConnectionUtil.close(connection,null,resultSet);
}
return false;
}
protected static List<String> getDBTableColumn(String tablename){
Connection connection = JdbcConnectionUtil.getConnection();
ResultSet resultSet = null;
PreparedStatement preparedStatement = null;
List<String> tablecolumn = new ArrayList<>();
String sql = SELECT +" "+"*"+" " + FROM+ " " + tablename;
try {
preparedStatement = connection.prepareStatement(sql);
ResultSetMetaData metaData = preparedStatement.getMetaData();
int columnCount = metaData.getColumnCount();
for(int i = 1; i <= columnCount; i++){
tablecolumn.add(metaData.getColumnName(i));
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcConnectionUtil.close(connection,preparedStatement,resultSet);
}
return tablecolumn;
}
protected static boolean outPutTitle(List<String> columnName,String excelVersion,String filePath){
Workbook workbook = null;
OutputStream os = null;
if("".equals(excelVersion) || null == excelVersion || "xlsx".equals(excelVersion)){
workbook = new XSSFWorkbook();
}else if("xls".equals(excelVersion)){
workbook = new HSSFWorkbook();
}else{
logger.info("所输入Excel版本不存在");
return false;
}
if(!tTAS(columnName,workbook)){
logger.info("字段名为空");
return false;
}
try {
os = new FileOutputStream(filePath.endsWith("/")?filePath+table+"."+excelVersion:filePath+"/"+table+"."+excelVersion);
workbook.write(os);
return true;
} catch (Exception e) {
logger.info("写出Excel时出现异常");
return false;
}finally {
try {
workbook.close();
os.close();
} catch (IOException e) {
logger.info("关闭输出流时出现异常");
return false;
}
}
}
protected static boolean tTAS(List<String> columnName,Workbook workbook){
if(columnName.isEmpty()){
return false;
}
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
Font font = workbook.createFont();
font.setBoldweight(Font.BOLDWEIGHT_BOLD);
cellStyle.setFont(font);
Sheet sheet = workbook.createSheet();
sheet.setDefaultColumnWidth(30);
Row row = sheet.createRow(0);
for(int i = 0; i<columnName.size(); i++){
Cell cell = row.createCell(i);
cell.setCellValue(columnName.get(i));
cell.setCellStyle(cellStyle);
}
return true;
}
protected static Map<List<String>,Integer> OutputData(List<String> columnName){
if(columnName.isEmpty()){
return null;
}
Connection connection = JdbcConnectionUtil.getConnection();
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
Map<List<String>,Integer> data = new HashMap<>();
sql = SELECT + " ";
for(int i=0; i<columnName.size(); i++){
if(i == columnName.size()-1){
sql = sql + columnName.get(i) + " ";
}else{
sql = sql + columnName.get(i) + ",";
}
}
sql = sql + FROM + " " + table;
try {
preparedStatement = connection.prepareStatement(sql);
resultSet = preparedStatement.executeQuery();
int columnNum = 0;
while(resultSet.next()){
List<String> hdata = new ArrayList<>();
for(int i=0; i<columnName.size(); i++){
String value = resultSet.getString(columnName.get(i));
if(value==null||"".equals(value)){
value = "";
}
hdata.add(value);
}
data.put(hdata,columnNum);
columnNum++;
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcConnectionUtil.close(connection,preparedStatement,resultSet);
}
return data;
}
protected static boolean OutputDBData(Map<List<String>,Integer> data,String excelVersion,String filePath){
InputStream inputStream;
Workbook workbook = null;
OutputStream outputStream = null;
try {
inputStream = new FileInputStream(filePath.endsWith("/")?filePath+table+"."+excelVersion:filePath+"/"+table+"."+excelVersion);
if("xlsx".equals(excelVersion)){
workbook = new XSSFWorkbook(inputStream);
}else if("xls".equals(excelVersion)){
workbook = new HSSFWorkbook(inputStream);
}
Sheet sheet = workbook.getSheetAt(0);
inputStream.close();
data.forEach((datas,size)->{
++size;
Row row = sheet.createRow(size);
for(int i=0; i<datas.size(); i++){
row.createCell(i).setCellValue(datas.get(i));
}
});
outputStream = new FileOutputStream(filePath.endsWith("/")?filePath+table+"."+excelVersion:filePath+"/"+table+"."+excelVersion);
workbook.write(outputStream);
return true;
} catch (Exception e) {
logger.info("写入数据时出现异常");
return false;
}finally {
try {
if(workbook!=null){
workbook.close();
}
if(outputStream!=null){
outputStream.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
public static void main(String[] args) {
PIOOutputExcel.OutputExcel("userDemo","xls","D:");
}
}