Java 使用POI修改Excel中自定义图形(shape)的超链接 解决乱码问题

参考资料

1. https://www.it1352.com/219746.html
2. https://www.fixes.pub/program/672636.html

---

😵出现的问题
下图的Excel的模板文件,模板文件中图形所包含的uri,被POI处理之后会乱码

💪解决方法

import java.net.URI;
import java.net.URLDecoder;

import org.apache.poi.openxml4j.opc.PackagePart;
import org.apache.poi.openxml4j.opc.PackageRelationship;
import org.apache.poi.openxml4j.opc.PackageRelationshipCollection;
import org.apache.poi.openxml4j.opc.TargetMode;
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.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFRelation;
import org.apache.poi.xssf.usermodel.XSSFShape;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFSimpleShape;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.openxmlformats.schemas.drawingml.x2006.main.CTHyperlink;

String toPath = "模板所在的位置";
InputStream is = new FileInputStream(toPath);
Workbook xssfWorkbook = new XSSFWorkbook(is);

// 获取Excel模板中所有的sheet页的数量
int numberOfSheets = xssfWorkbook.getNumberOfSheets();
for (int i = 0; i < numberOfSheets; i++) {
    
    Sheet sheetObj = xssfWorkbook.getSheetAt(i);
    // 如果Excel的sheet页中包含公式的话,强制公式执行,此处代码非必须
    sheetObj.setForceFormulaRecalculation(true);
    
    // 获取sheet页中所有的自定义图形对象
    XSSFDrawing draw = ((XSSFSheet)sheetObj).getDrawingPatriarch();
    List<XSSFShape> shapes = draw.getShapes();
    
    // 编码之后的uri的超链接
    String newUri = "";
    for (int j = 0; j < shapes.size(); j++) {
         
        XSSFDrawing drawing = shapes.get(j).getDrawing();
        PackagePart packagePart = drawing.getPackagePart();
        PackageRelationshipCollection prc = packagePart.getRelationships();
        
        // 第一个sheet中有多个图形
        if (i == 0) {
            // 根据图形对象的id获取PackageRelationship,自定义图形的id以rId开头
            PackageRelationship relationshipByID = prc.getRelationshipByID("rId" + (j + 1));
            // 模板中图形的超链接如果含有中文汉字的话,被POI处理之后会乱码,我们使用URLDecoder.decode()转码为utf-8
            newUri = URLDecoder.decode(relationshipByID.getTargetURI().toString(), "utf-8");

            /*
                1. sheet页之间的超链接应该是下面的这种格式
                    /xl/drawings/drawing1.xml#【测试】test!A1
                2. 超链接中不能含有空格,否则会报下面的这种错误
                    org.apache.poi.openxml4j.exceptions.OpenXML4JRuntimeException: Fail to save: an error occurs while saving the package : null
            */ 
        } else {
            // 从图形对象中获取uri
            for (PackageRelationship pr : prc) {
                URI targetURI = pr.getTargetURI();
                if (!ObjectUtils.isEmpty(targetURI)) {
                    // 将包含汉字的乱码uri转换为带汉字的uri
                    newUri = URLDecoder.decode(targetURI.toString(), "utf-8");
                }
            }
        }
        
        // 使用重新编码之后的uri构造一个新的URI对象
        URI uri = new URI(null, null, newUri, null, null);
        // 构造一个PackageRelationship对象
        PackageRelationship addRelationship = packagePart.addRelationship(uri, TargetMode.INTERNAL,
                        XSSFRelation.SHEET_HYPERLINKS.getRelation());
        // 获取PackageRelationship的ID
        String id = addRelationship.getId();
        
        if (shapes.get(j) instanceof XSSFSimpleShape) {
            
            XSSFSimpleShape shape = ((XSSFSimpleShape) shapes.get(j));
            
            // 构建一个新的uri对象
            CTHyperlink hyperlink = CTHyperlink.Factory.newInstance();
            // 将uri对象和PackageRelationship的id关联
            hyperlink.setId(id);
            // 将我们新构建的uri放入原有的图形对象中,替换掉之前乱码的uri
            shape.getCTShape().getNvSpPr().getCNvPr().setHlinkClick(hyperlink);
        }
    }
}

// 将模板文件输出
OutputStream out = new FileOutputStream(toPath);
xssfWorkbook.write(out);