poi导出word表格详解

清疚 2022-05-19 04:17 289阅读 0赞

一、效果如下

![70][]

二、js代码

function export_word(){ //导出word
        var url = "czzsca/exportWord.do";
        this.export(url);
    }
    function export(url){
        var currentyear = $("#mainYear").val() * 1;
        var key_columns = ['xh',"d_name","d_unit","d_number","d_estimate","d_lastyear","jnjh","d_remarks","d_depart","d_executeunit"];
        var value_columns = ['序号','项目名称','单位','数量','总概算','至'+(currentyear-1)+'年完成',currentyear+'年计划','附注','责任部门','计划执行单位'];
        window.location.href= url+"?key_columns="+key_columns+"&value_columns="+value_columns+"&title="+currentyear+"年 更新改造计划 正式计划草案";
    }

三、controller代码（controller代码比较长，主要是格式化每一行的数据，主要是往工具类里传List<List<String>>参数，泛型的List<String>是每一行，String是每一列）

//导出word
    	@RequestMapping("/exportWord.do")
    	@ResponseBody
    	public ReturnMsg exportWord(String[] key_columns,String[] value_columns,String title,HttpServletResponse response){
    		ReturnMsg rm = new ReturnMsg();
    		try {
    			response.setCharacterEncoding("UTF-8");
    	        response.setContentType("application/msexcle");   
    	        response.setHeader("content-disposition", "attachment;filename="+new String("正式计划草案".getBytes("gb2312"),"ISO8859-1")+".doc");
    	        List<List<String>> content = new ArrayList<List<String>>();
    	        List<List<String>> head = new ArrayList<List<String>>();
    	        String jnjhzh = new BigDecimal(this.map.get("jnjhzh")==null||"".equals(this.map.get("jnjhzh")+"")?"0":this.map.get("jnjhzh")+"").setScale(1, RoundingMode.UP)+"";
    	        String trimpro = new BigDecimal(this.map.get("tzjh")==null||"".equals(this.map.get("tzjh")+"")?"0":this.map.get("tzjh")+"").setScale(1, RoundingMode.UP)+"";
    	        String temp = new BigDecimal(trimpro).subtract(new BigDecimal(jnjhzh))+"";
    	        if("0".equals(temp)) {
    	        	temp = "";
    	        }
    	        head = ExportUtil.getCzzscaList(jnjhzh, trimpro, temp);
    	        Map dounit = new HashMap();
    	        dounit.put("dwlx", 0);
    	        dounit.put("user_type", 1);
                List<Map> d_executeunit = unitDao.selectByPrimaryKey(dounit);
                Map zrbm = new HashMap();
                zrbm.put("dwlx", 1);
                zrbm.put("user_type", 1);
                List<Map> d_zrbm = unitDao.selectByPrimaryKey(zrbm);
                List<Map> zmtype = nd_caController.findProType("2-3-4");
            	for (Map map: this.list) {
                	List<String> row = new ArrayList<String>();
                	for (int i = 0; i < key_columns.length; i++) {
                		System.out.print(map.get(key_columns[i])+",");
                		Object Otzjh = map.get("tzjh")!=null?map.get("tzjh"):0;
                		Object Ojnjh = map.get("jnjh")!=null?map.get("jnjh"):0;
                		BigDecimal tzjh = null;
                		BigDecimal jnjh = null;
                		if ("tzjh".equals(key_columns[i])||"jnjh".equals(key_columns[i])||"d_lnorde".equals(key_columns[i])) {
                			if(Otzjh instanceof BigDecimal) {
                    			tzjh = (BigDecimal)Otzjh;
                    		}else {
        						tzjh = new BigDecimal(Otzjh+"");
        					}
                    		if(Ojnjh instanceof BigDecimal) {
                    			jnjh = (BigDecimal)Ojnjh;
                    		}else {
                    			jnjh = new BigDecimal(Ojnjh+"");
        					}
                    		
                		}
                		if ("d_lnorde".equals(key_columns[i])) {
                			if(map.get("flag")!=null&&"2".equals((map.get("flag")+""))) {
                				row.add("");
                			}else {
                				row.add(tzjh.subtract(jnjh).setScale(1, RoundingMode.UP)+""); //不为整数则四舍五入
        					}
    					}else if ("xh".equals(key_columns[i])&&map.get("xh")!=null) {
                			if(map.get("xh").toString().indexOf("、")>-1) {
                				row.add(map.get("xh").toString().replace("、", "").trim());
                			}else {
                				row.add(map.get("xh")+""); //不为整数则四舍五入
        					}
    					}else if("tzjh".equals(key_columns[i])&&map.get("tzjh")!=null) { 
    						if(ExportUtil.isNumeric(tzjh+"")) { //判断相减是否为整数
    							row.add(tzjh+"");
            				}else {
            					String stzjh = tzjh.setScale(1, RoundingMode.UP)+"";
            					row.add("0".equals(stzjh.charAt(stzjh.indexOf(".")+1)+"")?stzjh.replace(".0", ""):stzjh);//不为整数则四舍五入
    						}
    					}else if("d_estimate".equals(key_columns[i])&&map.get("d_estimate")!=null) { 
    						if("0".equals(map.get("d_estimate")+"")) {
    							row.add("");
    						}else {
    							row.add(map.get("d_estimate")+"");
    						}
    					}else if("d_number".equals(key_columns[i])&&map.get("d_number")!=null) { 
    						if("0".equals(map.get("d_number")+"")) {
    							row.add("");
    						}else {
    							row.add(map.get("d_number")+"");
    						}
    					}else if("jnjh".equals(key_columns[i])&&map.get("jnjh")!=null) { 
    						if(ExportUtil.isNumeric(jnjh+"")) { //判断相减是否为整数
    							row.add(jnjh+"");
            				}else {
            					String sjnjh = jnjh.setScale(1, RoundingMode.UP)+"";
            					row.add("0".equals(sjnjh.charAt(sjnjh.indexOf(".")+1)+"")?sjnjh.replace(".0", ""):sjnjh); //不为整数则四舍五入
    						}
    					}else if("d_pro_type".equals(key_columns[i])&&map.get("d_pro_type")!=null) { 
    						for (Map m : zmtype) {
    							if((map.get("d_pro_type")+"").equals(m.get("TYPE_ID")+"")){
    								row.add(m.get("NUM")+"");
    								break;
    							}
    						}
    	            	}else if("d_depart".equals(key_columns[i])&&map.get("d_depart")!=null) { 
    	            		String d_depart = map.get("d_depart")+"";
    	            		String depart = "";
                			for (Map m : d_zrbm) {
                				if((d_depart).indexOf(",")>-1) {
                					for (String string : d_depart.split(",")) {
                						if((string).equals(m.get("ID")+"")) {
                							depart += ","+m.get("UNITNAME")+"";
                							break;
                    					}
    								}
                				}else {
                					if((d_depart).equals(m.get("ID")+"")) {
                						depart = ","+m.get("UNITNAME")+"";
                						break;
                					}
                				}
    						}
                			if (StringUtils.isNotBlank(depart)) {
                				depart = depart.substring(1, depart.length()).replace(",", "\r ");
                				row.add(depart);
    						}else {
    							row.add("");
    						}
    					}else if("d_executeunit".equals(key_columns[i])&&map.get("d_executeunit")!=null) { 
    						boolean flag;
                			for (Map m : d_executeunit) {
                				flag = false;
                				String executeunit = map.get("d_executeunit")+"";
                				if((executeunit).indexOf(",")>-1) {
                					for (String string : executeunit.split(",")) {
                						if((string).equals(m.get("ID")+"")) {
                							row.add((m.get("UNITNAME")+"").trim());
                    						flag = true;
                    						break;
                    					}
    								}
                					if (flag) {
                						break;
    								}
                					
                				}else {
                					if((executeunit).equals(m.get("ID")+"")) {
                						row.add((m.get("UNITNAME")+"").trim());
                						break;
                					}
                				}
    						}
                		}else {
                			if(map.get(key_columns[i])==null) {
                				row.add("");
                			}else {
                				row.add((map.get(key_columns[i])+"").trim());
    						}
    					}
        			}
                	content.add(row);
    			}
            	head.addAll(content);
            	int[] colWidths = new int[] { 600, 3000, 500, 500, 800, 800 ,800,3000,800,1500};  
    			XWPFDocument document = ExportUtil.exportWord(title,value_columns, head,colWidths,1,7);
    			OutputStream out =  response.getOutputStream();  
    	        document.write(out);
    	        out.close();
    	        rm.setCode("1");
    			rm.setMsg("导出成功");
    		} catch (Exception e) {
    			e.printStackTrace();
    			rm.setCode("0");
    			rm.setMsg("导出失败，请刷新重试。");
    		}
    		return rm;
    	}

四、工具类代码

/**
    	 * 
    	 * @Date 2018年7月19日 上午11:19:04
    	 * @Description 导出word 
    	 * @Fcunction exportWord
    	 * @param title
    	 * @param value_columns
    	 * @param list
    	 * @return XWPFDocument
    	 *
    	 */
    	public static XWPFDocument exportWord(String title,String[] value_columns, List<List<String>> list,int[] colWidths,int one,int two) {
    		XWPFDocument doc= new XWPFDocument();
    		
    		CTDocument1 document = doc.getDocument(); 
    	    CTBody body = document.getBody(); 
    	    if(!body.isSetSectPr()){ 
    	         body.addNewSectPr(); 
    	    } 
    	    CTSectPr section = body.getSectPr(); 
    
    	    if(!section.isSetPgSz()){ 
    	        section.addNewPgSz(); 
    	    } 
    	    CTPageSz pageSize = section.getPgSz(); 
    	    pageSize.setW(BigInteger.valueOf(15840));
    	    pageSize.setH(BigInteger.valueOf(12240));
    	    pageSize.setOrient(STPageOrientation.LANDSCAPE);
    		
    		//添加标题
    	    XWPFParagraph titleParagraph = doc.createParagraph();
    	    
    	    //设置段落居中
    	    titleParagraph.setAlignment(ParagraphAlignment.CENTER);
    	
    	    XWPFRun titleParagraphRun = titleParagraph.createRun();
    	    titleParagraphRun.setText(title);
    	    titleParagraphRun.setColor("000000");
    	    titleParagraphRun.setFontSize(20);
    	
    	    //表格
    	    XWPFTable ComTable = doc.createTable();
    	    
    	    //设置指定宽度
    	    CTTbl ttbl = ComTable.getCTTbl(); 
    	    CTTblGrid tblGrid = ttbl.addNewTblGrid();
    	    for (int i : colWidths) {
    	    	CTTblGridCol gridCol = tblGrid.addNewGridCol();  
    	    	gridCol.setW(new BigInteger(i+""));  
    		}
    	    
    	    //表头
    	    XWPFTableRow rowHead = ComTable.getRow(0);
    	    XWPFParagraph cellParagraph = rowHead.getCell(0).getParagraphs().get(0);
    	    cellParagraph.setAlignment(ParagraphAlignment.CENTER); //设置表头单元格居中
    	    XWPFRun cellParagraphRun  = cellParagraph.createRun();
    		cellParagraphRun.setFontSize(10); //设置表头单元格字号
    		cellParagraphRun.setBold(true); //设置表头单元格加粗
    		cellParagraphRun.setText(value_columns[0]);
    	    for (int i = 1; i < value_columns.length; i++) {
    	    	if(value_columns[i].indexOf("增减")>-1) {
    	    		cellParagraph = rowHead.addNewTableCell().getParagraphs().get(0);
    	    		cellParagraph.setAlignment(ParagraphAlignment.CENTER); //设置表头单元格居中
    	    		cellParagraphRun  = cellParagraph.createRun();
    				cellParagraphRun.setFontSize(10); //设置表头单元格居中
    				cellParagraphRun.setBold(true);
    				cellParagraphRun.setText("增减(+ / -)");
    			}else {
    				cellParagraph = rowHead.addNewTableCell().getParagraphs().get(0);
    	    		cellParagraph.setAlignment(ParagraphAlignment.CENTER); //设置表头单元格居中
    	    		cellParagraphRun  = cellParagraph.createRun();
    				cellParagraphRun.setFontSize(10); //设置表头单元格居中
    				cellParagraphRun.setBold(true); //设置表头单元格加粗
    				cellParagraphRun.setText(value_columns[i]);
    			}
    		}
    	    int rows = list.size();
    	    //表格内容
    	    for (int i = 0; i < rows; i++) {
    	    	XWPFTableRow rowsContent = ComTable.createRow();
    	    	for (int j = 0; j < list.get(i).size(); j++) {
    	    		XWPFParagraph cellParagraphC = rowsContent.getCell(j).getParagraphs().get(0);
    	    		if(j!=one&&j!=two) {
    	    			cellParagraphC.setAlignment(ParagraphAlignment.CENTER); //设置表格内容居中
    	    		}
    	    		XWPFRun cellParagraphRunC  = cellParagraphC.createRun();
    	    		cellParagraphRunC.setFontSize(10); //设置表格内容字号
    	    		cellParagraphRunC.setText(list.get(i).get(j)+""); //单元格段落加载内容
    			}
    	    }
    	    if(rows==0) {
    	    	for (int i = 0; i < value_columns.length; i++) {
    	    		XWPFTableCell cell = ComTable.getRow(0).getCell(i);
    	            cell.setVerticalAlignment(XWPFTableCell.XWPFVertAlign.CENTER); //垂直居中
    			}
    	    }else {
    	        //设置居中
    	        for (int i = 0; i <= rows; i++) {
    	            for (int j = 0; j < list.get(0).size(); j++) {
    	                XWPFTableCell cell = ComTable.getRow(i).getCell(j);
    	                cell.setVerticalAlignment(XWPFTableCell.XWPFVertAlign.CENTER); //垂直居中
    	            }
    	        }
    		}
    		return doc;
    	}
    
    	/**
    	 * 
    	 * @Date 2018年7月19日 上午11:19:21
    	 * @Description 判断是否是整数 
    	 * @Fcunction isNumeric
    	 * @param str
    	 * @return boolean
    	 *
    	 */
    	public static boolean isNumeric(String str) {
    		if(str.indexOf(".")>0) {
    			return false;
    		}
    		return true;
    	}
    	
    	/**
    	 * 
    	 * @Date 2018年7月19日 上午11:44:48
    	 * @Description 正式计划草案获取前两行 
    	 * @Fcunction getCzzscaList
    	 * @param jnjhzh
    	 * @param trimpro
    	 * @param temp
    	 * @return List<List<String>>
    	 *
    	 */
    	public static List<List<String>> getCzzscaList(String jnjhzh,String trimpro,String temp) {
    		List<List<String>> list = new ArrayList<List<String>>();
    		List<String> l1 = new ArrayList<String>(); //固定第一行
    	    l1.add(""); 
    	    l1.add("年度总计"); 
    	    l1.add(""); 
    	    l1.add(""); 
    	    l1.add(""); 
    	    l1.add(""); 
    	    l1.add(jnjhzh); 
    	    l1.add(""); 
    	    l1.add(""); 
    	    l1.add(""); 
    	    list.add(l1);
    	    List<String> l2 = new ArrayList<String>(); //固定第二行
    	    l2.add("");
    	    l2.add("搜索总计");
    	    l2.add("");
    	    l2.add("");
    	    l2.add("");
    	    l2.add("");
    	    l2.add("");
    //	    l2.add(trimpro+"\r100%");
    	    l2.add("");
    	    l2.add("");
    	    l2.add("");
    	    list.add(l2);
    		return list;
    	}

五、详解：

1.设置纸张大小

（1）创建完XWPFDocument后，默认为A4纸张，如需调整纸张大小，这里我是要用A3大小使用如下代码

XWPFDocument doc= new XWPFDocument();
    CTDocument1 document = doc.getDocument(); 
    CTBody body = document.getBody(); 
    if(!body.isSetSectPr()){ 
        body.addNewSectPr(); 
    } 
    CTSectPr section = body.getSectPr(); 
    
    if(!section.isSetPgSz()){ 
        section.addNewPgSz(); 
    } 
    CTPageSz pageSize = section.getPgSz(); 
    pageSize.setW(BigInteger.valueOf(15840));
    pageSize.setH(BigInteger.valueOf(12240));
    pageSize.setOrient(STPageOrientation.LANDSCAPE);

（2）默认A4纸张大小的话只用XWPFDocument

XWPFDocument doc= new XWPFDocument();

（3）使用CTPageSz类需要导入ooxml-schemas的jar包（所有需要的jar包maven依赖在底部）

2.单元格列宽度

（1）每一列宽度相等自适应，不需单独设置每一列宽度的话使用如下代码

//表格
    XWPFTable ComTable = doc.createTable();
    //表格自适应宽度
    CTTblWidth comTableWidth = ComTable.getCTTbl().addNewTblPr().addNewTblW();
    comTableWidth.setType(STTblWidth.DXA);
    comTableWidth.setW(BigInteger.valueOf(9072));

（2）单独设置每列宽度

//表格
    XWPFTable ComTable = doc.createTable();	    
    //设置指定宽度
    CTTbl ttbl = ComTable.getCTTbl(); 
    CTTblGrid tblGrid = ttbl.addNewTblGrid();
    int[] colWidths = new int[] { 600, 3000, 500, 500, 800, 800 ,800,3000,800,1500};  
    for (int i : colWidths) {
        CTTblGridCol gridCol = tblGrid.addNewGridCol();  
        gridCol.setW(new BigInteger(i+""));  
    }

（3）使用CTTblGrid类需要导入poi-ooxml-schemas的jar包（所有需要的jar包maven依赖在底部）

3.单元格内字体样式

（1）不需要设置单元格字体

for (int i = 0; i < rows; i++) {
        XWPFTableRow rowsContent = ComTable.createRow();
        for (int j = 0; j < list.get(i).size(); j++) {    
            rowsContent.getCell(j).setText(list.get(i).get(j)+""); //用单元格加载内容
        }
    }

（2）设置单元格内字体字号、是否加粗

for (int i = 0; i < rows; i++) {
        XWPFTableRow rowsContent = ComTable.createRow();
        for (int j = 0; j < list.get(i).size(); j++) {
            XWPFParagraph cellParagraphC = rowsContent.getCell(j).getParagraphs().get(0);
            if(j!=1&&j!=7) {
                cellParagraphC.setAlignment(ParagraphAlignment.CENTER); //设置表格内容居中
            }
            XWPFRun cellParagraphRunC  = cellParagraphC.createRun();
            cellParagraphRunC.setFontSize(10); //设置表格内容字号
            cellParagraphRun.setBold(true); //设置表头单元格加粗
            cellParagraphRunC.setText(list.get(i).get(j)+""); //单元格段落加载内容
        }
    }

4.单元格居中（水平居中、垂直居中）

（1）使用单元格加载内容

for (int i = 0; i < value_columns.length; i++) {
        XWPFTableCell cell = ComTable.getRow(0).getCell(i);
        cell.setVerticalAlignment(XWPFTableCell.XWPFVertAlign.CENTER); //垂直居中
        CTTc cttc = cell.getCTTc();
        CTP ctp = cttc.getPList().get(0);
        CTPPr ctppr = ctp.getPPr();
        if (ctppr == null) {
            ctppr = ctp.addNewPPr();
        }
        CTJc ctjc = ctppr.getJc();
        if (ctjc == null) {
            ctjc = ctppr.addNewJc();
        }
        ctjc.setVal(STJc.CENTER); //水平居中
    }

（2）使用单元格的段落加载内容，只用调用垂直居中的方法

水平居中在3-(2)里调用了段落的方法居中过了

cellParagraphC.setAlignment(ParagraphAlignment.CENTER);

5.单元格合并

（1）跨列合并单元格

public static 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 ) {				
    				cell.getCTTc().addNewTcPr().addNewHMerge().setVal(STMerge.RESTART);
    			} else {
    				cell.getCTTc().addNewTcPr().addNewHMerge().setVal(STMerge.CONTINUE);
    			}
    		}
    	}

（2）跨行合并单元格

public static void mergeCellsVertically(XWPFTable table, int col, int fromRow, int toRow) {
    		for (int rowIndex = fromRow; rowIndex <= toRow; rowIndex++) {
    			XWPFTableCell cell = table.getRow(rowIndex).getCell(col);
    			if ( rowIndex == fromRow ) {
    				cell.getCTTc().addNewTcPr().addNewVMerge().setVal(STMerge.RESTART);
    			} else {
    				cell.getCTTc().addNewTcPr().addNewVMerge().setVal(STMerge.CONTINUE);
    			}
    		}
    	}

6.单元格内换行，单元格内使用\\r换行没用，可以用addBreak方法

for (int i = 0; i < list_vPostdutyNorm_parm.size(); i++) {
    			row = ComTable.createRow();
    			index++;
    			for (int j = 0; j < list_vPostdutyNorm_parm.get(i).size(); j++) {
    				if (j == 3 || j == 4) {
    					cellParagraph.setAlignment(ParagraphAlignment.LEFT); //设置表头单元格居中
    				} else {
    					cellParagraph.setAlignment(ParagraphAlignment.CENTER); //设置表头单元格居中
    				}
    				String content = list_vPostdutyNorm_parm.get(i).get(j);
    				cellParagraph = row.getCell(j).getParagraphs().get(0);
    				cellParagraphRun  = cellParagraph.createRun();
    				cellParagraphRun.setFontSize(10); //设置表头单元格字号
    				cellParagraphRun.setBold(false); //设置表头单元格加粗
    				if (content.indexOf("\r") > -1) {
    					String[] text = content.split("\r");
    					for (int k = 0; k < text.length; k++) {
    						if (k == 0) {
    							cellParagraphRun.setText(text[k]);
    						}else {
    							cellParagraphRun.addBreak();
    							cellParagraphRun.setText(text[k]);
    						}
    					}
    				}else {
    					cellParagraphRun.setText(content);
    				}
    			}
    		}

六、使用到的Maven依赖

<dependency>
        <groupId>org.apache.xmlbeans</groupId>
        <artifactId>xmlbeans</artifactId>
        <version>2.6.0</version>
    </dependency>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>3.9</version>
    </dependency>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>ooxml-schemas</artifactId>
        <version>1.1</version>
    </dependency>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml-schemas</artifactId>
        <version>3.16</version>
    </dependency>

[70]: /images/20220519/bd5062015161499798192c3d7ea370ca.png