Excel 超链接 URI 片段未编码 [英] Excel Hyperlinks URI fragment not encoding
问题描述
我有一个类似的字符串
String uriString = "#'Tab1 Tab2'!A8";
URI uri = new URI(uriString);
这失败了，因为它不是一个格式正确的 URI(有一个空格).所以我改为
This fails as it is not a well formed URI (there is a space). So i changed to
String uriString = "#'Tab1%20Tab2'!A8";
URI uri = new URI(uriString);
这是正确编码但片段变成了'Tab1%20Tab2'!A8"
This is encoded correctly but the fragment becomes "'Tab1%20Tab2'!A8"
然后在 excel 中，链接(附加到图像)指向不存在的Tab1%20Tab2'!A8".
Then in the excel the link (attached to an image) points to "Tab1%20Tab2'!A8" which does not exists.
我试图将构造函数更改为
I tried to change constructor to
URI uri = new URI(null,null,uriString,null,null)
结果现在在 excel 上的链接是%23'Tab1 Tab2'!A8"，它现在不起作用，因为它现在是已转换的主题标签.这让我发疯.
As a result now on the excel the link is "%23'Tab1 Tab2'!A8" which is now not working because it is the hashtag now that has been converted. This is driving me crazy.
这是构建URI和设置链接的代码
Here is the code that builds the URI and sets the link
String hyperlinkAddress = "#'"+destinationTab + "'!" + destinationCell + "";
hyperlinkAddress = hyperlinkAddress.replaceAll(" ", "%20");
URI hyperLinkUri;
try {
hyperLinkUri = new URI(hyperlinkAddress);
PackageRelationship packagerelationship = drawing.getPackagePart().addRelationship(hyperLinkUri,TargetMode.EXTERNAL, PackageRelationshipTypes.HYPERLINK_PART);
String rid = packagerelationship.getId();
if (cthyperlink == null) cthyperlink = ctnonvisualdrawingprops.addNewHlinkClick();
cthyperlink.setId(rid);
} catch (URISyntaxException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
我怎样才能在超链接中实现空格只是一个空格并且 # 没有转换为 %23?
How can i achieve that in the hyperlink the space is just a space and the # is not converted to %23?
更新
显然在 Office 365 中它显示 %23 而在 office 2013 中则显示正确的名称文件
Appearently in Office 365 it displays %23 while in office 2013 the correct name file instead
更新这是导出excel时超链接之一在绘图xml中的显示方式.
UPDATE This is how one of the hyperlink shows in the drawing xml while export the excel.
<Relationship Id="rId28" Target="%23Coverage%20Summary%20(Pool)!A8" TargetMode="External" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/hyperlink"/>
但是，当我将链接悬停在 Excel 中时，链接显示为
However, when I hover the link in Excel the link shows as
file:///C:\Users\myname\download\%23Coverage Summary (Pool)!A8
因此，正确替换了空格而不是 # URI 片段.在 Office 2013 中，相同的文件，%23 被替换为正确打开超链接的文件名
So, spaces where correctly replaced but not the # URI fragment. In office 2013, same file, the %23 is replaced with the filename correctly opening the hyperlink
推荐答案
您似乎想要创建一个 HyperlinkType.DOCUMENT 链接到工作表Tab1 Tab2"中的单元格A8.
You seems want creating a hyperlink of HyperlinkType.DOCUMENT which links to cell A8 in sheet "Tab1 Tab2".
https://svn.apache.org/repos/asf/poi/tags/REL_3_17_FINAL/src/examples/src/org/apache/poi/xssf/usermodel/examples/ 用于 apache poi 3.17，https://svn.apache.org/repos/asf/poi/tags/REL_4_1_2/src/examples/src/org/apache/poi/xssf/usermodel/examples/ 用于 apache poi 4.1.2 和 https://svn.apache.org/repos/asf/poi/tags/REL_5_0_0/src/examples/src/org/apache/poi/examples/xssf/usermodel/ 用于 apache poi 5.0.0.
There is an example for how to create different types of hyperlinks in https://svn.apache.org/repos/asf/poi/tags/REL_3_17_FINAL/src/examples/src/org/apache/poi/xssf/usermodel/examples/ for apache poi 3.17, https://svn.apache.org/repos/asf/poi/tags/REL_4_1_2/src/examples/src/org/apache/poi/xssf/usermodel/examples/ for apache poi 4.1.2 and https://svn.apache.org/repos/asf/poi/tags/REL_5_0_0/src/examples/src/org/apache/poi/examples/xssf/usermodel/ for apache poi 5.0.0.
以下是此示例的摘录，仅显示了您的用例的完整代码示例:
Here is an excerpt of this showing complete code example for your use case only:
import java.io.FileOutputStream;
import org.apache.poi.common.usermodel.HyperlinkType;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
//See https://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/xssf/usermodel/examples/HyperlinkExample.java
public class HyperlinkTypeDocumentExample {
public static void main(String[]args) throws Exception {
try (Workbook wb = new XSSFWorkbook()) { //or new HSSFWorkbook();
CreationHelper createHelper = wb.getCreationHelper();
//cell style for hyperlinks
//by default hyperlinks are blue and underlined
CellStyle hlink_style = wb.createCellStyle();
Font hlink_font = wb.createFont();
hlink_font.setUnderline(Font.U_SINGLE);
hlink_font.setColor(IndexedColors.BLUE.getIndex());
hlink_style.setFont(hlink_font);
Cell cell;
Sheet sheet = wb.createSheet("Hyperlinks");
//URL
cell = sheet.createRow(0).createCell(0);
cell.setCellValue("URL Link");
//link to a place in this workbook
//create a target sheet and cell
Sheet sheet2 = wb.createSheet("Tab1 Tab2");
sheet2.createRow(7).createCell(0).setCellValue("Target Cell");
cell = sheet.createRow(1).createCell(0);
cell.setCellValue("Worksheet Link");
Hyperlink link = createHelper.createHyperlink(HyperlinkType.DOCUMENT);
link.setAddress("'" + sheet2.getSheetName() + "'!A8");
cell.setHyperlink(link);
cell.setCellStyle(hlink_style);
try (FileOutputStream out = new FileOutputStream("Hyperinks.xlsx")) {
wb.write(out);
}
}
}
}
来自问题作者的评论，即超链接应位于图像上.但是，如果工作表名称不包含任何空格，则只能将指向同一工作簿中另一工作表的超链接插入到图像中.此处显示:Apache poi 将超链接放入图像.
对于图片(图像)上的链接，目标必须是 URI，而 URI 必须是 URI 编码的.因此，如果工作表名称包含空格，则需要将其编码为 %20.所以#'Tab1 Tab2'!A8 需要是#'Tab1%20Tab2'!A8.但是 Excel 本身违反了这些规则，因为它期望目标是 #'Tab1 Tab2'!A8.我看不到任何使用 apache poi 生成错误 URI 的方法.
For links being on pictures (images) the target needs to be a URI and a URI must be URI-encoded. So if the sheet name contains spaces, then those needs to be encoded as %20. So #'Tab1 Tab2'!A8 needs to be #'Tab1%20Tab2'!A8. But Excel itself violates those rules as it expects the target to be #'Tab1 Tab2'!A8. I cannot see any way to produce that wrong URI using apache poi.
这篇关于Excel 超链接 URI 片段未编码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
