KickJava   Java API By Example, From Geeks To Geeks.

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


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.hssf.model.Workbook;
23 import org.apache.poi.hssf.record.BackupRecord;
24 import org.apache.poi.hssf.record.LabelSSTRecord;
25 import org.apache.poi.hssf.record.Record;
26 import org.apache.poi.hssf.record.aggregates.ValueRecordsAggregate;
27 import org.apache.poi.hssf.util.Region;
28 import org.apache.poi.poifs.filesystem.POIFSFileSystem;
29 import org.apache.poi.util.TempFile;
30
31 import java.io.File JavaDoc;
32 import java.io.FileInputStream JavaDoc;
33 import java.io.FileOutputStream JavaDoc;
34 import java.io.IOException JavaDoc;
35 import java.util.Iterator JavaDoc;
36
37 /**
38  * Class to test Workbook functionality
39  *
40  * @author Andrew C. Oliver
41  * @author Greg Merrill
42  * @author Siggi Cherem
43  */

44
45 public class TestWorkbook
46     extends TestCase
47 {
48     private static final String JavaDoc LAST_NAME_KEY = "lastName";
49     private static final String JavaDoc FIRST_NAME_KEY = "firstName";
50     private static final String JavaDoc SSN_KEY = "ssn";
51     private static final String JavaDoc REPLACE_ME = "replaceMe";
52     private static final String JavaDoc REPLACED = "replaced";
53     private static final String JavaDoc DO_NOT_REPLACE = "doNotReplace";
54     private static final String JavaDoc EMPLOYEE_INFORMATION = "Employee Info";
55     private static final String JavaDoc LAST_NAME_VALUE = "Bush";
56     private static final String JavaDoc FIRST_NAME_VALUE = "George";
57     private static final String JavaDoc SSN_VALUE = "555555555";
58     private SanityChecker sanityChecker = new SanityChecker();
59
60     /**
61      * Constructor TestWorkbook
62      *
63      * @param name
64      */

65
66     public TestWorkbook(String JavaDoc name)
67     {
68         super(name);
69     }
70
71     /**
72      * TEST NAME: Test Write Sheet Simple <P>
73      * OBJECTIVE: Test that HSSF can create a simple spreadsheet with numeric and string values.<P>
74      * SUCCESS: HSSF creates a sheet. Filesize matches a known good. HSSFSheet objects
75      * Last row, first row is tested against the correct values (99,0).<P>
76      * FAILURE: HSSF does not create a sheet or excepts. Filesize does not match the known good.
77      * HSSFSheet last row or first row is incorrect. <P>
78      *
79      */

80
81     public void testWriteSheetSimple()
82         throws IOException JavaDoc
83     {
84         File file = TempFile.createTempFile("testWriteSheetSimple",
85                                                     ".xls");
86         FileOutputStream JavaDoc out = new FileOutputStream JavaDoc(file);
87         HSSFWorkbook wb = new HSSFWorkbook();
88         HSSFSheet s = wb.createSheet();
89         HSSFRow r = null;
90         HSSFCell c = null;
91
92         for (short rownum = ( short ) 0; rownum < 100; rownum++)
93         {
94             r = s.createRow(rownum);
95
96             // r.setRowNum(( short ) rownum);
97
for (short cellnum = ( short ) 0; cellnum < 50; cellnum += 2)
98             {
99                 c = r.createCell(cellnum);
100                 c.setCellValue(rownum * 10000 + cellnum
101                                + ((( double ) rownum / 1000)
102                                   + (( double ) cellnum / 10000)));
103                 c = r.createCell(( short ) (cellnum + 1));
104                 c.setCellValue("TEST");
105             }
106         }
107         wb.write(out);
108         out.close();
109         sanityChecker.checkHSSFWorkbook(wb);
110         assertEquals("LAST ROW == 99", 99, s.getLastRowNum());
111         assertEquals("FIRST ROW == 0", 0, s.getFirstRowNum());
112
113         // assert((s.getLastRowNum() == 99));
114
}
115
116     /**
117      * TEST NAME: Test Write/Modify Sheet Simple <P>
118      * OBJECTIVE: Test that HSSF can create a simple spreadsheet with numeric and string values,
119      * remove some rows, yet still have a valid file/data.<P>
120      * SUCCESS: HSSF creates a sheet. Filesize matches a known good. HSSFSheet objects
121      * Last row, first row is tested against the correct values (74,25).<P>
122      * FAILURE: HSSF does not create a sheet or excepts. Filesize does not match the known good.
123      * HSSFSheet last row or first row is incorrect. <P>
124      *
125      */

126
127     public void testWriteModifySheetSimple()
128         throws IOException JavaDoc
129     {
130         File file = TempFile.createTempFile("testWriteSheetSimple",
131                                                     ".xls");
132         FileOutputStream JavaDoc out = new FileOutputStream JavaDoc(file);
133         HSSFWorkbook wb = new HSSFWorkbook();
134         HSSFSheet s = wb.createSheet();
135         HSSFRow r = null;
136         HSSFCell c = null;
137
138         for (short rownum = ( short ) 0; rownum < 100; rownum++)
139         {
140             r = s.createRow(rownum);
141
142             // r.setRowNum(( short ) rownum);
143
for (short cellnum = ( short ) 0; cellnum < 50; cellnum += 2)
144             {
145                 c = r.createCell(cellnum);
146                 c.setCellValue(rownum * 10000 + cellnum
147                                + ((( double ) rownum / 1000)
148                                   + (( double ) cellnum / 10000)));
149                 c = r.createCell(( short ) (cellnum + 1));
150                 c.setCellValue("TEST");
151             }
152         }
153         for (short rownum = ( short ) 0; rownum < 25; rownum++)
154         {
155             r = s.getRow(rownum);
156             s.removeRow(r);
157         }
158         for (short rownum = ( short ) 75; rownum < 100; rownum++)
159         {
160             r = s.getRow(rownum);
161             s.removeRow(r);
162         }
163         wb.write(out);
164         out.close();
165
166         sanityChecker.checkHSSFWorkbook(wb);
167         assertEquals("LAST ROW == 74", 74, s.getLastRowNum());
168         assertEquals("FIRST ROW == 25", 25, s.getFirstRowNum());
169     }
170
171     /**
172      * TEST NAME: Test Read Simple <P>
173      * OBJECTIVE: Test that HSSF can read a simple spreadsheet (Simple.xls).<P>
174      * SUCCESS: HSSF reads the sheet. Matches values in their particular positions.<P>
175      * FAILURE: HSSF does not read a sheet or excepts. HSSF cannot identify values
176      * in the sheet in their known positions.<P>
177      *
178      */

179
180     public void testReadSimple()
181         throws IOException JavaDoc
182     {
183         String JavaDoc filename = System.getProperty("HSSF.testdata.path");
184
185         filename = filename + "/Simple.xls";
186         FileInputStream JavaDoc stream = new FileInputStream JavaDoc(filename);
187         POIFSFileSystem fs = new POIFSFileSystem(stream);
188         HSSFWorkbook workbook = new HSSFWorkbook(fs);
189         HSSFSheet sheet = workbook.getSheetAt(0);
190
191         assertEquals(REPLACE_ME,
192                      sheet.getRow(( short ) 0).getCell(( short ) 0)
193                          .getStringCellValue());
194         stream.close();
195     }
196
197     /**
198      * TEST NAME: Test Read Simple w/ Data Format<P>
199      * OBJECTIVE: Test that HSSF can read a simple spreadsheet (SimpleWithDataFormat.xls).<P>
200      * SUCCESS: HSSF reads the sheet. Matches values in their particular positions and format is correct<P>
201      * FAILURE: HSSF does not read a sheet or excepts. HSSF cannot identify values
202      * in the sheet in their known positions.<P>
203      *
204      */

205
206     public void testReadSimpleWithDataFormat()
207         throws IOException JavaDoc
208     {
209         String JavaDoc filename = System.getProperty("HSSF.testdata.path");
210
211         filename = filename + "/SimpleWithDataFormat.xls";
212         FileInputStream JavaDoc stream = new FileInputStream JavaDoc(filename);
213         POIFSFileSystem fs = new POIFSFileSystem(stream);
214         HSSFWorkbook workbook = new HSSFWorkbook(fs);
215         HSSFSheet sheet = workbook.getSheetAt(0);
216         HSSFDataFormat format = workbook.createDataFormat();
217     HSSFCell cell =
218                      sheet.getRow(( short ) 0).getCell(( short ) 0);
219
220         assertEquals(1.25,cell.getNumericCellValue(), 1e-10);
221
222     assertEquals(format.getFormat(cell.getCellStyle().getDataFormat()), "0.0");
223         stream.close();
224     }
225
226 /**
227      * TEST NAME: Test Read/Write Simple w/ Data Format<P>
228      * OBJECTIVE: Test that HSSF can write a sheet with custom data formats and then read it and get the proper formats.<P>
229      * SUCCESS: HSSF reads the sheet. Matches values in their particular positions and format is correct<P>
230      * FAILURE: HSSF does not read a sheet or excepts. HSSF cannot identify values
231      * in the sheet in their known positions.<P>
232      *
233      */

234
235     public void testWriteDataFormat()
236         throws IOException JavaDoc
237     {
238     File file = TempFile.createTempFile("testWriteDataFormat",
239                                                     ".xls");
240         FileOutputStream JavaDoc out = new FileOutputStream JavaDoc(file);
241         HSSFWorkbook wb = new HSSFWorkbook();
242         HSSFSheet s = wb.createSheet();
243         HSSFRow r = null;
244         HSSFCell c = null;
245     HSSFDataFormat format = wb.createDataFormat();
246     HSSFCellStyle cs = wb.createCellStyle();
247     
248     short df = format.getFormat("0.0");
249     cs.setDataFormat(df);
250     
251     r = s.createRow((short)0);
252     c = r.createCell((short)0);
253     c.setCellStyle(cs);
254     c.setCellValue(1.25);
255
256         wb.write(out);
257         out.close();
258
259         FileInputStream JavaDoc stream = new FileInputStream JavaDoc(file);
260         POIFSFileSystem fs = new POIFSFileSystem(stream);
261         HSSFWorkbook workbook = new HSSFWorkbook(fs);
262         HSSFSheet sheet = workbook.getSheetAt(0);
263     HSSFCell cell =
264                      sheet.getRow(( short ) 0).getCell(( short ) 0);
265     format = workbook.createDataFormat();
266
267         assertEquals(1.25,cell.getNumericCellValue(), 1e-10);
268
269     assertEquals(format.getFormat(df), "0.0");
270
271     assertEquals(format, workbook.createDataFormat());
272     
273         stream.close();
274     }
275
276     /**
277      * TEST NAME: Test Read Employee Simple <P>
278      * OBJECTIVE: Test that HSSF can read a simple spreadsheet (Employee.xls).<P>
279      * SUCCESS: HSSF reads the sheet. Matches values in their particular positions.<P>
280      * FAILURE: HSSF does not read a sheet or excepts. HSSF cannot identify values
281      * in the sheet in their known positions.<P>
282      *
283      */

284
285     public void testReadEmployeeSimple()
286         throws IOException JavaDoc
287     {
288         String JavaDoc filename = System.getProperty("HSSF.testdata.path");
289
290         filename = filename + "/Employee.xls";
291         FileInputStream JavaDoc stream = new FileInputStream JavaDoc(filename);
292         POIFSFileSystem fs = new POIFSFileSystem(stream);
293         HSSFWorkbook workbook = new HSSFWorkbook(fs);
294         HSSFSheet sheet = workbook.getSheetAt(0);
295
296         assertEquals(EMPLOYEE_INFORMATION,
297                      sheet.getRow(1).getCell(( short ) 1)
298                          .getStringCellValue());
299         assertEquals(LAST_NAME_KEY,
300                      sheet.getRow(3).getCell(( short ) 2)
301                          .getStringCellValue());
302         assertEquals(FIRST_NAME_KEY,
303                      sheet.getRow(4).getCell(( short ) 2)
304                          .getStringCellValue());
305         assertEquals(SSN_KEY,
306                      sheet.getRow(5).getCell(( short ) 2)
307                          .getStringCellValue());
308         stream.close();
309     }
310
311     /**
312      * TEST NAME: Test Modify Sheet Simple <P>
313      * OBJECTIVE: Test that HSSF can read a simple spreadsheet with a string value and replace
314      * it with another string value.<P>
315      * SUCCESS: HSSF reads a sheet. HSSF replaces the cell value with another cell value. HSSF
316      * writes the sheet out to another file. HSSF reads the result and ensures the value
317      * has been properly replaced. <P>
318      * FAILURE: HSSF does not read a sheet or excepts. HSSF does not write the sheet or excepts.
319      * HSSF does not re-read the sheet or excepts. Upon re-reading the sheet the value
320      * is incorrect or has not been replaced. <P>
321      *
322      */

323
324     public void testModifySimple()
325         throws IOException JavaDoc
326     {
327         String JavaDoc filename = System.getProperty("HSSF.testdata.path");
328
329         filename = filename + "/Simple.xls";
330         FileInputStream JavaDoc instream = new FileInputStream JavaDoc(filename);
331         POIFSFileSystem fsin = new POIFSFileSystem(instream);
332         HSSFWorkbook workbook = new HSSFWorkbook(fsin);
333         HSSFSheet sheet = workbook.getSheetAt(0);
334         HSSFCell cell =
335             sheet.getRow(( short ) 0).getCell(( short ) 0);
336
337         cell.setCellValue(REPLACED);
338         File destination = TempFile.createTempFile("SimpleResult",
339                                            ".xls");
340         FileOutputStream JavaDoc outstream = new FileOutputStream JavaDoc(destination);
341
342         workbook.write(outstream);
343         instream.close();
344         outstream.close();
345         instream = new FileInputStream JavaDoc(destination);
346         workbook = new HSSFWorkbook(new POIFSFileSystem(instream));
347         sheet = workbook.getSheetAt(0);
348         cell = sheet.getRow(( short ) 0).getCell(( short ) 0);
349         assertEquals(REPLACED, cell.getStringCellValue());
350         instream.close();
351     }
352
353     /**
354      * TEST NAME: Test Modify Sheet Simple With Skipped cells<P>
355      * OBJECTIVE: Test that HSSF can read a simple spreadsheet with string values and replace
356      * them with other string values while not replacing other cells.<P>
357      * SUCCESS: HSSF reads a sheet. HSSF replaces the cell value with another cell value. HSSF
358      * writes the sheet out to another file. HSSF reads the result and ensures the value
359      * has been properly replaced and unreplaced values are still unreplaced. <P>
360      * FAILURE: HSSF does not read a sheet or excepts. HSSF does not write the sheet or excepts.
361      * HSSF does not re-read the sheet or excepts. Upon re-reading the sheet the value
362      * is incorrect or has not been replaced or the incorrect cell has its value replaced
363      * or is incorrect. <P>
364      *
365      */

366
367     public void testModifySimpleWithSkip()
368         throws IOException JavaDoc
369     {
370         String JavaDoc filename = System.getProperty("HSSF.testdata.path");
371
372         filename = filename + "/SimpleWithSkip.xls";
373         FileInputStream JavaDoc instream = new FileInputStream JavaDoc(filename);
374         POIFSFileSystem fsin = new POIFSFileSystem(instream);
375         HSSFWorkbook workbook = new HSSFWorkbook(fsin);
376         HSSFSheet sheet = workbook.getSheetAt(0);
377         HSSFCell cell =
378             sheet.getRow(( short ) 0).getCell(( short ) 1);
379
380         cell.setCellValue(REPLACED);
381         cell = sheet.getRow(( short ) 1).getCell(( short ) 0);
382         cell.setCellValue(REPLACED);
383         File destination =
384             TempFile.createTempFile("SimpleWithSkipResult", ".xls");
385         FileOutputStream JavaDoc outstream = new FileOutputStream JavaDoc(destination);
386
387         workbook.write(outstream);
388         instream.close();
389         outstream.close();
390         instream = new FileInputStream JavaDoc(destination);
391         workbook = new HSSFWorkbook(new POIFSFileSystem(instream));
392         sheet = workbook.getSheetAt(0);
393         cell = sheet.getRow(( short ) 0).getCell(( short ) 1);
394         assertEquals(REPLACED, cell.getStringCellValue());
395         cell = sheet.getRow(( short ) 0).getCell(( short ) 0);
396         assertEquals(DO_NOT_REPLACE, cell.getStringCellValue());
397         cell = sheet.getRow(( short ) 1).getCell(( short ) 0);
398         assertEquals(REPLACED, cell.getStringCellValue());
399         cell = sheet.getRow(( short ) 1).getCell(( short ) 1);
400         assertEquals(DO_NOT_REPLACE, cell.getStringCellValue());
401         instream.close();
402     }
403
404     /**
405      * TEST NAME: Test Modify Sheet With Styling<P>
406      * OBJECTIVE: Test that HSSF can read a simple spreadsheet with string values and replace
407      * them with other string values despite any styling. In this release of HSSF styling will
408      * probably be lost and is NOT tested.<P>
409      * SUCCESS: HSSF reads a sheet. HSSF replaces the cell values with other cell values. HSSF
410      * writes the sheet out to another file. HSSF reads the result and ensures the value
411      * has been properly replaced. <P>
412      * FAILURE: HSSF does not read a sheet or excepts. HSSF does not write the sheet or excepts.
413      * HSSF does not re-read the sheet or excepts. Upon re-reading the sheet the value
414      * is incorrect or has not been replaced. <P>
415      *
416      */

417
418     public void testModifySimpleWithStyling()
419         throws IOException JavaDoc
420     {
421         String JavaDoc filename = System.getProperty("HSSF.testdata.path");
422
423         filename = filename + "/SimpleWithStyling.xls";
424         FileInputStream JavaDoc instream = new FileInputStream JavaDoc(filename);
425         POIFSFileSystem fsin = new POIFSFileSystem(instream);
426         HSSFWorkbook workbook = new HSSFWorkbook(fsin);
427         HSSFSheet sheet = workbook.getSheetAt(0);
428
429         for (int k = 0; k < 4; k++)
430         {
431             HSSFCell cell = sheet.getRow(( short ) k).getCell(( short ) 0);
432
433             cell.setCellValue(REPLACED);
434         }
435         File destination =
436             TempFile.createTempFile("SimpleWithStylingResult", ".xls");
437         FileOutputStream JavaDoc outstream = new FileOutputStream JavaDoc(destination);
438
439         workbook.write(outstream);
440         instream.close();
441         outstream.close();
442         instream = new FileInputStream JavaDoc(destination);
443         workbook = new HSSFWorkbook(new POIFSFileSystem(instream));
444         sheet = workbook.getSheetAt(0);
445         for (int k = 0; k < 4; k++)
446         {
447             HSSFCell cell = sheet.getRow(( short ) k).getCell(( short ) 0);
448
449             assertEquals(REPLACED, cell.getStringCellValue());
450         }
451         instream.close();
452     }
453
454     /**
455      * TEST NAME: Test Modify Employee Sheet<P>
456      * OBJECTIVE: Test that HSSF can read a simple spreadsheet with string values and replace
457      * them with other string values despite any styling. In this release of HSSF styling will
458      * probably be lost and is NOT tested.<P>
459      * SUCCESS: HSSF reads a sheet. HSSF replaces the cell values with other cell values. HSSF
460      * writes the sheet out to another file. HSSF reads the result and ensures the value
461      * has been properly replaced. <P>
462      * FAILURE: HSSF does not read a sheet or excepts. HSSF does not write the sheet or excepts.
463      * HSSF does not re-read the sheet or excepts. Upon re-reading the sheet the value
464      * is incorrect or has not been replaced. <P>
465      *
466      */

467
468     public void testModifyEmployee()
469         throws IOException JavaDoc
470     {
471         String JavaDoc filename = System.getProperty("HSSF.testdata.path");
472
473         filename = filename + "/Employee.xls";
474         FileInputStream JavaDoc instream = new FileInputStream JavaDoc(filename);
475         POIFSFileSystem fsin = new POIFSFileSystem(instream);
476         HSSFWorkbook workbook = new HSSFWorkbook(fsin);
477         HSSFSheet sheet = workbook.getSheetAt(0);
478         HSSFCell cell =
479             sheet.getRow(( short ) 3).getCell(( short ) 2);
480
481         cell.setCellValue(LAST_NAME_VALUE);
482         cell = sheet.getRow(( short ) 4).getCell(( short ) 2);
483         cell.setCellValue(FIRST_NAME_VALUE);
484         cell = sheet.getRow(( short ) 5).getCell(( short ) 2);
485         cell.setCellValue(SSN_VALUE);
486         File destination = TempFile.createTempFile("EmployeeResult",
487                                            ".xls");
488         FileOutputStream JavaDoc outstream = new FileOutputStream JavaDoc(destination);
489
490         workbook.write(outstream);
491         instream.close();
492         outstream.close();
493         instream = new FileInputStream JavaDoc(destination);
494         workbook = new HSSFWorkbook(new POIFSFileSystem(instream));
495         sheet = workbook.getSheetAt(0);
496         assertEquals(EMPLOYEE_INFORMATION,
497                      sheet.getRow(1).getCell(( short ) 1)
498                          .getStringCellValue());
499         assertEquals(LAST_NAME_VALUE,
500                      sheet.getRow(3).getCell(( short ) 2)
501                          .getStringCellValue());
502         assertEquals(FIRST_NAME_VALUE,
503                      sheet.getRow(4).getCell(( short ) 2)
504                          .getStringCellValue());
505         assertEquals(SSN_VALUE,
506                      sheet.getRow(5).getCell(( short ) 2)
507                          .getStringCellValue());
508         instream.close();
509     }
510
511     /**
512      * TEST NAME: Test Read Sheet with an RK number<P>
513      * OBJECTIVE: Test that HSSF can read a simple spreadsheet with and RKRecord and correctly
514      * identify the cell as numeric and convert it to a NumberRecord. <P>
515      * SUCCESS: HSSF reads a sheet. HSSF returns that the cell is a numeric type cell. <P>
516      * FAILURE: HSSF does not read a sheet or excepts. HSSF incorrectly indentifies the cell<P>
517      *
518      */

519
520     public void testReadSheetWithRK()
521         throws IOException JavaDoc
522     {
523         String JavaDoc filename = System.getProperty("HSSF.testdata.path");
524
525         filename = filename + "/rk.xls";
526
527         // a.xls has a value on position (0,0)
528
FileInputStream JavaDoc in = new FileInputStream JavaDoc(filename);
529         POIFSFileSystem fs = new POIFSFileSystem(in);
530         HSSFWorkbook h = new HSSFWorkbook(fs);
531         HSSFSheet s = h.getSheetAt(0);
532         HSSFRow r = s.getRow(0);
533         HSSFCell c = r.getCell(( short ) 0);
534         int a = c.getCellType();
535
536         assertEquals(a, c.CELL_TYPE_NUMERIC);
537     }
538
539     /**
540      * TEST NAME: Test Write/Modify Sheet Simple <P>
541      * OBJECTIVE: Test that HSSF can create a simple spreadsheet with numeric and string values,
542      * remove some rows, yet still have a valid file/data.<P>
543      * SUCCESS: HSSF creates a sheet. Filesize matches a known good. HSSFSheet objects
544      * Last row, first row is tested against the correct values (74,25).<P>
545      * FAILURE: HSSF does not create a sheet or excepts. Filesize does not match the known good.
546      * HSSFSheet last row or first row is incorrect. <P>
547      *
548      */

549
550     public void testWriteModifySheetMerged()
551         throws IOException JavaDoc
552     {
553         File file = TempFile.createTempFile("testWriteSheetMerged",
554                                                     ".xls");
555         FileOutputStream JavaDoc out = new FileOutputStream JavaDoc(file);
556         FileInputStream JavaDoc in = null;
557         HSSFWorkbook wb = new HSSFWorkbook();
558         HSSFSheet s = wb.createSheet();
559         HSSFRow r = null;
560         HSSFCell c = null;
561
562         for (short rownum = ( short ) 0; rownum < 100; rownum++)
563         {
564             r = s.createRow(rownum);
565
566             // r.setRowNum(( short ) rownum);
567
for (short cellnum = ( short ) 0; cellnum < 50; cellnum += 2)
568             {
569                 c = r.createCell(cellnum);
570                 c.setCellValue(rownum * 10000 + cellnum
571                                + ((( double ) rownum / 1000)
572                                   + (( double ) cellnum / 10000)));
573                 c = r.createCell(( short ) (cellnum + 1));
574                 c.setCellValue("TEST");
575             }
576         }
577         s.addMergedRegion(new Region(( short ) 0, ( short ) 0, ( short ) 10,
578                                      ( short ) 10));
579         s.addMergedRegion(new Region(( short ) 30, ( short ) 5, ( short ) 40,
580                                      ( short ) 15));
581         wb.write(out);
582         out.close();
583         sanityChecker.checkHSSFWorkbook(wb);
584         in = new FileInputStream JavaDoc(file);
585         wb = new HSSFWorkbook(new POIFSFileSystem(in));
586         s = wb.getSheetAt(0);
587         Region r1 = s.getMergedRegionAt(0);
588         Region r2 = s.getMergedRegionAt(1);
589
590         in.close();
591
592         // System.out.println(file.length());
593
// assertEquals("FILE LENGTH == 87552",file.length(), 87552);
594
// System.out.println(s.getLastRowNum());
595
assertEquals("REGION1 = 0,0,10,10", 0,
596                      new Region(( short ) 0, ( short ) 0, ( short ) 10,
597                                 ( short ) 10).compareTo(r1));
598         assertEquals("REGION2 == 30,5,40,15", 0,
599                      new Region(( short ) 30, ( short ) 5, ( short ) 40,
600                                 ( short ) 15).compareTo(r2));
601     }
602
603     /**
604      * Test the backup field gets set as expected.
605      */

606
607     public void testBackupRecord()
608         throws Exception JavaDoc
609     {
610         HSSFWorkbook wb = new HSSFWorkbook();
611         wb.createSheet();
612         Workbook workbook = wb.getWorkbook();
613         BackupRecord record = workbook.getBackupRecord();
614
615         assertEquals(0, record.getBackup());
616         wb.setBackupFlag(true);
617         assertEquals(1, record.getBackup());
618     }
619
620     /**
621      * This tests is for bug [ #506658 ] Repeating output.
622      *
623      * We need to make sure only one LabelSSTRecord is produced.
624      */

625
626     public void testRepeatingBug()
627         throws Exception JavaDoc
628     {
629         HSSFWorkbook workbook = new HSSFWorkbook();
630         HSSFSheet sheet = workbook.createSheet("Design Variants");
631         HSSFRow row = sheet.createRow(( short ) 2);
632         HSSFCell cell = row.createCell(( short ) 1);
633
634         cell.setCellValue("Class");
635         cell = row.createCell(( short ) 2);
636
637         // workbook.write(new FileOutputStream("/a2.xls"));
638
ValueRecordsAggregate valueAggregate =
639             ( ValueRecordsAggregate ) sheet.getSheet()
640                 .findFirstRecordBySid(ValueRecordsAggregate.sid);
641         int sstRecords = 0;
642         Iterator JavaDoc iterator = valueAggregate.getIterator();
643
644         while (iterator.hasNext())
645         {
646             if ((( Record ) iterator.next()).getSid() == LabelSSTRecord.sid)
647             {
648                 sstRecords++;
649             }
650         }
651         assertEquals(1, sstRecords);
652     }
653
654
655     public void testManyRows()
656         throws Exception JavaDoc
657     {
658         String JavaDoc testName = "TestManyRows";
659         File file = TempFile.createTempFile(testName, ".xls");
660         FileOutputStream JavaDoc out = new FileOutputStream JavaDoc(file);
661         HSSFWorkbook workbook = new HSSFWorkbook();
662         HSSFSheet sheet = workbook.createSheet();
663         HSSFRow row = null;
664         HSSFCell cell = null;
665         int i, j;
666         for ( i = 0, j = 32771; j > 0; i++, j-- )
667         {
668             row = sheet.createRow(i);
669             cell = row.createCell((short) 0);
670             cell.setCellValue(i);
671         }
672         workbook.write(out);
673         out.close();
674         sanityChecker.checkHSSFWorkbook(workbook);
675         assertEquals("LAST ROW == 32770", 32770, sheet.getLastRowNum());
676         double lastVal = cell.getNumericCellValue();
677
678         FileInputStream JavaDoc in = new FileInputStream JavaDoc(file);
679         POIFSFileSystem fs = new POIFSFileSystem(in);
680         HSSFWorkbook wb = new HSSFWorkbook(fs);
681         HSSFSheet s = wb.getSheetAt(0);
682         row = s.getRow(32770);
683         cell = row.getCell(( short ) 0);
684         assertEquals("Value from last row == 32770", lastVal, cell.getNumericCellValue(), 0);
685         assertEquals("LAST ROW == 32770", 32770, s.getLastRowNum());
686         in.close();
687         file.deleteOnExit();
688     }
689     
690     /**
691      * Generate a file to visually/programmatically verify repeating rows and cols made it
692      */

693     public void testRepeatingColsRows() throws IOException JavaDoc
694     {
695         HSSFWorkbook workbook = new HSSFWorkbook();
696         HSSFSheet sheet = workbook.createSheet("Test Print Titles");
697         String JavaDoc sheetName = workbook.getSheetName(0);
698          
699         HSSFRow row = sheet.createRow(0);
700         
701         HSSFCell cell = row.createCell((short)1);
702         cell.setCellValue("hi");
703          
704          
705         workbook.setRepeatingRowsAndColumns(0, 0, 1, 0, 0);
706          
707         File file = TempFile.createTempFile("testPrintTitles",".xls");
708          
709         FileOutputStream JavaDoc fileOut = new FileOutputStream JavaDoc(file);
710         workbook.write(fileOut);
711         fileOut.close();
712          
713         assertTrue("file exists",file.exists());
714          
715         
716     }
717   
718     
719     public static void main(String JavaDoc [] ignored_args)
720     {
721         String JavaDoc filename = System.getProperty("HSSF.testdata.path");
722
723         // assume this is relative to basedir
724
if (filename == null)
725         {
726             System.setProperty(
727                 "HSSF.testdata.path",
728                 "src/testcases/org/apache/poi/hssf/data");
729         }
730         System.out
731             .println("Testing org.apache.poi.hssf.usermodel.HSSFWorkbook");
732         junit.textui.TestRunner.run(TestWorkbook.class);
733     }
734 }
735
Popular Tags