一、思路
poi最新版4.1.2中也没找到在图表中加文本框。
自己用Excel创建图表并在图表中加入文本框,重命名Excel文件为zip并解压,与没有加入文本框解压文件做对比:
1、观察到在 [Content_Types].xml 清单文件中多了:
<Override ContentType="application/vnd.openxmlformats-officedocument.drawingml.chartshapes+xml" PartName="/xl/drawings/drawing2.xml"/>
2、xl\drawings\文件加下多一个文件drawing2.xml (这里就是文本框了,后面会用到)
<c:userShapes
xmlns:c="http://schemas.openxmlformats.org/drawingml/2006/chart">
<cdr:relSizeAnchor
xmlns:cdr="http://schemas.openxmlformats.org/drawingml/2006/chartDrawing">
<cdr:from>
<cdr:x>0.05536</cdr:x>
<cdr:y>0.05176</cdr:y>
</cdr:from>
<cdr:to>
<cdr:x>0.29643</cdr:x>
<cdr:y>0.13458</cdr:y>
</cdr:to>
<cdr:sp macro="" textlink="">
<cdr:nvSpPr>
<cdr:cNvPr id="3" name="文本框 2"/>
<cdr:cNvSpPr txBox="1"/>
</cdr:nvSpPr>
<cdr:spPr>
<a:xfrm
xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main">
<a:off x="236220" y="190500"/>
<a:ext cx="1028700" cy="304800"/>
</a:xfrm>
<a:prstGeom
xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main" prst="rect">
<a:avLst/>
</a:prstGeom>
</cdr:spPr>
<cdr:txBody>
<a:bodyPr
xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main" vertOverflow="clip" wrap="square" rtlCol="0"/>
<a:lstStyle
xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main"/>
<a:p
xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main">
<a:r>
<a:rPr lang="zh-CN" altLang="en-US" sz="1100"/>
<a:t>测试:</a:t>
</a:r>
<a:r>
<a:rPr lang="en-US" altLang="zh-CN" sz="1100">
<a:solidFill>
<a:srgbClr val="FF0000"/>
</a:solidFill>
</a:rPr>
<a:t>100</a:t>
</a:r>
<a:endParaRPr lang="zh-CN" altLang="en-US" sz="1100">
<a:solidFill>
<a:srgbClr val="FF0000"/>
</a:solidFill>
</a:endParaRPr>
</a:p>
</cdr:txBody>
</cdr:sp>
</cdr:relSizeAnchor>
</c:userShapes>
3、在xl\charts\_rels下多了一个关系文件:chart1.xml.rels(映射表存放文件文本框文件的位置)
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<Relationships
xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
<Relationship Id="rId1" Target="../drawings/drawing2.xml" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/chartUserShapes"/>
</Relationships>
4、在xl\charts\chart1.xml文件多出一行 <c:userShapes r:id="rId1"/> (将文本框放在图表里面)
<?xml version="1.0" encoding="UTF-8"?>
<c:chartSpace
xmlns:c="http://schemas.openxmlformats.org/drawingml/2006/chart"
xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main"
xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"
xmlns:c16r2="http://schemas.microsoft.com/office/drawing/2015/06/chart">
<c:date1904 val="0"/>
<c:lang val="zh-CN"/>
<c:userShapes r:id="rId1"/> //多出该行
</c:chartSpace>
好了以上就是思路,要实现就慢慢熟悉api就可以了。
二、实现代码:
1、pom.xml
<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>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-scratchpad</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-collections4</artifactId>
<version>4.4</version>
</dependency>
<dependency>
<groupId>commons-codec</groupId>
<artifactId>commons-codec</artifactId>
<version>1.13</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-compress</artifactId>
<version>1.19</version>
</dependency>
<dependency>
<groupId>org.apache.xmlbeans</groupId>
<artifactId>xmlbeans</artifactId>
<version>3.1.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>ooxml-schemas</artifactId>
<version>1.4</version>
</dependency>
2、poi在图表中加入文本框
package com.poi.test;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import org.apache.poi.openxml4j.opc.PackagePart;
import org.apache.poi.openxml4j.opc.PackagePartName;
import org.apache.poi.openxml4j.opc.PackageRelationship;
import org.apache.poi.openxml4j.opc.PackagingURIHelper;
import org.apache.poi.openxml4j.opc.TargetMode;
import org.apache.poi.xssf.usermodel.XSSFChart;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTChartSpace;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTRelId;
public class TTest2 {
public static void main(String[] args) {
XSSFWorkbook wb = null;
FileInputStream fis = null;
try {
fis = new FileInputStream("D:/test516.xlsx");
wb = new XSSFWorkbook(fis);
XSSFSheet sheet = wb.getSheetAt(0);
XSSFDrawing drawing = sheet.getDrawingPatriarch();
XSSFChart chart = drawing.getCharts().get(0);
//创建关系
PackagePart packagePart = chart.getPackagePart();
///
String contentType = "application/vnd.openxmlformats-officedocument.drawingml.chartshapes+xml";
String relation = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/chartUserShapes";
String fileName = "/xl/drawings/drawing-textbook.xml";
PackagePartName ppName = PackagingURIHelper.createPartName(fileName);
PackagePart createPart = packagePart.getPackage().createPart(ppName, contentType);
PackageRelationship rel = packagePart.addRelationship(ppName, TargetMode.INTERNAL, relation);
String id = rel.getId();
///
//写入数据
String textbook_xml = Test.getStringDatas(null);//这里是文本框xml内容
OutputStream outputStream = createPart.getOutputStream();
outputStream.write(textbook_xml.getBytes());
outputStream.close();
//在图表中加入文本框
CTChartSpace ctChartSpace = chart.getCTChartSpace();
CTRelId addNewUserShapes = ctChartSpace.addNewUserShapes();
addNewUserShapes.setId(id);
System.out.println();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
FileOutputStream fos = new FileOutputStream("D:/test518.xlsx");
wb.write(fos);
fos.close();
fis.close();
wb.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
3、文本框内容示例textbook_xml
<c:userShapes
xmlns:c="http://schemas.openxmlformats.org/drawingml/2006/chart">
<cdr:relSizeAnchor
xmlns:cdr="http://schemas.openxmlformats.org/drawingml/2006/chartDrawing">
<cdr:from>
<cdr:x>0.05536</cdr:x>
<cdr:y>0.05176</cdr:y>
</cdr:from>
<cdr:to>
<cdr:x>0.29643</cdr:x>
<cdr:y>0.13458</cdr:y>
</cdr:to>
<cdr:sp macro="" textlink="">
<cdr:nvSpPr>
<cdr:cNvPr id="3" name="文本框 2"/>
<cdr:cNvSpPr txBox="1"/>
</cdr:nvSpPr>
<cdr:spPr>
<a:xfrm
xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main">
<a:off x="236220" y="190500"/>
<a:ext cx="1028700" cy="304800"/>
</a:xfrm>
<a:prstGeom
xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main" prst="rect">
<a:avLst/>
</a:prstGeom>
</cdr:spPr>
<cdr:txBody>
<a:bodyPr
xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main" vertOverflow="clip" wrap="square" rtlCol="0"/>
<a:lstStyle
xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main"/>
<a:p
xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main">
<a:r>
<a:rPr lang="zh-CN" altLang="en-US" sz="1100"/>
<a:t>测试:</a:t>
</a:r>
<a:r>
<a:rPr lang="en-US" altLang="zh-CN" sz="1100">
<a:solidFill>
<a:srgbClr val="FF0000"/>
</a:solidFill>
</a:rPr>
<a:t>100</a:t>
</a:r>
<a:endParaRPr lang="zh-CN" altLang="en-US" sz="1100">
<a:solidFill>
<a:srgbClr val="FF0000"/>
</a:solidFill>
</a:endParaRPr>
</a:p>
</cdr:txBody>
</cdr:sp>
</cdr:relSizeAnchor>
</c:userShapes>