默认所用数据库连接池参考: http://blog.csdn.net/dowith7/article/details/19112793
1. 下载POI所需JAR包引入工程: http://poi.apache.org/download.html#POI-3.10-FINAL
2. 根据传入表名查询表结构详细信息
public void queryTableDetails(String tableName) {
StringBuffer sb = new StringBuffer();
sb.append("SELECT C.COLUMN_NAME || ',' || C.DATA_TYPE || CASE");
sb.append(" WHEN C.CHAR_LENGTH > 0 THEN");
sb.append(" '(' || C.CHAR_LENGTH || ')'");
sb.append(" WHEN C.CHAR_LENGTH = 0 AND C.DATA_SCALE IS NOT NULL AND");
sb.append(" C.DATA_SCALE > 0 THEN");
sb.append(" '(' || C.DATA_PRECISION || ',' || C.DATA_SCALE || ')'");
sb.append(" WHEN C.CHAR_LENGTH = 0 AND C.DATA_SCALE IS NOT NULL AND");
sb.append(" C.DATA_SCALE = 0 THEN");
sb.append(" '(' || C.DATA_PRECISION || ')'");
sb.append(" END || ',' || NVL(M.COMMENTS, '无')");
sb.append(" FROM ALL_TAB_COLUMNS C, USER_COL_COMMENTS M");
sb.append(" WHERE C.TABLE_NAME = M.TABLE_NAME");
sb.append(" AND C.COLUMN_NAME = M.COLUMN_NAME");
sb.append(" AND C.TABLE_NAME = ?");
connectionPool = C3P0DataSourceManage.getInstance();
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
String result = "";
try {
connection = connectionPool.getConnection();
statement = connection.prepareStatement(sb.toString());
statement.setString(1, tableName.toUpperCase());
resultSet = statement.executeQuery();
while (resultSet.next()) {
result = result + resultSet.getString(1) + ";";
}
result = "字段名称,字段类型,字段备注;" + result.substring(0, result.lastIndexOf(";"));
System.out.println(result);
} catch (Exception e) {e.printStackTrace();} finally {
try {
connectionPool.finalizeConn(connection, resultSet, statement);
} catch (Exception e) {
e.printStackTrace();
}
}
}
3. 将得到的类似"行1字段名称,行1字段类型,行1字段备注;行2字段名称,行2字段类型,行2字段备注;行3... "的查询结果传入并转化为List<List<Sting>>对象 其中泛型为String的List为查询结果单行数据
private List<List<String>> str2List(String str) {
List<List<String>> sheetList = new ArrayList<List<String>>();
String[] rows = str.split(";");
for(int r = 0; r < rows.length; r++) {
List<String> rowList = new ArrayList<String>();
String[] cells = rows[r].split(",");
for (int c = 0; c < cells.length; c++) {
rowList.add(cells[c]);
}
sheetList.add(rowList);
}
return sheetList;
}
4. 传入生成的List对象与路径导出到xls文件中
public void doExport(List<List<String>> sheetList) throws Exception {
new EquipStateMacroscopicExport("d:/export").CreateExcel(sheetList);
}
导出类简单示例EquipStateMacroscopicExport.java:
package com.demo;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
@SuppressWarnings("unchecked")
public class EquipStateMacroscopicExport {
public static final String DEFAULT_FOLDER_PATH = "d:/export";
private HSSFWorkbook workbook;
private String filePath;
private String sheetName = "表结构";
private String time;
private Map<String, HSSFCellStyle> cellStyles;
public CopyOfEquipStateMacroscopicExport() {
this(DEFAULT_FOLDER_PATH);
}
public CopyOfEquipStateMacroscopicExport(String filePath) {
time = new SimpleDateFormat("yyyyMMddHHmmss").format(new Date());
workbook = new HSSFWorkbook();
createStyles();
if (filePath.indexOf("\\") > 0)
filePath = filePath.replaceAll("\\", "/");
if (filePath.indexOf("//") > 0)
filePath = filePath.replace("//", "/");
this.filePath = filePath + time + ".xls";
}
@SuppressWarnings("deprecation")
public String CreateExcel(List<List<String>> sheetList) {
HSSFSheet sheet = workbook.createSheet(sheetName);
for (int s = 0; s < sheetList.size(); s++) {
HSSFRow row = sheet.createRow(s);
List rowsList = sheetList.get(s);
for (int r = 0; r < rowsList.size(); r++) {
HSSFCell cell = row.createCell((short) r);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(new HSSFRichTextString(rowsList.get(r)
.toString()));
if (s == 0) {
if (r == 0) {
sheet.setColumnWidth((short) r, (short) (30 * 250));
} else
sheet.setColumnWidth((short) r, (short) (30 * 200));
cell.setCellStyle(cellStyles.get("HEAD"));
} else
cell.setCellStyle(cellStyles.get("NORMAL"));
}
}
return createFile();
}
private String createFile() {
FileOutputStream stream = null;
File file = null;
try {
file = new File(filePath);
stream = new FileOutputStream(file);
workbook.write(stream);
return filePath;
} catch (Exception e) {
e.printStackTrace();
return null;
} finally {
try {
if (stream != null)
stream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
};
private void createStyles() {
cellStyles = new HashMap<String, HSSFCellStyle>();
HSSFCellStyle cellStyle = workbook.createCellStyle();
HSSFFont font = workbook.createFont();
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
cellStyle.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyle.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index);
cellStyle.setWrapText(true);
font.setFontHeightInPoints((short) 14);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
font.setFontName("微软雅黑");
cellStyle.setFont(font);
cellStyles.put("HEAD", cellStyle);
cellStyle = workbook.createCellStyle();
font = workbook.createFont();
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
cellStyle.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
cellStyle.setWrapText(true);
font.setFontHeightInPoints((short) 12);
font.setFontName("微软雅黑");
cellStyle.setFont(font);
cellStyles.put("NORMAL", cellStyle);
}
public String getSheetName() {
return sheetName;
}
public void setSheetName(String sheetName) {
this.sheetName = sheetName;
}
}