欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

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;