一、需求背景
产品经理提出了一个在app中导出数据的需求,但是和一般常见的导出excel不同,要给用户导出具有数据可视化的pdf文件,具体样式就是在office中将ppt转为pdf后的效果。
那么第一个问题来了,如何根据数据生成ppt?至于ppt如何转pdf,本篇文章暂不详细讨论,最终根据转换效果选择了Aspose.Slides。
二、方案设计
我想出的解决方案就是先用office做个文本内容包含占位符的ppt模版文件,再通过poi实现ppt替换占位符的功能。之所以采用ppt模版的方式,是因为需求对ppt的样式美观程度有较高的要求,纯靠poi代码编写ppt样式实在是过于难调试了,不如预先在office中做好样式再用代码填充数据(还可以将制作ppt的工作丢给产品经理或者UI,毕竟后端开发人员最讨厌的就是调前端样式)。
考虑到后端开发人员一般比较熟悉thymeleaf的占位符格式,即${param}、${param.property}、${array[index].property}。对于ppt模版来说,也比较适合将每一页要填充的数据组装成一个对象,比较适合面向对象的模式。
三、pptx文件结构
要用poi处理ppt,首先要了解一下pptx文件的结构。之所以选择pptx而不是ppt,是因为pptx更新,而且对图表的支持更好,而这次的需求主要就是要用各种柱状图、折线图、饼图来展示数据。
pptx文件本质上就是一个zip包,在windows上修改后缀名后可以直接解压出来。而在mac上改后缀名后,系统并不会认为该pptx文件就是个标准的zip文件,但是不改后缀名直接用unzip命令是可以解压出来的。解压出来如下的文件目录结构。
可以看到pptx文件里实际上是大量的xml文件,poi操作pptx文件实际上就是在操作其中的各种xml。
四、代码实现
先分步讲解各部分功能的实现,文章最后会放出完整代码供读者直接复制使用。
1. jar依赖
由于我们的项目中还有其他jar依赖了poi,因此为了兼容性,没有采用最新版的poi,理论上换用最新的poi应该也没有问题。
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>4.1.2</version>
</dependency>
2. 参数预处理
虽然按照thymeleaf设计了占位符,但是并没有直接使用thymeleaf做占位符替换功能,毕竟将thymeleaf和poi结合起来,又是一大堆的开发工作量。于是简单的将参数处理成了key-value的形式,塞进一个map里,key就是参数名,也就是占位符,替换时直接从map中取值。
/**
* 对象转map,用于pptx模版参数替换
*
* @param obj
* @return
* @throws IllegalAccessException
*/
private static Map<String, String> convertObjectToMap(Object obj) throws IllegalAccessException {
long startTime = System.currentTimeMillis();
Map<String, String> result = new HashMap<>();
convertObjectToMap("", obj, result);
long endTime = System.currentTimeMillis();
log.info("param obj to map cost {}ms", endTime - startTime);
return result;
}
/**
* 对象转map递归
*
* @param prefix
* @param obj
* @param result
* @throws IllegalAccessException
*/
private static void convertObjectToMap(String prefix, Object obj, Map<String, String> result) throws IllegalAccessException {
if (obj == null) {
return;
}
Class<?> objClass = obj.getClass();
if (objClass.isPrimitive() || obj instanceof String || obj instanceof Number) {
result.put(prefix, obj.toString());
} else if (obj instanceof Collection) {
int index = 0;
for (Object item : (Collection<?>) obj) {
convertObjectToMap(prefix + "[" + index + "]", item, result);
index++;
}
} else if (objClass.isArray()) {
// todo jacoco覆盖率检测会往对象里添加boolean[],导致转Object[]报错,代码习惯使用List不使用Array,所以直接忽略不处理
// Object[] arr = (Object[]) obj;
// int length = arr.length;
// for (int i = 0; i < length; i++) {
// convertObjectToMap(prefix + "[" + i + "]", arr[i], result);
// }
} else if (obj instanceof Map) {
Map<String, Object> map = (Map<String, Object>) obj;
for (Map.Entry<String, Object> entry : map.entrySet()) {
Object fieldValue = entry.getValue();
String newPrefix = StringUtil.isBlank(prefix) ? entry.getKey() : prefix + "." + entry.getKey();
convertObjectToMap(newPrefix, fieldValue, result);
}
} else {
while (objClass != null) {
for (Field field : objClass.getDeclaredFields()) {
field.setAccessible(true);
Object fieldValue = field.get(obj);
String newPrefix = StringUtil.isBlank(prefix) ? field.getName() : prefix + "." + field.getName();
convertObjectToMap(newPrefix, fieldValue, result);
}
objClass = objClass.getSuperclass();
}
}
}
3. 递归处理每页所有元素
PPTX的元素大部分存在多层的嵌套结构,因此适合使用递归处理。
/**
* 将pptx模版渲染成pptx
*
* @param inputStream pptx模版文件输入流
* @param outputStream pptx渲染结果输出流
* @param param 占位符替换参数
* @throws IOException
* @throws InvalidFormatException
*/
public static void renderAsPptx(InputStream inputStream, OutputStream outputStream, Map<String, String> param) throws IOException, InvalidFormatException {
long startTime = System.currentTimeMillis();
try (XMLSlideShow slideShow = new XMLSlideShow(inputStream)) {
for (XSLFSlide slide : slideShow.getSlides()) {
// slide就是ppt的一页
for (XSLFShape shape : slide) {
// shape就是每页中的一个个元素
replaceData(shape, param);
}
}
// 将pptx从内存写入到输入流
slideShow.write(outputStream);
}
long endTime = System.currentTimeMillis();
log.info("render pptx cost {}ms", endTime - startTime);
}
/**
* pptx模版占位符替换为数据
*
* @param shape pptx模版中待处理的元素
* @param param 占位符替换参数
* @throws IOException
* @throws InvalidFormatException
*/
private static void replaceData(XSLFShape shape, Map<String, String> param) throws IOException, InvalidFormatException {
if (shape instanceof XSLFGroupShape) {
// 组合元素
XSLFGroupShape groupShape = (XSLFGroupShape) shape;
for (XSLFShape childShape : groupShape) {
// 递归替换占位符
replaceData(childShape, param);
}
} else if (shape instanceof XSLFTextShape) {
// 文本框
XSLFTextShape textShape = (XSLFTextShape) shape;
replaceTextData(textShape, param);
} else if (shape instanceof XSLFTable) {
// 表格
XSLFTable table = (XSLFTable) shape;
for (XSLFTableRow row : table) {
for (XSLFTableCell cell : row) {
// 表格单元格是文本框的子类,递归替换占位符
replaceData(cell, param);
}
}
} else if (shape instanceof XSLFGraphicFrame) {
// 图像
// 注意表格是图像的子类,所以图像处理必须在表格处理之后
XSLFGraphicFrame graphicFrame = (XSLFGraphicFrame) shape;
// 是否是图表,其他类型不处理
if (graphicFrame.hasChart()) {
XSLFChart chart = graphicFrame.getChart();
// 替换图表标题内容,标题类型是文本框,递归替换占位符
replaceData(chart.getTitleShape(), param);
// 替换图表关联的excel数据
replaceExcelData(chart, param);
// 更新图表缓存
updateChartCache(chart);
}
} else if (shape instanceof XSLFPictureShape) {
XSLFPictureShape pictureShape = (XSLFPictureShape) shape;
// todo 图片替换暂时没想到什么方便的方式,并且需求里也没有需要根据数据展示不同图片的功能,因此暂不实现
}
}
4. 文本元素占位符替换
PPTX的文本元素可以分多段设置成不同的文本格式,PPTX文件会将一个文本元素按文本格式、中英文、标点符号拆分成多个分段。因此一个占位符有可能被拆分为多个分段,替换占位符时需要遍历所有的分段,寻找完整的占位符再替换为具体数据。替换时只将占位符的第一个分段替换为数据,后续分段替换为空字符串,即文本样式套用占位符的第一个字符的样式。
private static final String PLACEHOLDER_PREFIX = "${";
private static final String PLACEHOLDER_SUFFIX = "}";
private static final Pattern PLACEHOLDER_PATTERN = Pattern.compile("\\$\\{[a-zA-Z0-9_.\\[\\]]+}");
private static final Pattern PLACEHOLDER_PREFIX_PATTERN = Pattern.compile("(\\$\\{[a-zA-Z0-9_.\\[\\]]*|\\$)$");
private static final Pattern PLACEHOLDER_SUFFIX_PATTERN = Pattern.compile("^[a-zA-Z0-9_.\\[\\]]*}");
/**
* 替换文本框里的占位符<br>
* 同一个占位符被分段赋予了不同的样式时,替换结果套用第一个占位符片段的样式
*
* @param textShape 文本框
* @param param 占位符替换参数
*/
private static void replaceTextData(XSLFTextShape textShape, Map<String, String> param) {
for (XSLFTextParagraph textParagraph : textShape) {
// 由于占位符有可能被分割到多个textRun中,用list缓存包含同一个占位符分割片段的连续几个textRun
List<XSLFTextRun> placeholderTextRunList = new ArrayList<>();
// 缓存分割后的占位符片段
List<String> placeholderList = new ArrayList<>();
for (XSLFTextRun textRun : textParagraph) {
String text = textRun.getRawText();
Matcher placeholderMatcher = PLACEHOLDER_PATTERN.matcher(text);
Matcher placeholderPrefixMatcher = PLACEHOLDER_PREFIX_PATTERN.matcher(text);
Matcher placeholderSuffixMatcher = PLACEHOLDER_SUFFIX_PATTERN.matcher(text);
if (CollectionUtil.isNotEmpty(placeholderTextRunList) && placeholderSuffixMatcher.find()) {
// 缓存不为空,说明在此之前已找到过占位符前缀
// 当前textRun开头处包含占位符后缀,存入缓存
String placeholderSuffix = placeholderSuffixMatcher.group();
placeholderTextRunList.add(textRun);
placeholderList.add(placeholderSuffix);
String placeholder = String.join("", placeholderList);
if (PLACEHOLDER_PATTERN.matcher(placeholder).matches()) {
// 缓存中的占位符片段拼接后确实是占位符
String paramKey = placeholder.replace(PLACEHOLDER_PREFIX, "").replace(PLACEHOLDER_SUFFIX, "");
String value = param.getOrDefault(paramKey, "");
for (int i = 0; i < placeholderTextRunList.size(); i++) {
XSLFTextRun placeholderTextRun = placeholderTextRunList.get(i);
String replaceTarget = placeholderList.get(i);
if (i == 0) {
// 仅第一个textRun,即包含占位符前缀的textRun,将占位符片段替换为数据
// 因此替换的数据套用该textRun的样式
placeholderTextRun.setText(placeholderTextRun.getRawText().replaceAll(Pattern.quote(replaceTarget) + "$", value));
} else {
// 其余textRun中的占位符片段替换为空字符串
placeholderTextRun.setText(placeholderTextRun.getRawText().replaceAll("^" + Pattern.quote(replaceTarget), ""));
}
}
}
// 找到占位符后缀并处理后,清空缓存,重新寻找下一个占位符
placeholderTextRunList.clear();
placeholderList.clear();
}
while (placeholderMatcher.find()) {
// 当前textRun包含完整的占位符,直接替换成数据
String placeholder = placeholderMatcher.group();
String paramKey = placeholder.replace(PLACEHOLDER_PREFIX, "").replace(PLACEHOLDER_SUFFIX, "");
String value = param.getOrDefault(paramKey, "");
textRun.setText(textRun.getRawText().replace(placeholder, value));
// 因为不支持占位符嵌套占位符
// 之前的缓存无效,并不是真的占位符
placeholderTextRunList.clear();
placeholderList.clear();
}
if (placeholderPrefixMatcher.find()) {
// 当前textRun结尾处找到了新的占位符前缀
// 之前的缓存无效,并不是真的占位符
placeholderTextRunList.clear();
placeholderList.clear();
String placeholderPrefix = placeholderPrefixMatcher.group();
placeholderTextRunList.add(textRun);
placeholderList.add(placeholderPrefix);
} else if (CollectionUtil.isNotEmpty(placeholderTextRunList)) {
// 缓存不为空,说明在此之前已找到过占位符前缀
// 且占位符前缀不是当前textRun找到的
placeholderTextRunList.add(textRun);
placeholderList.add(text);
}
}
}
}
5. 图表元素关联的Excel中的占位符替换
private static final String PLACEHOLDER_PREFIX = "${";
private static final String PLACEHOLDER_SUFFIX = "}";
private static final Pattern PLACEHOLDER_PATTERN = Pattern.compile("\\$\\{[a-zA-Z0-9_.\\[\\]]+}");
private static final Pattern NUMBER_PATTERN = Pattern.compile("^([+-])?\\d+(\\.\\d+)?$");
private static final Pattern PERCENT_PATTERN = Pattern.compile("^([+-])?\\d+(\\.\\d+)?%$");
private static final String PERCENT_FORMAT_STR = "0.00%";
private static final int PERCENT_FORMAT_CODE = BuiltinFormats.getBuiltinFormat(PERCENT_FORMAT_STR);
/**
* 替换图表关联的excel数据
*
* @param chart 图表
* @param param 占位符替换参数
* @throws IOException
* @throws InvalidFormatException
*/
private static void replaceExcelData(XSLFChart chart, Map<String, String> param) throws IOException, InvalidFormatException {
XSSFWorkbook workbook = chart.getWorkbook();
for (Sheet sheet : workbook) {
for (Row row : sheet) {
for (Cell cell : row) {
if (CellType.STRING.equals(cell.getCellType())) {
String cellValue = cell.getStringCellValue();
Matcher matcher = PLACEHOLDER_PATTERN.matcher(cellValue);
while (matcher.find()) {
String placeholder = matcher.group();
String paramKey = placeholder.replace(PLACEHOLDER_PREFIX, "").replace(PLACEHOLDER_SUFFIX, "");
String value = param.getOrDefault(paramKey, "");
cellValue = cellValue.replace(placeholder, value);
}
if (NUMBER_PATTERN.matcher(cellValue).matches()) {
// 图表渲染时无法自动将字符串转为数字,需要专门转换类型
cell.setCellValue(Double.parseDouble(cellValue));
} else if (PERCENT_PATTERN.matcher(cellValue).matches()) {
// 图表渲染时无法自动将字符串转为百分数,需要专门设置单元格格式
cell.setCellValue(Double.parseDouble(cellValue.replace("%", "")) / 100);
XSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setDataFormat(PERCENT_FORMAT_CODE);
cell.setCellStyle(cellStyle);
} else {
cell.setCellValue(cellValue);
}
}
}
}
}
workbook.write(chart.getPackagePart().getOutputStream());
}
6. 图表元素更新展示效果缓存
只修改图表关联的Excel数据,图表展示效果并不会自动发生改变,需要将Excel的数据同步到图表的展示效果缓存中。由于POI对PPTX图表的操作只是简单封装了一层,并没有做过多的抽象处理,每种图表都是一个单独的类,相互之间不存在继承关系也没有共同的父类,因此每种图表都要重复进行一次代码编写。
/**
* 更新图表缓存
*
* @param chart 图表
* @throws IOException
* @throws InvalidFormatException
*/
private static void updateChartCache(XSLFChart chart) throws IOException, InvalidFormatException {
XSSFWorkbook workbook = chart.getWorkbook();
CTPlotArea plotArea = chart.getCTChart().getPlotArea();
// 不同图表类型并没有继承相同的父类,无法复用代码
for (CTAreaChart ctChart : plotArea.getAreaChartList()) {
for (CTAreaSer ser : ctChart.getSerList()) {
// 系列标题缓存
updateStrRef(ser.getTx().getStrRef(), workbook);
// 条目数据缓存
updateStrRef(ser.getCat().getStrRef(), workbook);
// 数据缓存
updateNumRef(ser.getVal().getNumRef(), workbook);
// 附加元素缓存
updateExtLst(ser.getExtLst().getExtList(), workbook);
}
}
for (CTArea3DChart ctChart : plotArea.getArea3DChartList()) {
for (CTAreaSer ser : ctChart.getSerList()) {
updateStrRef(ser.getTx().getStrRef(), workbook);
updateStrRef(ser.getCat().getStrRef(), workbook);
updateNumRef(ser.getVal().getNumRef(), workbook);
updateExtLst(ser.getExtLst().getExtList(), workbook);
}
}
for (CTLineChart ctChart : plotArea.getLineChartList()) {
for (CTLineSer ser : ctChart.getSerList()) {
updateStrRef(ser.getTx().getStrRef(), workbook);
updateStrRef(ser.getCat().getStrRef(), workbook);
updateNumRef(ser.getVal().getNumRef(), workbook);
updateExtLst(ser.getExtLst().getExtList(), workbook);
}
}
for (CTLine3DChart ctChart : plotArea.getLine3DChartList()) {
for (CTLineSer ser : ctChart.getSerList()) {
updateStrRef(ser.getTx().getStrRef(), workbook);
updateStrRef(ser.getCat().getStrRef(), workbook);
updateNumRef(ser.getVal().getNumRef(), workbook);
updateExtLst(ser.getExtLst().getExtList(), workbook);
}
}
for (CTStockChart ctChart : plotArea.getStockChartList()) {
for (CTLineSer ser : ctChart.getSerList()) {
updateStrRef(ser.getTx().getStrRef(), workbook);
updateStrRef(ser.getCat().getStrRef(), workbook);
updateNumRef(ser.getVal().getNumRef(), workbook);
updateExtLst(ser.getExtLst().getExtList(), workbook);
}
}
for (CTRadarChart ctChart : plotArea.getRadarChartList()) {
for (CTRadarSer ser : ctChart.getSerList()) {
updateStrRef(ser.getTx().getStrRef(), workbook);
updateStrRef(ser.getCat().getStrRef(), workbook);
updateNumRef(ser.getVal().getNumRef(), workbook);
updateExtLst(ser.getExtLst().getExtList(), workbook);
}
}
// todo 散点图,格式和其他图表不太一样,需求中不需要使用,所以暂不处理了
// for (CTScatterChart ctChart : plotArea.getScatterChartList()) {
// for (CTScatterSer ser : ctChart.getSerList()) {
// updateStrRef(ser.getTx().getStrRef(), workbook);
// updateStrRef(ser.getXVal().getStrRef(), workbook);
// updateNumRef(ser.getYVal().getNumRef(), workbook);
// }
// }
for (CTPieChart ctChart : plotArea.getPieChartList()) {
for (CTPieSer ser : ctChart.getSerList()) {
updateStrRef(ser.getTx().getStrRef(), workbook);
updateStrRef(ser.getCat().getStrRef(), workbook);
updateNumRef(ser.getVal().getNumRef(), workbook);
updateExtLst(ser.getExtLst().getExtList(), workbook);
}
}
for (CTPie3DChart ctChart : plotArea.getPie3DChartList()) {
for (CTPieSer ser : ctChart.getSerList()) {
updateStrRef(ser.getTx().getStrRef(), workbook);
updateStrRef(ser.getCat().getStrRef(), workbook);
updateNumRef(ser.getVal().getNumRef(), workbook);
updateExtLst(ser.getExtLst().getExtList(), workbook);
}
}
for (CTDoughnutChart ctChart : plotArea.getDoughnutChartList()) {
for (CTPieSer ser : ctChart.getSerList()) {
updateStrRef(ser.getTx().getStrRef(), workbook);
updateStrRef(ser.getCat().getStrRef(), workbook);
updateNumRef(ser.getVal().getNumRef(), workbook);
updateExtLst(ser.getExtLst().getExtList(), workbook);
}
}
for (CTBarChart ctChart : plotArea.getBarChartList()) {
for (CTBarSer ser : ctChart.getSerList()) {
updateStrRef(ser.getTx().getStrRef(), workbook);
updateStrRef(ser.getCat().getStrRef(), workbook);
updateNumRef(ser.getVal().getNumRef(), workbook);
updateExtLst(ser.getExtLst().getExtList(), workbook);
}
}
for (CTBar3DChart ctChart : plotArea.getBar3DChartList()) {
for (CTBarSer ser : ctChart.getSerList()) {
updateStrRef(ser.getTx().getStrRef(), workbook);
updateStrRef(ser.getCat().getStrRef(), workbook);
updateNumRef(ser.getVal().getNumRef(), workbook);
updateExtLst(ser.getExtLst().getExtList(), workbook);
}
}
for (CTOfPieChart ctChart : plotArea.getOfPieChartList()) {
for (CTPieSer ser : ctChart.getSerList()) {
updateStrRef(ser.getTx().getStrRef(), workbook);
updateStrRef(ser.getCat().getStrRef(), workbook);
updateNumRef(ser.getVal().getNumRef(), workbook);
updateExtLst(ser.getExtLst().getExtList(), workbook);
}
}
for (CTSurfaceChart ctChart : plotArea.getSurfaceChartList()) {
for (CTSurfaceSer ser : ctChart.getSerList()) {
updateStrRef(ser.getTx().getStrRef(), workbook);
updateStrRef(ser.getCat().getStrRef(), workbook);
updateNumRef(ser.getVal().getNumRef(), workbook);
updateExtLst(ser.getExtLst().getExtList(), workbook);
}
}
for (CTSurface3DChart ctChart : plotArea.getSurface3DChartList()) {
for (CTSurfaceSer ser : ctChart.getSerList()) {
updateStrRef(ser.getTx().getStrRef(), workbook);
updateStrRef(ser.getCat().getStrRef(), workbook);
updateNumRef(ser.getVal().getNumRef(), workbook);
updateExtLst(ser.getExtLst().getExtList(), workbook);
}
}
// todo 气泡图,格式和其他图表不太一样,需求中不需要使用,所以暂不处理了
// for (CTBubbleChart ctChart : plotArea.getBubbleChartList()) {
// for (CTBubbleSer ser : ctChart.getSerList()) {
// updateStrRef(ser.getTx().getStrRef(), workbook);
// updateStrRef(ser.getXVal().getStrRef(), workbook);
// updateNumRef(ser.getYVal().getNumRef(), workbook);
// updateNumRef(ser.getBubbleSize().getNumRef(), workbook);
// }
// }
}
每种图表的数据结构其实都大致相同,主要有Tx、Cat、Val、ExtLst四部分缓存。Tx、Cat、Val有StrRef和NumRef两种格式,POI对这三部分缓存的封装支持较完善。ExtLst缓存的是图表各项设置,例如数据标签设置,POI对这部分没有做支持,需要自己编写代码操作XML。
/**
* 更新图表字符串元素缓存
*
* @param strRef 字符串元素缓存
* @param workbook 图表关联的excel
*/
private static void updateStrRef(CTStrRef strRef, XSSFWorkbook workbook) {
if (strRef == null) {
return;
}
CellReference[] cellReferences = new AreaReference(strRef.getF(), SpreadsheetVersion.EXCEL2007).getAllReferencedCells();
List<CTStrVal> ptList = strRef.getStrCache().getPtList();
for (int i = 0; i < ptList.size(); i++) {
XSSFCell cell = getCell(workbook, cellReferences[i]);
if (cell == null) {
log.warn("cell is null, cellReference: {}", cellReferences[i]);
continue;
}
CTStrVal pt = ptList.get(i);
if (CellType.NUMERIC.equals(cell.getCellType())) {
pt.setV(String.valueOf(cell.getNumericCellValue()));
} else if (CellType.STRING.equals(cell.getCellType())) {
pt.setV(cell.getStringCellValue());
} else {
pt.setV("");
}
}
}
/**
* 更新图表数字元素缓存
*
* @param numRef 数字元素缓存
* @param workbook 图表关联的excel
*/
private static void updateNumRef(CTNumRef numRef, XSSFWorkbook workbook) {
if (numRef == null) {
return;
}
CellReference[] cellReferences = new AreaReference(numRef.getF(), SpreadsheetVersion.EXCEL2007).getAllReferencedCells();
CTNumData numCache = numRef.getNumCache();
List<CTNumVal> ptList = numCache.getPtList();
for (int i = 0; i < ptList.size(); i++) {
XSSFCell cell = getCell(workbook, cellReferences[i]);
if (cell == null) {
log.warn("cell is null, cellReference: {}", cellReferences[i]);
continue;
}
CTNumVal pt = ptList.get(i);
if (CellType.NUMERIC.equals(cell.getCellType())) {
pt.setV(String.valueOf(cell.getNumericCellValue()));
if (cell.getCellStyle().getDataFormat() == PERCENT_FORMAT_CODE) {
numCache.setFormatCode(PERCENT_FORMAT_STR);
}
} else if (CellType.STRING.equals(cell.getCellType())) {
pt.setV(cell.getStringCellValue());
} else {
pt.setV("");
}
}
}
/**
* 更新图表附加元素缓存<br>
* 目前只支持选项:数据标签-单元格中的值
*
* @param extList 附加元素缓存
* @param workbook 图表关联的excel
*/
private static void updateExtLst(List<CTExtension> extList, XSSFWorkbook workbook) {
if (extList == null) {
return;
}
for (CTExtension ext : extList) {
TypeStoreUser datalabelsRange = ((CTExtensionImpl) ext).get_store().find_element_user(new QName("http://schemas.microsoft.com/office/drawing/2012/chart", "datalabelsRange"), 0);
if (datalabelsRange == null) {
return;
}
TypeStoreUser f = datalabelsRange.get_store().find_element_user(new QName("http://schemas.microsoft.com/office/drawing/2012/chart", "f"), 0);
CellReference[] cellReferences = new AreaReference(((SimpleValue) f).getStringValue(), SpreadsheetVersion.EXCEL2007).getAllReferencedCells();
ArrayList<TypeStoreUser> ptList = new ArrayList<>();
datalabelsRange.get_store().find_element_user(new QName("http://schemas.microsoft.com/office/drawing/2012/chart", "dlblRangeCache"), 0)
.get_store().find_all_element_users(new QName("http://schemas.openxmlformats.org/drawingml/2006/chart", "pt"), ptList);
for (int i = 0; i < ptList.size(); i++) {
XSSFCell cell = getCell(workbook, cellReferences[i]);
if (cell == null) {
log.warn("cell is null, cellReference: {}", cellReferences[i]);
continue;
}
TypeStoreUser v = ptList.get(i).get_store().find_element_user(new QName("http://schemas.openxmlformats.org/drawingml/2006/chart", "v"), 0);
if (CellType.NUMERIC.equals(cell.getCellType())) {
((SimpleValue) v).setStringValue(String.valueOf(cell.getNumericCellValue()));
} else if (CellType.STRING.equals(cell.getCellType())) {
((SimpleValue) v).setStringValue(cell.getStringCellValue());
} else {
((SimpleValue) v).setStringValue("");
}
}
}
}
/**
* 获取单元格
*
* @param workbook excel
* @param cellReference 单元格位置信息
* @return
*/
private static XSSFCell getCell(XSSFWorkbook workbook, CellReference cellReference) {
XSSFSheet sheet = workbook.getSheet(cellReference.getSheetName());
if (sheet == null) {
return null;
}
XSSFRow row = sheet.getRow(cellReference.getRow());
if (row == null) {
return null;
}
return row.getCell(cellReference.getCol());
}
7. 完整代码
包含使用Aspose.Slides做PPTX转PDF,Aspose.Slides使用的是破解版,具体怎么破解请自行搜索,网上的破解教程很多,当然还是推荐购买正版。
还有一些工具类使用的是我们公司自己封装的,读者可自行替换为其他实现。
import com.aspose.slides.*;
import com.ly.fn.inf.util.CollectionUtil;
import com.ly.fn.inf.util.ResourceUtil;
import com.ly.fn.inf.util.StringUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.SpreadsheetVersion;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.AreaReference;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xslf.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.apache.xmlbeans.SimpleValue;
import org.apache.xmlbeans.impl.values.TypeStoreUser;
import org.openxmlformats.schemas.drawingml.x2006.chart.*;
import org.openxmlformats.schemas.drawingml.x2006.chart.impl.CTExtensionImpl;
import javax.xml.namespace.QName;
import java.io.*;
import java.lang.reflect.Field;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
/**
* ppt模版帮助类
*
* @author guangyi.shi
*/
@Slf4j
public class PowerPointTemplateHelper {
private static final String PLACEHOLDER_PREFIX = "${";
private static final String PLACEHOLDER_SUFFIX = "}";
private static final Pattern PLACEHOLDER_PATTERN = Pattern.compile("\\$\\{[a-zA-Z0-9_.\\[\\]]+}");
private static final Pattern PLACEHOLDER_PREFIX_PATTERN = Pattern.compile("(\\$\\{[a-zA-Z0-9_.\\[\\]]*|\\$)$");
private static final Pattern PLACEHOLDER_SUFFIX_PATTERN = Pattern.compile("^[a-zA-Z0-9_.\\[\\]]*}");
private static final Pattern NUMBER_PATTERN = Pattern.compile("^([+-])?\\d+(\\.\\d+)?$");
private static final Pattern PERCENT_PATTERN = Pattern.compile("^([+-])?\\d+(\\.\\d+)?%$");
private static final String PERCENT_FORMAT_STR = "0.00%";
private static final int PERCENT_FORMAT_CODE = BuiltinFormats.getBuiltinFormat(PERCENT_FORMAT_STR);
private static boolean FONT_LOADED = false;
/**
* 将pptx模版渲染成pdf
*
* @param inputStream pptx模版文件输入流
* @param outputStream pdf渲染结果输出流
* @param param 占位符替换参数
* @throws IOException
* @throws InvalidFormatException
*/
public static void renderAsPdf(InputStream inputStream, OutputStream outputStream, Object param) throws IOException, InvalidFormatException, IllegalAccessException {
Map<String, String> paramMap = convertObjectToMap(param);
renderAsPdf(inputStream, outputStream, paramMap);
}
/**
* 将pptx模版渲染成pptx
*
* @param inputStream pptx模版文件输入流
* @param outputStream pptx渲染结果输出流
* @param param 占位符替换参数
* @throws IOException
* @throws InvalidFormatException
*/
public static void renderAsPptx(InputStream inputStream, OutputStream outputStream, Object param) throws IOException, InvalidFormatException, IllegalAccessException {
Map<String, String> paramMap = convertObjectToMap(param);
renderAsPptx(inputStream, outputStream, paramMap);
}
/**
* 对象转map,用于pptx模版参数替换
*
* @param obj
* @return
* @throws IllegalAccessException
*/
private static Map<String, String> convertObjectToMap(Object obj) throws IllegalAccessException {
long startTime = System.currentTimeMillis();
Map<String, String> result = new HashMap<>();
convertObjectToMap("", obj, result);
long endTime = System.currentTimeMillis();
log.info("param obj to map cost {}ms", endTime - startTime);
return result;
}
/**
* 对象转map递归
*
* @param prefix
* @param obj
* @param result
* @throws IllegalAccessException
*/
private static void convertObjectToMap(String prefix, Object obj, Map<String, String> result) throws IllegalAccessException {
if (obj == null) {
return;
}
Class<?> objClass = obj.getClass();
if (objClass.isPrimitive() || obj instanceof String || obj instanceof Number) {
result.put(prefix, obj.toString());
} else if (obj instanceof Collection) {
int index = 0;
for (Object item : (Collection<?>) obj) {
convertObjectToMap(prefix + "[" + index + "]", item, result);
index++;
}
} else if (objClass.isArray()) {
// todo jacoco覆盖率检测会往对象里添加boolean[],导致转Object[]报错,代码习惯使用List不使用Array,所以直接忽略不处理
// Object[] arr = (Object[]) obj;
// int length = arr.length;
// for (int i = 0; i < length; i++) {
// convertObjectToMap(prefix + "[" + i + "]", arr[i], result);
// }
} else if (obj instanceof Map) {
Map<String, Object> map = (Map<String, Object>) obj;
for (Map.Entry<String, Object> entry : map.entrySet()) {
Object fieldValue = entry.getValue();
String newPrefix = StringUtil.isBlank(prefix) ? entry.getKey() : prefix + "." + entry.getKey();
convertObjectToMap(newPrefix, fieldValue, result);
}
} else {
while (objClass != null) {
for (Field field : objClass.getDeclaredFields()) {
field.setAccessible(true);
Object fieldValue = field.get(obj);
String newPrefix = StringUtil.isBlank(prefix) ? field.getName() : prefix + "." + field.getName();
convertObjectToMap(newPrefix, fieldValue, result);
}
objClass = objClass.getSuperclass();
}
}
}
/**
* 将pptx模版渲染成pdf
*
* @param inputStream pptx模版文件输入流
* @param outputStream pdf渲染结果输出流
* @param param 占位符替换参数
* @throws IOException
* @throws InvalidFormatException
*/
public static void renderAsPdf(InputStream inputStream, OutputStream outputStream, Map<String, String> param) throws IOException, InvalidFormatException {
long startTime = System.currentTimeMillis();
try (ByteArrayOutputStream tempOutputStream = new ByteArrayOutputStream()) {
renderAsPptx(inputStream, tempOutputStream, param);
try (ByteArrayInputStream tempInputStream = new ByteArrayInputStream(tempOutputStream.toByteArray())) {
pptxToPdf(tempInputStream, outputStream);
}
}
long endTime = System.currentTimeMillis();
log.info("render pdf cost {}ms", endTime - startTime);
}
/**
* 将pptx模版渲染成pptx
*
* @param inputStream pptx模版文件输入流
* @param outputStream pptx渲染结果输出流
* @param param 占位符替换参数
* @throws IOException
* @throws InvalidFormatException
*/
public static void renderAsPptx(InputStream inputStream, OutputStream outputStream, Map<String, String> param) throws IOException, InvalidFormatException {
long startTime = System.currentTimeMillis();
try (XMLSlideShow slideShow = new XMLSlideShow(inputStream)) {
for (XSLFSlide slide : slideShow.getSlides()) {
// log.info("slide:{}", slide);
for (XSLFShape shape : slide) {
// log.info("shape:{}", shape);
replaceData(shape, param);
}
}
slideShow.write(outputStream);
}
long endTime = System.currentTimeMillis();
log.info("render pptx cost {}ms", endTime - startTime);
}
/**
* pptx转pdf<br>
* 首次调用后会启动一个单独的java进程
*
* @param inputStream pptx文件输入流
* @param outputStream pdf文件输出流
*/
public static void pptxToPdf(InputStream inputStream, OutputStream outputStream) throws IOException {
asposeLicense();
loadFonts();
long startTime = System.currentTimeMillis();
Presentation presentation = new Presentation(inputStream);
try {
// 优化pdf文件大小的各种设置
PdfOptions pdfOptions = new PdfOptions();
pdfOptions.setTextCompression(PdfTextCompression.Flate);
pdfOptions.setBestImagesCompressionRatio(true);
pdfOptions.setJpegQuality((byte) 90);
pdfOptions.setCompliance(PdfCompliance.Pdf15);
pdfOptions.setSaveMetafilesAsPng(true);
// 转换为pdf
presentation.save(outputStream, SaveFormat.Pdf, pdfOptions);
} finally {
// 释放资源
presentation.dispose();
}
long endTime = System.currentTimeMillis();
log.info("pptx to pdf cost {}ms", endTime - startTime);
}
/**
* aspose框架认证<br>
* 项目启动时调用一次即可<br>
* 可去除水印和pptx页数限制
*
* @throws IOException
*/
private synchronized static void asposeLicense() throws IOException {
License license = new License();
if (license.isLicensed()) {
log.info("aspose is licensed");
return;
}
long startTime = System.currentTimeMillis();
String licenseXml = "<License>\n" +
" <Data>\n" +
" <Products>\n" +
" <Product>Aspose.Total for Java</Product>\n" +
" <Product>Aspose.Words for Java</Product>\n" +
" </Products>\n" +
" <EditionType>Enterprise</EditionType>\n" +
" <SubscriptionExpiry>20991231</SubscriptionExpiry>\n" +
" <LicenseExpiry>20991231</LicenseExpiry>\n" +
" <SerialNumber>8bfe198c-7f0c-4ef8-8ff0-acc3237bf0d7</SerialNumber>\n" +
" </Data>\n" +
" <Signature>\n" +
" sNLLKGMUdF0r8O1kKilWAGdgfs2BvJb/2Xp8p5iuDVfZXmhppo+d0Ran1P9TKdjV4ABwAgKXxJ3jcQTqE/2IRfqwnPf8itN8aFZlV3TJPYeD3yWE7IT55Gz6EijUpC7aKeoohTb4w2fpox58wWoF3SNp6sK6jDfiAUGEHYJ9pjU=\n" +
" </Signature>\n" +
"</License>";
try (ByteArrayInputStream licenseStream = new ByteArrayInputStream(licenseXml.getBytes())) {
license.setLicense(licenseStream);
}
long endTime = System.currentTimeMillis();
log.info("aspose set license cost {}ms", endTime - startTime);
}
/**
* 加载字体<br>
* 默认字体很多汉字不符合中文正常书写习惯,建议加载自定义的字体<br>
* 字体文件放在项目的src/main/resources/fonts目录下<br>
* 项目启动时调用一次即可<br>
* 注意!加载顺序要从细到粗排列,否则易出现字体粗细混乱
*
* @throws IOException
*/
private synchronized static void loadFonts() throws IOException {
if (!FONT_LOADED) {
loadFont("fonts/SourceHanSansCN-ExtraLight.ttf");
loadFont("fonts/SourceHanSansCN-Light.ttf");
loadFont("fonts/SourceHanSansCN-Normal.ttf");
loadFont("fonts/SourceHanSansCN-Regular.ttf");
loadFont("fonts/SourceHanSansCN-Medium.ttf");
loadFont("fonts/SourceHanSansCN-Bold.ttf");
loadFont("fonts/SourceHanSansCN-Heavy.ttf");
FONT_LOADED = true;
}
}
/**
* 加载字体
*
* @throws IOException
*/
private static void loadFont(String classpath) throws IOException {
byte[] fontBytes = ResourceUtil.readResource(classpath);
FontsLoader.loadExternalFont(fontBytes);
}
/**
* pptx模版占位符替换为数据
*
* @param shape pptx模版中待处理的元素
* @param param 占位符替换参数
* @throws IOException
* @throws InvalidFormatException
*/
private static void replaceData(XSLFShape shape, Map<String, String> param) throws IOException, InvalidFormatException {
if (shape instanceof XSLFGroupShape) {
// 组合元素
XSLFGroupShape groupShape = (XSLFGroupShape) shape;
for (XSLFShape childShape : groupShape) {
// 递归替换占位符
replaceData(childShape, param);
}
} else if (shape instanceof XSLFTextShape) {
// 文本框
XSLFTextShape textShape = (XSLFTextShape) shape;
replaceTextData(textShape, param);
} else if (shape instanceof XSLFTable) {
// 表格
XSLFTable table = (XSLFTable) shape;
for (XSLFTableRow row : table) {
for (XSLFTableCell cell : row) {
// 表格单元格是文本框的子类,递归替换占位符
replaceData(cell, param);
}
}
} else if (shape instanceof XSLFGraphicFrame) {
// 图像
// 注意表格是图像的子类,所以图像处理必须在表格处理之后
XSLFGraphicFrame graphicFrame = (XSLFGraphicFrame) shape;
// 是否是图表,其他类型不处理
if (graphicFrame.hasChart()) {
XSLFChart chart = graphicFrame.getChart();
// 替换图表标题内容,标题类型是文本框,递归替换占位符
replaceData(chart.getTitleShape(), param);
// 替换图表关联的excel数据
replaceExcelData(chart, param);
// 更新图表缓存
updateChartCache(chart);
}
} else if (shape instanceof XSLFPictureShape) {
XSLFPictureShape pictureShape = (XSLFPictureShape) shape;
// todo 图片替换暂时没想到什么方便的方式,并且需求里也没有需要根据数据展示不同图片的功能,因此暂不实现
}
}
/**
* 替换文本框里的占位符<br>
* 同一个占位符被分段赋予了不同的样式时,替换结果套用第一个占位符片段的样式
*
* @param textShape 文本框
* @param param 占位符替换参数
*/
private static void replaceTextData(XSLFTextShape textShape, Map<String, String> param) {
for (XSLFTextParagraph textParagraph : textShape) {
// 由于占位符有可能被分割到多个textRun中,用list缓存包含同一个占位符分割片段的连续几个textRun
List<XSLFTextRun> placeholderTextRunList = new ArrayList<>();
// 缓存分割后的占位符片段
List<String> placeholderList = new ArrayList<>();
for (XSLFTextRun textRun : textParagraph) {
String text = textRun.getRawText();
Matcher placeholderMatcher = PLACEHOLDER_PATTERN.matcher(text);
Matcher placeholderPrefixMatcher = PLACEHOLDER_PREFIX_PATTERN.matcher(text);
Matcher placeholderSuffixMatcher = PLACEHOLDER_SUFFIX_PATTERN.matcher(text);
if (CollectionUtil.isNotEmpty(placeholderTextRunList) && placeholderSuffixMatcher.find()) {
// 缓存不为空,说明在此之前已找到过占位符前缀
// 当前textRun开头处包含占位符后缀,存入缓存
String placeholderSuffix = placeholderSuffixMatcher.group();
placeholderTextRunList.add(textRun);
placeholderList.add(placeholderSuffix);
String placeholder = String.join("", placeholderList);
if (PLACEHOLDER_PATTERN.matcher(placeholder).matches()) {
// 缓存中的占位符片段拼接后确实是占位符
String paramKey = placeholder.replace(PLACEHOLDER_PREFIX, "").replace(PLACEHOLDER_SUFFIX, "");
String value = param.getOrDefault(paramKey, "");
for (int i = 0; i < placeholderTextRunList.size(); i++) {
XSLFTextRun placeholderTextRun = placeholderTextRunList.get(i);
String replaceTarget = placeholderList.get(i);
if (i == 0) {
// 仅第一个textRun,即包含占位符前缀的textRun,将占位符片段替换为数据
// 因此替换的数据套用该textRun的样式
placeholderTextRun.setText(placeholderTextRun.getRawText().replaceAll(Pattern.quote(replaceTarget) + "$", value));
} else {
// 其余textRun中的占位符片段替换为空字符串
placeholderTextRun.setText(placeholderTextRun.getRawText().replaceAll("^" + Pattern.quote(replaceTarget), ""));
}
}
}
// 找到占位符后缀并处理后,清空缓存,重新寻找下一个占位符
placeholderTextRunList.clear();
placeholderList.clear();
}
while (placeholderMatcher.find()) {
// 当前textRun包含完整的占位符,直接替换成数据
String placeholder = placeholderMatcher.group();
String paramKey = placeholder.replace(PLACEHOLDER_PREFIX, "").replace(PLACEHOLDER_SUFFIX, "");
String value = param.getOrDefault(paramKey, "");
textRun.setText(textRun.getRawText().replace(placeholder, value));
// 因为不支持占位符嵌套占位符
// 之前的缓存无效,并不是真的占位符
placeholderTextRunList.clear();
placeholderList.clear();
}
if (placeholderPrefixMatcher.find()) {
// 当前textRun结尾处找到了新的占位符前缀
// 之前的缓存无效,并不是真的占位符
placeholderTextRunList.clear();
placeholderList.clear();
String placeholderPrefix = placeholderPrefixMatcher.group();
placeholderTextRunList.add(textRun);
placeholderList.add(placeholderPrefix);
} else if (CollectionUtil.isNotEmpty(placeholderTextRunList)) {
// 缓存不为空,说明在此之前已找到过占位符前缀
// 且占位符前缀不是当前textRun找到的
placeholderTextRunList.add(textRun);
placeholderList.add(text);
}
}
}
}
/**
* 替换图表关联的excel数据
*
* @param chart 图表
* @param param 占位符替换参数
* @throws IOException
* @throws InvalidFormatException
*/
private static void replaceExcelData(XSLFChart chart, Map<String, String> param) throws IOException, InvalidFormatException {
XSSFWorkbook workbook = chart.getWorkbook();
for (Sheet sheet : workbook) {
for (Row row : sheet) {
for (Cell cell : row) {
if (CellType.STRING.equals(cell.getCellType())) {
String cellValue = cell.getStringCellValue();
Matcher matcher = PLACEHOLDER_PATTERN.matcher(cellValue);
while (matcher.find()) {
String placeholder = matcher.group();
String paramKey = placeholder.replace(PLACEHOLDER_PREFIX, "").replace(PLACEHOLDER_SUFFIX, "");
String value = param.getOrDefault(paramKey, "");
cellValue = cellValue.replace(placeholder, value);
}
if (NUMBER_PATTERN.matcher(cellValue).matches()) {
// 图表渲染时无法自动将字符串转为数字,需要专门转换类型
cell.setCellValue(Double.parseDouble(cellValue));
} else if (PERCENT_PATTERN.matcher(cellValue).matches()) {
// 图表渲染时无法自动将字符串转为百分数,需要专门设置单元格格式
cell.setCellValue(Double.parseDouble(cellValue.replace("%", "")) / 100);
XSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setDataFormat(PERCENT_FORMAT_CODE);
cell.setCellStyle(cellStyle);
} else {
cell.setCellValue(cellValue);
}
}
}
}
}
workbook.write(chart.getPackagePart().getOutputStream());
}
/**
* 更新图表缓存
*
* @param chart 图表
* @throws IOException
* @throws InvalidFormatException
*/
private static void updateChartCache(XSLFChart chart) throws IOException, InvalidFormatException {
XSSFWorkbook workbook = chart.getWorkbook();
CTPlotArea plotArea = chart.getCTChart().getPlotArea();
// 不同图表类型并没有继承相同的父类,无法复用代码
for (CTAreaChart ctChart : plotArea.getAreaChartList()) {
for (CTAreaSer ser : ctChart.getSerList()) {
// 系列标题缓存
updateStrRef(ser.getTx().getStrRef(), workbook);
// 条目数据缓存
updateStrRef(ser.getCat().getStrRef(), workbook);
// 数据缓存
updateNumRef(ser.getVal().getNumRef(), workbook);
// 附加元素缓存
updateExtLst(ser.getExtLst().getExtList(), workbook);
}
}
for (CTArea3DChart ctChart : plotArea.getArea3DChartList()) {
for (CTAreaSer ser : ctChart.getSerList()) {
updateStrRef(ser.getTx().getStrRef(), workbook);
updateStrRef(ser.getCat().getStrRef(), workbook);
updateNumRef(ser.getVal().getNumRef(), workbook);
updateExtLst(ser.getExtLst().getExtList(), workbook);
}
}
for (CTLineChart ctChart : plotArea.getLineChartList()) {
for (CTLineSer ser : ctChart.getSerList()) {
updateStrRef(ser.getTx().getStrRef(), workbook);
updateStrRef(ser.getCat().getStrRef(), workbook);
updateNumRef(ser.getVal().getNumRef(), workbook);
updateExtLst(ser.getExtLst().getExtList(), workbook);
}
}
for (CTLine3DChart ctChart : plotArea.getLine3DChartList()) {
for (CTLineSer ser : ctChart.getSerList()) {
updateStrRef(ser.getTx().getStrRef(), workbook);
updateStrRef(ser.getCat().getStrRef(), workbook);
updateNumRef(ser.getVal().getNumRef(), workbook);
updateExtLst(ser.getExtLst().getExtList(), workbook);
}
}
for (CTStockChart ctChart : plotArea.getStockChartList()) {
for (CTLineSer ser : ctChart.getSerList()) {
updateStrRef(ser.getTx().getStrRef(), workbook);
updateStrRef(ser.getCat().getStrRef(), workbook);
updateNumRef(ser.getVal().getNumRef(), workbook);
updateExtLst(ser.getExtLst().getExtList(), workbook);
}
}
for (CTRadarChart ctChart : plotArea.getRadarChartList()) {
for (CTRadarSer ser : ctChart.getSerList()) {
updateStrRef(ser.getTx().getStrRef(), workbook);
updateStrRef(ser.getCat().getStrRef(), workbook);
updateNumRef(ser.getVal().getNumRef(), workbook);
updateExtLst(ser.getExtLst().getExtList(), workbook);
}
}
// todo 散点图,格式和其他图表不太一样,需求中不需要使用,所以暂不处理了
// for (CTScatterChart ctChart : plotArea.getScatterChartList()) {
// for (CTScatterSer ser : ctChart.getSerList()) {
// updateStrRef(ser.getTx().getStrRef(), workbook);
// updateStrRef(ser.getXVal().getStrRef(), workbook);
// updateNumRef(ser.getYVal().getNumRef(), workbook);
// }
// }
for (CTPieChart ctChart : plotArea.getPieChartList()) {
for (CTPieSer ser : ctChart.getSerList()) {
updateStrRef(ser.getTx().getStrRef(), workbook);
updateStrRef(ser.getCat().getStrRef(), workbook);
updateNumRef(ser.getVal().getNumRef(), workbook);
updateExtLst(ser.getExtLst().getExtList(), workbook);
}
}
for (CTPie3DChart ctChart : plotArea.getPie3DChartList()) {
for (CTPieSer ser : ctChart.getSerList()) {
updateStrRef(ser.getTx().getStrRef(), workbook);
updateStrRef(ser.getCat().getStrRef(), workbook);
updateNumRef(ser.getVal().getNumRef(), workbook);
updateExtLst(ser.getExtLst().getExtList(), workbook);
}
}
for (CTDoughnutChart ctChart : plotArea.getDoughnutChartList()) {
for (CTPieSer ser : ctChart.getSerList()) {
updateStrRef(ser.getTx().getStrRef(), workbook);
updateStrRef(ser.getCat().getStrRef(), workbook);
updateNumRef(ser.getVal().getNumRef(), workbook);
updateExtLst(ser.getExtLst().getExtList(), workbook);
}
}
for (CTBarChart ctChart : plotArea.getBarChartList()) {
for (CTBarSer ser : ctChart.getSerList()) {
updateStrRef(ser.getTx().getStrRef(), workbook);
updateStrRef(ser.getCat().getStrRef(), workbook);
updateNumRef(ser.getVal().getNumRef(), workbook);
updateExtLst(ser.getExtLst().getExtList(), workbook);
}
}
for (CTBar3DChart ctChart : plotArea.getBar3DChartList()) {
for (CTBarSer ser : ctChart.getSerList()) {
updateStrRef(ser.getTx().getStrRef(), workbook);
updateStrRef(ser.getCat().getStrRef(), workbook);
updateNumRef(ser.getVal().getNumRef(), workbook);
updateExtLst(ser.getExtLst().getExtList(), workbook);
}
}
for (CTOfPieChart ctChart : plotArea.getOfPieChartList()) {
for (CTPieSer ser : ctChart.getSerList()) {
updateStrRef(ser.getTx().getStrRef(), workbook);
updateStrRef(ser.getCat().getStrRef(), workbook);
updateNumRef(ser.getVal().getNumRef(), workbook);
updateExtLst(ser.getExtLst().getExtList(), workbook);
}
}
for (CTSurfaceChart ctChart : plotArea.getSurfaceChartList()) {
for (CTSurfaceSer ser : ctChart.getSerList()) {
updateStrRef(ser.getTx().getStrRef(), workbook);
updateStrRef(ser.getCat().getStrRef(), workbook);
updateNumRef(ser.getVal().getNumRef(), workbook);
updateExtLst(ser.getExtLst().getExtList(), workbook);
}
}
for (CTSurface3DChart ctChart : plotArea.getSurface3DChartList()) {
for (CTSurfaceSer ser : ctChart.getSerList()) {
updateStrRef(ser.getTx().getStrRef(), workbook);
updateStrRef(ser.getCat().getStrRef(), workbook);
updateNumRef(ser.getVal().getNumRef(), workbook);
updateExtLst(ser.getExtLst().getExtList(), workbook);
}
}
// todo 气泡图,格式和其他图表不太一样,需求中不需要使用,所以暂不处理了
// for (CTBubbleChart ctChart : plotArea.getBubbleChartList()) {
// for (CTBubbleSer ser : ctChart.getSerList()) {
// updateStrRef(ser.getTx().getStrRef(), workbook);
// updateStrRef(ser.getXVal().getStrRef(), workbook);
// updateNumRef(ser.getYVal().getNumRef(), workbook);
// updateNumRef(ser.getBubbleSize().getNumRef(), workbook);
// }
// }
}
/**
* 更新图表字符串元素缓存
*
* @param strRef 字符串元素缓存
* @param workbook 图表关联的excel
*/
private static void updateStrRef(CTStrRef strRef, XSSFWorkbook workbook) {
if (strRef == null) {
return;
}
CellReference[] cellReferences = new AreaReference(strRef.getF(), SpreadsheetVersion.EXCEL2007).getAllReferencedCells();
List<CTStrVal> ptList = strRef.getStrCache().getPtList();
for (int i = 0; i < ptList.size(); i++) {
XSSFCell cell = getCell(workbook, cellReferences[i]);
if (cell == null) {
log.warn("cell is null, cellReference: {}", cellReferences[i]);
continue;
}
CTStrVal pt = ptList.get(i);
if (CellType.NUMERIC.equals(cell.getCellType())) {
pt.setV(String.valueOf(cell.getNumericCellValue()));
} else if (CellType.STRING.equals(cell.getCellType())) {
pt.setV(cell.getStringCellValue());
} else {
pt.setV("");
}
}
}
/**
* 更新图表数字元素缓存
*
* @param numRef 数字元素缓存
* @param workbook 图表关联的excel
*/
private static void updateNumRef(CTNumRef numRef, XSSFWorkbook workbook) {
if (numRef == null) {
return;
}
CellReference[] cellReferences = new AreaReference(numRef.getF(), SpreadsheetVersion.EXCEL2007).getAllReferencedCells();
CTNumData numCache = numRef.getNumCache();
List<CTNumVal> ptList = numCache.getPtList();
for (int i = 0; i < ptList.size(); i++) {
XSSFCell cell = getCell(workbook, cellReferences[i]);
if (cell == null) {
log.warn("cell is null, cellReference: {}", cellReferences[i]);
continue;
}
CTNumVal pt = ptList.get(i);
if (CellType.NUMERIC.equals(cell.getCellType())) {
pt.setV(String.valueOf(cell.getNumericCellValue()));
if (cell.getCellStyle().getDataFormat() == PERCENT_FORMAT_CODE) {
numCache.setFormatCode(PERCENT_FORMAT_STR);
}
} else if (CellType.STRING.equals(cell.getCellType())) {
pt.setV(cell.getStringCellValue());
} else {
pt.setV("");
}
}
}
/**
* 更新图表附加元素缓存<br>
* 目前只支持选项:数据标签-单元格中的值
*
* @param extList 附加元素缓存
* @param workbook 图表关联的excel
*/
private static void updateExtLst(List<CTExtension> extList, XSSFWorkbook workbook) {
if (extList == null) {
return;
}
for (CTExtension ext : extList) {
TypeStoreUser datalabelsRange = ((CTExtensionImpl) ext).get_store().find_element_user(new QName("http://schemas.microsoft.com/office/drawing/2012/chart", "datalabelsRange"), 0);
if (datalabelsRange == null) {
return;
}
TypeStoreUser f = datalabelsRange.get_store().find_element_user(new QName("http://schemas.microsoft.com/office/drawing/2012/chart", "f"), 0);
CellReference[] cellReferences = new AreaReference(((SimpleValue) f).getStringValue(), SpreadsheetVersion.EXCEL2007).getAllReferencedCells();
ArrayList<TypeStoreUser> ptList = new ArrayList<>();
datalabelsRange.get_store().find_element_user(new QName("http://schemas.microsoft.com/office/drawing/2012/chart", "dlblRangeCache"), 0)
.get_store().find_all_element_users(new QName("http://schemas.openxmlformats.org/drawingml/2006/chart", "pt"), ptList);
for (int i = 0; i < ptList.size(); i++) {
XSSFCell cell = getCell(workbook, cellReferences[i]);
if (cell == null) {
log.warn("cell is null, cellReference: {}", cellReferences[i]);
continue;
}
TypeStoreUser v = ptList.get(i).get_store().find_element_user(new QName("http://schemas.openxmlformats.org/drawingml/2006/chart", "v"), 0);
if (CellType.NUMERIC.equals(cell.getCellType())) {
((SimpleValue) v).setStringValue(String.valueOf(cell.getNumericCellValue()));
} else if (CellType.STRING.equals(cell.getCellType())) {
((SimpleValue) v).setStringValue(cell.getStringCellValue());
} else {
((SimpleValue) v).setStringValue("");
}
}
}
}
/**
* 获取单元格
*
* @param workbook excel
* @param cellReference 单元格位置信息
* @return
*/
private static XSSFCell getCell(XSSFWorkbook workbook, CellReference cellReference) {
XSSFSheet sheet = workbook.getSheet(cellReference.getSheetName());
if (sheet == null) {
return null;
}
XSSFRow row = sheet.getRow(cellReference.getRow());
if (row == null) {
return null;
}
return row.getCell(cellReference.getCol());
}
}