2 /* ====================================================================
3 Licensed to the Apache Software Foundation (ASF) under one or more
4 contributor license agreements. See the NOTICE file distributed with
5 this work for additional information regarding copyright ownership.
6 The ASF licenses this file to You under the Apache License, Version 2.0
7 (the "License"); you may not use this file except in compliance with
8 the License. You may obtain a copy of the License at
10 http://www.apache.org/licenses/LICENSE-2.0
12 Unless required by applicable law or agreed to in writing, software
13 distributed under the License is distributed on an "AS IS" BASIS,
14 WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
15 See the License for the specific language governing permissions and
16 limitations under the License.
17 ==================================================================== */
20 package org
.apache
.poi
.hssf
.usermodel
;
24 import junit
.framework
.TestCase
;
26 import org
.apache
.poi
.hssf
.model
.Sheet
;
27 import org
.apache
.poi
.hssf
.record
.HCenterRecord
;
28 import org
.apache
.poi
.hssf
.record
.ProtectRecord
;
29 import org
.apache
.poi
.hssf
.record
.PasswordRecord
;
30 import org
.apache
.poi
.hssf
.record
.SCLRecord
;
31 import org
.apache
.poi
.hssf
.record
.VCenterRecord
;
32 import org
.apache
.poi
.hssf
.record
.WSBoolRecord
;
33 import org
.apache
.poi
.hssf
.record
.WindowTwoRecord
;
34 import org
.apache
.poi
.hssf
.util
.Region
;
35 import org
.apache
.poi
.poifs
.filesystem
.POIFSFileSystem
;
36 import org
.apache
.poi
.util
.TempFile
;
39 * Tests HSSFSheet. This test case is very incomplete at the moment.
42 * @author Glen Stampoultzis (glens at apache.org)
43 * @author Andrew C. Oliver (acoliver apache org)
46 public class TestHSSFSheet
49 public TestHSSFSheet(String s
)
55 * Test the gridset field gets set as expected.
58 public void testBackupRecord()
61 HSSFWorkbook wb
= new HSSFWorkbook();
62 HSSFSheet s
= wb
.createSheet();
63 Sheet sheet
= s
.getSheet();
65 assertEquals(true, sheet
.getGridsetRecord().getGridset());
66 s
.setGridsPrinted(true);
67 assertEquals(false, sheet
.getGridsetRecord().getGridset());
71 * Test vertically centered output.
74 public void testVerticallyCenter()
77 HSSFWorkbook wb
= new HSSFWorkbook();
78 HSSFSheet s
= wb
.createSheet();
79 Sheet sheet
= s
.getSheet();
80 VCenterRecord record
=
81 (VCenterRecord
) sheet
.findFirstRecordBySid(VCenterRecord
.sid
);
83 assertEquals(false, record
.getVCenter());
84 s
.setVerticallyCenter(true);
85 assertEquals(true, record
.getVCenter());
87 // wb.write(new FileOutputStream("c:\\test.xls"));
91 * Test horizontally centered output.
94 public void testHorizontallyCenter()
97 HSSFWorkbook wb
= new HSSFWorkbook();
98 HSSFSheet s
= wb
.createSheet();
99 Sheet sheet
= s
.getSheet();
100 HCenterRecord record
=
101 (HCenterRecord
) sheet
.findFirstRecordBySid(HCenterRecord
.sid
);
103 assertEquals(false, record
.getHCenter());
104 s
.setHorizontallyCenter(true);
105 assertEquals(true, record
.getHCenter());
111 * Test WSBboolRecord fields get set in the user model.
114 public void testWSBool()
116 HSSFWorkbook wb
= new HSSFWorkbook();
117 HSSFSheet s
= wb
.createSheet();
118 Sheet sheet
= s
.getSheet();
119 WSBoolRecord record
=
120 (WSBoolRecord
) sheet
.findFirstRecordBySid(WSBoolRecord
.sid
);
123 assertEquals(true, record
.getAlternateExpression());
124 assertEquals(true, record
.getAlternateFormula());
125 assertEquals(false, record
.getAutobreaks());
126 assertEquals(false, record
.getDialog());
127 assertEquals(false, record
.getDisplayGuts());
128 assertEquals(true, record
.getFitToPage());
129 assertEquals(false, record
.getRowSumsBelow());
130 assertEquals(false, record
.getRowSumsRight());
133 s
.setAlternativeExpression(false);
134 s
.setAlternativeFormula(false);
135 s
.setAutobreaks(true);
137 s
.setDisplayGuts(true);
138 s
.setFitToPage(false);
139 s
.setRowSumsBelow(true);
140 s
.setRowSumsRight(true);
143 assertEquals(false, record
.getAlternateExpression());
144 assertEquals(false, record
.getAlternateFormula());
145 assertEquals(true, record
.getAutobreaks());
146 assertEquals(true, record
.getDialog());
147 assertEquals(true, record
.getDisplayGuts());
148 assertEquals(false, record
.getFitToPage());
149 assertEquals(true, record
.getRowSumsBelow());
150 assertEquals(true, record
.getRowSumsRight());
151 assertEquals(false, s
.getAlternateExpression());
152 assertEquals(false, s
.getAlternateFormula());
153 assertEquals(true, s
.getAutobreaks());
154 assertEquals(true, s
.getDialog());
155 assertEquals(true, s
.getDisplayGuts());
156 assertEquals(false, s
.getFitToPage());
157 assertEquals(true, s
.getRowSumsBelow());
158 assertEquals(true, s
.getRowSumsRight());
161 public void testReadBooleans()
164 HSSFWorkbook workbook
= new HSSFWorkbook();
165 HSSFSheet sheet
= workbook
.createSheet("Test boolean");
166 HSSFRow row
= sheet
.createRow((short) 2);
167 HSSFCell cell
= row
.createCell((short) 9);
168 cell
.setCellValue(true);
169 cell
= row
.createCell((short) 11);
170 cell
.setCellValue(true);
171 File tempFile
= TempFile
.createTempFile("bool", "test.xls");
172 FileOutputStream stream
= new FileOutputStream(tempFile
);
173 workbook
.write(stream
);
176 FileInputStream readStream
= new FileInputStream(tempFile
);
177 workbook
= new HSSFWorkbook(readStream
);
178 sheet
= workbook
.getSheetAt(0);
179 row
= sheet
.getRow(2);
183 assertEquals(2, row
.getPhysicalNumberOfCells());
186 public void testRemoveRow()
188 HSSFWorkbook workbook
= new HSSFWorkbook();
189 HSSFSheet sheet
= workbook
.createSheet("Test boolean");
190 HSSFRow row
= sheet
.createRow((short) 2);
191 sheet
.removeRow(row
);
194 public void testCloneSheet() {
195 HSSFWorkbook workbook
= new HSSFWorkbook();
196 HSSFSheet sheet
= workbook
.createSheet("Test Clone");
197 HSSFRow row
= sheet
.createRow((short) 0);
198 HSSFCell cell
= row
.createCell((short) 0);
199 cell
.setCellValue("clone_test");
200 HSSFSheet cloned
= workbook
.cloneSheet(0);
202 //Check for a good clone
203 assertEquals(cloned
.getRow((short)0).getCell((short)0).getStringCellValue(), "clone_test");
205 //Check that the cells are not somehow linked
206 cell
.setCellValue("Difference Check");
207 assertEquals(cloned
.getRow((short)0).getCell((short)0).getStringCellValue(), "clone_test");
210 /** tests that the sheet name for multiple clones of the same sheet is unique
213 public void testCloneSheetMultipleTimes() {
214 HSSFWorkbook workbook
= new HSSFWorkbook();
215 HSSFSheet sheet
= workbook
.createSheet("Test Clone");
216 HSSFRow row
= sheet
.createRow((short) 0);
217 HSSFCell cell
= row
.createCell((short) 0);
218 cell
.setCellValue("clone_test");
219 //Clone the sheet multiple times
220 workbook
.cloneSheet(0);
221 workbook
.cloneSheet(0);
223 assertNotNull(workbook
.getSheet("Test Clone"));
224 assertNotNull(workbook
.getSheet("Test Clone(1)"));
225 assertNotNull(workbook
.getSheet("Test Clone(2)"));
229 * Setting landscape and portrait stuff on new sheets
231 public void testPrintSetupLandscapeNew() throws Exception
{
232 HSSFWorkbook workbook
= new HSSFWorkbook();
233 HSSFSheet sheetL
= workbook
.createSheet("LandscapeS");
234 HSSFSheet sheetP
= workbook
.createSheet("LandscapeP");
236 // Check two aspects of the print setup
237 assertFalse(sheetL
.getPrintSetup().getLandscape());
238 assertFalse(sheetP
.getPrintSetup().getLandscape());
239 assertEquals(0, sheetL
.getPrintSetup().getCopies());
240 assertEquals(0, sheetP
.getPrintSetup().getCopies());
242 // Change one on each
243 sheetL
.getPrintSetup().setLandscape(true);
244 sheetP
.getPrintSetup().setCopies((short)3);
247 assertTrue(sheetL
.getPrintSetup().getLandscape());
248 assertFalse(sheetP
.getPrintSetup().getLandscape());
249 assertEquals(0, sheetL
.getPrintSetup().getCopies());
250 assertEquals(3, sheetP
.getPrintSetup().getCopies());
252 // Save and re-load, and check still there
253 ByteArrayOutputStream baos
= new ByteArrayOutputStream();
254 workbook
.write(baos
);
255 workbook
= new HSSFWorkbook(new ByteArrayInputStream(baos
.toByteArray()));
257 assertTrue(sheetL
.getPrintSetup().getLandscape());
258 assertFalse(sheetP
.getPrintSetup().getLandscape());
259 assertEquals(0, sheetL
.getPrintSetup().getCopies());
260 assertEquals(3, sheetP
.getPrintSetup().getCopies());
264 * Setting landscape and portrait stuff on existing sheets
266 public void testPrintSetupLandscapeExisting() throws Exception
{
267 String filename
= System
.getProperty("HSSF.testdata.path");
268 filename
= filename
+ "/SimpleWithPageBreaks.xls";
269 HSSFWorkbook workbook
=
270 new HSSFWorkbook(new FileInputStream(filename
));
272 assertEquals(3, workbook
.getNumberOfSheets());
274 HSSFSheet sheetL
= workbook
.getSheetAt(0);
275 HSSFSheet sheetPM
= workbook
.getSheetAt(1);
276 HSSFSheet sheetLS
= workbook
.getSheetAt(2);
278 // Check two aspects of the print setup
279 assertFalse(sheetL
.getPrintSetup().getLandscape());
280 assertTrue(sheetPM
.getPrintSetup().getLandscape());
281 assertTrue(sheetLS
.getPrintSetup().getLandscape());
282 assertEquals(1, sheetL
.getPrintSetup().getCopies());
283 assertEquals(1, sheetPM
.getPrintSetup().getCopies());
284 assertEquals(1, sheetLS
.getPrintSetup().getCopies());
286 // Change one on each
287 sheetL
.getPrintSetup().setLandscape(true);
288 sheetPM
.getPrintSetup().setLandscape(false);
289 sheetPM
.getPrintSetup().setCopies((short)3);
292 assertTrue(sheetL
.getPrintSetup().getLandscape());
293 assertFalse(sheetPM
.getPrintSetup().getLandscape());
294 assertTrue(sheetLS
.getPrintSetup().getLandscape());
295 assertEquals(1, sheetL
.getPrintSetup().getCopies());
296 assertEquals(3, sheetPM
.getPrintSetup().getCopies());
297 assertEquals(1, sheetLS
.getPrintSetup().getCopies());
299 // Save and re-load, and check still there
300 ByteArrayOutputStream baos
= new ByteArrayOutputStream();
301 workbook
.write(baos
);
302 workbook
= new HSSFWorkbook(new ByteArrayInputStream(baos
.toByteArray()));
304 assertTrue(sheetL
.getPrintSetup().getLandscape());
305 assertFalse(sheetPM
.getPrintSetup().getLandscape());
306 assertTrue(sheetLS
.getPrintSetup().getLandscape());
307 assertEquals(1, sheetL
.getPrintSetup().getCopies());
308 assertEquals(3, sheetPM
.getPrintSetup().getCopies());
309 assertEquals(1, sheetLS
.getPrintSetup().getCopies());
312 public void testGroupRows() throws Exception
{
313 HSSFWorkbook workbook
= new HSSFWorkbook();
314 HSSFSheet s
= workbook
.createSheet();
315 HSSFRow r1
= s
.createRow(0);
316 HSSFRow r2
= s
.createRow(1);
317 HSSFRow r3
= s
.createRow(2);
318 HSSFRow r4
= s
.createRow(3);
319 HSSFRow r5
= s
.createRow(4);
321 assertEquals(0, r1
.getOutlineLevel());
322 assertEquals(0, r2
.getOutlineLevel());
323 assertEquals(0, r3
.getOutlineLevel());
324 assertEquals(0, r4
.getOutlineLevel());
325 assertEquals(0, r5
.getOutlineLevel());
329 assertEquals(0, r1
.getOutlineLevel());
330 assertEquals(0, r2
.getOutlineLevel());
331 assertEquals(1, r3
.getOutlineLevel());
332 assertEquals(1, r4
.getOutlineLevel());
333 assertEquals(0, r5
.getOutlineLevel());
336 ByteArrayOutputStream baos
= new ByteArrayOutputStream();
337 workbook
.write(baos
);
338 workbook
= new HSSFWorkbook(
339 new ByteArrayInputStream(baos
.toByteArray())
342 s
= workbook
.getSheetAt(0);
349 assertEquals(0, r1
.getOutlineLevel());
350 assertEquals(0, r2
.getOutlineLevel());
351 assertEquals(1, r3
.getOutlineLevel());
352 assertEquals(1, r4
.getOutlineLevel());
353 assertEquals(0, r5
.getOutlineLevel());
356 public void testGroupRowsExisting() throws Exception
{
357 String filename
= System
.getProperty("HSSF.testdata.path");
358 filename
= filename
+ "/NoGutsRecords.xls";
359 HSSFWorkbook workbook
=
360 new HSSFWorkbook(new FileInputStream(filename
));
362 HSSFSheet s
= workbook
.getSheetAt(0);
363 HSSFRow r1
= s
.getRow(0);
364 HSSFRow r2
= s
.getRow(1);
365 HSSFRow r3
= s
.getRow(2);
366 HSSFRow r4
= s
.getRow(3);
367 HSSFRow r5
= s
.getRow(4);
368 HSSFRow r6
= s
.getRow(5);
370 assertEquals(0, r1
.getOutlineLevel());
371 assertEquals(0, r2
.getOutlineLevel());
372 assertEquals(0, r3
.getOutlineLevel());
373 assertEquals(0, r4
.getOutlineLevel());
374 assertEquals(0, r5
.getOutlineLevel());
375 assertEquals(0, r6
.getOutlineLevel());
377 // This used to complain about lacking guts records
380 assertEquals(0, r1
.getOutlineLevel());
381 assertEquals(0, r2
.getOutlineLevel());
382 assertEquals(1, r3
.getOutlineLevel());
383 assertEquals(1, r4
.getOutlineLevel());
384 assertEquals(1, r5
.getOutlineLevel());
385 assertEquals(0, r6
.getOutlineLevel());
388 ByteArrayOutputStream baos
= new ByteArrayOutputStream();
389 workbook
.write(baos
);
390 workbook
= new HSSFWorkbook(
391 new ByteArrayInputStream(baos
.toByteArray())
394 s
= workbook
.getSheetAt(0);
402 assertEquals(0, r1
.getOutlineLevel());
403 assertEquals(0, r2
.getOutlineLevel());
404 assertEquals(1, r3
.getOutlineLevel());
405 assertEquals(1, r4
.getOutlineLevel());
406 assertEquals(1, r5
.getOutlineLevel());
407 assertEquals(0, r6
.getOutlineLevel());
411 * Test that the ProtectRecord is included when creating or cloning a sheet
413 public void testProtect() {
414 HSSFWorkbook workbook
= new HSSFWorkbook();
415 HSSFSheet hssfSheet
= workbook
.createSheet();
416 Sheet sheet
= hssfSheet
.getSheet();
417 ProtectRecord protect
= sheet
.getProtect();
419 assertFalse(protect
.getProtect());
421 // This will tell us that cloneSheet, and by extension,
422 // the list forms of createSheet leave us with an accessible
424 hssfSheet
.setProtect(true);
425 Sheet cloned
= sheet
.cloneSheet();
426 assertNotNull(cloned
.getProtect());
427 assertTrue(hssfSheet
.getProtect());
430 public void testProtectSheet() {
431 short expected
= (short)0xfef1;
432 HSSFWorkbook wb
= new HSSFWorkbook();
433 HSSFSheet s
= wb
.createSheet();
434 s
.protectSheet("abcdefghij");
435 Sheet sheet
= s
.getSheet();
436 ProtectRecord protect
= sheet
.getProtect();
437 PasswordRecord pass
= sheet
.getPassword();
438 assertTrue("protection should be on",protect
.getProtect());
439 assertTrue("object protection should be on",sheet
.isProtected()[1]);
440 assertTrue("scenario protection should be on",sheet
.isProtected()[2]);
441 assertEquals("well known value for top secret hash should be "+Integer
.toHexString(expected
).substring(4),expected
,pass
.getPassword());
445 public void testZoom()
448 HSSFWorkbook wb
= new HSSFWorkbook();
449 HSSFSheet sheet
= wb
.createSheet();
450 assertEquals(-1, sheet
.getSheet().findFirstRecordLocBySid(SCLRecord
.sid
));
452 assertTrue(sheet
.getSheet().findFirstRecordLocBySid(SCLRecord
.sid
) > 0);
453 SCLRecord sclRecord
= (SCLRecord
) sheet
.getSheet().findFirstRecordBySid(SCLRecord
.sid
);
454 assertEquals(3, sclRecord
.getNumerator());
455 assertEquals(4, sclRecord
.getDenominator());
457 int sclLoc
= sheet
.getSheet().findFirstRecordLocBySid(SCLRecord
.sid
);
458 int window2Loc
= sheet
.getSheet().findFirstRecordLocBySid(WindowTwoRecord
.sid
);
459 assertTrue(sclLoc
== window2Loc
+ 1);
465 * When removing one merged region, it would break
468 public void testRemoveMerged() {
469 HSSFWorkbook wb
= new HSSFWorkbook();
470 HSSFSheet sheet
= wb
.createSheet();
471 Region region
= new Region(0, (short)0, 1, (short)1);
472 sheet
.addMergedRegion(region
);
473 region
= new Region(1, (short)0, 2, (short)1);
474 sheet
.addMergedRegion(region
);
476 sheet
.removeMergedRegion(0);
478 region
= sheet
.getMergedRegionAt(0);
479 assertEquals("Left over region should be starting at row 1", 1, region
.getRowFrom());
481 sheet
.removeMergedRegion(0);
483 assertEquals("there should be no merged regions left!", 0, sheet
.getNumMergedRegions());
485 //an, add, remove, get(0) would null pointer
486 sheet
.addMergedRegion(region
);
487 assertEquals("there should now be one merged region!", 1, sheet
.getNumMergedRegions());
488 sheet
.removeMergedRegion(0);
489 assertEquals("there should now be zero merged regions!", 0, sheet
.getNumMergedRegions());
493 sheet
.addMergedRegion(region
);
494 assertEquals("there should now be one merged region!", 1, sheet
.getNumMergedRegions());
497 assertTrue("there isn't more than one merged region in there", 1 <= sheet
.getNumMergedRegions());
498 region
= sheet
.getMergedRegionAt(0);
499 assertEquals("the merged row to doesnt match the one we put in ", 4, region
.getRowTo());
503 public void testShiftMerged() {
504 HSSFWorkbook wb
= new HSSFWorkbook();
505 HSSFSheet sheet
= wb
.createSheet();
506 HSSFRow row
= sheet
.createRow(0);
507 HSSFCell cell
= row
.createCell((short)0);
508 cell
.setCellValue("first row, first cell");
510 row
= sheet
.createRow(1);
511 cell
= row
.createCell((short)1);
512 cell
.setCellValue("second row, second cell");
514 Region region
= new Region(1, (short)0, 1, (short)1);
515 sheet
.addMergedRegion(region
);
517 sheet
.shiftRows(1, 1, 1);
519 region
= sheet
.getMergedRegionAt(0);
520 assertEquals("Merged region not moved over to row 2", 2, region
.getRowFrom());
525 * Tests the display of gridlines, formulas, and rowcolheadings.
526 * @author Shawn Laubach (slaubach at apache dot org)
528 public void testDisplayOptions() throws Exception
{
529 HSSFWorkbook wb
= new HSSFWorkbook();
530 HSSFSheet sheet
= wb
.createSheet();
532 File tempFile
= TempFile
.createTempFile("display", "test.xls");
533 FileOutputStream stream
= new FileOutputStream(tempFile
);
537 FileInputStream readStream
= new FileInputStream(tempFile
);
538 wb
= new HSSFWorkbook(readStream
);
539 sheet
= wb
.getSheetAt(0);
542 assertEquals(sheet
.isDisplayGridlines(), true);
543 assertEquals(sheet
.isDisplayRowColHeadings(), true);
544 assertEquals(sheet
.isDisplayFormulas(), false);
546 sheet
.setDisplayGridlines(false);
547 sheet
.setDisplayRowColHeadings(false);
548 sheet
.setDisplayFormulas(true);
550 tempFile
= TempFile
.createTempFile("display", "test.xls");
551 stream
= new FileOutputStream(tempFile
);
555 readStream
= new FileInputStream(tempFile
);
556 wb
= new HSSFWorkbook(readStream
);
557 sheet
= wb
.getSheetAt(0);
561 assertEquals(sheet
.isDisplayGridlines(), false);
562 assertEquals(sheet
.isDisplayRowColHeadings(), false);
563 assertEquals(sheet
.isDisplayFormulas(), true);
568 * Make sure the excel file loads work
571 public void testPageBreakFiles() throws Exception
{
572 FileInputStream fis
= null;
573 HSSFWorkbook wb
= null;
575 String filename
= System
.getProperty("HSSF.testdata.path");
577 filename
= filename
+ "/SimpleWithPageBreaks.xls";
578 fis
= new FileInputStream(filename
);
579 wb
= new HSSFWorkbook(fis
);
582 HSSFSheet sheet
= wb
.getSheetAt(0);
583 assertNotNull(sheet
);
585 assertEquals("1 row page break", 1, sheet
.getRowBreaks().length
);
586 assertEquals("1 column page break", 1, sheet
.getColumnBreaks().length
);
588 assertTrue("No row page break", sheet
.isRowBroken(22));
589 assertTrue("No column page break", sheet
.isColumnBroken((short)4));
591 sheet
.setRowBreak(10);
592 sheet
.setColumnBreak((short)13);
594 assertEquals("row breaks number", 2, sheet
.getRowBreaks().length
);
595 assertEquals("column breaks number", 2, sheet
.getColumnBreaks().length
);
597 File tempFile
= TempFile
.createTempFile("display", "testPagebreaks.xls");
598 FileOutputStream stream
= new FileOutputStream(tempFile
);
602 wb
= new HSSFWorkbook(new FileInputStream(tempFile
));
603 sheet
= wb
.getSheetAt(0);
605 assertTrue("No row page break", sheet
.isRowBroken(22));
606 assertTrue("No column page break", sheet
.isColumnBroken((short)4));
609 assertEquals("row breaks number", 2, sheet
.getRowBreaks().length
);
610 assertEquals("column breaks number", 2, sheet
.getColumnBreaks().length
);
615 public void testDBCSName () throws Exception
{
616 FileInputStream fis
= null;
617 HSSFWorkbook wb
= null;
619 String filename
= System
.getProperty("HSSF.testdata.path");
621 filename
= filename
+ "/DBCSSheetName.xls";
622 fis
= new FileInputStream(filename
);
623 wb
= new HSSFWorkbook(fis
);
624 HSSFSheet s
= wb
.getSheetAt(1);
625 assertEquals ("DBCS Sheet Name 2", wb
.getSheetName(1),"\u090f\u0915" );
626 assertEquals("DBCS Sheet Name 1", wb
.getSheetName(0),"\u091c\u093e");
630 * Testing newly added method that exposes the WINDOW2.toprow
631 * parameter to allow setting the toprow in the visible view
632 * of the sheet when it is first opened.
634 public void testTopRow() throws Exception
636 FileInputStream fis
= null;
637 HSSFWorkbook wb
= null;
639 String filename
= System
.getProperty("HSSF.testdata.path");
641 filename
= filename
+ "/SimpleWithPageBreaks.xls";
642 fis
= new FileInputStream(filename
);
643 wb
= new HSSFWorkbook(fis
);
646 HSSFSheet sheet
= wb
.getSheetAt(0);
647 assertNotNull(sheet
);
649 short toprow
= (short) 100;
650 short leftcol
= (short) 50;
651 sheet
.showInPane(toprow
,leftcol
);
652 assertEquals("HSSFSheet.getTopRow()", toprow
, sheet
.getTopRow());
653 assertEquals("HSSFSheet.getLeftCol()", leftcol
, sheet
.getLeftCol());
656 /** cell with formula becomes null on cloning a sheet*/
657 public void test35084() {
659 HSSFWorkbook wb
= new HSSFWorkbook();
660 HSSFSheet s
=wb
.createSheet("Sheet1");
661 HSSFRow r
= s
.createRow(0);
662 r
.createCell((short)0).setCellValue(1);
663 r
.createCell((short)1).setCellFormula("A1*2");
664 HSSFSheet s1
= wb
.cloneSheet(0);
666 assertEquals("double" ,r
.getCell((short)0).getNumericCellValue(),(double)1,0); //sanity check
667 assertNotNull(r
.getCell((short)1));
668 assertEquals("formula", r
.getCell((short)1).getCellFormula(), "A1*2");
671 /** test that new default column styles get applied */
672 public void testDefaultColumnStyle() {
673 HSSFWorkbook wb
= new HSSFWorkbook();
674 HSSFCellStyle style
= wb
.createCellStyle();
675 HSSFSheet s
= wb
.createSheet();
676 s
.setDefaultColumnStyle((short)0, style
);
677 HSSFRow r
= s
.createRow(0);
678 HSSFCell c
= r
.createCell((short)0);
679 assertEquals("style should match", style
.getIndex(), c
.getCellStyle().getIndex());
686 public void testAddEmptyRow() throws Exception
{
687 //try to add 5 empty rows to a new sheet
688 HSSFWorkbook workbook
= new HSSFWorkbook();
689 HSSFSheet sheet
= workbook
.createSheet();
690 for (int i
= 0; i
< 5; i
++) sheet
.createRow(i
);
692 ByteArrayOutputStream out
= new ByteArrayOutputStream();
696 workbook
= new HSSFWorkbook(new ByteArrayInputStream(out
.toByteArray()));
697 assertTrue("No Exceptions while reading file", true);
699 //try adding empty rows in an existing worksheet
700 String cwd
= System
.getProperty("HSSF.testdata.path");
701 FileInputStream in
= new FileInputStream(new File(cwd
, "Simple.xls"));
702 workbook
= new HSSFWorkbook(in
);
704 assertTrue("No Exceptions while reading file", true);
706 sheet
= workbook
.getSheetAt(0);
707 for (int i
= 3; i
< 10; i
++) sheet
.createRow(i
);
709 out
= new ByteArrayOutputStream();
713 workbook
= new HSSFWorkbook(new ByteArrayInputStream(out
.toByteArray()));
714 assertTrue("No Exceptions while reading file", true);
718 public void testAutoSizeColumn() throws Exception
{
719 String filename
= System
.getProperty("HSSF.testdata.path");
720 filename
= filename
+ "/43902.xls";
721 String sheetName
= "my sheet";
722 FileInputStream is
= new FileInputStream(filename
);
723 POIFSFileSystem fs
= new POIFSFileSystem(is
);
724 HSSFWorkbook wb
= new HSSFWorkbook(fs
);
725 HSSFSheet sheet
= wb
.getSheet(sheetName
);
727 // Can't use literal numbers for column sizes, as
728 // will come out with different values on different
729 // machines based on the fonts available.
730 // So, we use ranges, which are pretty large, but
731 // thankfully don't overlap!
732 int minWithRow1And2
= 6400;
733 int maxWithRow1And2
= 7800;
734 int minWithRow1Only
= 3024;
735 int maxWithRow1Only
= 3300;
737 // autoSize the first column and check its size before the merged region (1,0,1,1) is set:
738 // it has to be based on the 2nd row width
739 sheet
.autoSizeColumn((short)0);
740 assertTrue("Column autosized with only one row: wrong width", sheet
.getColumnWidth((short)0) >= minWithRow1And2
);
741 assertTrue("Column autosized with only one row: wrong width", sheet
.getColumnWidth((short)0) <= maxWithRow1And2
);
743 //create a region over the 2nd row and auto size the first column
744 sheet
.addMergedRegion(new Region(1,(short)0,1,(short)1));
745 sheet
.autoSizeColumn((short)0);
746 ByteArrayOutputStream out
= new ByteArrayOutputStream();
750 // check that the autoSized column width has ignored the 2nd row
751 // because it is included in a merged region (Excel like behavior)
752 HSSFWorkbook wb2
= new HSSFWorkbook(new ByteArrayInputStream(out
.toByteArray()));
753 HSSFSheet sheet2
= wb2
.getSheet(sheetName
);
754 assertTrue(sheet2
.getColumnWidth((short)0) >= minWithRow1Only
);
755 assertTrue(sheet2
.getColumnWidth((short)0) <= maxWithRow1Only
);
757 // remove the 2nd row merged region and check that the 2nd row value is used to the autoSizeColumn width
758 sheet2
.removeMergedRegion(1);
759 sheet2
.autoSizeColumn((short)0);
760 out
= new ByteArrayOutputStream();
763 HSSFWorkbook wb3
= new HSSFWorkbook(new ByteArrayInputStream(out
.toByteArray()));
764 HSSFSheet sheet3
= wb3
.getSheet(sheetName
);
765 assertTrue(sheet3
.getColumnWidth((short)0) >= minWithRow1And2
);
766 assertTrue(sheet3
.getColumnWidth((short)0) <= maxWithRow1And2
);
769 public static void main(java
.lang
.String
[] args
) {
770 junit
.textui
.TestRunner
.run(TestHSSFSheet
.class);