POI-JAVA读取excel生成word表格文档
程序员文章站
2022-06-05 22:37:48
...
平时,使用POI操作Excel,但是很少操作WORD。今天碰到操作WORD的需求,借鉴网上的文章,讲的非常透彻。
其中表格宽度设置,不起效果,代码以注释。通过每个单元格的宽度设置,间接放大宽度。
我的代码如下:
package sym_cpts.poi.docx; import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.IOException; import java.io.InputStream; import java.io.OutputStream; import java.math.BigInteger; import java.util.ArrayList; import java.util.List; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.poifs.filesystem.POIFSFileSystem; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellType; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.apache.poi.xwpf.usermodel.XWPFDocument; import org.apache.poi.xwpf.usermodel.XWPFParagraph; import org.apache.poi.xwpf.usermodel.XWPFRun; import org.apache.poi.xwpf.usermodel.XWPFTable; import org.apache.poi.xwpf.usermodel.XWPFTableCell; import org.apache.poi.xwpf.usermodel.XWPFTableRow; import org.junit.Test; import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTTcPr; public class DocTest { /** * 通过XWPFDocument对内容进行访问。对于XWPF文档而言,用这种方式进行读操作更佳。 * @throws Exception */ @Test public void testReadByDoc() throws Exception { InputStream is = new FileInputStream("D:\\table.docx"); XWPFDocument doc = new XWPFDocument(is); List<XWPFParagraph> paras = doc.getParagraphs(); } private static POIFSFileSystem fs; private static Workbook wb; private static Sheet sheet; private static Row row; public List<Row> readExcelToEntity(InputStream is,String ext) throws IllegalArgumentException, IllegalAccessException, NoSuchFieldException, SecurityException { List<Row> list = new ArrayList<Row>(); try { if(ext.equals("xls")){ //fs = new POIFSFileSystem(is); wb = new HSSFWorkbook(is); }else{ //fs = new POIFSFileSystem(is); wb = new XSSFWorkbook(is); } } catch (IOException e) { e.printStackTrace(); } sheet = wb.getSheetAt(0); // 得到总行数 int rowNum = sheet.getLastRowNum(); row = sheet.getRow(0); // 正文内容应该从第二行开始,第一行为表头的标题 for (int i = 1; i <= rowNum; i++) { row = sheet.getRow(i); list.add(row); } return list; } /** * 获取单元格数据内容为字符串类型的数据 * * @param cell Excel单元格 * @return String 单元格数据内容 */ private static String getStringCellValue(Cell cell) { if(cell == null) return ""; String strCell = ""; cell.setCellType(CellType.STRING); switch (cell.getCellTypeEnum()) { case STRING: strCell = cell.getStringCellValue(); break; case NUMERIC: strCell = String.valueOf(cell.getNumericCellValue()); break; case BOOLEAN: strCell = String.valueOf(cell.getBooleanCellValue()); break; case BLANK: strCell = ""; break; default: strCell = ""; break; } if (strCell.equals("") || strCell == null || cell == null) { return ""; } return strCell; } //生成word表格,并插入表格 public XWPFTable createData(XWPFDocument doc,String tableName,List<Row> subList){ //创建一个段落 XWPFParagraph para = doc.createParagraph(); //添加表名 XWPFRun run = para.createRun(); run.setBold(true); //加粗 run.setText(tableName); XWPFTable table = null; if(table == null){ table = doc.createTable(1, 5); List<XWPFTableRow> rows = table.getRows(); XWPFTableRow firstRow = rows.get(0); List<XWPFTableCell> firstRowCells = firstRow.getTableCells(); //单元格属性 CTTcPr cellPr0 = firstRowCells.get(0).getCTTc().addNewTcPr(); cellPr0.addNewTcW().setW(BigInteger.valueOf(1500)); CTTcPr cellPr1 = firstRowCells.get(1).getCTTc().addNewTcPr(); cellPr1.addNewTcW().setW(BigInteger.valueOf(1500)); CTTcPr cellPr2 = firstRowCells.get(2).getCTTc().addNewTcPr(); cellPr2.addNewTcW().setW(BigInteger.valueOf(1500)); CTTcPr cellPr3 = firstRowCells.get(3).getCTTc().addNewTcPr(); cellPr3.addNewTcW().setW(BigInteger.valueOf(1500)); CTTcPr cellPr4 = firstRowCells.get(4).getCTTc().addNewTcPr(); cellPr4.addNewTcW().setW(BigInteger.valueOf(1500)); //cellPr.addNewVAlign().setVal(STVerticalJc.CENTER); firstRowCells.get(0).setText("字段编码"); firstRowCells.get(1).setText("字段类型"); firstRowCells.get(2).setText("是否为空"); firstRowCells.get(3).setText("默认值"); firstRowCells.get(4).setText("字段描述"); //设置表格属性 // CTTblPr tablePr = table.getCTTbl().addNewTblPr(); // int width2 = table.getWidth(); //System.out.println(width2); // table.setWidth(60000); //System.out.println(table.getWidth()); //table.setWidth(width); //表格宽度 //CTTblWidth width = tablePr.addNewTblW(); // width.setW(BigInteger.valueOf(60000)); //BigInteger.valueOf("14.69") } for(int i=0;i<subList.size();i++){ XWPFTableRow newRow = table.createRow(); Row row = subList.get(i); String bianma = getStringCellValue(row.getCell(2)); String leixing = getStringCellValue(row.getCell(3)); String kong = getStringCellValue(row.getCell(4)); String moren = getStringCellValue(row.getCell(5)); String miaoshu = getStringCellValue(row.getCell(6)); List<XWPFTableCell> firstRowCells = newRow.getTableCells(); CTTcPr cellPr0 = firstRowCells.get(0).getCTTc().addNewTcPr(); cellPr0.addNewTcW().setW(BigInteger.valueOf(1500)); CTTcPr cellPr1 = firstRowCells.get(1).getCTTc().addNewTcPr(); cellPr1.addNewTcW().setW(BigInteger.valueOf(1500)); CTTcPr cellPr2 = firstRowCells.get(2).getCTTc().addNewTcPr(); cellPr2.addNewTcW().setW(BigInteger.valueOf(1500)); CTTcPr cellPr3 = firstRowCells.get(3).getCTTc().addNewTcPr(); cellPr3.addNewTcW().setW(BigInteger.valueOf(1500)); CTTcPr cellPr4 = firstRowCells.get(4).getCTTc().addNewTcPr(); cellPr4.addNewTcW().setW(BigInteger.valueOf(1500)); firstRowCells.get(0).setText(bianma); firstRowCells.get(1).setText(leixing); firstRowCells.get(2).setText(kong); firstRowCells.get(3).setText(moren); firstRowCells.get(4).setText(miaoshu); } return table; } /*** * 写一个表格 * @throws Exception */ @Test public void testWriteTable() throws Exception { XWPFDocument doc = new XWPFDocument(); List<Row> rowList = readExcelToEntity(new FileInputStream("C:\\Users\\Administrator\\Desktop\\cod-columns.xlsx"),"xlsx"); boolean flag = false; List<Row> subList = null; String tableName = null; for(int i=0;i<rowList.size();i++){ Row row = rowList.get(i); String cellTableName = getStringCellValue(row.getCell(1)); if(tableName == null || "".equals(tableName.trim())){ tableName = cellTableName; subList = new ArrayList<Row>(); } if(tableName.equals(cellTableName)){ subList.add(row); }else{ //对前面的subList进行word表格 createData(doc,tableName,subList); tableName = cellTableName; subList = new ArrayList<Row>(); //对 } } //文件不存在时会自动创建 OutputStream os = new FileOutputStream("C:\\Users\\Administrator\\Desktop\\table.docx"); //写入文件 doc.write(os); this.close(os); } /** * 关闭输出流 * @param os */ private void close(OutputStream os) { if (os != null) { try { os.close(); } catch (IOException e) { e.printStackTrace(); } } } }
辅助SQL
SELECT table_schema,table_name,column_name,column_type,IS_NULLABLE,COLUMN_DEFAULT,column_comment FROM `information_schema`.`COLUMNS` WHERE table_schema LIKE 'pr_cod_%' ORDER BY table_schema ASC,table_name ASC,ORDINAL_POSITION ASC LIMIT 20000;
下一篇: php+json用户注册返回json数据
推荐阅读
-
使用NOPI读取Word、Excel文档内容
-
使用NOPI读取Word、Excel文档内容
-
php导出word文档与excel电子表格的简单示例代码
-
将Excel2007表格粘贴到Word2007文档中
-
php导出word文档与excel电子表格的简单示例代码
-
使用python批量读取word文档并整理关键信息到excel表格的实例
-
excel根据rgb自动填充颜色_根据EXCEL数据自动生成WORD文档
-
荐 Python-docx 读写 Word 文档:读取正文、表格文本信息、段落格式、字体格式等
-
将Excel2010表格粘贴到Word2010文档中
-
在Word2010文档中插入Excel电子表格