You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@poi.apache.org by ab...@apache.org on 2018/10/01 19:27:32 UTC
svn commit: r1842548 - in /poi/trunk:
src/ooxml/java/org/apache/poi/xddf/usermodel/chart/
src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/
src/ooxml/testcases/org/apache/poi/xssf/usermodel/ test-data/spreadsheet/
Author: abearez
Date: Mon Oct 1 19:27:32 2018
New Revision: 1842548
URL: http://svn.apache.org/viewvc?rev=1842548&view=rev
Log:
extract valuable code from 54470 submitted patch
Added:
poi/trunk/test-data/spreadsheet/60509.xlsx (with props)
Modified:
poi/trunk/src/ooxml/java/org/apache/poi/xddf/usermodel/chart/XDDFCategoryDataSource.java
poi/trunk/src/ooxml/java/org/apache/poi/xddf/usermodel/chart/XDDFDataSource.java
poi/trunk/src/ooxml/java/org/apache/poi/xddf/usermodel/chart/XDDFDataSourcesFactory.java
poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFFormulaUtils.java
poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFWorkbook.java
Modified: poi/trunk/src/ooxml/java/org/apache/poi/xddf/usermodel/chart/XDDFCategoryDataSource.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/java/org/apache/poi/xddf/usermodel/chart/XDDFCategoryDataSource.java?rev=1842548&r1=1842547&r2=1842548&view=diff
==============================================================================
--- poi/trunk/src/ooxml/java/org/apache/poi/xddf/usermodel/chart/XDDFCategoryDataSource.java (original)
+++ poi/trunk/src/ooxml/java/org/apache/poi/xddf/usermodel/chart/XDDFCategoryDataSource.java Mon Oct 1 19:27:32 2018
@@ -23,4 +23,23 @@ import org.apache.poi.util.Beta;
@Beta
public interface XDDFCategoryDataSource extends XDDFDataSource<String> {
+ @Override
+ default int getColIndex() {
+ return 0;
+ }
+
+ @Override
+ default boolean isNumeric() {
+ return false;
+ }
+
+ @Override
+ default boolean isReference() {
+ return true;
+ }
+
+ @Override
+ default String getDataRangeReference() {
+ return getFormula();
+ }
}
Modified: poi/trunk/src/ooxml/java/org/apache/poi/xddf/usermodel/chart/XDDFDataSource.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/java/org/apache/poi/xddf/usermodel/chart/XDDFDataSource.java?rev=1842548&r1=1842547&r2=1842548&view=diff
==============================================================================
--- poi/trunk/src/ooxml/java/org/apache/poi/xddf/usermodel/chart/XDDFDataSource.java (original)
+++ poi/trunk/src/ooxml/java/org/apache/poi/xddf/usermodel/chart/XDDFDataSource.java Mon Oct 1 19:27:32 2018
@@ -34,4 +34,6 @@ public interface XDDFDataSource<T> {
int getColIndex();
String getDataRangeReference();
+
+ String getFormula();
}
Modified: poi/trunk/src/ooxml/java/org/apache/poi/xddf/usermodel/chart/XDDFDataSourcesFactory.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/java/org/apache/poi/xddf/usermodel/chart/XDDFDataSourcesFactory.java?rev=1842548&r1=1842547&r2=1842548&view=diff
==============================================================================
--- poi/trunk/src/ooxml/java/org/apache/poi/xddf/usermodel/chart/XDDFDataSourcesFactory.java (original)
+++ poi/trunk/src/ooxml/java/org/apache/poi/xddf/usermodel/chart/XDDFDataSourcesFactory.java Mon Oct 1 19:27:32 2018
@@ -41,39 +41,50 @@ public class XDDFDataSourcesFactory {
}
public static XDDFCategoryDataSource fromDataSource(final CTAxDataSource categoryDS) {
- return new XDDFCategoryDataSource() {
- private CTStrData category = (CTStrData) categoryDS.getStrRef().getStrCache().copy();
-
- @Override
- public boolean isNumeric() {
- return false;
- }
-
- @Override
- public boolean isReference() {
- return true;
- }
-
- @Override
- public int getPointCount() {
- return (int) category.getPtCount().getVal();
- }
-
- @Override
- public String getPointAt(int index) {
- return category.getPtArray(index).getV();
- }
-
- @Override
- public String getDataRangeReference() {
- return categoryDS.getStrRef().getF();
- }
-
- @Override
- public int getColIndex() {
- return 0;
- }
- };
+ if (categoryDS.getStrRef() == null) {
+ return new XDDFCategoryDataSource() {
+ private CTNumData category = (CTNumData) categoryDS.getNumRef().getNumCache().copy();
+
+ @Override
+ public boolean isNumeric() {
+ return true;
+ }
+
+ @Override
+ public String getFormula() {
+ return categoryDS.getNumRef().getF();
+ }
+
+ @Override
+ public int getPointCount() {
+ return (int) category.getPtCount().getVal();
+ }
+
+ @Override
+ public String getPointAt(int index) {
+ return category.getPtArray(index).getV();
+ }
+ };
+ } else {
+ return new XDDFCategoryDataSource() {
+ private CTStrData category = (CTStrData) categoryDS.getStrRef().getStrCache().copy();
+
+ @Override
+ public String getFormula() {
+ return categoryDS.getStrRef().getF();
+ }
+
+ @Override
+ public int getPointCount() {
+ return (int) category.getPtCount().getVal();
+ }
+
+ @Override
+ public String getPointAt(int index) {
+ return category.getPtArray(index).getV();
+ }
+ };
+ }
}
public static XDDFNumericalDataSource<Double> fromDataSource(final CTNumDataSource valuesDS) {
@@ -82,6 +93,11 @@ public class XDDFDataSourcesFactory {
private String formatCode = values.isSetFormatCode() ? values.getFormatCode() : null;
@Override
+ public String getFormula() {
+ return valuesDS.getNumRef().getF();
+ }
+
+ @Override
public String getFormatCode() {
return formatCode;
}
@@ -124,7 +140,7 @@ public class XDDFDataSourcesFactory {
}
public static <T extends Number> XDDFNumericalDataSource<T> fromArray(T[] elements, String dataRange) {
- return new NumericalArrayDataSource<T>(elements, dataRange);
+ return new NumericalArrayDataSource<>(elements, dataRange);
}
public static XDDFCategoryDataSource fromArray(String[] elements, String dataRange) {
@@ -132,7 +148,7 @@ public class XDDFDataSourcesFactory {
}
public static <T extends Number> XDDFNumericalDataSource<T> fromArray(T[] elements, String dataRange, int col) {
- return new NumericalArrayDataSource<T>(elements, dataRange, col);
+ return new NumericalArrayDataSource<>(elements, dataRange, col);
}
public static XDDFCategoryDataSource fromArray(String[] elements, String dataRange, int col) {
@@ -213,6 +229,11 @@ public class XDDFDataSourcesFactory {
}
@Override
+ public String getFormula() {
+ return getDataRangeReference();
+ }
+
+ @Override
public String getFormatCode() {
return formatCode;
}
@@ -232,6 +253,11 @@ public class XDDFDataSourcesFactory {
public StringArrayDataSource(String[] elements, String dataRange, int col) {
super(elements, dataRange, col);
}
+
+ @Override
+ public String getFormula() {
+ return getDataRangeReference();
+ }
}
private abstract static class AbstractCellRangeDataSource<T> implements XDDFDataSource<T> {
@@ -290,6 +316,11 @@ public class XDDFDataSourcesFactory {
super(sheet, cellRangeAddress);
}
+ @Override
+ public String getFormula() {
+ return getDataRangeReference();
+ }
+
private String formatCode;
@Override
@@ -325,6 +356,11 @@ public class XDDFDataSourcesFactory {
}
@Override
+ public String getFormula() {
+ return getDataRangeReference();
+ }
+
+ @Override
public String getPointAt(int index) {
CellValue cellValue = getCellValueAt(index);
if (cellValue != null && cellValue.getCellType() == CellType.STRING) {
Modified: poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFFormulaUtils.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFFormulaUtils.java?rev=1842548&r1=1842547&r2=1842548&view=diff
==============================================================================
--- poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFFormulaUtils.java (original)
+++ poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFFormulaUtils.java Mon Oct 1 19:27:32 2018
@@ -19,6 +19,10 @@
package org.apache.poi.xssf.usermodel.helpers;
+import java.util.Iterator;
+import java.util.List;
+
+import org.apache.poi.ooxml.POIXMLDocumentPart;
import org.apache.poi.ss.formula.FormulaParser;
import org.apache.poi.ss.formula.FormulaRenderer;
import org.apache.poi.ss.formula.FormulaType;
@@ -30,10 +34,14 @@ import org.apache.poi.ss.usermodel.CellT
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFCell;
+import org.apache.poi.xssf.usermodel.XSSFChart;
+import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFEvaluationWorkbook;
import org.apache.poi.xssf.usermodel.XSSFName;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCellFormula;
+import org.w3c.dom.Node;
+import org.w3c.dom.NodeList;
/**
* Utility to update formulas and named ranges when a sheet name was changed
@@ -50,7 +58,7 @@ public final class XSSFFormulaUtils {
}
/**
- * Update sheet name in all formulas and named ranges.
+ * Update sheet name in all charts, formulas and named ranges.
* Called from {@link XSSFWorkbook#setSheetName(int, String)}
* <p>
* <p>
@@ -81,6 +89,20 @@ public final class XSSFFormulaUtils {
}
}
}
+
+ // update charts
+ List<POIXMLDocumentPart> rels = _wb.getSheetAt(sheetIndex).getRelations();
+ for (POIXMLDocumentPart r : rels) {
+ if (r instanceof XSSFDrawing) {
+ XSSFDrawing dg = (XSSFDrawing) r;
+ Iterator<XSSFChart> it = dg.getCharts().iterator();
+ while (it.hasNext()) {
+ XSSFChart chart = it.next();
+ Node dom = chart.getCTChartSpace().getDomNode();
+ updateDomSheetReference(dom, oldName, newName);
+ }
+ }
+ }
}
/**
@@ -99,7 +121,9 @@ public final class XSSFFormulaUtils {
updatePtg(ptg, oldName, newName);
}
String updatedFormula = FormulaRenderer.toFormulaString(_fpwb, ptgs);
- if (!formula.equals(updatedFormula)) f.setStringValue(updatedFormula);
+ if (!formula.equals(updatedFormula)) {
+ f.setStringValue(updatedFormula);
+ }
}
}
}
@@ -119,10 +143,12 @@ public final class XSSFFormulaUtils {
updatePtg(ptg, oldName, newName);
}
String updatedFormula = FormulaRenderer.toFormulaString(_fpwb, ptgs);
- if (!formula.equals(updatedFormula)) name.setRefersToFormula(updatedFormula);
+ if (!formula.equals(updatedFormula)) {
+ name.setRefersToFormula(updatedFormula);
+ }
}
}
-
+
private void updatePtg(Ptg ptg, String oldName, String newName) {
if (ptg instanceof Pxg) {
Pxg pxg = (Pxg)ptg;
@@ -141,4 +167,32 @@ public final class XSSFFormulaUtils {
}
}
}
+
+
+ /**
+ * Parse the DOM tree recursively searching for text containing reference to the old sheet name and replacing it.
+ *
+ * @param dom the XML node in which to perform the replacement.
+ *
+ * Code extracted from: <a href="https://bz.apache.org/bugzilla/show_bug.cgi?id=54470">Bug 54470</a>
+ */
+ private void updateDomSheetReference(Node dom, final String oldName, final String newName) {
+ String value = dom.getNodeValue();
+ if (value != null) {
+ // make sure the value contains the old sheet and not a similar sheet
+ // (ex: Valid: 'Sheet1'! or Sheet1! ; NotValid: 'Sheet1Test'! or Sheet1Test!)
+ if (value.contains(oldName+"!") || value.contains(oldName+"'!")) {
+ XSSFName temporary = _wb.createName();
+ temporary.setRefersToFormula(value);
+ updateName(temporary, oldName, newName);
+ dom.setNodeValue(temporary.getRefersToFormula());
+ _wb.removeName(temporary);
+ }
+ }
+ NodeList nl = dom.getChildNodes();
+ for (int i = 0; i < nl.getLength(); i++) {
+ updateDomSheetReference(nl.item(i), oldName, newName);
+ }
+ }
+
}
Modified: poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFWorkbook.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFWorkbook.java?rev=1842548&r1=1842547&r2=1842548&view=diff
==============================================================================
--- poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFWorkbook.java (original)
+++ poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFWorkbook.java Mon Oct 1 19:27:32 2018
@@ -39,8 +39,8 @@ import java.util.List;
import java.util.zip.CRC32;
import org.apache.poi.POIDataSamples;
-import org.apache.poi.ooxml.POIXMLProperties;
import org.apache.poi.hssf.HSSFTestDataSamples;
+import org.apache.poi.ooxml.POIXMLProperties;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.opc.ContentTypes;
import org.apache.poi.openxml4j.opc.OPCPackage;
@@ -67,6 +67,8 @@ import org.apache.poi.ss.util.CellRefere
import org.apache.poi.util.IOUtils;
import org.apache.poi.util.LocaleUtil;
import org.apache.poi.util.TempFile;
+import org.apache.poi.xddf.usermodel.chart.XDDFBarChartData;
+import org.apache.poi.xddf.usermodel.chart.XDDFChartData;
import org.apache.poi.xssf.XSSFITestDataProvider;
import org.apache.poi.xssf.XSSFTestDataSamples;
import org.apache.poi.xssf.model.StylesTable;
@@ -553,7 +555,9 @@ public final class TestXSSFWorkbook exte
Sheet sheet = wb.getSheetAt(0);
sheet.shiftRows(2, sheet.getLastRowNum(), 1, true, false);
Row newRow = sheet.getRow(2);
- if (newRow == null) newRow = sheet.createRow(2);
+ if (newRow == null) {
+ newRow = sheet.createRow(2);
+ }
newRow.createCell(0).setCellValue(" Another Header");
wb.cloneSheet(0);
@@ -667,8 +671,8 @@ public final class TestXSSFWorkbook exte
XSSFWorkbook wb3 = XSSFTestDataSamples.writeOutAndReadBack(wb2);
assertNotNull(wb3);
sheet = wb3.getSheetAt(0);
- row = sheet.getRow(2);
-
+ row = sheet.getRow(2);
+
assertEquals("test1", row.getCell(3).getStringCellValue());
assertEquals("test2", row.getCell(4).getStringCellValue());
wb3.close();
@@ -700,6 +704,24 @@ public final class TestXSSFWorkbook exte
}
}
+ @Test
+ public void bug60509() throws Exception {
+ XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("60509.xlsx");
+ assertSheetOrder(wb, "Sheet1", "Sheet2", "Sheet3");
+ int sheetIndex = wb.getSheetIndex("Sheet1");
+ wb.setSheetName(sheetIndex, "Sheet1-Renamed");
+ Workbook read = XSSFTestDataSamples.writeOutAndReadBack(wb);
+ assertNotNull(read);
+ assertSheetOrder(read, "Sheet1-Renamed", "Sheet2", "Sheet3");
+ XSSFSheet sheet = (XSSFSheet) read.getSheet("Sheet1-Renamed");
+ XDDFChartData.Series series = sheet.getDrawingPatriarch().getCharts().get(0).getChartSeries().get(0).getSeries().get(0);
+ assertTrue("should be a bar chart data series", series instanceof XDDFBarChartData.Series);
+ String formula = ((XDDFBarChartData.Series) series).getCategoryData().getFormula();
+ assertTrue("should contain new sheet name", formula.startsWith("'Sheet1-Renamed'!"));
+ read.close();
+ wb.close();
+ }
+
private static final int INDEX_NOT_FOUND = -1;
private static boolean isEmpty(CharSequence cs) {
@@ -1009,22 +1031,22 @@ public final class TestXSSFWorkbook exte
final String filename = "SampleSS.xlsx";
final File file = POIDataSamples.getSpreadSheetInstance().getFile(filename);
Workbook wb;
-
+
// Some tests commented out because close() modifies the file
// See bug 58779
-
+
// String
//wb = new XSSFWorkbook(file.getPath());
//assertCloseDoesNotModifyFile(filename, wb);
-
+
// File
//wb = new XSSFWorkbook(file);
//assertCloseDoesNotModifyFile(filename, wb);
-
+
// InputStream
wb = new XSSFWorkbook(new FileInputStream(file));
assertCloseDoesNotModifyFile(filename, wb);
-
+
// OPCPackage
//wb = new XSSFWorkbook(OPCPackage.open(file));
//assertCloseDoesNotModifyFile(filename, wb);
@@ -1070,7 +1092,7 @@ public final class TestXSSFWorkbook exte
XSSFTable table2 = wb.getSheet("Foglio2").createTable();
table2.setName("Table2");
assertSame("Did not find Table2", table2, wb.getTable("Table2"));
-
+
// If table name is modified after getTable is called, the table can only be found by its new name
// This test makes sure that if any caching is done that getTable never uses a stale cache
table1.setName("Table1");
Added: poi/trunk/test-data/spreadsheet/60509.xlsx
URL: http://svn.apache.org/viewvc/poi/trunk/test-data/spreadsheet/60509.xlsx?rev=1842548&view=auto
==============================================================================
Binary files poi/trunk/test-data/spreadsheet/60509.xlsx (added) and poi/trunk/test-data/spreadsheet/60509.xlsx Mon Oct 1 19:27:32 2018 differ
Propchange: poi/trunk/test-data/spreadsheet/60509.xlsx
------------------------------------------------------------------------------
svn:executable = *
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org