poi导出word表格的操作讲解(poi,Word,导出,开发技术)

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+"年 更新改造计划 正式计划草案";}

 //导出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 2023年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 2023年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 2023年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; }

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);

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

//表格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+"")); }

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)+""); //用单元格加载内容 }}

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)+""); //单元格段落加载内容 }}

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); //水平居中}

for (int i = 0; i < value_columns.length; i++) { XWPFTableCell cell = ComTable.getRow(0).getCell(i); cell.setVerticalAlignment(XWPFTableCell.XWPFVertAlign.CENTER); //垂直居中}

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); } } }

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); } } }

 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); } } }

<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>