Attachment #21355 for bug #44167

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

Lines 18-32 Link Here

(-)src/java/org/apache/poi/hssf/util/AreaReference.java (-1 / +40 lines)
18		18
19	package org.apache.poi.hssf.util;	19	package org.apache.poi.hssf.util;
20		20
		21	import java.util.ArrayList;
		22	import java.util.StringTokenizer;
		23
21	public class AreaReference {	24	public class AreaReference {
22		25
23		26
24	private CellReference [] cells;	27	private CellReference [] cells;
25	private int dim;	28	private int dim;
26		29
27	/** Create an area ref from a string representation	30	/**
		31	* Create an area ref from a string representation.
		32	* The area reference must be contiguous
28	*/	33	*/
29	public AreaReference(String reference) {	34	public AreaReference(String reference) {
		35	if(! isContiguous(reference)) {
		36	throw new IllegalArgumentException("References passed to the AreaReference must be contiguous, use generateContiguous(ref) if you have non-contiguous references");
		37	}
		38
30	String[] refs = seperateAreaRefs(reference);	39	String[] refs = seperateAreaRefs(reference);
31	dim = refs.length;	40	dim = refs.length;
32	cells = new CellReference[dim];	41	cells = new CellReference[dim];
Lines 34-39 Link Here
34	cells[i]=new CellReference(refs[i]);	43	cells[i]=new CellReference(refs[i]);
35	}	44	}
36	}	45	}
		46
		47	/**
		48	* Is the reference for a contiguous (i.e.
		49	* unbroken) area, or is it made up of
		50	* several different parts?
		51	* (If it is, you will need to call
		52	* ....
		53	*/
		54	public static boolean isContiguous(String reference) {
		55	if(reference.indexOf(',') == -1) {
		56	return true;
		57	}
		58	return false;
		59	}
		60
		61	/**
		62	* Takes a non-contiguous area reference, and
		63	* returns an array of contiguous area references.
		64	*/
		65	public static AreaReference[] generateContiguous(String reference) {
		66	ArrayList refs = new ArrayList();
		67	StringTokenizer st = new StringTokenizer(reference, ",");
		68	while(st.hasMoreTokens()) {
		69	refs.add(
		70	new AreaReference(st.nextToken())
		71	);
		72	}
		73	return (AreaReference[])refs.toArray(new AreaReference[refs.size()]);
		74	}
		75
37	//not sure if we need to be flexible here!	76	//not sure if we need to be flexible here!
38	/** return the dimensions of this area	77	/** return the dimensions of this area
39	**/	78	**/





import java.util.List;
import java.util.Stack;
import java.util.Iterator;

import org.apache.poi.hssf.model.Workbook;
import org.apache.poi.hssf.record.formula.*;
import org.apache.poi.hssf.record.formula.DeletedArea3DPtg;
import org.apache.poi.hssf.record.formula.DeletedRef3DPtg;
import org.apache.poi.hssf.record.formula.Ptg;
import org.apache.poi.hssf.record.formula.Ref3DPtg;
import org.apache.poi.hssf.util.RangeAddress;
import org.apache.poi.util.HexDump;
import org.apache.poi.util.LittleEndian;

        Ptg ptg = (Ptg) field_13_name_definition.peek();
        String result = "";

        // If it's a union, descend in and process
        if (ptg.getClass() == UnionPtg.class) {
            Iterator it =field_13_name_definition.iterator();
            while( it.hasNext() ) {
                Ptg p = (Ptg)it.next();

                String thisRes = getAreaRefString(p, book);
                if(thisRes.length() > 0) {
                    // Add a comma to the end if needed
                    if(result.length() > 0 && !result.endsWith(",")) {
                        result += ",";
                    }
                    // And add the string it corresponds to
                    result += thisRes;
                }
            }
        } else {
            // Otherwise just get the string
            result = getAreaRefString(ptg, book);
        }

        return result;
    }

    /**
     * Turn the given ptg into a string, or
     *  return an empty string if nothing is possible
     *  for it.
     */
    private String getAreaRefString(Ptg ptg,Workbook book) {
        if (ptg.getClass() == Area3DPtg.class){
            return ptg.toFormulaString(book);
        } else if (ptg.getClass() == Ref3DPtg.class){
            return ptg.toFormulaString(book);
        } else if (ptg.getClass() == DeletedArea3DPtg.class || ptg.getClass() == DeletedRef3DPtg.class) {
        	return "#REF!";
        }
        return "";
    }

    /** sets the reference , the area only (range)
     * @param ref area reference
     */




        // extract the cell contents based on cell type etc.
    }
            </source>
            <p>
            Reading from non-contiguous Named Ranges
            </p>
            <source>
    // Setup code
    String cname = "TestName";
    HSSFWorkbook wb = getMyWorkbook(); // retrieve workbook

    // Retrieve the named range
    // Will be something like "$C$10,$D$12:$D$14";
    int namedCellIdx = wb.getNameIndex(cellName);
    HSSFName aNamedCell = wb.getNameAt(namedCellIdx);

    // Retrieve the cell at the named range and test its contents
    // Will get back one AreaReference for C10, and
    //  another for D12 to D14
    AreaReference[] arefs = AreaReference.generateContiguous(aNamedCell.getReference());
    for(int i=0; i<arefs.length; i++) {
        CellReference[] crefs = arefs[i].getCells();
        for (int j=0; j<crefs.length; j++) {
	    // Check it turns into real stuff
	    HSSFSheet s = wb.getSheet(crefs[j].getSheetName());
	    HSSFRow r = s.getRow(crefs[j].getRow());
	    HSSFCell c = r.getCell(crefs[j].getCol());
            // extract the cell contents based on cell type etc.
        }
    }
            </source>
        </section>
        <anchor id="CellComments"/>
        <section><title>Cell Comments</title>

Lines 21-30 Link Here

(-)src/testcases/org/apache/poi/hssf/util/TestAreaReference.java (-4 / +154 lines)
21		21
22	import junit.framework.TestCase;	22	import junit.framework.TestCase;
23		23
24	import org.apache.poi.hssf.usermodel.HSSFCell;	24	import org.apache.poi.hssf.usermodel.*;
25	import org.apache.poi.hssf.usermodel.HSSFRow;	25	import org.apache.poi.hssf.model.Workbook;
26	import org.apache.poi.hssf.usermodel.HSSFSheet;	26	import org.apache.poi.hssf.record.NameRecord;
		27	import org.apache.poi.hssf.record.formula.MemFuncPtg;
		28	import org.apache.poi.hssf.record.formula.Area3DPtg;
		29	import org.apache.poi.hssf.record.formula.UnionPtg;
27		30
		31	import java.io.FileInputStream;
		32	import java.io.InputStream;
		33	import java.util.List;
		34
28	public class TestAreaReference extends TestCase {	35	public class TestAreaReference extends TestCase {
29	public TestAreaReference(String s) {	36	public TestAreaReference(String s) {
30	super(s);	37	super(s);
Lines 60-67 Link Here
60	assertEquals("Not Column B", (short)1,myCellReference[0].getCol());	67	assertEquals("Not Column B", (short)1,myCellReference[0].getCol());
61	assertEquals("Not Row 5", 4,myCellReference[0].getRow());	68	assertEquals("Not Row 5", 4,myCellReference[0].getRow());
62	}	69	}
		70
		71	private static class HSSFWB extends HSSFWorkbook {
		72	private HSSFWB(InputStream in) throws Exception {
		73	super(in);
		74	}
		75	public Workbook getWorkbook() {
		76	return super.getWorkbook();
		77	}
		78	}
		79
		80	public void testContiguousReferences() throws Exception {
		81	String refSimple = "$C$10";
		82	String ref2D = "$C$10:$D$11";
		83	String refDCSimple = "$C$10,$D$12,$E$14";
		84	String refDC2D = "$C$10:$C$11,$D$12,$E$14:$E$20";
		85	String refDC3D = "Tabelle1!$C$10:$C$14,Tabelle1!$D$10:$D$12";
		86
		87	// Check that we detect as contiguous properly
		88	assertTrue(AreaReference.isContiguous(refSimple));
		89	assertTrue(AreaReference.isContiguous(ref2D));
		90	assertFalse(AreaReference.isContiguous(refDCSimple));
		91	assertFalse(AreaReference.isContiguous(refDC2D));
		92	assertFalse(AreaReference.isContiguous(refDC3D));
		93
		94	// Check we can only create contiguous entries
		95	new AreaReference(refSimple);
		96	new AreaReference(ref2D);
		97	try {
		98	new AreaReference(refDCSimple);
		99	fail();
		100	} catch(IllegalArgumentException e) {}
		101	try {
		102	new AreaReference(refDC2D);
		103	fail();
		104	} catch(IllegalArgumentException e) {}
		105	try {
		106	new AreaReference(refDC3D);
		107	fail();
		108	} catch(IllegalArgumentException e) {}
		109
		110	// Test that we split as expected
		111	AreaReference[] refs;
		112
		113	refs = AreaReference.generateContiguous(refSimple);
		114	assertEquals(1, refs.length);
		115	assertEquals(1, refs[0].getDim());
		116	assertEquals("$C$10", refs[0].toString());
		117
		118	refs = AreaReference.generateContiguous(ref2D);
		119	assertEquals(1, refs.length);
		120	assertEquals(2, refs[0].getDim());
		121	assertEquals("$C$10:$D$11", refs[0].toString());
		122
		123	refs = AreaReference.generateContiguous(refDCSimple);
		124	assertEquals(3, refs.length);
		125	assertEquals(1, refs[0].getDim());
		126	assertEquals(1, refs[1].getDim());
		127	assertEquals(1, refs[2].getDim());
		128	assertEquals("$C$10", refs[0].toString());
		129	assertEquals("$D$12", refs[1].toString());
		130	assertEquals("$E$14", refs[2].toString());
		131
		132	refs = AreaReference.generateContiguous(refDC2D);
		133	assertEquals(3, refs.length);
134	assertEquals(2, refs[0].getDim());
135	assertEquals(1, refs[1].getDim());
136	assertEquals(2, refs[2].getDim());
137	assertEquals("$C$10:$C$11", refs[0].toString());
138	assertEquals("$D$12", refs[1].toString());
139	assertEquals("$E$14:$E$20", refs[2].toString());
140
141	refs = AreaReference.generateContiguous(refDC3D);
142	assertEquals(2, refs.length);
143	assertEquals(2, refs[0].getDim());
144	assertEquals(2, refs[1].getDim());
145	assertEquals("$C$10:$C$14", refs[0].toString());
146	assertEquals("$D$10:$D$12", refs[1].toString());
147	assertEquals("Tabelle1", refs[0].getCells()[0].getSheetName());
148	assertEquals("Tabelle1", refs[0].getCells()[1].getSheetName());
149	assertEquals("Tabelle1", refs[1].getCells()[0].getSheetName());
150	assertEquals("Tabelle1", refs[1].getCells()[1].getSheetName());
151	}
152
153	public void testDiscontinousReference() throws Exception {
154	String filename = System.getProperty( "HSSF.testdata.path" );
155	filename = filename + "/44167.xls";
156	FileInputStream fin = new FileInputStream( filename );
157	HSSFWB wb = new HSSFWB( fin );
158	Workbook workbook = wb.getWorkbook();
159	fin.close();
160
161	assertEquals(1, wb.getNumberOfNames());
162	String sheetName = "Tabelle1";
163	String rawRefA = "$C$10:$C$14";
164	String rawRefB = "$C$16:$C$18";
165	String refA = sheetName + "!" + rawRefA;
166	String refB = sheetName + "!" + rawRefB;
167	String ref = refA + "," + refB;
168
169	// Check the low level record
170	NameRecord nr = workbook.getNameRecord(0);
171	assertNotNull(nr);
172	assertEquals("test", nr.getNameText());
173
174	List def =nr.getNameDefinition();
175	assertEquals(4, def.size());
176
177	MemFuncPtg ptgA = (MemFuncPtg)def.get(0);
178	Area3DPtg ptgB = (Area3DPtg)def.get(1);
179	Area3DPtg ptgC = (Area3DPtg)def.get(2);
180	UnionPtg ptgD = (UnionPtg)def.get(3);
181	assertEquals("", ptgA.toFormulaString(workbook));
182	assertEquals(refA, ptgB.toFormulaString(workbook));
183	assertEquals(refB, ptgC.toFormulaString(workbook));
184	assertEquals(",", ptgD.toFormulaString(workbook));
185
186	assertEquals(ref, nr.getAreaReference(workbook));
187
188	// Check the high level definition
189	int idx = wb.getNameIndex("test");
190	assertEquals(0, idx);
191	HSSFName aNamedCell = wb.getNameAt(idx);
192
193	// Should have 2 references
194	assertEquals(ref, aNamedCell.getReference());
195
196	// Check the parsing of the reference into cells
197	assertFalse(AreaReference.isContiguous(aNamedCell.getReference()));
198	AreaReference[] arefs = AreaReference.generateContiguous(aNamedCell.getReference());
199	assertEquals(2, arefs.length);
200	assertEquals(rawRefA, arefs[0].toString());
201	assertEquals(rawRefB, arefs[1].toString());
202
203	for(int i=0; i<arefs.length; i++) {
204	CellReference[] crefs = arefs[i].getCells();
205	for (int j=0; j<crefs.length; j++) {
206	// Check it turns into real stuff
207	HSSFSheet s = wb.getSheet(crefs[j].getSheetName());
208	HSSFRow r = s.getRow(crefs[j].getRow());
209	HSSFCell c = r.getCell(crefs[j].getCol());
210	}
211	}
212	}
63		213
64	public static void main(java.lang.String[] args) {	214	public static void main(java.lang.String[] args) {
65	junit.textui.TestRunner.run(TestAreaReference.class);	215	junit.textui.TestRunner.run(TestAreaReference.class);
66	}	216	}
67		217

Return to bug 44167

Lines 1159-1165 Link Here

(-)src/documentation/content/xdocs/hssf/quick-guide.xml (+27 lines)
1159	// extract the cell contents based on cell type etc.	1159	// extract the cell contents based on cell type etc.
1160	}	1160	}
1161	</source>	1161	</source>
		1162	<p>
		1163	Reading from non-contiguous Named Ranges
		1164	</p>
		1165	<source>
		1166	// Setup code
		1167	String cname = "TestName";
		1168	HSSFWorkbook wb = getMyWorkbook(); // retrieve workbook
1162		1169
		1170	// Retrieve the named range
		1171	// Will be something like "$C$10,$D$12:$D$14";
		1172	int namedCellIdx = wb.getNameIndex(cellName);
		1173	HSSFName aNamedCell = wb.getNameAt(namedCellIdx);
		1174
		1175	// Retrieve the cell at the named range and test its contents
		1176	// Will get back one AreaReference for C10, and
		1177	// another for D12 to D14
		1178	AreaReference[] arefs = AreaReference.generateContiguous(aNamedCell.getReference());
		1179	for(int i=0; i<arefs.length; i++) {
		1180	CellReference[] crefs = arefs[i].getCells();
		1181	for (int j=0; j<crefs.length; j++) {
		1182	// Check it turns into real stuff
		1183	HSSFSheet s = wb.getSheet(crefs[j].getSheetName());
		1184	HSSFRow r = s.getRow(crefs[j].getRow());
		1185	HSSFCell c = r.getCell(crefs[j].getCol());
		1186	// extract the cell contents based on cell type etc.
		1187	}
		1188	}
		1189	</source>
1163	</section>	1190	</section>
1164	<anchor id="CellComments"/>	1191	<anchor id="CellComments"/>
1165	<section><title>Cell Comments</title>	1192	<section><title>Cell Comments</title>

Lines 20-32 Link Here

(-)src/java/org/apache/poi/hssf/record/NameRecord.java (-11 / +37 lines)
20		20
21	import java.util.List;	21	import java.util.List;
22	import java.util.Stack;	22	import java.util.Stack;
		23	import java.util.Iterator;
23		24
24	import org.apache.poi.hssf.model.Workbook;	25	import org.apache.poi.hssf.model.Workbook;
25	import org.apache.poi.hssf.record.formula.Area3DPtg;	26	import org.apache.poi.hssf.record.formula.*;
26	import org.apache.poi.hssf.record.formula.DeletedArea3DPtg;
27	import org.apache.poi.hssf.record.formula.DeletedRef3DPtg;
28	import org.apache.poi.hssf.record.formula.Ptg;
29	import org.apache.poi.hssf.record.formula.Ref3DPtg;
30	import org.apache.poi.hssf.util.RangeAddress;	27	import org.apache.poi.hssf.util.RangeAddress;
31	import org.apache.poi.util.HexDump;	28	import org.apache.poi.util.HexDump;
32	import org.apache.poi.util.LittleEndian;	29	import org.apache.poi.util.LittleEndian;
Lines 648-664 Link Here
648	Ptg ptg = (Ptg) field_13_name_definition.peek();	645	Ptg ptg = (Ptg) field_13_name_definition.peek();
649	String result = "";	646	String result = "";
650		647
651	if (ptg.getClass() == Area3DPtg.class){	648	// If it's a union, descend in and process
652	result = ptg.toFormulaString(book);	649	if (ptg.getClass() == UnionPtg.class) {
		650	Iterator it =field_13_name_definition.iterator();
		651	while( it.hasNext() ) {
		652	Ptg p = (Ptg)it.next();
653		653
654	} else if (ptg.getClass() == Ref3DPtg.class){	654	String thisRes = getAreaRefString(p, book);
655	result = ptg.toFormulaString(book);	655	if(thisRes.length() > 0) {
656	} else if (ptg.getClass() == DeletedArea3DPtg.class \|\| ptg.getClass() == DeletedRef3DPtg.class) {	656	// Add a comma to the end if needed
657	result = "#REF!" ; }	657	if(result.length() > 0 && !result.endsWith(",")) {
		658	result += ",";
		659	}
		660	// And add the string it corresponds to
		661	result += thisRes;
		662	}
		663	}
		664	} else {
		665	// Otherwise just get the string
		666	result = getAreaRefString(ptg, book);
		667	}
658		668
659	return result;	669	return result;
660	}	670	}
661		671
		672	/**
		673	* Turn the given ptg into a string, or
		674	* return an empty string if nothing is possible
		675	* for it.
		676	*/
		677	private String getAreaRefString(Ptg ptg,Workbook book) {
		678	if (ptg.getClass() == Area3DPtg.class){
		679	return ptg.toFormulaString(book);
		680	} else if (ptg.getClass() == Ref3DPtg.class){
		681	return ptg.toFormulaString(book);
		682	} else if (ptg.getClass() == DeletedArea3DPtg.class \|\| ptg.getClass() == DeletedRef3DPtg.class) {
		683	return "#REF!";
		684	}
		685	return "";
		686	}
		687
662	/** sets the reference , the area only (range)	688	/** sets the reference , the area only (range)
663	* @param ref area reference	689	* @param ref area reference
664	*/	690	*/