Java > Open Source Codes > org > apache > poi > hssf > usermodel > TestNamedRange


1
2 /* ====================================================================
3    Copyright 2002-2004 Apache Software Foundation
4
5    Licensed under the Apache License, Version 2.0 (the "License");
6    you may not use this file except in compliance with the License.
7    You may obtain a copy of the License at
8
9        http://www.apache.org/licenses/LICENSE-2.0
10
11    Unless required by applicable law or agreed to in writing, software
12    distributed under the License is distributed on an "AS IS" BASIS,
13    WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
14    See the License for the specific language governing permissions and
15    limitations under the License.
16 ==================================================================== */
17         
18
19 package org.apache.poi.hssf.usermodel;
20
21 import junit.framework.TestCase;
22 import org.apache.poi.poifs.filesystem.POIFSFileSystem;
23 import org.apache.poi.util.TempFile;
24
25 import java.io.File ;
26 import java.io.FileInputStream ;
27 import java.io.FileOutputStream ;
28 import java.io.IOException ;
29
30
31 /**
32  *
33  * @author ROMANL
34  * @author Andrew C. Oliver (acoliver at apache dot org)
35  * @author Danny Mui (danny at muibros.com)
36  */
37 public class TestNamedRange
38     extends TestCase {
39     
40     public TestNamedRange(String testName) {
41         super(testName);
42     }
43     
44     public static void main(java.lang.String [] args) {
45         String filename = System.getProperty("HSSF.testdata.path");
46         
47         // assume andy is running this in the debugger
48 if (filename == null)
49         {
50             if (args != null && args.length == 1) {
51             System.setProperty(
52                 "HSSF.testdata.path",
53                 args[0]);
54             } else {
55                 System.err.println("Geesh, no HSSF.testdata.path system " +
56                           "property, no command line arg with the path "+
57                           "what do you expect me to do, guess where teh data " +
58                           "files are? Sorry, I give up!");
59                                    
60             }
61             
62         }
63         
64         
65         junit.textui.TestRunner.run(TestNamedRange.class);
66     }
67     
68     /** Test of TestCase method, of class test.RangeTest. */
69     public void testNamedRange()
70         throws IOException
71     {
72         FileInputStream fis = null;
73         POIFSFileSystem fs = null;
74         HSSFWorkbook wb = null;
75         
76         String filename = System.getProperty("HSSF.testdata.path");
77
78         filename = filename + "/Simple.xls";
79         
80         
81         fis = new FileInputStream (filename);
82         fs = new POIFSFileSystem(fis);
83         wb = new HSSFWorkbook(fs);
84         
85         
86         //Creating new Named Range
87 HSSFName newNamedRange = wb.createName();
88         
89         //Getting Sheet Name for the reference
90 String sheetName = wb.getSheetName(0);
91         
92         //Setting its name
93 newNamedRange.setNameName("RangeTest");
94         //Setting its reference
95 newNamedRange.setReference(sheetName + "!$D$4:$E$8");
96   
97         //Getting NAmed Range
98 HSSFName namedRange1 = wb.getNameAt(0);
99         //Getting it sheet name
100 sheetName = namedRange1.getSheetName();
101         //Getting its reference
102 String referece = namedRange1.getReference();
103
104         // sanity check
105 SanityChecker c = new SanityChecker();
106         c.checkHSSFWorkbook(wb);
107
108         File file = TempFile.createTempFile("testNamedRange",
109                                         ".xls");
110
111         FileOutputStream fileOut = new FileOutputStream (file);
112         wb.write(fileOut);
113         fis.close();
114         fileOut.close();
115         
116         assertTrue("file exists",file.exists());
117             
118         FileInputStream in = new FileInputStream (file);
119         wb = new HSSFWorkbook(in);
120         HSSFName nm =wb.getNameAt(wb.getNameIndex("RangeTest"));
121         assertTrue("Name is "+nm.getNameName(),"RangeTest".equals(nm.getNameName()));
122         assertEquals(wb.getSheetName(0)+"!$D$4:$E$8", nm.getReference());
123         
124         
125     }
126      
127     /**
128      * Reads an excel file already containing a named range.
129      * <p>
130      * Addresses Bug <a HREF="http://issues.apache.org/bugzilla/show_bug.cgi?id=9632" target="_bug">#9632</a>
131      */
132     public void testNamedRead() throws IOException
133     {
134         FileInputStream fis = null;
135         POIFSFileSystem fs = null;
136         HSSFWorkbook wb = null;
137         
138         String filename = System.getProperty("HSSF.testdata.path");
139
140         filename = filename + "/namedinput.xls";
141         
142         
143         fis = new FileInputStream (filename);
144         fs = new POIFSFileSystem(fis);
145         wb = new HSSFWorkbook(fs);
146
147         //Get index of the namedrange with the name = "NamedRangeName" , which was defined in input.xls as A1:D10
148 int NamedRangeIndex = wb.getNameIndex("NamedRangeName");
149
150         //Getting NAmed Range
151 HSSFName namedRange1 = wb.getNameAt(NamedRangeIndex);
152         String sheetName = wb.getSheetName(0);
153
154         //Getting its reference
155 String reference = namedRange1.getReference();
156          
157         fis.close();
158          
159         assertEquals(sheetName+"!$A$1:$D$10", reference);
160         
161         HSSFName namedRange2 = wb.getNameAt(1);
162         
163         assertEquals(sheetName+"!$D$17:$G$27", namedRange2.getReference());
164         assertEquals("SecondNamedRange", namedRange2.getNameName());
165         
166     }
167         
168     /**
169      * Reads an excel file already containing a named range and updates it
170      * <p>
171      * Addresses Bug <a HREF="http://issues.apache.org/bugzilla/show_bug.cgi?id=16411" target="_bug">#16411</a>
172      */
173     public void testNamedReadModify() throws IOException
174     {
175         FileInputStream fis = null;
176         POIFSFileSystem fs = null;
177         HSSFWorkbook wb = null;
178         
179         String filename = System.getProperty("HSSF.testdata.path");
180
181         filename = filename + "/namedinput.xls";
182         
183         
184         fis = new FileInputStream (filename);
185         fs = new POIFSFileSystem(fis);
186         wb = new HSSFWorkbook(fs);
187         
188         
189         HSSFName name = wb.getNameAt(0);
190         String sheetName = wb.getSheetName(0);
191         
192         assertEquals(sheetName+"!$A$1:$D$10", name.getReference());
193         
194         name = wb.getNameAt(1);
195         String newReference = sheetName +"!$A$1:$C$36";
196          
197         name.setReference(newReference);
198         assertEquals(newReference, name.getReference());
199
200     }
201         
202     /**
203      * Test that multiple named ranges can be added written and read
204      */
205     public void testMultipleNamedWrite()
206         throws IOException
207     {
208         HSSFWorkbook wb = new HSSFWorkbook();
209          
210
211         HSSFSheet sheet = wb.createSheet("Sheet1");
212         String sheetName = wb.getSheetName(0);
213         
214         assertEquals("Sheet1", sheetName);
215          
216         //Creating new Named Range
217 HSSFName newNamedRange = wb.createName();
218
219         newNamedRange.setNameName("RangeTest");
220         newNamedRange.setReference(sheetName + "!$D$4:$E$8");
221
222         //Creating another new Named Range
223 HSSFName newNamedRange2 = wb.createName();
224         
225         newNamedRange2.setNameName("AnotherTest");
226         newNamedRange2.setReference(sheetName + "!$F$1:$G$6");
227         
228         
229         HSSFName namedRange1 = wb.getNameAt(0);
230         String referece = namedRange1.getReference();
231                                 
232         File file = TempFile.createTempFile("testMultiNamedRange", ".xls");
233  
234         FileOutputStream fileOut = new FileOutputStream (file);
235         wb.write(fileOut);
236         fileOut.close();
237         
238          
239         assertTrue("file exists",file.exists());
240             
241         
242         FileInputStream in = new FileInputStream (file);
243         wb = new HSSFWorkbook(in);
244         HSSFName nm =wb.getNameAt(wb.getNameIndex("RangeTest"));
245         assertTrue("Name is "+nm.getNameName(),"RangeTest".equals(nm.getNameName()));
246         assertTrue("Reference is "+nm.getReference(),(wb.getSheetName(0)+"!$D$4:$E$8").equals(nm.getReference()));
247          
248         nm = wb.getNameAt(wb.getNameIndex("AnotherTest"));
249         assertTrue("Name is "+nm.getNameName(),"AnotherTest".equals(nm.getNameName()));
250         assertTrue("Reference is "+nm.getReference(),newNamedRange2.getReference().equals(nm.getReference()));
251         
252         
253     }
254
255     /**
256      * Test case provided by czhang@cambian.com (Chun Zhang)
257      * <p>
258      * Addresses Bug <a HREF="http://issues.apache.org/bugzilla/show_bug.cgi?id=13775" target="_bug">#13775</a>
259      * @throws IOException
260      */
261     public void testMultiNamedRange()
262          throws IOException
263      {
264         
265          // Create a new workbook
266 HSSFWorkbook wb = new HSSFWorkbook ();
267
268
269          // Create a worksheet 'sheet1' in the new workbook
270 wb.createSheet ();
271          wb.setSheetName (0, "sheet1");
272         
273          // Create another worksheet 'sheet2' in the new workbook
274 wb.createSheet ();
275          wb.setSheetName (1, "sheet2");
276         
277          // Create a new named range for worksheet 'sheet1'
278 HSSFName namedRange1 = wb.createName();
279         
280          // Set the name for the named range for worksheet 'sheet1'
281 namedRange1.setNameName("RangeTest1");
282
283          // Set the reference for the named range for worksheet 'sheet1'
284 namedRange1.setReference("sheet1" + "!$A$1:$L$41");
285
286          // Create a new named range for worksheet 'sheet2'
287 HSSFName namedRange2 = wb.createName();
288         
289          // Set the name for the named range for worksheet 'sheet2'
290 namedRange2.setNameName("RangeTest2");
291
292          // Set the reference for the named range for worksheet 'sheet2'
293 namedRange2.setReference("sheet2" + "!$A$1:$O$21");
294  
295          // Write the workbook to a file
296 File file = TempFile.createTempFile("testMuiltipletNamedRanges", ".xls");
297          FileOutputStream fileOut = new FileOutputStream (file);
298          wb.write(fileOut);
299          fileOut.close();
300         
301          assertTrue("file exists",file.exists());
302             
303          // Read the Excel file and verify its content
304 FileInputStream in = new FileInputStream (file);
305          wb = new HSSFWorkbook(in);
306          HSSFName nm1 =wb.getNameAt(wb.getNameIndex("RangeTest1"));
307          assertTrue("Name is "+nm1.getNameName(),"RangeTest1".equals(nm1.getNameName()));
308          assertTrue("Reference is "+nm1.getReference(),(wb.getSheetName(0)+"!$A$1:$L$41").equals(nm1.getReference()));
309
310          HSSFName nm2 =wb.getNameAt(wb.getNameIndex("RangeTest2"));
311          assertTrue("Name is "+nm2.getNameName(),"RangeTest2".equals(nm2.getNameName()));
312          assertTrue("Reference is "+nm2.getReference(),(wb.getSheetName(1)+"!$A$1:$O$21").equals(nm2.getReference()));
313      }
314         
315         
316      /**
317       * Test to see if the print areas can be retrieved/created in memory
318       */
319      public void testSinglePrintArea()
320      {
321          HSSFWorkbook workbook = new HSSFWorkbook();
322          HSSFSheet sheet = workbook.createSheet("Test Print Area");
323          String sheetName = workbook.getSheetName(0);
324          
325          String reference = sheetName+"!$A$1:$B$1";
326          workbook.setPrintArea(0, reference);
327                  
328          String retrievedPrintArea = workbook.getPrintArea(0);
329         
330          assertNotNull("Print Area not defined for first sheet", retrievedPrintArea);
331          assertEquals(reference, retrievedPrintArea);
332          
333      }
334
335      /**
336       * For Convenience, dont force sheet names to be used
337       */
338      public void testSinglePrintAreaWOSheet()
339      {
340          HSSFWorkbook workbook = new HSSFWorkbook();
341          HSSFSheet sheet = workbook.createSheet("Test Print Area");
342          String sheetName = workbook.getSheetName(0);
343          
344          String reference = "$A$1:$B$1";
345          workbook.setPrintArea(0, reference);
346                  
347          String retrievedPrintArea = workbook.getPrintArea(0);
348         
349          assertNotNull("Print Area not defined for first sheet", retrievedPrintArea);
350          assertEquals(sheetName+"!"+reference, retrievedPrintArea);
351          
352      }
353
354
355      /**
356       * Test to see if the print area can be retrieved from an excel created file
357       */
358      public void testPrintAreaFileRead()
359      throws IOException
360      {
361         FileInputStream fis = null;
362         POIFSFileSystem fs = null;
363         HSSFWorkbook workbook = null;
364         
365         String filename = System.getProperty("HSSF.testdata.path");
366
367         filename = filename + "/SimpleWithPrintArea.xls";
368         
369         try {
370         
371             fis = new FileInputStream (filename);
372             fs = new POIFSFileSystem(fis);
373             workbook = new HSSFWorkbook(fs);
374             
375             String sheetName = workbook.getSheetName(0);
376             String reference = sheetName+"!$A$1:$C$5";
377             
378             assertEquals(reference, workbook.getPrintArea(0));
379         
380         } finally {
381             fis.close();
382         
383         }
384         
385              
386          
387     }
388
389
390      /**
391       * Test to see if the print area made it to the file
392       */
393      public void testPrintAreaFile()
394      throws IOException
395      {
396         HSSFWorkbook workbook = new HSSFWorkbook();
397         HSSFSheet sheet = workbook.createSheet("Test Print Area");
398         String sheetName = workbook.getSheetName(0);
399          
400      
401         String reference = sheetName+"!$A$1:$B$1";
402         workbook.setPrintArea(0, reference);
403          
404         File file = TempFile.createTempFile("testPrintArea",".xls");
405          
406         FileOutputStream fileOut = new FileOutputStream (file);
407         workbook.write(fileOut);
408         fileOut.close();
409          
410         assertTrue("file exists",file.exists());
411          
412         FileInputStream in = new FileInputStream (file);
413         workbook = new HSSFWorkbook(in);
414          
415         String retrievedPrintArea = workbook.getPrintArea(0);
416         assertNotNull("Print Area not defined for first sheet", retrievedPrintArea);
417         assertEquals("References Match", reference, retrievedPrintArea);
418          
419     }
420
421     /**
422      * Test to see if multiple print areas made it to the file
423      */
424     public void testMultiplePrintAreaFile()
425     throws IOException
426     {
427         HSSFWorkbook workbook = new HSSFWorkbook();
428         
429         HSSFSheet sheet = workbook.createSheet("Sheet 1");
430         sheet = workbook.createSheet("Sheet 2");
431         sheet = workbook.createSheet("Sheet 3");
432         
433         String sheetName = workbook.getSheetName(0);
434         String reference = null;
435
436         reference = sheetName+"!$A$1:$B$1";
437         workbook.setPrintArea(0, reference);
438
439         sheetName = workbook.getSheetName(1);
440         String reference2 = sheetName+"!$B$2:$D$5";
441         workbook.setPrintArea(1, reference2);
442
443         sheetName = workbook.getSheetName(2);
444         String reference3 = sheetName+"!$D$2:$F$5";
445         workbook.setPrintArea(2, reference3);
446         
447         File file = TempFile.createTempFile("testMultiPrintArea",".xls");
448         
449         FileOutputStream fileOut = new FileOutputStream (file);
450         workbook.write(fileOut);
451         fileOut.close();
452         
453         assertTrue("file exists",file.exists());
454         
455         FileInputStream in = new FileInputStream (file);
456         workbook = new HSSFWorkbook(in);
457         
458         String retrievedPrintArea = workbook.getPrintArea(0);
459         assertNotNull("Print Area Not Found (Sheet 1)", retrievedPrintArea);
460         assertEquals(reference, retrievedPrintArea);
461         
462         String retrievedPrintArea2 = workbook.getPrintArea(1);
463         assertNotNull("Print Area Not Found (Sheet 2)", retrievedPrintArea2);
464         assertEquals(reference2, retrievedPrintArea2);
465         
466         String retrievedPrintArea3 = workbook.getPrintArea(2);
467         assertNotNull("Print Area Not Found (Sheet 3)", retrievedPrintArea3);
468         assertEquals(reference3, retrievedPrintArea3);
469         
470         
471     }
472     
473     /**
474      * Tests the setting of print areas with coordinates (Row/Column designations)
475      *
476      */
477     public void testPrintAreaCoords(){
478         HSSFWorkbook workbook = new HSSFWorkbook();
479         HSSFSheet sheet = workbook.createSheet("Test Print Area");
480         String sheetName = workbook.getSheetName(0);
481          
482         String reference = sheetName+"!$A$1:$B$1";
483         workbook.setPrintArea(0, 0, 1, 0, 0);
484                  
485         String retrievedPrintArea = workbook.getPrintArea(0);
486         
487         assertNotNull("Print Area not defined for first sheet", retrievedPrintArea);
488         assertEquals(reference, retrievedPrintArea);
489     }
490      
491     /**
492      * Verifies an existing print area is deleted
493      *
494      */
495     public void testPrintAreaRemove() {
496         HSSFWorkbook workbook = new HSSFWorkbook();
497         HSSFSheet sheet = workbook.createSheet("Test Print Area");
498         String sheetName = workbook.getSheetName(0);
499          
500         String reference = sheetName+"!$A$1:$B$1";
501         workbook.setPrintArea(0, 0, 1, 0, 0);
502                  
503         String retrievedPrintArea = workbook.getPrintArea(0);
504         
505         assertNotNull("Print Area not defined for first sheet", retrievedPrintArea);
506         
507         workbook.removePrintArea(0);
508         assertNull("PrintArea was not removed", workbook.getPrintArea(0));
509     }
510         
511 }
512
513

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags