[简单]poi 创建word 2007表格(oracle)

      

import java.io.BufferedReader;
import java.io.FileOutputStream;
import java.io.Reader;
import java.math.BigInteger;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.xwpf.usermodel.BreakType;
import org.apache.poi.xwpf.usermodel.XWPFDocument;
import org.apache.poi.xwpf.usermodel.XWPFParagraph;
import org.apache.poi.xwpf.usermodel.XWPFTable;
import org.apache.poi.xwpf.usermodel.XWPFTableCell;
import org.apache.poi.xwpf.usermodel.XWPFTableRow;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTJc;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTShd;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTTbl;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTTblPr;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTTblWidth;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTTc;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTTcPr;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.STJc;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.STMerge;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.STShd;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.STTblWidth;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.STVerticalJc;

public class POI_创建Oracle表文档_S4_Test {
	public static void main(String[] args) throws Exception {
		POI_创建Oracle表文档_S4_Test t = new POI_创建Oracle表文档_S4_Test();
		t.createSimpleTableDocx("f:/saveFile/temp/sys_table_"
				+ System.currentTimeMillis() + ".docx");
	}

	public void createSimpleTableDocx(String savePath) throws Exception {
		XWPFDocument xdoc = new XWPFDocument();
		OracleTableInfo_S3 s3 = new OracleTableInfo_S3();
		List<List<String>> tableList = s3.getUserAllTable();
		for (int i = 0, len = tableList.size(); i < len; i++) {
			List<String> tableInfo = tableList.get(i);
			System.out.println("-----------=" + tableInfo.get(0));
			List<List<String>> tableColumnList = s3
					.getTableColumnInfo(tableInfo.get(0));
			createOracleTableInfo(xdoc, tableInfo, tableColumnList);
			addNewPage(xdoc, BreakType.COLUMN);
		}
		saveDocument(xdoc, savePath);
	}

	public void createOracleTableInfo(XWPFDocument xdoc,
			List<String> tableInfo, List<List<String>> tableColumnList) {
		XWPFTable xTable = xdoc.createTable(3 + tableColumnList.size(), 6);
		setTableWidth(xTable, "8000");
		int[] colWidthArr = new int[] { 1500, 3000, 1200, 850, 600, 850 };
		String[] colInfo = new String[] { "列名", "注释", "类型", "默认值", "空值", "主键" };
		XWPFTableRow row = xTable.getRow(2);
		row.setHeight(380);
		createColumnTitle(row, colInfo, colWidthArr);
		row = xTable.getRow(0);
		row.setHeight(380);
		setRowCellText(row.getCell(0), "中文名称", colWidthArr[0], true, 3,
				"BFBFBF", STVerticalJc.CENTER, STJc.CENTER);
		setRowCellText(row.getCell(1), "", colWidthArr[1], false, 0, null,
				STVerticalJc.CENTER, STJc.CENTER);
		setRowCellText(row.getCell(2), "英文名称", colWidthArr[2], true, 3,
				"BFBFBF", STVerticalJc.CENTER, STJc.CENTER);
		setRowCellText(row.getCell(3), tableInfo.get(0), colWidthArr[3], false,
				0, null, STVerticalJc.CENTER, STJc.CENTER);
		mergeCellsHorizontal(xTable, 0, 3, 5);
		row = xTable.getRow(1);
		row.setHeight(380);
		setRowCellText(row.getCell(0), "功能描述", colWidthArr[0], true, 3,
				"BFBFBF", STVerticalJc.CENTER, STJc.CENTER);
		setRowCellText(row.getCell(1), tableInfo.get(1), colWidthArr[1], false,
				0, null, STVerticalJc.CENTER, STJc.LEFT);
		mergeCellsHorizontal(xTable, 1, 1, 5);
		for (int i = 0, len = tableColumnList.size(); i < len; i++) {
			row = xTable.getRow(3 + i);
			row.setHeight(380);
			List<String> columnList = tableColumnList.get(i);
			for (int j = 0, jlen = columnList.size(); j < jlen; j++) {
				setRowCellText(row.getCell(j), columnList.get(j),
						colWidthArr[j], false, 0, null, STVerticalJc.CENTER,
						STJc.CENTER);
			}
		}
	}

	public void createColumnTitle(XWPFTableRow row, String[] colInfo,
			int[] colWidthArr) {
		for (int i = 0, len = colInfo.length; i < len; i++) {
			setRowCellText(row.getCell(i), colInfo[i], colWidthArr[i], true, 3,
					"BFBFBF", STVerticalJc.CENTER, STJc.CENTER);
		}
	}

	// 设置单元格文字
	public void setRowCellText(XWPFTableCell cell, String text, int width,
			boolean isShd, int shdValue, String shdColor, STVerticalJc.Enum jc,
			STJc.Enum stJc) {
		CTTc cttc = cell.getCTTc();
		CTTcPr ctPr = cttc.isSetTcPr() ? cttc.getTcPr() : cttc.addNewTcPr();
		CTShd ctshd = ctPr.isSetShd() ? ctPr.getShd() : ctPr.addNewShd();
		CTTblWidth cTblWidth = ctPr.addNewTcW();
		cTblWidth.setW(BigInteger.valueOf(width));
		cTblWidth.setType(STTblWidth.Enum.forString("dxa"));
		if (isShd) {
			if (shdValue > 0 && shdValue <= 38) {
				ctshd.setVal(STShd.Enum.forInt(shdValue));
			}
			if (shdColor != null) {
				ctshd.setColor(shdColor);
			}
		}
		ctPr.addNewVAlign().setVal(jc);
		cttc.getPList().get(0).addNewPPr().addNewJc().setVal(stJc);
		cell.setText(text);
	}

	// 跨列合并单元格
	public void mergeCellsHorizontal(XWPFTable table, int row, int fromCell,
			int toCell) {
		for (int cellIndex = fromCell; cellIndex <= toCell; cellIndex++) {
			XWPFTableCell cell = table.getRow(row).getCell(cellIndex);
			if (cellIndex == fromCell) {
				// The first merged cell is set with RESTART merge value
				cell.getCTTc().addNewTcPr().addNewHMerge()
						.setVal(STMerge.RESTART);
			} else {
				// Cells which join (merge) the first one, are set with CONTINUE
				cell.getCTTc().addNewTcPr().addNewHMerge()
						.setVal(STMerge.CONTINUE);
			}
		}
	}

	public void setTableWidth(XWPFTable table, String width) {
		CTTbl ttbl = table.getCTTbl();
		CTTblPr tblPr = ttbl.getTblPr() == null ? ttbl.addNewTblPr() : ttbl
				.getTblPr();
		CTTblWidth tblWidth = tblPr.isSetTblW() ? tblPr.getTblW() : tblPr
				.addNewTblW();
		CTJc cTJc = tblPr.addNewJc();
		cTJc.setVal(STJc.Enum.forString("center"));
		tblWidth.setW(new BigInteger(width));
		tblWidth.setType(STTblWidth.DXA);
	}

	public void addNewPage(XWPFDocument document, BreakType breakType) {
		XWPFParagraph xp = document.createParagraph();
		xp.createRun().addBreak(breakType);
	}

	public void saveDocument(XWPFDocument document, String savePath)
			throws Exception {
		FileOutputStream fos = new FileOutputStream(savePath);
		document.write(fos);
		fos.close();
	}
}

class OracleTableInfo_S3 {
	public List<List<String>> getUserAllTable() throws Exception {
		Connection conn = getOracleConnection();
		String sql = "select table_name,comments from user_tab_comments";
		PreparedStatement ps = null;
		ResultSet rs = null;
		ps = conn.prepareStatement(sql);
		rs = ps.executeQuery();
		List<List<String>> tableList = new ArrayList<List<String>>();
		while (rs.next()) {
			List<String> resultList = new ArrayList<String>();
			resultList.add(rs.getString(1));
			if (StringUtils.isNotBlank(rs.getString(2))) {
				resultList.add(rs.getString(2));
			} else {
				resultList.add("");
			}
			tableList.add(resultList);
		}
		closeConnection(rs, null, conn);
		return tableList;
	}

	public List<List<String>> getTableColumnInfo(String tableName)
			throws Exception {
		Connection conn = getOracleConnection();
		String sql = "select a.column_name as column_name, b.comments,a.data_type || '(' || nvl(a.data_precision, a.data_length) || (case when a.data_scale > 0 then ',' || a.data_scale end) || ')' as data_type, a.data_default, a.nullable, (select decode(d.constraint_type, 'P','Y','')  from all_constraints d, all_cons_columns c where d.constraint_type in ('P') and d.status = 'ENABLED' and d.constraint_name = c.constraint_name and d.owner = c.owner and c.column_name = a.column_name and c.table_name = a.table_name) as key_type from user_tab_columns a, user_col_comments b where a.column_name = b.column_name and a.table_name = b.table_name and b.table_name = upper('"
				+ tableName + "')";
		PreparedStatement ps = null;
		ResultSet rs = null;
		ps = conn.prepareStatement(sql);
		rs = ps.executeQuery();
		Reader reader;
		List<List<String>> columnList = new ArrayList<List<String>>();
		while (rs.next()) {
			List<String> resultList = new ArrayList<String>();
			resultList.add(rs.getString(1));
			resultList.add(rs.getString(2));
			resultList.add(rs.getString(3));
			reader = rs.getCharacterStream(4);
			if (reader != null) {
				BufferedReader br = new BufferedReader(reader);
				StringBuffer sb = new StringBuffer();
				String temp;
				while ((temp = br.readLine()) != null) {
					sb.append(temp);
				}
				if (sb.length() > 0) {
					resultList.add(sb.toString());
				} else {
					resultList.add("");
				}
			} else {
				resultList.add("");
			}
			resultList.add(rs.getString(5));
			resultList.add(rs.getString(6));
			columnList.add(resultList);
		}
		for (List<String> list : columnList) {
			System.out.println(list);
		}
		closeConnection(rs, null, conn);
		return columnList;
	}

	public void closeConnection(ResultSet rs, Statement statement,
			Connection conn) {
		try {
			if (rs != null) {
				rs.close();
			}
			if (statement != null) {
				statement.close();
			}
			if (conn != null) {
				conn.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public Connection getOracleConnection() {
		String driver = "oracle.jdbc.driver.OracleDriver";
		String url = "jdbc:oracle:thin:@localhost:1521:xe";// 要操作的数据库名称
		String username = "admin";// 数据库用户名
		String password = "123456";// 密码
		return getConnection(driver, url, username, password);
	}

	public Connection getConnection(String driver, String url, String userName,
			String passwd) {
		Connection conn = null;
		try {
			Class.forName(driver);
			conn = DriverManager.getConnection(url, userName, passwd);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return conn;
	}
}

 

    结果为: