poi导出复杂模板excel和word
导出复杂木板使用这个老哥的东西很好用
https://blog.csdn.net/jiangyongbing008/article/details/100795250
很方便的完成了，插入图片也有，只需要把图片转换成字节流。代码如下
//初始化工具 ExcelTemplate excel = new ExcelTemplate(filePath + fileName); /*javaBean转map*/ Map<String, Object> map = getSevenInfo(affairSevenKnowledge); // 第一个参数，需要操作的sheet的索引 // 第二个参数，需要复制的区域的第一行索引（占位符所在的第一行） // 第三个参数，需要复制的区域的最后一行索引（占位符所在的最后一行） // 第四个参数，需要插入的位置的索引(占位符的下一行) // 第五个参数，填充行区域中${}的值 // 第六个参数，是否需要删除原来的区域 // 需要注意的是，行的索引一般要减一 try { excel.fillVariable(0, (Map<String, String>) map.get("param")); int i = excel.addRowByExist(0,14,14,15, (LinkedHashMap<Integer, LinkedList<String>>) map.get("rows"),true); //家庭成员和主要社会关系下方多处来的列合并到一个单元格 //没有关系就不需要合并了 if (i>0){ // excel.mergedRegion(0,16,16+i+1,1,1); } // excel.addRowByExist(0,14,14,15,rows,true); } catch (IOException e) { e.printStackTrace(); } // 第一个参数，需要操作的sheet的索引 // 第二个参数，替换sheet当中${<变量的值>}的值 //下载 try { // 清除buffer缓存 response.reset(); response.setContentType("application/octet-stream; charset=utf-8"); //指定下载名字 response.setHeader("Content-Disposition", "attachment; filename="+ Encodes.urlEncode(affairSevenKnowledge.getName()+fileName)); response.addHeader("Pargam", "no-cache"); response.addHeader("Cache-Control", "no-cache"); OutputStream fout = response.getOutputStream(); excel.getWorkbook().write(fout); fout.close(); } catch (IOException e) { e.printStackTrace(); }
导出word模板使用的这个老哥的东西
https://blog.csdn.net/qq_27828675/article/details/88708983
导出word模板还是挺坑的，word是由xml组成的。再word里编辑好占位符后，转成xml文档会发现，占位符已经分开了。我使用数字做占位符，转换成xml后把数字转换成需要的占位符。但是，
转换完后，有些占位符还是无法替换，再次转成xml发现，占位符还是分开了。。。。最后
我看了下老哥写的代码，我给改动了下，模板未匹配到的区域我给替换成了空字符串。修改后的代码如下
import java.io.*; import java.util.Map; import com.thinkgem.jeesite.common.utils.StringUtils; import org.apache.poi.ooxml.POIXMLDocument; import org.apache.poi.openxml4j.opc.OPCPackage; import org.apache.poi.xwpf.usermodel.*; import org.apache.xmlbeans.XmlException; import org.apache.xmlbeans.XmlToken; import org.openxmlformats.schemas.drawingml.x2006.main.CTNonVisualDrawingProps; import org.openxmlformats.schemas.drawingml.x2006.main.CTPositiveSize2D; import org.openxmlformats.schemas.drawingml.x2006.wordprocessingDrawing.CTInline; import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTFonts; import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTP; import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTRPr; import org.openxmlformats.schemas.wordprocessingml.x2006.main.STMerge; import java.util.*; /** * https://blog.csdn.net/qq_27828675/article/details/88708983 */ public class WordTemplate { /** * 根据模板生成新word文档 * 判断表格是需要替换还是需要插入，判断逻辑有$为替换，表格无$为插入 * * @param inputUrl 模板存放地址 * @param outputUrl 新文档存放地址 * @param textMap 需要替换的信息集合 * @param tableList 需要插入的表格信息集合 * @return 成功返回true, 失败返回false */ public static CustomXWPFDocument changWord(String inputUrl, String outputUrl, Map<String, Object> textMap, List<String[]> tableList) { CustomXWPFDocument document = null; //模板转换默认成功 boolean changeFlag = true; try { //获取docx解析对象 document = new CustomXWPFDocument(POIXMLDocument.openPackage(inputUrl)); //解析替换文本段落对象 WordTemplate.changeText(document, textMap); //解析替换表格对象 WordTemplate.changeTable(document, textMap, tableList); //生成新的word /*File file = new File(outputUrl); if (!file.getParentFile().exists()) { file.getParentFile().mkdirs(); } FileInputStream stream = new FileInputStream(file);*/ // document.write(stream); // stream.close(); } catch (IOException e) { e.printStackTrace(); changeFlag = false; } return document; } /** * 替换段落文本 * * @param document docx解析对象 * @param textMap 需要替换的信息集合 */ public static void changeText(XWPFDocument document, Map<String, Object> textMap) { //获取段落集合 List<XWPFParagraph> paragraphs = document.getParagraphs(); for (XWPFParagraph paragraph : paragraphs) { //判断此段落时候需要进行替换 String text = paragraph.getText(); if (checkText(text)) { List<XWPFRun> runs = paragraph.getRuns(); for (XWPFRun run : runs) { //替换模板原来位置 run.setText((String) changeValue(run.toString(), textMap), 0); } } } } /** * 替换表格对象方法 * * @param document docx解析对象 * @param textMap 需要替换的信息集合 * @param tableList 需要插入的表格信息集合 */ public static void changeTable(CustomXWPFDocument document, Map<String, Object> textMap, List<String[]> tableList) { //获取表格对象集合 List<XWPFTable> tables = document.getTables(); for (int i = 0; i < tables.size(); i++) { //只处理行数大于等于2的表格，且不循环表头 XWPFTable table = tables.get(i); if (table.getRows().size() > 1) { //判断表格是需要替换还是需要插入，判断逻辑有$为替换，表格无$为插入 if (checkText(table.getText())) { List<XWPFTableRow> rows = table.getRows(); //遍历表格,并替换模板 eachTable(document, rows, textMap); } else { // System.out.println("插入"+table.getText()); insertTable(table, tableList); } } } } /** * 遍历表格，包含对图片内容的替换 * * @param rows 表格行对象 * @param textMap 需要替换的信息集合 */ public static void eachTable(CustomXWPFDocument document, List<XWPFTableRow> rows, Map<String, Object> textMap) { for (XWPFTableRow row : rows) { List<XWPFTableCell> cells = row.getTableCells(); for (XWPFTableCell cell : cells) { //判断单元格是否需要替换 if (checkText(cell.getText())) { List<XWPFParagraph> paragraphs = cell.getParagraphs(); for (XWPFParagraph paragraph : paragraphs) { List<XWPFRun> runs = paragraph.getRuns(); for (XWPFRun run : runs) { // run.setText(changeValue(run.toString(), textMap),0); Object ob = changeValue(run.toString(), textMap); if (ob instanceof String) { run.setText((String) ob, 0); } else if (ob instanceof Map) { run.setText("", 0); Map pic = (Map) ob; int width = Integer.parseInt(pic.get("width").toString()); int height = Integer.parseInt(pic.get("height").toString()); int picType = getPictureType(pic.get("type").toString()); byte[] byteArray = (byte[]) pic.get("content"); ByteArrayInputStream byteInputStream = new ByteArrayInputStream(byteArray); try { String ind = document.addPictureData(byteInputStream, picType); document.createPicture(run, ind, document.getNextPicNameNumber(picType), width, height); } catch (Exception e) { e.printStackTrace(); } } } } } } } } /** * 为表格插入数据，行数不够添加新行 * * @param table 需要插入数据的表格 * @param tableList 插入数据集合 */ public static void insertTable(XWPFTable table, List<String[]> tableList) { //创建行,根据需要插入的数据添加新行，不处理表头 for (int i = 1; i < tableList.size(); i++) { XWPFTableRow row = table.createRow(); } //遍历表格插入数据 List<XWPFTableRow> rows = table.getRows(); for (int i = 1; i < rows.size(); i++) { XWPFTableRow newRow = table.getRow(i); List<XWPFTableCell> cells = newRow.getTableCells(); for (int j = 0; j < cells.size(); j++) { XWPFTableCell cell = cells.get(j); cell.setText(tableList.get(i - 1)[j]); } } } /** * word跨列合并单元格 */ 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) { // 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); } } } /** * word跨行并单元格 */ 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) { // The first merged cell is set with RESTART merge value cell.getCTTc().addNewTcPr().addNewVMerge().setVal(STMerge.RESTART); } else { // Cells which join (merge) the first one, are set with CONTINUE cell.getCTTc().addNewTcPr().addNewVMerge().setVal(STMerge.CONTINUE); } } } /** * 单元格设置字体 * @param cell * @param cellText */ private static void getParagraph(XWPFTableCell cell,String cellText){ CTP ctp = CTP.Factory.newInstance(); XWPFParagraph p = new XWPFParagraph(ctp, cell); p.setAlignment(ParagraphAlignment.CENTER); XWPFRun run = p.createRun(); run.setText(cellText); CTRPr rpr = run.getCTR().isSetRPr() ? run.getCTR().getRPr() : run.getCTR().addNewRPr(); CTFonts fonts = rpr.isSetRFonts() ? rpr.getRFonts() : rpr.addNewRFonts(); fonts.setAscii("仿宋"); fonts.setEastAsia("仿宋"); fonts.setHAnsi("仿宋"); cell.setParagraph(p); } /** * 判断文本中时候包含$ * * @param text 文本 * @return 包含返回true, 不包含返回false */ public static boolean checkText(String text) { boolean check = false; if (text.indexOf("$") != -1) { check = true; } return check; } /** * 匹配传入信息集合与模板 * * @param value 模板需要替换的区域 * @param textMap 传入信息集合 * @return 模板需要替换区域信息集合对应值 */ public static Object changeValue(String value, Map<String, Object> textMap) { Object obj = null; Set<Map.Entry<String, Object>> textSets = textMap.entrySet(); for (Map.Entry<String, Object> textSet : textSets) { if (textSet.getKey().contains("jobFullname") && value.contains("jobFullname")){ System.out.println(textSet.getKey()); } //匹配模板与替换值 格式${key} String key = "${" + textSet.getKey() + "}"; if (value.indexOf(key) != -1) { obj = textSet.getValue(); } } if (!(obj instanceof Map)) { //模板未匹配到区域替换为空 if (obj != null && checkText(obj.toString())) { obj = ""; } } if (obj==null){ obj=""; } return obj; } /** * 根据图片类型，取得对应的图片类型代码 * * @param picType * @return int */ private static int getPictureType(String picType) { int res = CustomXWPFDocument.PICTURE_TYPE_PICT; if (picType != null) { if (picType.equalsIgnoreCase("png")) { res = CustomXWPFDocument.PICTURE_TYPE_PNG; } else if (picType.equalsIgnoreCase("dib")) { res = CustomXWPFDocument.PICTURE_TYPE_DIB; } else if (picType.equalsIgnoreCase("emf")) { res = CustomXWPFDocument.PICTURE_TYPE_EMF; } else if (picType.equalsIgnoreCase("jpg") || picType.equalsIgnoreCase("jpeg")) { res = CustomXWPFDocument.PICTURE_TYPE_JPEG; } else if (picType.equalsIgnoreCase("wmf")) { res = CustomXWPFDocument.PICTURE_TYPE_WMF; } } return res; } /** * 将输入流中的数据写入字节数组 * * @param in * @return */ public static byte[] inputStream2ByteArray(InputStream in, boolean isClose) { byte[] byteArray = null; try { int total = in.available(); byteArray = new byte[total]; in.read(byteArray); } catch (IOException e) { e.printStackTrace(); } finally { if (isClose) { try { in.close(); } catch (Exception e2) { System.out.println("关闭流失败"); } } } return byteArray; } } /** * @BelongsProject: exchange * @BelongsPackage: com.elens.util * @Author: xuweichao * @CreateTime: 2019-03-20 12:34 * @Description: 重写XWPFDocument的方法，插入图片 */
import org.apache.poi.openxml4j.opc.OPCPackage; import org.apache.poi.xwpf.usermodel.XWPFDocument; import org.apache.poi.xwpf.usermodel.XWPFRun; import org.apache.xmlbeans.XmlException; import org.apache.xmlbeans.XmlToken; import org.openxmlformats.schemas.drawingml.x2006.main.CTNonVisualDrawingProps; import org.openxmlformats.schemas.drawingml.x2006.main.CTPositiveSize2D; import org.openxmlformats.schemas.drawingml.x2006.wordprocessingDrawing.CTInline; import java.io.IOException; import java.io.InputStream; public class CustomXWPFDocument extends XWPFDocument { public CustomXWPFDocument() { super(); } public CustomXWPFDocument(OPCPackage opcPackage) throws IOException { super(opcPackage); } public CustomXWPFDocument(InputStream in) throws IOException { super(in); } public void createPicture(XWPFRun run, String blipId, int id, int width, int height) { final int EMU = 9525; width *= EMU; height *= EMU; //旧版本方法 .getPackageRelationship() 在该依赖包下已被删除 //String blipId = getAllPictures().get(id).getPackageRelationship().getId(); //在docment下创建XWPFRun 图片会被添加到文档末尾 // CTInline inline = createParagraph().createRun().getCTR().addNewDrawing().addNewInline(); CTInline inline =run.getCTR().addNewDrawing().addNewInline(); String picXml = "" + "<a:graphic xmlns:a=\"http://schemas.openxmlformats.org/drawingml/2006/main\">" + " <a:graphicData uri=\"http://schemas.openxmlformats.org/drawingml/2006/picture\">" + " <pic:pic xmlns:pic=\"http://schemas.openxmlformats.org/drawingml/2006/picture\">" + " <pic:nvPicPr>" + " <pic:cNvPr id=\"" + id + "\" name=\"Generated\"/>" + " <pic:cNvPicPr/>" + " </pic:nvPicPr>" + " <pic:blipFill>" + " <a:blip r:embed=\"" + blipId + "\" xmlns:r=\"http://schemas.openxmlformats.org/officeDocument/2006/relationships\"/>" + " <a:stretch>" + " <a:fillRect/>" + " </a:stretch>" + " </pic:blipFill>" + " <pic:spPr>" + " <a:xfrm>" + " <a:off x=\"0\" y=\"0\"/>" + " <a:ext cx=\"" + width + "\" cy=\"" + height + "\"/>" + " </a:xfrm>" + " <a:prstGeom prst=\"rect\">" + " <a:avLst/>" + " </a:prstGeom>" + " </pic:spPr>" + " </pic:pic>" + " </a:graphicData>" + "</a:graphic>"; //CTGraphicalObjectData graphicData = inline.addNewGraphic().addNewGraphicData(); XmlToken xmlToken = null; try { xmlToken = XmlToken.Factory.parse(picXml); } catch (XmlException xe) { xe.printStackTrace(); } inline.set(xmlToken); //graphicData.set(xmlToken); inline.setDistT(0); inline.setDistB(0); inline.setDistL(0); inline.setDistR(0); CTPositiveSize2D extent = inline.addNewExtent(); extent.setCx(width); extent.setCy(height); CTNonVisualDrawingProps docPr = inline.addNewDocPr(); docPr.setId(id); docPr.setName("Picture " + id); docPr.setDescr("Generated"); } }
使用起来也是很方便的，代码如下
response.reset(); response.setContentType("application/octet-stream; charset=utf-8"); //指定下载名字 response.setHeader("Content-Disposition", "attachment; filename="+ Encodes.urlEncode("文件名.zip")); response.addHeader("Pargam", "no-cache"); response.addHeader("Cache-Control", "no-cache"); String fileSeperator = File.separator; String filePath = Global.getUserfilesBaseDir() + fileSeperator + "userfiles" + fileSeperator + "template" + fileSeperator; ZipOutputStream out = null; try { out = new ZipOutputStream(response.getOutputStream()); } catch (IOException e) { e.printStackTrace(); } ZipOutputStream finalOut = out; try { /*遍历所有人，每个对象准换为map*/ for (PersonnelBase item : page.getList()) { Map<String, Object> map = getPersonnelBaseInfo(item, request); /*写入xlsx*/ Map<String,Object> photo = (Map<String, Object>) map.get("excelPhoto"); byte[] imageBytes = (byte[]) photo.get("imageBytes"); ExcelTemplate excel = new ExcelTemplate(filePath + "文件名.xlsx"); excel.fillVariable(0, (Map<String, String>) map.get("sheet1")); excel.fillVariable(1, (Map<String, String>) map.get("sheet2")); int line = excel.addRowByExist(1, 5, 5, 6, (LinkedHashMap<Integer, LinkedList<String>>) map.get("rows"), true); if (photo.get("imageType") != null && imageBytes != null) { int imageType = (int) photo.get("imageType"); // excel.insertPicture(0,imageBytes, imageType,1,5,8,8); } ZipEntry xlsx = new ZipEntry(item.getName() + "文件名.xlsx"); finalOut.putNextEntry(xlsx); excel.getWorkbook().write(finalOut); /*写入doc*/ ZipEntry docx = new ZipEntry(item.getName() + "文件名.docx"); finalOut.putNextEntry(docx); String fileNameInResource = filePath + "文件名.docx"; CustomXWPFDocument document = WordTemplate.changWord(fileNameInResource, personnelBase.getName() + "干部任免审批表.docx", (Map<String, Object>) map.get("doc"), null); document.write(finalOut); } } catch (IOException e) { e.printStackTrace(); } finally { try { /*注意顺序 否则导致文件错误*/ finalOut.flush(); finalOut.close(); response.getOutputStream().close(); } catch (IOException e) { e.printStackTrace(); } }
就这么记记吧，突然想起来的，记的不是很详细了。
