临时有个需求，要求通过读取EXCEL文件，然后通过文件名称去给定的文件夹内查找图片，并将图片写入PPT中，详情以表格的形式写到图片右侧，每张图片一页PPT。效果如图所示：
用到的jar包如下（maven）：
<dependency><groupId>net.sourceforge.jexcelapi</groupId><artifactId>jxl</artifactId><version>2.6.12</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>3.8</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>3.8</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-scratchpad</artifactId><version>3.8</version></dependency><dependency><groupId>commons-io</groupId><artifactId>commons-io</artifactId><version>2.6</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>ooxml-schemas</artifactId><version>1.1</version></dependency>
思路如下：
读取EXCEL内容，将EXCEL每行数据存成map用于PPT生成表格以及读取图片路径。生成PPT，创建空白页，根据所传的EXCEL读取结果填充PPT。EXCEL读取结束后生成PPT文件。读取EXCEL代码：
package com.ddhc.poi;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.formula.functions.T;import org.apache.poi.ss.usermodel.Cell;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.xslf.usermodel.XMLSlideShow;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import java.io.File;import java.io.FileInputStream;import java.io.InputStream;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import java.util.Map;/*** 读取excel类*/public class ReadExcel {public static void main(String[] args) throws Exception {ReadExcel excelTest = new ReadExcel();Workbook wb = excelTest.getExcel("C:\\Users\\hzw\\Desktop\\靳工——PPT\\附件1.怀柔城区地区电力设施统计表——汇总版.xls");if (wb == null)System.out.println("文件读入出错");else {excelTest.analyzeExcel(wb);}}/*** 读取文件流** @param filePath* @return*/public Workbook getExcel(String filePath) {Workbook wb = null;File file = new File(filePath);if (!file.exists()) {System.out.println("文件不存在");wb = null;} else {String fileType = filePath.substring(filePath.lastIndexOf("."));//获得后缀名try {InputStream is = new FileInputStream(filePath);if (".xls".equals(fileType)) {wb = new HSSFWorkbook(is);} else if (".xlsx".equals(fileType)) {wb = new XSSFWorkbook(is);} else {System.out.println("格式不正确");wb = null;}} catch (Exception e) {e.printStackTrace();}}return wb;}/*** 读取文件结果** @param wb*/public void analyzeExcel(Workbook wb) throws Exception {int a = 0;XMLSlideShow ppt = new XMLSlideShow();TestExportPptx tp = new TestExportPptx();Sheet sheet = wb.getSheetAt(0);//读取sheet(从0计数)int rowNum = sheet.getLastRowNum();//读取行数(从0计数)for (int i = 0; i <= rowNum; i++) {Row row = sheet.getRow(i);//获得行int colNum = row.getLastCellNum();//获得当前行的列数Map<String, String> rowMap = new HashMap<>();for (int j = 0; j < colNum; j++) {Cell cell = row.getCell(j);//获取单元格if (cell == null) {System.out.print("null");} else {if (i > 1) {Row tempRow = sheet.getRow(1);//获得标题行Cell tempRowCell = tempRow.getCell(j); //获取标题行的单元格rowMap.put(tempRowCell.toString(), cell.toString()); // 将标题作为key，结果作为value}//System.out.print(cell.toString() + "-");}}if (i > 1) {String x = tp.setPPT(rowMap, ppt, a);if (!x.equals("")) {a -= 1;}a += 1;}// System.out.println();}Map<String, String> endMap = new HashMap<>();endMap.put("序号", "yes");tp.setPPT(endMap, ppt, 0);}}
生成PPT代码：
package com.ddhc.poi;import java.awt.Color;import java.awt.geom.Rectangle2D;import java.io.File;import java.io.FileOutputStream;import java.util.List;import java.util.Map;import mons.io.FileUtils;import org.apache.poi.xslf.usermodel.TextAlign;import org.apache.poi.xslf.usermodel.VerticalAlignment;import org.apache.poi.xslf.usermodel.XMLSlideShow;import org.apache.poi.xslf.usermodel.XSLFHyperlink;import org.apache.poi.xslf.usermodel.XSLFPictureData;import org.apache.poi.xslf.usermodel.XSLFPictureShape;import org.apache.poi.xslf.usermodel.XSLFSlide;import org.apache.poi.xslf.usermodel.XSLFTable;import org.apache.poi.xslf.usermodel.XSLFTableCell;import org.apache.poi.xslf.usermodel.XSLFTableRow;import org.apache.poi.xslf.usermodel.XSLFTextBox;import org.apache.poi.xslf.usermodel.XSLFTextParagraph;import org.apache.poi.xslf.usermodel.XSLFTextRun;public class TestExportPptx {/*** 创建ppt*/public String setPPT(Map<String, String> rowMap, XMLSlideShow ppt, int index) throws Exception {String x = "";try {if (rowMap.get("PMS系统编号（如PMS系统无编号，命名方式请参考：XX路+编号+设备名称") != null) {XSLFSlide slide = ppt.createSlide();//创建空白幻灯片页//插入表格Object[][] datas = {{rowMap.get("PMS系统编号（如PMS系统无编号，命名方式请参考：XX路+编号+设备名称"), ""},{"街道名称", rowMap.get("街道名称")},{"设备属性", rowMap.get("设备属性")},{"数量", Integer.parseInt(rowMap.get("数量").substring(0, 1))},{"是否影响通行", rowMap.get("是否影响通行")},{"是否影响美观", rowMap.get("是否影响美观")},{"是否具备迁移条件", rowMap.get("是否具备迁移条件")},{"是否用户设备", rowMap.get("是否用户设备")},{"是否占用绿地", rowMap.get("是否占用绿地")},{"是否占用步道", rowMap.get("是否占用步道")},};XSLFTable table = slide.createTable();//创建表格table.setAnchor(new Rectangle2D.Double(380, 93, 0, 0));for (int i = 0; i < datas.length; i++) {XSLFTableRow tableRow = table.addRow(); //创建表格行for (int j = 0; j < datas[i].length; j++) {XSLFTableCell tableCell = tableRow.addCell();//创建表格单元格XSLFTextParagraph p = tableCell.addNewTextParagraph();XSLFTextRun tr = p.addNewTextRun();tr.setText(String.valueOf(datas[i][j]));tableCell.setFillColor(Color.getColor("0xdd7e6b"));p.setTextAlign(TextAlign.CENTER);tableCell.setVerticalAlignment(VerticalAlignment.MIDDLE);if (i == datas.length - 1 && j == 3 - 1) {tr.setFontSize(16);tr.setBold(true);tr.setItalic(true);tr.setUnderline(true);tr.setFontFamily("\u5b8b\u4f53");tr.setFontColor(Color.RED);}tableCell.setBorderBottom(1);tableCell.setBorderLeft(1);tableCell.setBorderTop(1);tableCell.setBorderRight(1);tableCell.setBorderBottomColor(Color.BLACK);tableCell.setBorderLeftColor(Color.BLACK);tableCell.setBorderTopColor(Color.BLACK);tableCell.setBorderRightColor(Color.BLACK);}tableRow.setHeight(30);}//设置列宽table.setColumnWidth(0, 150);table.setColumnWidth(1, 150);//合并单元格table.mergeCells(0, 0, 0, 1);//插入图片String url = "C:\\Users\\hzw\\Desktop\\靳工——PPT\\低压 压缩";String houzhui = ".JPG";String fileName = rowMap.get("PMS系统编号（如PMS系统无编号，命名方式请参考：XX路+编号+设备名称") + houzhui;String resultFile = url + "\\" + fileName;File file = new File(resultFile);if (!file.exists()) {String path = resultFile.replace("/", "-");file = new File(path);if (!file.exists()) {String path1 = path.replace("JPG", "jpg");file = new File(path1);if (!file.exists()) {file = new File(path1.replace(".jpg", " .jpg"));if (!file.exists()) {System.out.println(index + " " + rowMap.get("PMS系统编号（如PMS系统无编号，命名方式请参考：XX路+编号+设备名称") + ".JPG");x = "err";// ppt.removeSlide(index); //删除页数为index的PPTreturn x;}}}}byte[] bt = FileUtils.readFileToByteArray(file);int idx = ppt.addPicture(bt, XSLFPictureData.PICTURE_TYPE_PNG);XSLFPictureShape pic = slide.createPicture(idx);pic.setAnchor(new Rectangle2D.Double(20, 115, 339, 300));}TestExportPptx testExportPptx = new TestExportPptx();if (rowMap.get("序号") != null && rowMap.get("序号").equals("yes")) {testExportPptx.createPPT(ppt);}} catch (Exception e) {x = "err";// ppt.removeSlide(index);e.printStackTrace();}return x;}/*** 保存ppt** @param ppt* @throws Exception*/public void createPPT(XMLSlideShow ppt) throws Exception {ppt.write(new FileOutputStream("C:\\Users\\hzw\\Desktop\\靳工——PPT\\怀柔城区地区电力设施.pptx"));}}
遇到的坑：
jar包版本不一致运行会直接报错，所以一定要注意版本兼容。PPT生成表格时，需要ooxml-schemas这个包。调整图片与表格偏移量时，Y轴升高代表位置向下。代码中有好多替换，是由于客户给的文件与EXCEL中的文件名称对应不上，经常多少空格之类的。更好的思路：
遍历EXCEL中所有内容，存进list<Map<String,String>>。遍历文件夹中所有文件。将EXCEL、文件夹中所有字符去空格、大小转小写统一格式。比较两者，生成PPT。源码:
链接: /s/1Xun1D5cYpMGcYy7ltFCJNA 提取码: su2h
