POI 图表中加入文本框

小灰灰 2023-10-06 09:05 45阅读 0赞

### 一、思路 ###

poi最新版4.1.2中也没找到在图表中加文本框。

自己用Excel创建图表并在图表中加入文本框，重命名Excel文件为zip并解压，与没有加入文本框解压文件做对比：

1、观察到在 \[Content\_Types\].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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTQ2NDQ1NzQ_size_16_color_FFFFFF_t_70][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTQ2NDQ1NzQ_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200519004354335.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTQ2NDQ1NzQ=,size_16,color_FFFFFF,t_70