[简单]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, colWidthArr, 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;
}
}
　　
　　结果为: