Bug 63189 - Error when cloning sheet with a hyperlink
Summary: Error when cloning sheet with a hyperlink
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 4.0.0-FINAL
Hardware: PC All
: P2 major (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2019-02-19 14:00 UTC by Darya
Modified: 2024-05-17 11:09 UTC (History)
1 user (show)

Error that excel shows when the documents is opened (10.90 KB, image/png)
2019-02-19 14:00 UTC, Darya
Corrupted document (11.02 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2019-02-19 14:04 UTC, Darya

Note You need to log in before you can comment on or make changes to this bug.
Description Darya 2019-02-19 14:00:21 UTC
Created attachment 36443 [details]
Error that excel shows when the documents is opened

I have .xlsx document with some sheets. And I need to clone one sheet.
The problem is that this sheet contains image with hyperlink to another sheet on it. And after cloning the sheet the document is corrupted and shows attached message when the document is opened.
Without this hyperlink sheet is cloned correctly.
Comment 1 Darya 2019-02-19 14:04:26 UTC
Created attachment 36444 [details]
Corrupted document
Comment 2 Kwon Ohyoung 2024-04-08 03:31:59 UTC
This issue is due to hyperlinks connected to picture objects not being properly handled.

The picture object and the hyperlink object are stored in drawing1.xml, and the picture file location to be displayed and hyperlink target information are stored in drawing1.xml.rels.

γ…†he correlation information stored in drawing1.xml.rels is processed based on the existence of the actual target file when copying from poi. 

However, the hyperlink target information does not have actual file information, so it is copied with the wrong target when copying.

original relationship in /xl/drawings/_rels/drawing1.xml.rels:
<Relationship Id="rId1" Target="#Sheet3!A1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/hyperlink"/>

copied relationship in /xl/drawings/_rels/drawing2.xml.rels:
<Relationship Id="rId1" Target="drawing1.xml" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/hyperlink"/>

When the PackageRelationship.getTargetURI() method returns, the URI is normalized to change it to the target file path. In this process, the fragment information (#Sheet3!A1) is lost and the file name (/xl/drawing1.xml) will remain.

However, /xl/drawing1.xml is recognized as an invalid file because it cannot be a hyperlink target.
Comment 3 Kwon Ohyoung 2024-04-08 03:41:44 UTC
To fundamentally solve this problem, I think we need to distinguish between internal file-based relationships and reference-based relationships like open-xml-sdk.

feat: https://github.com/dotnet/Open-XML-SDK/blob/main/src/DocumentFormat.OpenXml.Framework/Packaging/PartRelationshipsFeature.cs

If we do that, I think it will be a bigger task than a minor version up rather than just a single bug patch.

If it were a simple patch at the single commit level, I thought about contributing, but it seems to be at the level of feature addition/change beyond multiple commits, so I think it is something that needs to be reviewed within the poi community.
Comment 4 Kwon Ohyoung 2024-04-08 07:18:28 UTC
I have published github PR : https://github.com/apache/poi/pull/617
Comment 5 PJ Fanning 2024-05-17 11:09:55 UTC
PR617 was merged