java 导出 Excel ,能替换字体和图片
逻辑思想,采用替换的逻辑,就是通过一个map把Excel模板中字段进行相应的替换。
相应工具类如下:
使用测试excel模板文件:公众号:知识浅谈 后台回复 Excel模板
对应的依赖
引入以下依赖: <properties> <poi.version>4.1.2</poi.version> </properties> <!-- poi --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>${poi.version}</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-scratchpad</artifactId> <version>${poi.version}</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>${poi.version}</version> </dependency>
工具类
package com.englishcode; import com.sun.media.sound.InvalidFormatException; import org.apache.poi.ss.usermodel.*; //import org.apache.tomcat.util.http.fileupload.FileUtils; import java.io.*; import java.util.HashMap; import java.util.Map; import java.util.regex.Matcher; import java.util.regex.Pattern; import org.apache.commons.io.FileUtils; /** * @author YinLei * @version 1.0 * @date 2021/3/1 13:05 */ public class JavaToExcelUtils { public void outExcel(String inPath, String outPath, Map params, int sheetnum) throws IOException, InvalidFormatException { //inPath是模板路径 outPath是生成的新的excel的路径 InputStream is = new FileInputStream(new File(inPath)); //读取模板文件 Workbook wb = WorkbookFactory.create(is); // Sheet sheet = wb.getSheetAt(sheetnum);//获取Excel的工作表sheet,下标从0开始,因为一个excel文件中可能有多个sheet int trLength = sheet.getLastRowNum();//获取Excel的行数 //int a=0; for (int i = 0; i < trLength; i++) { Row row = sheet.getRow(i);//获取Excel的行,下标从0开始 if (row == null) {//若行为空,则遍历下一行 continue; } int minColIx = row.getFirstCellNum(); //获取该行中列的最左边的下标 int maxColIx = row.getLastCellNum(); //获取该行中列的最右边的下标 for (int colIx = minColIx; colIx < maxColIx; colIx++) { //遍历该行 Cell cell = row.getCell(colIx);//获取指定单元格,单元格从左到右下标从0开始 // int flag =0; if(cell!=null) //当单元格为空的时候,不处理 { cell.setCellType(CellType.STRING); //设置单元格的类型 String runText = cell.getStringCellValue(); //获取单元格中的内容 if (runText.equals("")){ continue; } // System.out.println(cell); Matcher matcher = this.matcher(runText); //把单元格中原始的内容作为要匹配的字符串 if (matcher.find()) { //当有匹配的的字符串 while ((matcher = this.matcher(runText)).find()) { //group(1)返回第一组匹配到的字符串,就是正则匹配规则中的第一个()中的匹配 if(String.valueOf(params.get(matcher.group(1))).equals("null")) break; else { runText = matcher.replaceFirst(String.valueOf(params.get(matcher.group(1)))); // flag=1; // System.out.println(runText); } //把该表格中的原始数据替换成param中key是matcher.group(1)的value值 } if (runText.endsWith(".jpg")||runText.endsWith(".png")){//替换图片 cell.setCellValue(""); byte[] bt = FileUtils.readFileToByteArray(new File(runText)); int pictureIdx; if(runText.endsWith(".jpg")) pictureIdx= wb.addPicture(bt, Workbook.PICTURE_TYPE_PNG); else pictureIdx= wb.addPicture(bt, Workbook.PICTURE_TYPE_JPEG); CreationHelper helper = wb.getCreationHelper(); Drawing drawing = sheet.createDrawingPatriarch(); ClientAnchor anchor = helper.createClientAnchor(); anchor.setCol1(colIx); //图片开始列数 anchor.setRow1(i);//图片开始行数 anchor.setCol2(colIx+1);//图片结束列数 anchor.setRow2(i+1);//图片结束行数 drawing.createPicture(anchor, pictureIdx); } else cell.setCellValue(runText); //替换字体 // System.out.println(cell); //a=1; } } // if(flag==1){ // System.out.println(cell); // } } //if(a==1) break; } File file = new File(outPath); OutputStream out = new FileOutputStream(file);//输出文件变成对应的输出流 // out.flush(); wb.write(out);//把wb中数据写入到文件输出流中 is.close(); out.close(); } /** * 正则匹配字符串 * @param str * @return */ //@org.jetbrains.annotations.NotNull private Matcher matcher(String str) { Pattern pattern = Pattern.compile("\\{(.+?)\\}", Pattern.CASE_INSENSITIVE); Matcher matcher = pattern.matcher(str); return matcher; } public static void main(String[] args) throws IOException { String path = "C:\\Users\\93676\\Desktop\\新产品需求计划表模板.xls"; String outPath = "C:\\Users\\93676\\Desktop\\testw.xls"; Map<String, Object> params = new HashMap<String, Object>(); params.put("新产品需求计划表", "gou"); //new ReplaceExcelUtil().replaceExcel(path,outPath,params,0); params.put("编号", "123456"); params.put("产品型号", "123456"); //new ReplaceExcelUtil().replaceExcel(outPath,outPath,params,0); params.put("□2", "√"); //new ReplaceExcelUtil().replaceExcel(outPath,outPath,params,0); new JavaToExcelUtils().outExcel(path,outPath,params,0); } }
Java 导出 pdf
具体使用链接 见我另一篇文章:
https://blog.csdn.net/qq_37699336/article/details/114444752
相对应的模板:公众号 EnglishCode 后台回复 pdf模板
java 导出word (适用于docx)
相对应的方法二的模板:公众号 EnglishCode 后台回复 word模板
注意:这个模板中的字段在记事本上写好之后,再粘贴到对应的你自己制作模板的位置,eg ${proName} 这个 在记事本上写好之后再粘贴到word中
原因见我另一篇博客:https://blog.csdn.net/qq_37699336/article/details/114445839
因为doc使用的库没有docx使用的库易操作,所以选择了生成docx的库
方法一:只替换文本中的文本信息(就两行主要的代码)
对应的依赖
<!--JavaToWord使用依赖--> <dependency> <groupId>cn.afterturn</groupId> <artifactId>easypoi-base</artifactId> <version>3.0.3</version> </dependency> <dependency> <groupId>cn.afterturn</groupId> <artifactId>easypoi-web</artifactId> <version>3.0.3</version> </dependency> <dependency> <groupId>cn.afterturn</groupId> <artifactId>easypoi-annotation</artifactId> <version>3.0.3</version> </dependency>
package com.englishcode; import cn.afterturn.easypoi.word.WordExportUtil; import org.apache.poi.xwpf.usermodel.XWPFDocument; import org.springframework.util.Assert; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import java.io.File; import java.io.FileOutputStream; import java.io.OutputStream; import java.net.URLEncoder; import java.util.HashMap; import java.util.Map; public class JavaToWordTextUtils { /** * 对.docx文件进行处理 * @param templatePath 模板路径 * @param exportPath 导出文件路径 * @param params //模板中对应替换的内容是{{替换的内容}} 但是Map中不需要key是{{要替换的内容}},而要求key是要替换的内容,因为下边会自动加上{{}} */ public static void xwpfExportWord(String templatePath, String exportPath,Map<String,Object>params){ Assert.isTrue(exportPath.endsWith(".docx"), "word导出请使用docx格式"); try { //替换文字 XWPFDocument doc = WordExportUtil.exportWord07(templatePath, params); FileOutputStream fos = new FileOutputStream(exportPath); /* // 下边是输出到前端的文件 // 设置强制下载不打开 response.setContentType("application/force-download"); response.setCharacterEncoding("utf-8"); // 设置文件名 File tempFile = new File(exportPath.trim()); //主要是用于获取路径中的文件名 response.addHeader("Content-Disposition", "attachment;fileName=" + tempFile.getName()); OutputStream out = response.getOutputStream(); doc.write(out); out.close(); */ doc.write(fos); doc.close(); fos.close(); } catch (Exception e) { e.printStackTrace(); } } public static void main(String[] args) { Map<String,Object>params= new HashMap<String, Object>(); params.put("张三","李四"); xwpfExportWord("C:\\Users\\93676\\Desktop\\测试.docx","C:\\Users\\93676\\Desktop\\测试1.docx",params); }
方法二:既能替换文本,又能填充图片(包含两个类)
对应的依赖
<!-- poi --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>${poi.version}</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-scratchpad</artifactId> <version>${poi.version}</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>${poi.version}</version> </dependency>
package com.englishcode; /** * @author YinLei * @version 1.0 * @date 2021/3/6 13:35 */ import org.apache.poi.ooxml.POIXMLDocument; import org.apache.poi.openxml4j.exceptions.InvalidFormatException; import org.apache.poi.openxml4j.opc.OPCPackage; import org.apache.poi.util.Units; 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 java.io.*; import java.util.HashMap; import java.util.Iterator; import java.util.List; import java.util.Map; import java.util.Map.Entry; public class JavaToWordUtils { /** * * @param param 对应的替换 * @param templatepath 对应的模板路径 * @param outpath 对应的输出路径 */ public static void outWord(String templatepath, String outpath, Map<String, Object> param) { try { OPCPackage pack = POIXMLDocument.openPackage(templatepath);//通过路径获取word模板 XWPFDocument doc = new CustomXWPFDocument(pack); //通过InputStream 获取模板,此方法适用于jar包部署 // doc = new CustomXWPFDocument(template); if (param != null && param.size() > 0) { //处理页眉 List<XWPFHeader> headerList = doc.getHeaderList(); for (XWPFHeader header:headerList ) { List<XWPFParagraph> paras = header.getParagraphs(); //处理段落 processParagraphs(paras, param, doc); List<XWPFTable> tables = header.getTables(); //处理表格 processTable(tables,param,doc); } //处理页脚 List<XWPFFooter> footerList = doc.getFooterList(); for (XWPFFooter footer:footerList ) { List<XWPFParagraph> paras = footer.getParagraphs(); //处理段落 processParagraphs(paras, param, doc); List<XWPFTable> tables = footer.getTables(); //处理表格 processTable(tables,param,doc); } /* 处理正文 */ //处理段落 图片直接包括在其中了 List<XWPFParagraph> paragraphList = doc.getParagraphs(); processParagraphs(paragraphList, param, doc); //处理表格 List<XWPFTable> tables = doc.getTables(); processTable(tables,param,doc); } FileOutputStream fopts = new FileOutputStream(outpath); doc.write(fopts); fopts.close(); // doc.close(); //这个万一千万不能写,协商之后模板文件也变了 } catch (Exception e) { e.printStackTrace(); } } /** * 处理表格 * @param tables 表格列表 * @param param * @param doc */ public static void processTable(List<XWPFTable> tables,Map<String, Object> param, XWPFDocument doc) throws IOException, InvalidFormatException { for (XWPFTable table: tables) { List<XWPFTableRow> rows = table.getRows(); for (XWPFTableRow row : rows) { List<XWPFTableCell> cells = row.getTableCells(); for (XWPFTableCell cell : cells) { List<XWPFParagraph> paragraphListTable = cell.getParagraphs(); processParagraphs(paragraphListTable, param, doc); } } } } /** * 处理段落 * @param paragraphList * @throws FileNotFoundException * @throws InvalidFormatException */ public static void processParagraphs(List<XWPFParagraph> paragraphList, Map<String, Object> param, XWPFDocument doc) throws InvalidFormatException, IOException { if (paragraphList != null && paragraphList.size() > 0) { for (XWPFParagraph paragraph : paragraphList) { List<XWPFRun> runs = paragraph.getRuns(); for (XWPFRun run : runs) { String text = run.getText(0); if (text != null) { System.out.println(text); boolean isSetText = false; for (Entry<String, Object> entry : param.entrySet()) { String key = "${" + entry.getKey() + "}"; if (text.indexOf(key) != -1) { isSetText = true; Object value = entry.getValue(); if (value instanceof String) { //文本替换 text = text.replace(key, value.toString()); } else if (value instanceof Map) { //图片处理替换 text = text.replace(key, ""); Map pic = (Map) value; int width = Integer.parseInt(pic.get("width").toString()); int height = Integer.parseInt(pic.get("height").toString()); int picType = getPictureType(pic.get("type").toString()); String picPath = (String) pic.get("content"); //获取文件的路径 String imgFile = new File(picPath.trim()).getName(); run.setText(imgFile); //设置文件名 //旧版的图片处理方法已不可用 run.addBreak(); run.addPicture(new FileInputStream(picPath), picType, imgFile, Units.toEMU(width), Units.toEMU(height)); // 200x200 pixels run.addBreak(BreakType.PAGE); } } } if (isSetText) { run.setText(text, 0); } } } } } } /** * 获取图片对应类型代码 * @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; } /** * get the xml of the picture * * @param blipId * @param width * @param height * @return */ private static String getPicXml(String blipId, int width, int height) { 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=\"" + 0 + "\" 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>"; return picXml; } public static void main(String[] args) throws Exception { Map<String,Object> param = new HashMap<String, Object>(); // param.put("客户","李四"); param.put("proName","合规性分析测试"); param.put("proType","总体规划"); param.put("nonConstrArea","25438834.17"); param.put("constrArea","0.00"); param.put("选项","qwe"); Map<String,Object> header = new HashMap<String, Object>(); header.put("width", 300); header.put("height", 230); header.put("type", "png"); header.put("content", "C:\\Users\\93676\\Desktop\\测试使用\\vju.png");//图片路径 param.put("picture",header); // JavaToWordUtils.outWord( // "C:\\Users\\93676\\Desktop\\test.docx", // "C:\\Users\\93676\\Desktop\\test1.docx", // param); JavaToWordUtils.outWord( "C:\\Users\\93676\\Desktop\\测试使用\\测试图片.docx", "C:\\Users\\93676\\Desktop\\测试使用\\test.docx", param); } }
package com.englishcode; import org.apache.poi.openxml4j.opc.OPCPackage; import org.apache.poi.xwpf.usermodel.XWPFDocument; import java.io.IOException; import java.io.InputStream; /** * @author YinLei * @version 1.0 * @date 2021/3/6 14:16 */ public class CustomXWPFDocument extends XWPFDocument { public CustomXWPFDocument(InputStream in) throws IOException { super(in); } public CustomXWPFDocument() { super(); } public CustomXWPFDocument(OPCPackage pkg) throws IOException { super(pkg); } }