/// <summary>
/// Shift the Hyperlink anchors(not the hyperlink text, even if the hyperlink is of type LINK_DOCUMENT and refers to a cell that was shifted). Hyperlinks do not track the content they point to.
/// </summary>
/// <param name="sheet"></param>
/// <param name="shifter"></param>
public static void UpdateHyperlinks(ISheet sheet, FormulaShifter shifter)
{
XSSFSheet xsheet = (XSSFSheet)sheet;
int sheetIndex = xsheet.GetWorkbook().GetSheetIndex(sheet);
List <IHyperlink> hyperlinkList = sheet.GetHyperlinkList();
foreach (IHyperlink hyperlink1 in hyperlinkList)
{
XSSFHyperlink hyperlink = hyperlink1 as XSSFHyperlink;
String cellRef = hyperlink.CellRef;
CellRangeAddress cra = CellRangeAddress.ValueOf(cellRef);
CellRangeAddress shiftedRange = BaseRowColShifter.ShiftRange(shifter, cra, sheetIndex);
if (shiftedRange != null && shiftedRange != cra)
{
// shiftedRange should not be null. If shiftedRange is null, that means
// that a hyperlink wasn't deleted at the beginning of shiftRows when
// identifying rows that should be removed because they will be overwritten
hyperlink.SetCellReference(shiftedRange.FormatAsString());
}
}
}
public void TestCopyHyperlink()
{
IWorkbook wb = _testDataProvider.CreateWorkbook();
ICreationHelper createHelper = wb.GetCreationHelper();
ISheet sheet = wb.CreateSheet("Hyperlinks");
IRow row = sheet.CreateRow(0);
ICell cell1, cell2;
IHyperlink link1, link2;
//URL
cell1 = row.CreateCell(0);
cell2 = row.CreateCell(1);
cell1.SetCellValue("URL Link");
link1 = createHelper.CreateHyperlink(HyperlinkType.Url);
link1.Address = ("http://poi.apache.org/");
cell1.Hyperlink = (link1);
link2 = CopyHyperlink(link1);
// Change address (type is not changeable)
link2.Address = ("http://apache.org/");
cell2.Hyperlink = (link2);
// Make sure hyperlinks were deep-copied, and modifying one does not modify the other.
Assert.AreNotSame(link1, link2);
Assert.AreNotEqual(link1, link2);
Assert.AreEqual("http://poi.apache.org/", link1.Address);
Assert.AreEqual("http://apache.org/", link2.Address);
Assert.AreEqual(link1, cell1.Hyperlink);
Assert.AreEqual(link2, cell2.Hyperlink);
// Make sure both hyperlinks were added to the sheet
List <IHyperlink> actualHyperlinks = sheet.GetHyperlinkList();
Assert.AreEqual(2, actualHyperlinks.Count);
Assert.AreEqual(link1, actualHyperlinks[0]);
Assert.AreEqual(link2, actualHyperlinks[1]);
wb.Close();
}
public void TestBug46742_52903_shiftHyperlinks()
{
IWorkbook wb = _testDataProvider.CreateWorkbook();
ISheet sheet = wb.CreateSheet("test");
IRow row = sheet.CreateRow(0);
// How to create hyperlinks
// https://poi.apache.org/spreadsheet/quick-guide.html#Hyperlinks
ICreationHelper helper = wb.GetCreationHelper();
ICellStyle hlinkStyle = wb.CreateCellStyle();
IFont hlinkFont = wb.CreateFont();
hlinkFont.Underline = FontUnderlineType.Single;
hlinkFont.Color = (IndexedColors.Blue.Index);
hlinkStyle.SetFont(hlinkFont);
// 3D relative document link
// CellAddress=A1, shifted to A4
ICell cell = row.CreateCell(0);
cell.CellStyle = (hlinkStyle);
CreateHyperlink(helper, cell, HyperlinkType.Document, "test!E1");
// URL
cell = row.CreateCell(1);
// CellAddress=B1, shifted to B4
cell.CellStyle = (hlinkStyle);
CreateHyperlink(helper, cell, HyperlinkType.Url, "http://poi.apache.org/");
// row0 will be shifted on top of row1, so this URL should be removed from the workbook
IRow overwrittenRow = sheet.CreateRow(3);
cell = overwrittenRow.CreateCell(2);
// CellAddress=C4, will be overwritten (deleted)
cell.CellStyle = (hlinkStyle);
CreateHyperlink(helper, cell, HyperlinkType.Email, "mailto:[email protected]");
// hyperlinks on this row are unaffected by the row shifting, so the hyperlinks should not move
IRow unaffectedRow = sheet.CreateRow(20);
cell = unaffectedRow.CreateCell(3);
// CellAddress=D21, will be unaffected
cell.CellStyle = (hlinkStyle);
CreateHyperlink(helper, cell, HyperlinkType.File, "54524.xlsx");
cell = wb.CreateSheet("other").CreateRow(0).CreateCell(0);
// CellAddress=Other!A1, will be unaffected
cell.CellStyle = (hlinkStyle);
CreateHyperlink(helper, cell, HyperlinkType.Url, "http://apache.org/");
int startRow = 0;
int endRow = 0;
int n = 3;
sheet.ShiftRows(startRow, endRow, n);
IWorkbook read = _testDataProvider.WriteOutAndReadBack(wb);
wb.Close();
ISheet sh = read.GetSheet("test");
IRow shiftedRow = sh.GetRow(3);
// document link anchored on a shifted cell should be moved
// Note that hyperlinks do not track what they point to, so this hyperlink should still refer to test!E1
VerifyHyperlink(shiftedRow.GetCell(0), HyperlinkType.Document, "test!E1");
// URL, EMAIL, and FILE links anchored on a shifted cell should be moved
VerifyHyperlink(shiftedRow.GetCell(1), HyperlinkType.Url, "http://poi.apache.org/");
// Make sure hyperlinks were moved and not copied
Assert.IsNull(sh.GetHyperlink(0, 0), "Document hyperlink should be moved, not copied");
Assert.IsNull(sh.GetHyperlink(0, 1), "URL hyperlink should be moved, not copied");
// Make sure hyperlink in overwritten row is deleted
//System.out.println(sh.getHyperlinkList());
Assert.AreEqual(3, sh.GetHyperlinkList().Count);
CellAddress unexpectedLinkAddress = new CellAddress("C4");
foreach (IHyperlink link in sh.GetHyperlinkList())
{
CellAddress linkAddress = new CellAddress(link.FirstRow, link.FirstColumn);
//System.out.println(linkAddress.formatAsString());
if (linkAddress.Equals(unexpectedLinkAddress))
{
Assert.Fail("Row 4, including the hyperlink at C4, should have " +
"been deleted when Row 1 was shifted on top of it.");
}
}
// Make sure unaffected rows are not shifted
ICell unaffectedCell = sh.GetRow(20).GetCell(3);
Assert.IsTrue(CellHasHyperlink(unaffectedCell));
VerifyHyperlink(unaffectedCell, HyperlinkType.File, "54524.xlsx");
// Make sure cells on other sheets are not affected
unaffectedCell = read.GetSheet("other").GetRow(0).GetCell(0);
Assert.IsTrue(CellHasHyperlink(unaffectedCell));
VerifyHyperlink(unaffectedCell, HyperlinkType.Url, "http://apache.org/");
read.Close();
}