Attachment #28280 for bug #52576

View | Details | Raw Unified | Return to bug 52576
Collapse All | Expand All

Lines 32-39 Link Here

(-)src/testcases/org/apache/poi/ss/formula/TestMissingWorkbook.java (-2 lines)
32	private static final String SOURCE_DUMMY_WORKBOOK_FILENAME = "source_dummy.xls";	32	private static final String SOURCE_DUMMY_WORKBOOK_FILENAME = "source_dummy.xls";
33	private static final String SOURCE_WORKBOOK_FILENAME = "52575_source.xls";	33	private static final String SOURCE_WORKBOOK_FILENAME = "52575_source.xls";
34		34
35	private static final String propertyKey = WorkbookEvaluator.class.getName() + ".IGNORE_MISSING_WORKBOOKS";
36
37	private HSSFWorkbook mainWorkbook;	35	private HSSFWorkbook mainWorkbook;
38	private HSSFWorkbook sourceWorkbook;	36	private HSSFWorkbook sourceWorkbook;
39		37

Lines 20-25 Link Here

(-)src/testcases/org/apache/poi/hssf/record/TestSupBookRecord.java (+44 lines)
20		20
21	import junit.framework.TestCase;	21	import junit.framework.TestCase;
22		22
		23	import static org.apache.poi.hssf.record.SupBookRecord.*;
		24
23	/**	25	/**
24	* Tests the serialization and deserialization of the SupBook record	26	* Tests the serialization and deserialization of the SupBook record
25	* class works correctly.	27	* class works correctly.
Lines 110-113 Link Here
110	assertTrue(record.isAddInFunctions());	112	assertTrue(record.isAddInFunctions());
111	TestcaseRecordInputStream.confirmRecordEncoding(0x01AE, dataAIF, record.serialize());	113	TestcaseRecordInputStream.confirmRecordEncoding(0x01AE, dataAIF, record.serialize());
112	}	114	}
		115
		116	public void testExternalReferenceUrl() {
		117	String[] sheetNames = new String[]{"SampleSheet"};
		118	final char startMarker = (char)1;
		119
		120	SupBookRecord record;
		121
		122	record = new SupBookRecord(startMarker + "test.xls", sheetNames);
		123	assertEquals("test.xls", record.getURL());
		124
		125	//UNC path notation
		126	record = new SupBookRecord(startMarker + "" + CH_VOLUME + "@servername" + CH_DOWN_DIR + "test.xls", sheetNames);
		127	assertEquals("\\\\servername" + PATH_SEPERATOR + "test.xls", record.getURL());
		128
		129	//Absolute path notation - different device
		130	record = new SupBookRecord(startMarker + "" + CH_VOLUME + "D" + CH_DOWN_DIR + "test.xls", sheetNames);
		131	assertEquals("D:" + PATH_SEPERATOR + "test.xls", record.getURL());
		132
		133	//Absolute path notation - same device
		134	record = new SupBookRecord(startMarker + "" + CH_SAME_VOLUME + "folder" + CH_DOWN_DIR + "test.xls", sheetNames);
		135	assertEquals(PATH_SEPERATOR + "folder" + PATH_SEPERATOR + "test.xls", record.getURL());
		136
		137	//Relative path notation - down
		138	record = new SupBookRecord(startMarker + "folder" + CH_DOWN_DIR + "test.xls", sheetNames);
		139	assertEquals("folder" + PATH_SEPERATOR + "test.xls", record.getURL());
		140
		141	//Relative path notation - up
		142	record = new SupBookRecord(startMarker +""+ CH_UP_DIR + "test.xls", sheetNames);
		143	assertEquals(".." + PATH_SEPERATOR + "test.xls", record.getURL());
		144
		145	//Relative path notation - for EXCEL.exe - fallback
		146	record = new SupBookRecord(startMarker +""+ CH_STARTUP_DIR + "test.xls", sheetNames);
		147	assertEquals("." + PATH_SEPERATOR + "test.xls", record.getURL());
		148
		149	//Relative path notation - for EXCEL lib folder - fallback
		150	record = new SupBookRecord(startMarker +""+ CH_LIB_DIR + "test.xls", sheetNames);
		151	assertEquals("." + PATH_SEPERATOR + "test.xls", record.getURL());
		152
		153	//Relative path notation - for alternative EXCEL.exe - fallback
		154	record = new SupBookRecord(startMarker +""+ CH_ALT_STARTUP_DIR+ "test.xls", sheetNames);
		155	assertEquals("." + PATH_SEPERATOR + "test.xls", record.getURL());
		156	}
113	}	157	}




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);

	}
	
}




        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);
    }
}

Lines 18-23 Link Here

(-)src/java/org/apache/poi/hssf/record/SupBookRecord.java (-13 / +56 lines)
18	package org.apache.poi.hssf.record;	18	package org.apache.poi.hssf.record;
19		19
20	import org.apache.poi.util.LittleEndianOutput;	20	import org.apache.poi.util.LittleEndianOutput;
		21	import org.apache.poi.util.POILogFactory;
		22	import org.apache.poi.util.POILogger;
21	import org.apache.poi.util.StringUtil;	23	import org.apache.poi.util.StringUtil;
22		24
23	/**	25	/**
Lines 31-36 Link Here
31	*/	33	*/
32	public final class SupBookRecord extends StandardRecord {	34	public final class SupBookRecord extends StandardRecord {
33		35
		36	private final static POILogger logger = POILogFactory.getLogger(SupBookRecord.class);
		37
34	public final static short sid = 0x01AE;	38	public final static short sid = 0x01AE;
35		39
36	private static final short SMALL_RECORD_SIZE = 4;	40	private static final short SMALL_RECORD_SIZE = 4;
Lines 42-47 Link Here
42	private String[] field_3_sheet_names;	46	private String[] field_3_sheet_names;
43	private boolean _isAddInFunctions;	47	private boolean _isAddInFunctions;
44		48
		49	protected static final char CH_VOLUME = 1;
		50	protected static final char CH_SAME_VOLUME = 2;
		51	protected static final char CH_DOWN_DIR = 3;
		52	protected static final char CH_UP_DIR = 4;
		53	protected static final char CH_LONG_VOLUME = 5;
		54	protected static final char CH_STARTUP_DIR = 6;
		55	protected static final char CH_ALT_STARTUP_DIR = 7;
		56	protected static final char CH_LIB_DIR = 8;
		57	protected static final String PATH_SEPERATOR = System.getProperty("file.separator");
45		58
46	public static SupBookRecord createInternalReferences(short numberOfSheets) {	59	public static SupBookRecord createInternalReferences(short numberOfSheets) {
47	return new SupBookRecord(false, numberOfSheets);	60	return new SupBookRecord(false, numberOfSheets);
Lines 192-212 Link Here
192	return encodedUrl;	205	return encodedUrl;
193	}	206	}
194	private static String decodeFileName(String encodedUrl) {	207	private static String decodeFileName(String encodedUrl) {
195	return encodedUrl.substring(1);	208	/* see "MICROSOFT OFFICE EXCEL 97-2007 BINARY FILE FORMAT SPECIFICATION" */
196	// TODO the following special characters may appear in the rest of the string, and need to get interpreted	209	StringBuilder sb = new StringBuilder();
197	/* see "MICROSOFT OFFICE EXCEL 97-2007 BINARY FILE FORMAT SPECIFICATION"	210	for(int i=1; i<encodedUrl.length(); i++) {
198	chVolume 1	211	char c = encodedUrl.charAt(i);
199	chSameVolume 2	212	switch (c) {
200	chDownDir 3	213	case CH_VOLUME:
201	chUpDir 4	214	char driveLetter = encodedUrl.charAt(++i);
202	chLongVolume 5	215	if (driveLetter == '@') {
203	chStartupDir 6	216	sb.append("\\\\");
204	chAltStartupDir 7	217	} else {
205	chLibDir 8	218	//Windows notation for drive letters
206		219	sb.append(driveLetter).append(":");
207	*/	220	}
		221	break;
		222	case CH_SAME_VOLUME:
		223	sb.append(PATH_SEPERATOR);
		224	break;
		225	case CH_DOWN_DIR:
		226	sb.append(PATH_SEPERATOR);
		227	break;
		228	case CH_UP_DIR:
		229	sb.append("..").append(PATH_SEPERATOR);
		230	break;
		231	case CH_LONG_VOLUME:
		232	//Don't known to handle...
		233	logger.log(POILogger.WARN, "Found unexpected key: ChLongVolume - IGNORING");
		234	break;
		235	case CH_STARTUP_DIR:
		236	case CH_ALT_STARTUP_DIR:
		237	case CH_LIB_DIR:
		238	logger.log(POILogger.WARN, "EXCEL.EXE path unkown - using this directoy instead: .");
		239	sb.append(".").append(PATH_SEPERATOR);
		240	break;
		241	default:
		242	sb.append(c);
		243	}
		244	}
		245	return sb.toString();
208	}	246	}
209	public String[] getSheetNames() {	247	public String[] getSheetNames() {
210	return field_3_sheet_names.clone();	248	return field_3_sheet_names.clone();
211	}	249	}
		250
		251	public void setURL(String pUrl) {
		252	//Keep the first marker character!
		253	field_2_encoded_url = field_2_encoded_url.substring(0, 1) + pUrl;
		254	}
212	}	255	}




        }
        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);
    }
}




    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;
	}

}

Return to bug 52576

Lines 1798-1802 Link Here

(-)src/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java (-1 / +12 lines)
1798	return recalc != null && recalc.getEngineId() != 0;	1798	return recalc != null && recalc.getEngineId() != 0;
1799	}	1799	}
1800		1800
1801		1801	/**
		1802	* Changes an external referenced file to another file.
		1803	* A formular in Excel which refers a cell in another file is saved in two parts:
		1804	* The referenced file is stored in an reference table. the row/cell information is saved separate.
		1805	* This method invokation will only change the reference in the lookup-table itself.
		1806	* @param oldUrl The old URL to search for and which is to be replaced
		1807	* @param newUrl The URL replacement
		1808	* @return true if the oldUrl was found and replaced with newUrl. Otherwise false
		1809	*/
		1810	public boolean changeExternalReference(String oldUrl, String newUrl) {
		1811	return workbook.changeExternalReference(oldUrl, newUrl);
		1812	}
1802	}	1813	}

Lines 2457-2460 Link Here

(-)src/java/org/apache/poi/hssf/model/InternalWorkbook.java (+14 lines)
2457	}	2457	}
2458	return record;	2458	return record;
2459	}	2459	}
		2460
		2461
		2462	/**
		2463	* Changes an external referenced file to another file.
		2464	* A formular in Excel which refers a cell in another file is saved in two parts:
		2465	* The referenced file is stored in an reference table. the row/cell information is saved separate.
		2466	* This method invokation will only change the reference in the lookup-table itself.
		2467	* @param oldUrl The old URL to search for and which is to be replaced
		2468	* @param newUrl The URL replacement
		2469	* @return true if the oldUrl was found and replaced with newUrl. Otherwise false
		2470	*/
		2471	public boolean changeExternalReference(String oldUrl, String newUrl) {
		2472	return linkTable.changeExternalReference(oldUrl, newUrl);
		2473	}
2460	}	2474	}

Lines 552-555 Link Here

(-)src/java/org/apache/poi/hssf/model/LinkTable.java (+23 lines)
552	private int findRefIndexFromExtBookIndex(int extBookIndex) {	552	private int findRefIndexFromExtBookIndex(int extBookIndex) {
553	return _externSheetRecord.findRefIndexFromExtBookIndex(extBookIndex);	553	return _externSheetRecord.findRefIndexFromExtBookIndex(extBookIndex);
554	}	554	}
		555
		556	/**
		557	* Changes an external referenced file to another file.
		558	* A formular in Excel which refers a cell in another file is saved in two parts:
		559	* The referenced file is stored in an reference table. the row/cell information is saved separate.
		560	* This method invokation will only change the reference in the lookup-table itself.
		561	* @param oldUrl The old URL to search for and which is to be replaced
		562	* @param newUrl The URL replacement
		563	* @return true if the oldUrl was found and replaced with newUrl. Otherwise false
		564	*/
		565	public boolean changeExternalReference(String oldUrl, String newUrl) {
		566	for(ExternalBookBlock ex : _externalBookBlocks) {
		567	SupBookRecord externalRecord = ex.getExternalBookRecord();
		568	if (externalRecord.isExternalReferences()
		569	&& externalRecord.getURL().equals(oldUrl)) {
		570
		571	externalRecord.setURL(newUrl);
		572	return true;
		573	}
		574	}
		575	return false;
		576	}
		577
555	}	578	}

Line 0 Link Here

(-)src/testcases/org/apache/poi/hssf/usermodel/TestExternalReferenceChange.java (+52 lines)
	1	package org.apache.poi.hssf.usermodel;
	2
	3	import java.io.IOException;
	4
	5	import org.apache.poi.hssf.HSSFTestDataSamples;
	6	import org.apache.poi.ss.usermodel.Cell;
	7
	8	import junit.framework.TestCase;
	9
	10	public class TestExternalReferenceChange extends TestCase {
	11
	12	private static final String MAIN_WORKBOOK_FILENAME = "52575_main.xls";
	13	private static final String SOURCE_DUMMY_WORKBOOK_FILENAME = "source_dummy.xls";
	14	private static final String SOURCE_WORKBOOK_FILENAME = "52575_source.xls";
	15
	16	private HSSFWorkbook mainWorkbook;
	17	private HSSFWorkbook sourceWorkbook;
	18
	19	@Override
	20	protected void setUp() throws Exception {
	21	mainWorkbook = HSSFTestDataSamples.openSampleWorkbook(MAIN_WORKBOOK_FILENAME);
	22	sourceWorkbook = HSSFTestDataSamples.openSampleWorkbook(SOURCE_WORKBOOK_FILENAME);
	23
	24	assertNotNull(mainWorkbook);
	25	assertNotNull(sourceWorkbook);
	26	}
	27
	28	public void testDummyToSource() throws IOException {
	29	boolean changed = mainWorkbook.changeExternalReference("DOESNOTEXIST", SOURCE_WORKBOOK_FILENAME);
	30	assertFalse(changed);
	31
	32	changed = mainWorkbook.changeExternalReference(SOURCE_DUMMY_WORKBOOK_FILENAME, SOURCE_WORKBOOK_FILENAME);
	33	assertTrue(changed);
	34
	35	HSSFSheet lSheet = mainWorkbook.getSheetAt(0);
	36	HSSFCell lA1Cell = lSheet.getRow(0).getCell(0);
	37
	38	assertEquals(Cell.CELL_TYPE_FORMULA, lA1Cell.getCellType());
	39
	40	HSSFFormulaEvaluator lMainWorkbookEvaluator = new HSSFFormulaEvaluator(mainWorkbook);
	41	HSSFFormulaEvaluator lSourceEvaluator = new HSSFFormulaEvaluator(sourceWorkbook);
	42	HSSFFormulaEvaluator.setupEnvironment(
	43	new String[]{MAIN_WORKBOOK_FILENAME, SOURCE_WORKBOOK_FILENAME},
	44	new HSSFFormulaEvaluator[] {lMainWorkbookEvaluator, lSourceEvaluator});
	45
	46	assertEquals(Cell.CELL_TYPE_NUMERIC, lMainWorkbookEvaluator.evaluateFormulaCell(lA1Cell));
	47
	48	assertEquals(20.0d, lA1Cell.getNumericCellValue(), 0.00001d);
	49
	50	}
	51
	52	}