### Eclipse Workspace Patch 1.0 #P poi Index: src/testcases/org/apache/poi/ss/formula/TestMissingWorkbook.java =================================================================== --- src/testcases/org/apache/poi/ss/formula/TestMissingWorkbook.java (revision 1241376) +++ src/testcases/org/apache/poi/ss/formula/TestMissingWorkbook.java (working copy) @@ -32,8 +32,6 @@ 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; Index: src/testcases/org/apache/poi/hssf/record/TestSupBookRecord.java =================================================================== --- src/testcases/org/apache/poi/hssf/record/TestSupBookRecord.java (revision 1240784) +++ src/testcases/org/apache/poi/hssf/record/TestSupBookRecord.java (working copy) @@ -20,6 +20,8 @@ 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 @@ 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()); + } } Index: src/testcases/org/apache/poi/hssf/usermodel/TestExternalReferenceChange.java =================================================================== --- src/testcases/org/apache/poi/hssf/usermodel/TestExternalReferenceChange.java (revision 0) +++ src/testcases/org/apache/poi/hssf/usermodel/TestExternalReferenceChange.java (revision 0) @@ -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); + + } + +} Index: src/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java =================================================================== --- src/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java (revision 1240784) +++ src/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java (working copy) @@ -1798,5 +1798,16 @@ 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); + } } Index: src/java/org/apache/poi/hssf/record/SupBookRecord.java =================================================================== --- src/java/org/apache/poi/hssf/record/SupBookRecord.java (revision 1240784) +++ src/java/org/apache/poi/hssf/record/SupBookRecord.java (working copy) @@ -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 @@ */ 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 @@ 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 @@ 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