poi-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From ye...@apache.org
Subject 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...
Date Fri, 10 Feb 2012 08:26:45 GMT
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


Mime
View raw message