You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@poi.apache.org by on...@apache.org on 2015/10/29 07:53:56 UTC
svn commit: r1711185 - in /poi/trunk/src/ooxml:
java/org/apache/poi/xssf/usermodel/
java/org/apache/poi/xssf/usermodel/helpers/
testcases/org/apache/poi/xssf/usermodel/
Author: onealj
Date: Thu Oct 29 06:53:55 2015
New Revision: 1711185
URL: http://svn.apache.org/viewvc?rev=1711185&view=rev
Log:
bug 58557: fix from Alessandro Guarascio, shift hyperlinks when shifting rows on an XSSFSheet
Modified:
poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFHyperlink.java
poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java
poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFRowShifter.java
poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFSheetShiftRows.java
Modified: poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFHyperlink.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFHyperlink.java?rev=1711185&r1=1711184&r2=1711185&view=diff
==============================================================================
--- poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFHyperlink.java (original)
+++ poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFHyperlink.java Thu Oct 29 06:53:55 2015
@@ -23,6 +23,7 @@ import org.apache.poi.openxml4j.opc.Pack
import org.apache.poi.openxml4j.opc.PackageRelationship;
import org.apache.poi.ss.usermodel.Hyperlink;
import org.apache.poi.ss.util.CellReference;
+import org.apache.poi.util.Internal;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTHyperlink;
/**
@@ -33,8 +34,8 @@ import org.openxmlformats.schemas.spread
public class XSSFHyperlink implements Hyperlink {
private int _type;
private PackageRelationship _externalRel;
- private CTHyperlink _ctHyperlink;
- private String _location;
+ private CTHyperlink _ctHyperlink; //contains a reference to the cell where the hyperlink is anchored, getRef()
+ private String _location; //what the hyperlink refers to
/**
* Create a new XSSFHyperlink. This method is protected to be used only by XSSFCreationHelper
@@ -94,6 +95,7 @@ public class XSSFHyperlink implements Hy
/**
* @return the underlying CTHyperlink object
*/
+ @Internal
public CTHyperlink getCTHyperlink() {
return _ctHyperlink;
}
@@ -219,7 +221,8 @@ public class XSSFHyperlink implements Hy
/**
* Assigns this hyperlink to the given cell reference
*/
- protected void setCellReference(String ref) {
+ @Internal
+ public void setCellReference(String ref) {
_ctHyperlink.setRef(ref);
}
protected void setCellReference(CellReference ref) {
Modified: poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java?rev=1711185&r1=1711184&r2=1711185&view=diff
==============================================================================
--- poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java (original)
+++ poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java Thu Oct 29 06:53:55 2015
@@ -25,6 +25,7 @@ import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Arrays;
+import java.util.Collections;
import java.util.Comparator;
import java.util.HashMap;
import java.util.Iterator;
@@ -669,6 +670,13 @@ public class XSSFSheet extends POIXMLDoc
vml == null ? null : vml.findCommentShape(row, column));
}
+ /**
+ * Get a Hyperlink in this sheet anchored at row, column
+ *
+ * @param row
+ * @param column
+ * @return hyperlink if there is a hyperlink anchored at row, column; otherwise returns null
+ */
public XSSFHyperlink getHyperlink(int row, int column) {
String ref = new CellReference(row, column).formatAsString();
for(XSSFHyperlink hyperlink : hyperlinks) {
@@ -678,6 +686,15 @@ public class XSSFSheet extends POIXMLDoc
}
return null;
}
+
+ /**
+ * Get a list of Hyperlinks in this sheet
+ *
+ * @return
+ */
+ public List<XSSFHyperlink> getHyperlinkList() {
+ return Collections.unmodifiableList(hyperlinks);
+ }
@SuppressWarnings("deprecation")
private int[] getBreaks(CTPageBreak ctPageBreak) {
@@ -2610,6 +2627,7 @@ public class XSSFSheet extends POIXMLDoc
// remove row from _rows
it.remove();
+ // FIXME: (performance optimization) this should be moved outside the for-loop so that comments only needs to be iterated over once.
// also remove any comments associated with this row
if(sheetComments != null){
CTCommentList lst = sheetComments.getCTComments().getCommentList();
@@ -2624,6 +2642,16 @@ public class XSSFSheet extends POIXMLDoc
}
}
}
+ // FIXME: (performance optimization) this should be moved outside the for-loop so that hyperlinks only needs to be iterated over once.
+ // also remove any hyperlinks associated with this row
+ if (hyperlinks != null) {
+ for (XSSFHyperlink link : new ArrayList<XSSFHyperlink>(hyperlinks)) {
+ CellReference ref = new CellReference(link.getCellRef());
+ if (ref.getRow() == rownum) {
+ hyperlinks.remove(link);
+ }
+ }
+ }
}
}
@@ -2707,6 +2735,7 @@ public class XSSFSheet extends POIXMLDoc
rowShifter.updateFormulas(shifter);
rowShifter.shiftMerged(startRow, endRow, n);
rowShifter.updateConditionalFormatting(shifter);
+ rowShifter.updateHyperlinks(shifter);
//rebuild the _rows map
SortedMap<Integer, XSSFRow> map = new TreeMap<Integer, XSSFRow>();
@@ -2902,6 +2931,9 @@ public class XSSFSheet extends POIXMLDoc
*/
@Internal
public void removeHyperlink(int row, int column) {
+ // CTHyperlinks is regenerated from scratch when writing out the spreadsheet
+ // so don't worry about maintaining hyperlinks and CTHyperlinks in parallel.
+ // only maintain hyperlinks
String ref = new CellReference(row, column).formatAsString();
for (Iterator<XSSFHyperlink> it = hyperlinks.iterator(); it.hasNext();) {
XSSFHyperlink hyperlink = it.next();
Modified: poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFRowShifter.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFRowShifter.java?rev=1711185&r1=1711184&r2=1711185&view=diff
==============================================================================
--- poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFRowShifter.java (original)
+++ poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFRowShifter.java Thu Oct 29 06:53:55 2015
@@ -22,6 +22,7 @@ import java.util.HashSet;
import java.util.List;
import java.util.Set;
+import org.apache.poi.common.usermodel.Hyperlink;
import org.apache.poi.ss.formula.FormulaParseException;
import org.apache.poi.ss.formula.FormulaParser;
import org.apache.poi.ss.formula.FormulaRenderer;
@@ -38,6 +39,7 @@ import org.apache.poi.util.POILogFactory
import org.apache.poi.util.POILogger;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFEvaluationWorkbook;
+import org.apache.poi.xssf.usermodel.XSSFHyperlink;
import org.apache.poi.xssf.usermodel.XSSFName;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
@@ -280,6 +282,30 @@ public final class XSSFRowShifter {
}
}
}
+
+ /**
+ * 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.
+ *
+ * @param shifter
+ */
+ public void updateHyperlinks(FormulaShifter shifter) {
+ int sheetIndex = sheet.getWorkbook().getSheetIndex(sheet);
+ List<XSSFHyperlink> hyperlinkList = sheet.getHyperlinkList();
+
+ for (XSSFHyperlink hyperlink : hyperlinkList) {
+ String cellRef = hyperlink.getCellRef();
+ CellRangeAddress cra = CellRangeAddress.valueOf(cellRef);
+ CellRangeAddress shiftedRange = 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());
+ }
+ }
+ }
private static CellRangeAddress shiftRange(FormulaShifter shifter, CellRangeAddress cra, int currentExternSheetIx) {
// FormulaShifter works well in terms of Ptgs - so convert CellRangeAddress to AreaPtg (and back) here
Modified: poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFSheetShiftRows.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFSheetShiftRows.java?rev=1711185&r1=1711184&r2=1711185&view=diff
==============================================================================
--- poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFSheetShiftRows.java (original)
+++ poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFSheetShiftRows.java Thu Oct 29 06:53:55 2015
@@ -21,7 +21,12 @@ import java.io.IOException;
import org.apache.poi.ss.usermodel.BaseTestSheetShiftRows;
import org.apache.poi.ss.usermodel.Cell;
+import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Comment;
+import org.apache.poi.ss.usermodel.CreationHelper;
+import org.apache.poi.ss.usermodel.Font;
+import org.apache.poi.ss.usermodel.Hyperlink;
+import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
@@ -366,4 +371,105 @@ public final class TestXSSFSheetShiftRow
wb.close();
}
+
+ public void testBug46742_shiftHyperlinks() throws IOException {
+ XSSFWorkbook wb = new XSSFWorkbook();
+ Sheet sheet = wb.createSheet("test");
+ Row row = sheet.createRow(0);
+
+ // How to create hyperlinks
+ // https://poi.apache.org/spreadsheet/quick-guide.html#Hyperlinks
+ XSSFCreationHelper helper = wb.getCreationHelper();
+ CellStyle hlinkStyle = wb.createCellStyle();
+ Font hlinkFont = wb.createFont();
+ hlinkFont.setUnderline(Font.U_SINGLE);
+ hlinkFont.setColor(IndexedColors.BLUE.getIndex());
+ hlinkStyle.setFont(hlinkFont);
+
+ // 3D relative document link
+ Cell cell = row.createCell(0);
+ cell.setCellStyle(hlinkStyle);
+ createHyperlink(helper, cell, Hyperlink.LINK_DOCUMENT, "test!E1");
+
+ // URL
+ cell = row.createCell(1);
+ cell.setCellStyle(hlinkStyle);
+ createHyperlink(helper, cell, Hyperlink.LINK_URL, "http://poi.apache.org/");
+
+ // row0 will be shifted on top of row1, so this URL should be removed from the workbook
+ Row overwrittenRow = sheet.createRow(3);
+ cell = overwrittenRow.createCell(2);
+ cell.setCellStyle(hlinkStyle);
+ createHyperlink(helper, cell, Hyperlink.LINK_EMAIL, "mailto:poi@apache.org");
+
+ // hyperlinks on this row are unaffected by the row shifting, so the hyperlinks should not move
+ Row unaffectedRow = sheet.createRow(20);
+ cell = unaffectedRow.createCell(3);
+ cell.setCellStyle(hlinkStyle);
+ createHyperlink(helper, cell, Hyperlink.LINK_FILE, "54524.xlsx");
+
+ cell = wb.createSheet("other").createRow(0).createCell(0);
+ cell.setCellStyle(hlinkStyle);
+ createHyperlink(helper, cell, Hyperlink.LINK_URL, "http://apache.org/");
+
+ int startRow = 0;
+ int endRow = 0;
+ int n = 3;
+ sheet.shiftRows(startRow, endRow, n);
+
+ XSSFWorkbook read = XSSFTestDataSamples.writeOutAndReadBack(wb);
+ wb.close();
+
+ XSSFSheet sh = read.getSheet("test");
+
+ Row 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), Hyperlink.LINK_DOCUMENT, "test!E1");
+
+ // URL, EMAIL, and FILE links anchored on a shifted cell should be moved
+ verifyHyperlink(shiftedRow.getCell(1), Hyperlink.LINK_URL, "http://poi.apache.org/");
+
+ // Make sure hyperlinks were moved and not copied
+ assertNull(sh.getRow(0));
+
+ // Make sure hyperlink in overwritten row is deleted
+ assertEquals(3, sh.getHyperlinkList().size());
+ for (XSSFHyperlink link : sh.getHyperlinkList()) {
+ if ("C4".equals(link.getCellRef())) {
+ 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
+ Cell unaffectedCell = sh.getRow(20).getCell(3);
+ assertTrue(cellHasHyperlink(unaffectedCell));
+ verifyHyperlink(unaffectedCell, Hyperlink.LINK_FILE, "54524.xlsx");
+
+ // Make sure cells on other sheets are not affected
+ unaffectedCell = read.getSheet("other").getRow(0).getCell(0);
+ assertTrue(cellHasHyperlink(unaffectedCell));
+ verifyHyperlink(unaffectedCell, Hyperlink.LINK_URL, "http://apache.org/");
+
+ read.close();
+ }
+
+ private void createHyperlink(CreationHelper helper, Cell cell, int linkType, String ref) {
+ cell.setCellValue(ref);
+ Hyperlink link = helper.createHyperlink(linkType);
+ link.setAddress(ref);
+ cell.setHyperlink(link);
+ }
+
+ private void verifyHyperlink(Cell cell, int linkType, String ref) {
+ assertTrue(cellHasHyperlink(cell));
+ Hyperlink link = cell.getHyperlink();
+ assertEquals(linkType, link.getType());
+ assertEquals(ref, link.getAddress());
+ }
+
+ private boolean cellHasHyperlink(Cell cell) {
+ return (cell != null) && (cell.getHyperlink() != null);
+ }
}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org