You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@poi.apache.org by ye...@apache.org on 2012/02/10 09:26:45 UTC
svn commit: r1242701 - in /poi/trunk: src/documentation/content/xdocs/
src/java/org/apache/poi/hssf/model/ src/java/org/apache/poi/hssf/record/
src/java/org/apache/poi/hssf/usermodel/
src/testcases/org/apache/poi/hssf/record/ src/testcases/org/apache/p...
Author: yegor
Date: Fri Feb 10 08:26:45 2012
New Revision: 1242701
URL: http://svn.apache.org/viewvc?rev=1242701&view=rev
Log:
Bugzilla 52576: support changing external file references in HSSFWorkbook
Added:
poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestExternalReferenceChange.java
poi/trunk/test-data/spreadsheet/49896.xls (with props)
Modified:
poi/trunk/src/documentation/content/xdocs/status.xml
poi/trunk/src/java/org/apache/poi/hssf/model/InternalWorkbook.java
poi/trunk/src/java/org/apache/poi/hssf/model/LinkTable.java
poi/trunk/src/java/org/apache/poi/hssf/record/SupBookRecord.java
poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java
poi/trunk/src/testcases/org/apache/poi/hssf/record/TestSupBookRecord.java
poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestBugs.java
poi/trunk/src/testcases/org/apache/poi/ss/formula/TestMissingWorkbook.java
Modified: poi/trunk/src/documentation/content/xdocs/status.xml
URL: http://svn.apache.org/viewvc/poi/trunk/src/documentation/content/xdocs/status.xml?rev=1242701&r1=1242700&r2=1242701&view=diff
==============================================================================
--- poi/trunk/src/documentation/content/xdocs/status.xml (original)
+++ poi/trunk/src/documentation/content/xdocs/status.xml Fri Feb 10 08:26:45 2012
@@ -34,6 +34,8 @@
<changes>
<release version="3.8-beta6" date="2012-??-??">
+ <action dev="poi-developers" type="add">52576 - support changing external file references in HSSFWorkbook</action>
+ <action dev="poi-developers" type="add">49896 - support external references in FormulaRenderer</action>
<action dev="poi-developers" type="fix">52527 - avoid exception when matching shared formula records in HSSF</action>
<action dev="poi-developers" type="add">52568 - Added methods to set/get an XWPFRun's text color</action>
<action dev="poi-developers" type="add">52566 - Added methods to set/get vertical alignment and color in XWPFTableCell</action>
Modified: poi/trunk/src/java/org/apache/poi/hssf/model/InternalWorkbook.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/model/InternalWorkbook.java?rev=1242701&r1=1242700&r2=1242701&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/model/InternalWorkbook.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/model/InternalWorkbook.java Fri Feb 10 08:26:45 2012
@@ -2457,4 +2457,18 @@ public final class InternalWorkbook {
}
return record;
}
+
+
+ /**
+ * Changes an external referenced file to another file.
+ * A formular in Excel which refers a cell in another file is saved in two parts:
+ * The referenced file is stored in an reference table. the row/cell information is saved separate.
+ * This method invokation will only change the reference in the lookup-table itself.
+ * @param oldUrl The old URL to search for and which is to be replaced
+ * @param newUrl The URL replacement
+ * @return true if the oldUrl was found and replaced with newUrl. Otherwise false
+ */
+ public boolean changeExternalReference(String oldUrl, String newUrl) {
+ return linkTable.changeExternalReference(oldUrl, newUrl);
+ }
}
Modified: poi/trunk/src/java/org/apache/poi/hssf/model/LinkTable.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/model/LinkTable.java?rev=1242701&r1=1242700&r2=1242701&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/model/LinkTable.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/model/LinkTable.java Fri Feb 10 08:26:45 2012
@@ -552,4 +552,27 @@ final class LinkTable {
private int findRefIndexFromExtBookIndex(int extBookIndex) {
return _externSheetRecord.findRefIndexFromExtBookIndex(extBookIndex);
}
+
+ /**
+ * Changes an external referenced file to another file.
+ * A formular in Excel which refers a cell in another file is saved in two parts:
+ * The referenced file is stored in an reference table. the row/cell information is saved separate.
+ * This method invokation will only change the reference in the lookup-table itself.
+ * @param oldUrl The old URL to search for and which is to be replaced
+ * @param newUrl The URL replacement
+ * @return true if the oldUrl was found and replaced with newUrl. Otherwise false
+ */
+ public boolean changeExternalReference(String oldUrl, String newUrl) {
+ for(ExternalBookBlock ex : _externalBookBlocks) {
+ SupBookRecord externalRecord = ex.getExternalBookRecord();
+ if (externalRecord.isExternalReferences()
+ && externalRecord.getURL().equals(oldUrl)) {
+
+ externalRecord.setURL(newUrl);
+ return true;
+ }
+ }
+ return false;
+ }
+
}
Modified: poi/trunk/src/java/org/apache/poi/hssf/record/SupBookRecord.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/record/SupBookRecord.java?rev=1242701&r1=1242700&r2=1242701&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/record/SupBookRecord.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/record/SupBookRecord.java Fri Feb 10 08:26:45 2012
@@ -18,6 +18,8 @@
package org.apache.poi.hssf.record;
import org.apache.poi.util.LittleEndianOutput;
+import org.apache.poi.util.POILogFactory;
+import org.apache.poi.util.POILogger;
import org.apache.poi.util.StringUtil;
/**
@@ -31,6 +33,8 @@ import org.apache.poi.util.StringUtil;
*/
public final class SupBookRecord extends StandardRecord {
+ private final static POILogger logger = POILogFactory.getLogger(SupBookRecord.class);
+
public final static short sid = 0x01AE;
private static final short SMALL_RECORD_SIZE = 4;
@@ -42,6 +46,15 @@ public final class SupBookRecord extends
private String[] field_3_sheet_names;
private boolean _isAddInFunctions;
+ protected static final char CH_VOLUME = 1;
+ protected static final char CH_SAME_VOLUME = 2;
+ protected static final char CH_DOWN_DIR = 3;
+ protected static final char CH_UP_DIR = 4;
+ protected static final char CH_LONG_VOLUME = 5;
+ protected static final char CH_STARTUP_DIR = 6;
+ protected static final char CH_ALT_STARTUP_DIR = 7;
+ protected static final char CH_LIB_DIR = 8;
+ protected static final String PATH_SEPERATOR = System.getProperty("file.separator");
public static SupBookRecord createInternalReferences(short numberOfSheets) {
return new SupBookRecord(false, numberOfSheets);
@@ -192,21 +205,51 @@ public final class SupBookRecord extends
return encodedUrl;
}
private static String decodeFileName(String encodedUrl) {
- return encodedUrl.substring(1);
- // TODO the following special characters may appear in the rest of the string, and need to get interpreted
- /* see "MICROSOFT OFFICE EXCEL 97-2007 BINARY FILE FORMAT SPECIFICATION"
- chVolume 1
- chSameVolume 2
- chDownDir 3
- chUpDir 4
- chLongVolume 5
- chStartupDir 6
- chAltStartupDir 7
- chLibDir 8
-
- */
+ /* see "MICROSOFT OFFICE EXCEL 97-2007 BINARY FILE FORMAT SPECIFICATION" */
+ StringBuilder sb = new StringBuilder();
+ for(int i=1; i<encodedUrl.length(); i++) {
+ char c = encodedUrl.charAt(i);
+ switch (c) {
+ case CH_VOLUME:
+ char driveLetter = encodedUrl.charAt(++i);
+ if (driveLetter == '@') {
+ sb.append("\\\\");
+ } else {
+ //Windows notation for drive letters
+ sb.append(driveLetter).append(":");
+ }
+ break;
+ case CH_SAME_VOLUME:
+ sb.append(PATH_SEPERATOR);
+ break;
+ case CH_DOWN_DIR:
+ sb.append(PATH_SEPERATOR);
+ break;
+ case CH_UP_DIR:
+ sb.append("..").append(PATH_SEPERATOR);
+ break;
+ case CH_LONG_VOLUME:
+ //Don't known to handle...
+ logger.log(POILogger.WARN, "Found unexpected key: ChLongVolume - IGNORING");
+ break;
+ case CH_STARTUP_DIR:
+ case CH_ALT_STARTUP_DIR:
+ case CH_LIB_DIR:
+ logger.log(POILogger.WARN, "EXCEL.EXE path unkown - using this directoy instead: .");
+ sb.append(".").append(PATH_SEPERATOR);
+ break;
+ default:
+ sb.append(c);
+ }
+ }
+ return sb.toString();
}
public String[] getSheetNames() {
return field_3_sheet_names.clone();
}
+
+ public void setURL(String pUrl) {
+ //Keep the first marker character!
+ field_2_encoded_url = field_2_encoded_url.substring(0, 1) + pUrl;
+ }
}
Modified: poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java?rev=1242701&r1=1242700&r2=1242701&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java Fri Feb 10 08:26:45 2012
@@ -1798,5 +1798,16 @@ public final class HSSFWorkbook extends
return recalc != null && recalc.getEngineId() != 0;
}
-
+ /**
+ * Changes an external referenced file to another file.
+ * A formular in Excel which refers a cell in another file is saved in two parts:
+ * The referenced file is stored in an reference table. the row/cell information is saved separate.
+ * This method invokation will only change the reference in the lookup-table itself.
+ * @param oldUrl The old URL to search for and which is to be replaced
+ * @param newUrl The URL replacement
+ * @return true if the oldUrl was found and replaced with newUrl. Otherwise false
+ */
+ public boolean changeExternalReference(String oldUrl, String newUrl) {
+ return workbook.changeExternalReference(oldUrl, newUrl);
+ }
}
Modified: poi/trunk/src/testcases/org/apache/poi/hssf/record/TestSupBookRecord.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/record/TestSupBookRecord.java?rev=1242701&r1=1242700&r2=1242701&view=diff
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/hssf/record/TestSupBookRecord.java (original)
+++ poi/trunk/src/testcases/org/apache/poi/hssf/record/TestSupBookRecord.java Fri Feb 10 08:26:45 2012
@@ -20,6 +20,8 @@ package org.apache.poi.hssf.record;
import junit.framework.TestCase;
+import static org.apache.poi.hssf.record.SupBookRecord.*;
+
/**
* Tests the serialization and deserialization of the SupBook record
* class works correctly.
@@ -110,4 +112,46 @@ public final class TestSupBookRecord ext
assertTrue(record.isAddInFunctions());
TestcaseRecordInputStream.confirmRecordEncoding(0x01AE, dataAIF, record.serialize());
}
+
+ public void testExternalReferenceUrl() {
+ String[] sheetNames = new String[]{"SampleSheet"};
+ final char startMarker = (char)1;
+
+ SupBookRecord record;
+
+ record = new SupBookRecord(startMarker + "test.xls", sheetNames);
+ assertEquals("test.xls", record.getURL());
+
+ //UNC path notation
+ record = new SupBookRecord(startMarker + "" + CH_VOLUME + "@servername" + CH_DOWN_DIR + "test.xls", sheetNames);
+ assertEquals("\\\\servername" + PATH_SEPERATOR + "test.xls", record.getURL());
+
+ //Absolute path notation - different device
+ record = new SupBookRecord(startMarker + "" + CH_VOLUME + "D" + CH_DOWN_DIR + "test.xls", sheetNames);
+ assertEquals("D:" + PATH_SEPERATOR + "test.xls", record.getURL());
+
+ //Absolute path notation - same device
+ record = new SupBookRecord(startMarker + "" + CH_SAME_VOLUME + "folder" + CH_DOWN_DIR + "test.xls", sheetNames);
+ assertEquals(PATH_SEPERATOR + "folder" + PATH_SEPERATOR + "test.xls", record.getURL());
+
+ //Relative path notation - down
+ record = new SupBookRecord(startMarker + "folder" + CH_DOWN_DIR + "test.xls", sheetNames);
+ assertEquals("folder" + PATH_SEPERATOR + "test.xls", record.getURL());
+
+ //Relative path notation - up
+ record = new SupBookRecord(startMarker +""+ CH_UP_DIR + "test.xls", sheetNames);
+ assertEquals(".." + PATH_SEPERATOR + "test.xls", record.getURL());
+
+ //Relative path notation - for EXCEL.exe - fallback
+ record = new SupBookRecord(startMarker +""+ CH_STARTUP_DIR + "test.xls", sheetNames);
+ assertEquals("." + PATH_SEPERATOR + "test.xls", record.getURL());
+
+ //Relative path notation - for EXCEL lib folder - fallback
+ record = new SupBookRecord(startMarker +""+ CH_LIB_DIR + "test.xls", sheetNames);
+ assertEquals("." + PATH_SEPERATOR + "test.xls", record.getURL());
+
+ //Relative path notation - for alternative EXCEL.exe - fallback
+ record = new SupBookRecord(startMarker +""+ CH_ALT_STARTUP_DIR+ "test.xls", sheetNames);
+ assertEquals("." + PATH_SEPERATOR + "test.xls", record.getURL());
+ }
}
Modified: poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestBugs.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestBugs.java?rev=1242701&r1=1242700&r2=1242701&view=diff
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestBugs.java (original)
+++ poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestBugs.java Fri Feb 10 08:26:45 2012
@@ -1678,17 +1678,17 @@ public final class TestBugs extends Base
row = s.getRow(4);
assertEquals(Cell.CELL_TYPE_FORMULA, row.getCell(1).getCellType());
- assertEquals("'[\u0005$http://gagravarr.org/FormulaRefs.xls]Sheet1'!B1", row.getCell(1).getCellFormula());
+ assertEquals("'[$http://gagravarr.org/FormulaRefs.xls]Sheet1'!B1", row.getCell(1).getCellFormula());
assertEquals(112.0, row.getCell(1).getNumericCellValue());
// Change 4
- row.getCell(1).setCellFormula("'[\u0005$http://gagravarr.org/FormulaRefs2.xls]Sheet1'!B2");
+ row.getCell(1).setCellFormula("'[$http://gagravarr.org/FormulaRefs2.xls]Sheet1'!B2");
row.getCell(1).setCellValue(123.0);
// Add 5
row = s.createRow(5);
row.createCell(1, Cell.CELL_TYPE_FORMULA);
- row.getCell(1).setCellFormula("'[\u0005$http://example.com/FormulaRefs.xls]Sheet1'!B1");
+ row.getCell(1).setCellFormula("'[$http://example.com/FormulaRefs.xls]Sheet1'!B1");
row.getCell(1).setCellValue(234.0);
@@ -1719,12 +1719,12 @@ public final class TestBugs extends Base
if(1==2) {
row = s.getRow(4);
assertEquals(Cell.CELL_TYPE_FORMULA, row.getCell(1).getCellType());
- assertEquals("'[\u0005$http://gagravarr.org/FormulaRefs2.xls]Sheet1'!B2", row.getCell(1).getCellFormula());
+ assertEquals("'[$http://gagravarr.org/FormulaRefs2.xls]Sheet1'!B2", row.getCell(1).getCellFormula());
assertEquals(123.0, row.getCell(1).getNumericCellValue());
row = s.getRow(5);
assertEquals(Cell.CELL_TYPE_FORMULA, row.getCell(1).getCellType());
- assertEquals("'[\u0005$http://example.com/FormulaRefs.xls]Sheet1'!B1", row.getCell(1).getCellFormula());
+ assertEquals("'[$http://example.com/FormulaRefs.xls]Sheet1'!B1", row.getCell(1).getCellFormula());
assertEquals(234.0, row.getCell(1).getNumericCellValue());
}
}
@@ -2193,4 +2193,12 @@ if(1==2) {
// Good
}
}
+
+ public void test49896() {
+ HSSFWorkbook wb = openSample("49896.xls");
+ HSSFCell cell = wb.getSheetAt(0).getRow(1).getCell(1);
+ assertEquals("VLOOKUP(A2,'[C:Documents and Settings/Yegor/My Documents/csco.xls]Sheet1'!$A$2:$B$3,2,FALSE)",
+ cell.getCellFormula());
+ }
+
}
Added: poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestExternalReferenceChange.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestExternalReferenceChange.java?rev=1242701&view=auto
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestExternalReferenceChange.java (added)
+++ poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestExternalReferenceChange.java Fri Feb 10 08:26:45 2012
@@ -0,0 +1,52 @@
+package org.apache.poi.hssf.usermodel;
+
+import java.io.IOException;
+
+import org.apache.poi.hssf.HSSFTestDataSamples;
+import org.apache.poi.ss.usermodel.Cell;
+
+import junit.framework.TestCase;
+
+public class TestExternalReferenceChange extends TestCase {
+
+ private static final String MAIN_WORKBOOK_FILENAME = "52575_main.xls";
+ private static final String SOURCE_DUMMY_WORKBOOK_FILENAME = "source_dummy.xls";
+ private static final String SOURCE_WORKBOOK_FILENAME = "52575_source.xls";
+
+ private HSSFWorkbook mainWorkbook;
+ private HSSFWorkbook sourceWorkbook;
+
+ @Override
+ protected void setUp() throws Exception {
+ mainWorkbook = HSSFTestDataSamples.openSampleWorkbook(MAIN_WORKBOOK_FILENAME);
+ sourceWorkbook = HSSFTestDataSamples.openSampleWorkbook(SOURCE_WORKBOOK_FILENAME);
+
+ assertNotNull(mainWorkbook);
+ assertNotNull(sourceWorkbook);
+ }
+
+ public void testDummyToSource() throws IOException {
+ boolean changed = mainWorkbook.changeExternalReference("DOESNOTEXIST", SOURCE_WORKBOOK_FILENAME);
+ assertFalse(changed);
+
+ changed = mainWorkbook.changeExternalReference(SOURCE_DUMMY_WORKBOOK_FILENAME, SOURCE_WORKBOOK_FILENAME);
+ assertTrue(changed);
+
+ HSSFSheet lSheet = mainWorkbook.getSheetAt(0);
+ HSSFCell lA1Cell = lSheet.getRow(0).getCell(0);
+
+ assertEquals(Cell.CELL_TYPE_FORMULA, lA1Cell.getCellType());
+
+ HSSFFormulaEvaluator lMainWorkbookEvaluator = new HSSFFormulaEvaluator(mainWorkbook);
+ HSSFFormulaEvaluator lSourceEvaluator = new HSSFFormulaEvaluator(sourceWorkbook);
+ HSSFFormulaEvaluator.setupEnvironment(
+ new String[]{MAIN_WORKBOOK_FILENAME, SOURCE_WORKBOOK_FILENAME},
+ new HSSFFormulaEvaluator[] {lMainWorkbookEvaluator, lSourceEvaluator});
+
+ assertEquals(Cell.CELL_TYPE_NUMERIC, lMainWorkbookEvaluator.evaluateFormulaCell(lA1Cell));
+
+ assertEquals(20.0d, lA1Cell.getNumericCellValue(), 0.00001d);
+
+ }
+
+}
Modified: poi/trunk/src/testcases/org/apache/poi/ss/formula/TestMissingWorkbook.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/ss/formula/TestMissingWorkbook.java?rev=1242701&r1=1242700&r2=1242701&view=diff
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/ss/formula/TestMissingWorkbook.java (original)
+++ poi/trunk/src/testcases/org/apache/poi/ss/formula/TestMissingWorkbook.java Fri Feb 10 08:26:45 2012
@@ -32,8 +32,6 @@ public class TestMissingWorkbook extends
private static final String SOURCE_DUMMY_WORKBOOK_FILENAME = "source_dummy.xls";
private static final String SOURCE_WORKBOOK_FILENAME = "52575_source.xls";
- private static final String propertyKey = WorkbookEvaluator.class.getName() + ".IGNORE_MISSING_WORKBOOKS";
-
private HSSFWorkbook mainWorkbook;
private HSSFWorkbook sourceWorkbook;
Added: poi/trunk/test-data/spreadsheet/49896.xls
URL: http://svn.apache.org/viewvc/poi/trunk/test-data/spreadsheet/49896.xls?rev=1242701&view=auto
==============================================================================
Binary file - no diff available.
Propchange: poi/trunk/test-data/spreadsheet/49896.xls
------------------------------------------------------------------------------
svn:mime-type = application/octet-stream
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org