Beispiel #1
0
        /// <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());
                }
            }
        }
Beispiel #2
0
        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();
        }
Beispiel #3
0
        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();
        }