临时有个需求,要求通过读取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 org.apache.commons.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的PPT
return 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。
源码:
链接: https://pan.baidu.com/s/1Xun1D5cYpMGcYy7ltFCJNA 提取码: su2h