POI生成XLS: 以导出ORACLE中表结构为例

默认所用数据库连接池参考: 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;
	}

}