001: /* ====================================================================
002: Licensed to the Apache Software Foundation (ASF) under one or more
003: contributor license agreements. See the NOTICE file distributed with
004: this work for additional information regarding copyright ownership.
005: The ASF licenses this file to You under the Apache License, Version 2.0
006: (the "License"); you may not use this file except in compliance with
007: the License. You may obtain a copy of the License at
008:
009: http://www.apache.org/licenses/LICENSE-2.0
010:
011: Unless required by applicable law or agreed to in writing, software
012: distributed under the License is distributed on an "AS IS" BASIS,
013: WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
014: See the License for the specific language governing permissions and
015: limitations under the License.
016: ==================================================================== */
017:
018: package org.apache.poi.hssf.usermodel;
019:
020: import junit.framework.TestCase;
021:
022: import org.apache.poi.hssf.util.AreaReference;
023: import org.apache.poi.hssf.util.CellReference;
024: import org.apache.poi.poifs.filesystem.POIFSFileSystem;
025: import org.apache.poi.util.TempFile;
026:
027: import java.io.File;
028: import java.io.FileInputStream;
029: import java.io.FileOutputStream;
030: import java.io.IOException;
031:
032: /**
033: *
034: * @author ROMANL
035: * @author Andrew C. Oliver (acoliver at apache dot org)
036: * @author Danny Mui (danny at muibros.com)
037: * @author Amol S. Deshmukh < amol at ap ache dot org >
038: */
039: public class TestNamedRange extends TestCase {
040:
041: public TestNamedRange(String testName) {
042: super (testName);
043: }
044:
045: public static void main(java.lang.String[] args) {
046: String filename = System.getProperty("HSSF.testdata.path");
047:
048: // assume andy is running this in the debugger
049: if (filename == null) {
050: if (args != null && args.length == 1) {
051: System.setProperty("HSSF.testdata.path", args[0]);
052: } else {
053: System.err
054: .println("Geesh, no HSSF.testdata.path system "
055: + "property, no command line arg with the path "
056: + "what do you expect me to do, guess where teh data "
057: + "files are? Sorry, I give up!");
058:
059: }
060:
061: }
062:
063: junit.textui.TestRunner.run(TestNamedRange.class);
064: }
065:
066: /** Test of TestCase method, of class test.RangeTest. */
067: public void testNamedRange() throws IOException {
068: FileInputStream fis = null;
069: POIFSFileSystem fs = null;
070: HSSFWorkbook wb = null;
071:
072: String filename = System.getProperty("HSSF.testdata.path");
073:
074: filename = filename + "/Simple.xls";
075:
076: fis = new FileInputStream(filename);
077: fs = new POIFSFileSystem(fis);
078: wb = new HSSFWorkbook(fs);
079:
080: //Creating new Named Range
081: HSSFName newNamedRange = wb.createName();
082:
083: //Getting Sheet Name for the reference
084: String sheetName = wb.getSheetName(0);
085:
086: //Setting its name
087: newNamedRange.setNameName("RangeTest");
088: //Setting its reference
089: newNamedRange.setReference(sheetName + "!$D$4:$E$8");
090:
091: //Getting NAmed Range
092: HSSFName namedRange1 = wb.getNameAt(0);
093: //Getting it sheet name
094: sheetName = namedRange1.getSheetName();
095: //Getting its reference
096: String referece = namedRange1.getReference();
097:
098: // sanity check
099: SanityChecker c = new SanityChecker();
100: c.checkHSSFWorkbook(wb);
101:
102: File file = TempFile.createTempFile("testNamedRange", ".xls");
103:
104: FileOutputStream fileOut = new FileOutputStream(file);
105: wb.write(fileOut);
106: fis.close();
107: fileOut.close();
108:
109: assertTrue("file exists", file.exists());
110:
111: FileInputStream in = new FileInputStream(file);
112: wb = new HSSFWorkbook(in);
113: HSSFName nm = wb.getNameAt(wb.getNameIndex("RangeTest"));
114: assertTrue("Name is " + nm.getNameName(), "RangeTest".equals(nm
115: .getNameName()));
116: assertEquals(wb.getSheetName(0) + "!$D$4:$E$8", nm
117: .getReference());
118:
119: }
120:
121: /**
122: * Reads an excel file already containing a named range.
123: * <p>
124: * Addresses Bug <a href="http://issues.apache.org/bugzilla/show_bug.cgi?id=9632" target="_bug">#9632</a>
125: */
126: public void testNamedRead() throws IOException {
127: FileInputStream fis = null;
128: POIFSFileSystem fs = null;
129: HSSFWorkbook wb = null;
130:
131: String filename = System.getProperty("HSSF.testdata.path");
132:
133: filename = filename + "/namedinput.xls";
134:
135: fis = new FileInputStream(filename);
136: fs = new POIFSFileSystem(fis);
137: wb = new HSSFWorkbook(fs);
138:
139: //Get index of the namedrange with the name = "NamedRangeName" , which was defined in input.xls as A1:D10
140: int NamedRangeIndex = wb.getNameIndex("NamedRangeName");
141:
142: //Getting NAmed Range
143: HSSFName namedRange1 = wb.getNameAt(NamedRangeIndex);
144: String sheetName = wb.getSheetName(0);
145:
146: //Getting its reference
147: String reference = namedRange1.getReference();
148:
149: fis.close();
150:
151: assertEquals(sheetName + "!$A$1:$D$10", reference);
152:
153: HSSFName namedRange2 = wb.getNameAt(1);
154:
155: assertEquals(sheetName + "!$D$17:$G$27", namedRange2
156: .getReference());
157: assertEquals("SecondNamedRange", namedRange2.getNameName());
158:
159: }
160:
161: /**
162: * Reads an excel file already containing a named range and updates it
163: * <p>
164: * Addresses Bug <a href="http://issues.apache.org/bugzilla/show_bug.cgi?id=16411" target="_bug">#16411</a>
165: */
166: public void testNamedReadModify() throws IOException {
167: FileInputStream fis = null;
168: POIFSFileSystem fs = null;
169: HSSFWorkbook wb = null;
170:
171: String filename = System.getProperty("HSSF.testdata.path");
172:
173: filename = filename + "/namedinput.xls";
174:
175: fis = new FileInputStream(filename);
176: fs = new POIFSFileSystem(fis);
177: wb = new HSSFWorkbook(fs);
178:
179: HSSFName name = wb.getNameAt(0);
180: String sheetName = wb.getSheetName(0);
181:
182: assertEquals(sheetName + "!$A$1:$D$10", name.getReference());
183:
184: name = wb.getNameAt(1);
185: String newReference = sheetName + "!$A$1:$C$36";
186:
187: name.setReference(newReference);
188: assertEquals(newReference, name.getReference());
189:
190: }
191:
192: /**
193: * Test that multiple named ranges can be added written and read
194: */
195: public void testMultipleNamedWrite() throws IOException {
196: HSSFWorkbook wb = new HSSFWorkbook();
197:
198: HSSFSheet sheet = wb.createSheet("testSheet1");
199: String sheetName = wb.getSheetName(0);
200:
201: assertEquals("testSheet1", sheetName);
202:
203: //Creating new Named Range
204: HSSFName newNamedRange = wb.createName();
205:
206: newNamedRange.setNameName("RangeTest");
207: newNamedRange.setReference(sheetName + "!$D$4:$E$8");
208:
209: //Creating another new Named Range
210: HSSFName newNamedRange2 = wb.createName();
211:
212: newNamedRange2.setNameName("AnotherTest");
213: newNamedRange2.setReference(sheetName + "!$F$1:$G$6");
214:
215: HSSFName namedRange1 = wb.getNameAt(0);
216: String referece = namedRange1.getReference();
217:
218: File file = TempFile.createTempFile("testMultiNamedRange",
219: ".xls");
220:
221: FileOutputStream fileOut = new FileOutputStream(file);
222: wb.write(fileOut);
223: fileOut.close();
224:
225: assertTrue("file exists", file.exists());
226:
227: FileInputStream in = new FileInputStream(file);
228: wb = new HSSFWorkbook(in);
229: HSSFName nm = wb.getNameAt(wb.getNameIndex("RangeTest"));
230: assertTrue("Name is " + nm.getNameName(), "RangeTest".equals(nm
231: .getNameName()));
232: assertTrue("Reference is " + nm.getReference(), (wb
233: .getSheetName(0) + "!$D$4:$E$8").equals(nm
234: .getReference()));
235:
236: nm = wb.getNameAt(wb.getNameIndex("AnotherTest"));
237: assertTrue("Name is " + nm.getNameName(), "AnotherTest"
238: .equals(nm.getNameName()));
239: assertTrue("Reference is " + nm.getReference(), newNamedRange2
240: .getReference().equals(nm.getReference()));
241:
242: }
243:
244: /**
245: * Test case provided by czhang@cambian.com (Chun Zhang)
246: * <p>
247: * Addresses Bug <a href="http://issues.apache.org/bugzilla/show_bug.cgi?id=13775" target="_bug">#13775</a>
248: * @throws IOException
249: */
250: public void testMultiNamedRange() throws IOException {
251:
252: // Create a new workbook
253: HSSFWorkbook wb = new HSSFWorkbook();
254:
255: // Create a worksheet 'sheet1' in the new workbook
256: wb.createSheet();
257: wb.setSheetName(0, "sheet1");
258:
259: // Create another worksheet 'sheet2' in the new workbook
260: wb.createSheet();
261: wb.setSheetName(1, "sheet2");
262:
263: // Create a new named range for worksheet 'sheet1'
264: HSSFName namedRange1 = wb.createName();
265:
266: // Set the name for the named range for worksheet 'sheet1'
267: namedRange1.setNameName("RangeTest1");
268:
269: // Set the reference for the named range for worksheet 'sheet1'
270: namedRange1.setReference("sheet1" + "!$A$1:$L$41");
271:
272: // Create a new named range for worksheet 'sheet2'
273: HSSFName namedRange2 = wb.createName();
274:
275: // Set the name for the named range for worksheet 'sheet2'
276: namedRange2.setNameName("RangeTest2");
277:
278: // Set the reference for the named range for worksheet 'sheet2'
279: namedRange2.setReference("sheet2" + "!$A$1:$O$21");
280:
281: // Write the workbook to a file
282: File file = TempFile.createTempFile(
283: "testMuiltipletNamedRanges", ".xls");
284: FileOutputStream fileOut = new FileOutputStream(file);
285: wb.write(fileOut);
286: fileOut.close();
287:
288: assertTrue("file exists", file.exists());
289:
290: // Read the Excel file and verify its content
291: FileInputStream in = new FileInputStream(file);
292: wb = new HSSFWorkbook(in);
293: HSSFName nm1 = wb.getNameAt(wb.getNameIndex("RangeTest1"));
294: assertTrue("Name is " + nm1.getNameName(), "RangeTest1"
295: .equals(nm1.getNameName()));
296: assertTrue("Reference is " + nm1.getReference(), (wb
297: .getSheetName(0) + "!$A$1:$L$41").equals(nm1
298: .getReference()));
299:
300: HSSFName nm2 = wb.getNameAt(wb.getNameIndex("RangeTest2"));
301: assertTrue("Name is " + nm2.getNameName(), "RangeTest2"
302: .equals(nm2.getNameName()));
303: assertTrue("Reference is " + nm2.getReference(), (wb
304: .getSheetName(1) + "!$A$1:$O$21").equals(nm2
305: .getReference()));
306: }
307:
308: /**
309: * Test to see if the print areas can be retrieved/created in memory
310: */
311: public void testSinglePrintArea() {
312: HSSFWorkbook workbook = new HSSFWorkbook();
313: HSSFSheet sheet = workbook.createSheet("Test Print Area");
314: String sheetName = workbook.getSheetName(0);
315:
316: String reference = sheetName + "!$A$1:$B$1";
317: workbook.setPrintArea(0, reference);
318:
319: String retrievedPrintArea = workbook.getPrintArea(0);
320:
321: assertNotNull("Print Area not defined for first sheet",
322: retrievedPrintArea);
323: assertEquals(reference, retrievedPrintArea);
324:
325: }
326:
327: /**
328: * For Convenience, dont force sheet names to be used
329: */
330: public void testSinglePrintAreaWOSheet() {
331: HSSFWorkbook workbook = new HSSFWorkbook();
332: HSSFSheet sheet = workbook.createSheet("Test Print Area");
333: String sheetName = workbook.getSheetName(0);
334:
335: String reference = "$A$1:$B$1";
336: workbook.setPrintArea(0, reference);
337:
338: String retrievedPrintArea = workbook.getPrintArea(0);
339:
340: assertNotNull("Print Area not defined for first sheet",
341: retrievedPrintArea);
342: assertEquals(sheetName + "!" + reference, retrievedPrintArea);
343:
344: }
345:
346: /**
347: * Test to see if the print area can be retrieved from an excel created file
348: */
349: public void testPrintAreaFileRead() throws IOException {
350: FileInputStream fis = null;
351: POIFSFileSystem fs = null;
352: HSSFWorkbook workbook = null;
353:
354: String filename = System.getProperty("HSSF.testdata.path");
355:
356: filename = filename + "/SimpleWithPrintArea.xls";
357:
358: try {
359:
360: fis = new FileInputStream(filename);
361: fs = new POIFSFileSystem(fis);
362: workbook = new HSSFWorkbook(fs);
363:
364: String sheetName = workbook.getSheetName(0);
365: String reference = sheetName + "!$A$1:$C$5";
366:
367: assertEquals(reference, workbook.getPrintArea(0));
368:
369: } finally {
370: fis.close();
371:
372: }
373:
374: }
375:
376: /**
377: * Test to see if the print area made it to the file
378: */
379: public void testPrintAreaFile() throws IOException {
380: HSSFWorkbook workbook = new HSSFWorkbook();
381: HSSFSheet sheet = workbook.createSheet("Test Print Area");
382: String sheetName = workbook.getSheetName(0);
383:
384: String reference = sheetName + "!$A$1:$B$1";
385: workbook.setPrintArea(0, reference);
386:
387: File file = TempFile.createTempFile("testPrintArea", ".xls");
388:
389: FileOutputStream fileOut = new FileOutputStream(file);
390: workbook.write(fileOut);
391: fileOut.close();
392:
393: assertTrue("file exists", file.exists());
394:
395: FileInputStream in = new FileInputStream(file);
396: workbook = new HSSFWorkbook(in);
397:
398: String retrievedPrintArea = workbook.getPrintArea(0);
399: assertNotNull("Print Area not defined for first sheet",
400: retrievedPrintArea);
401: assertEquals("References Match", reference, retrievedPrintArea);
402:
403: }
404:
405: /**
406: * Test to see if multiple print areas made it to the file
407: */
408: public void testMultiplePrintAreaFile() throws IOException {
409: HSSFWorkbook workbook = new HSSFWorkbook();
410:
411: HSSFSheet sheet = workbook.createSheet("Sheet 1");
412: sheet = workbook.createSheet("Sheet 2");
413: sheet = workbook.createSheet("Sheet 3");
414:
415: String sheetName = workbook.getSheetName(0);
416: String reference = null;
417:
418: reference = sheetName + "!$A$1:$B$1";
419: workbook.setPrintArea(0, reference);
420:
421: sheetName = workbook.getSheetName(1);
422: String reference2 = sheetName + "!$B$2:$D$5";
423: workbook.setPrintArea(1, reference2);
424:
425: sheetName = workbook.getSheetName(2);
426: String reference3 = sheetName + "!$D$2:$F$5";
427: workbook.setPrintArea(2, reference3);
428:
429: File file = TempFile.createTempFile("testMultiPrintArea",
430: ".xls");
431:
432: FileOutputStream fileOut = new FileOutputStream(file);
433: workbook.write(fileOut);
434: fileOut.close();
435:
436: assertTrue("file exists", file.exists());
437:
438: FileInputStream in = new FileInputStream(file);
439: workbook = new HSSFWorkbook(in);
440:
441: String retrievedPrintArea = workbook.getPrintArea(0);
442: assertNotNull("Print Area Not Found (Sheet 1)",
443: retrievedPrintArea);
444: assertEquals(reference, retrievedPrintArea);
445:
446: String retrievedPrintArea2 = workbook.getPrintArea(1);
447: assertNotNull("Print Area Not Found (Sheet 2)",
448: retrievedPrintArea2);
449: assertEquals(reference2, retrievedPrintArea2);
450:
451: String retrievedPrintArea3 = workbook.getPrintArea(2);
452: assertNotNull("Print Area Not Found (Sheet 3)",
453: retrievedPrintArea3);
454: assertEquals(reference3, retrievedPrintArea3);
455:
456: }
457:
458: /**
459: * Tests the setting of print areas with coordinates (Row/Column designations)
460: *
461: */
462: public void testPrintAreaCoords() {
463: HSSFWorkbook workbook = new HSSFWorkbook();
464: HSSFSheet sheet = workbook.createSheet("Test Print Area");
465: String sheetName = workbook.getSheetName(0);
466:
467: String reference = sheetName + "!$A$1:$B$1";
468: workbook.setPrintArea(0, 0, 1, 0, 0);
469:
470: String retrievedPrintArea = workbook.getPrintArea(0);
471:
472: assertNotNull("Print Area not defined for first sheet",
473: retrievedPrintArea);
474: assertEquals(reference, retrievedPrintArea);
475: }
476:
477: /**
478: * Verifies an existing print area is deleted
479: *
480: */
481: public void testPrintAreaRemove() {
482: HSSFWorkbook workbook = new HSSFWorkbook();
483: HSSFSheet sheet = workbook.createSheet("Test Print Area");
484: String sheetName = workbook.getSheetName(0);
485:
486: String reference = sheetName + "!$A$1:$B$1";
487: workbook.setPrintArea(0, 0, 1, 0, 0);
488:
489: String retrievedPrintArea = workbook.getPrintArea(0);
490:
491: assertNotNull("Print Area not defined for first sheet",
492: retrievedPrintArea);
493:
494: workbook.removePrintArea(0);
495: assertNull("PrintArea was not removed", workbook
496: .getPrintArea(0));
497: }
498:
499: /**
500: * Verifies correct functioning for "single cell named range" (aka "named cell")
501: */
502: public void testNamedCell_1() {
503:
504: // setup for this testcase
505: String sheetName = "Test Named Cell";
506: String cellName = "A name for a named cell";
507: String cellValue = "TEST Value";
508: HSSFWorkbook wb = new HSSFWorkbook();
509: HSSFSheet sheet = wb.createSheet(sheetName);
510: sheet.createRow(0).createCell((short) 0)
511: .setCellValue(cellValue);
512:
513: // create named range for a single cell using areareference
514: HSSFName namedCell = wb.createName();
515: namedCell.setNameName(cellName);
516: String reference = sheetName + "!A1:A1";
517: namedCell.setReference(reference);
518:
519: // retrieve the newly created named range
520: int namedCellIdx = wb.getNameIndex(cellName);
521: HSSFName aNamedCell = wb.getNameAt(namedCellIdx);
522: assertNotNull(aNamedCell);
523:
524: // retrieve the cell at the named range and test its contents
525: AreaReference aref = new AreaReference(aNamedCell
526: .getReference());
527: CellReference[] crefs = aref.getCells();
528: assertNotNull(crefs);
529: assertEquals("Should be exactly 1 cell in the named cell :'"
530: + cellName + "'", 1, crefs.length);
531: for (int i = 0, iSize = crefs.length; i < iSize; i++) {
532: CellReference cref = crefs[i];
533: assertNotNull(cref);
534: HSSFSheet s = wb.getSheet(cref.getSheetName());
535: HSSFRow r = sheet.getRow(cref.getRow());
536: HSSFCell c = r.getCell(cref.getCol());
537: String contents = c.getStringCellValue();
538: assertEquals(
539: "Contents of cell retrieved by its named reference",
540: contents, cellValue);
541: }
542: }
543:
544: /**
545: * Verifies correct functioning for "single cell named range" (aka "named cell")
546: */
547: public void testNamedCell_2() {
548:
549: // setup for this testcase
550: String sname = "TestSheet", cname = "TestName", cvalue = "TestVal";
551: HSSFWorkbook wb = new HSSFWorkbook();
552: HSSFSheet sheet = wb.createSheet(sname);
553: sheet.createRow(0).createCell((short) 0).setCellValue(cvalue);
554:
555: // create named range for a single cell using cellreference
556: HSSFName namedCell = wb.createName();
557: namedCell.setNameName(cname);
558: String reference = sname + "!A1";
559: namedCell.setReference(reference);
560:
561: // retrieve the newly created named range
562: int namedCellIdx = wb.getNameIndex(cname);
563: HSSFName aNamedCell = wb.getNameAt(namedCellIdx);
564: assertNotNull(aNamedCell);
565:
566: // retrieve the cell at the named range and test its contents
567: CellReference cref = new CellReference(aNamedCell
568: .getReference());
569: assertNotNull(cref);
570: HSSFSheet s = wb.getSheet(cref.getSheetName());
571: HSSFRow r = sheet.getRow(cref.getRow());
572: HSSFCell c = r.getCell(cref.getCol());
573: String contents = c.getStringCellValue();
574: assertEquals(
575: "Contents of cell retrieved by its named reference",
576: contents, cvalue);
577: }
578:
579: }
|