src/testcases/org/apache/poi/hssf/usermodel/TestHSSFSheet.java

   1
   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
   9
  10        http://www.apache.org/licenses/LICENSE-2.0
  11
  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 ==================================================================== */
  18
  19
  20 package org.apache.poi.hssf.usermodel;
  21
  22 import java.io.*;
  23
  24 import junit.framework.TestCase;
  25
  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;
  37
  38 /**
  39  * Tests HSSFSheet.  This test case is very incomplete at the moment.
  40  *
  41  *
  42  * @author Glen Stampoultzis (glens at apache.org)
  43  * @author Andrew C. Oliver (acoliver apache org)
  44  */
  45
  46 public class TestHSSFSheet
  47         extends TestCase
  48 {
  49     public TestHSSFSheet(String s)
  50     {
  51         super(s);
  52     }
  53
  54     /**
  55      * Test the gridset field gets set as expected.
  56      */
  57
  58     public void testBackupRecord()
  59             throws Exception
  60     {
  61         HSSFWorkbook wb = new HSSFWorkbook();
  62         HSSFSheet s = wb.createSheet();
  63         Sheet sheet = s.getSheet();
  64
  65         assertEquals(true, sheet.getGridsetRecord().getGridset());
  66         s.setGridsPrinted(true);
  67         assertEquals(false, sheet.getGridsetRecord().getGridset());
  68     }
  69
  70     /**
  71      * Test vertically centered output.
  72      */
  73
  74     public void testVerticallyCenter()
  75             throws Exception
  76     {
  77         HSSFWorkbook wb = new HSSFWorkbook();
  78         HSSFSheet s = wb.createSheet();
  79         Sheet sheet = s.getSheet();
  80         VCenterRecord record =
  81                 (VCenterRecord) sheet.findFirstRecordBySid(VCenterRecord.sid);
  82
  83         assertEquals(false, record.getVCenter());
  84         s.setVerticallyCenter(true);
  85         assertEquals(true, record.getVCenter());
  86
  87         // wb.write(new FileOutputStream("c:\\test.xls"));
  88     }
  89
  90     /**
  91      * Test horizontally centered output.
  92      */
  93
  94     public void testHorizontallyCenter()
  95             throws Exception
  96     {
  97         HSSFWorkbook wb = new HSSFWorkbook();
  98         HSSFSheet s = wb.createSheet();
  99         Sheet sheet = s.getSheet();
 100         HCenterRecord record =
 101                 (HCenterRecord) sheet.findFirstRecordBySid(HCenterRecord.sid);
 102
 103         assertEquals(false, record.getHCenter());
 104         s.setHorizontallyCenter(true);
 105         assertEquals(true, record.getHCenter());
 106
 107     }
 108
 109
 110     /**
 111      * Test WSBboolRecord fields get set in the user model.
 112      */
 113
 114     public void testWSBool()
 115     {
 116         HSSFWorkbook wb = new HSSFWorkbook();
 117         HSSFSheet s = wb.createSheet();
 118         Sheet sheet = s.getSheet();
 119         WSBoolRecord record =
 120                 (WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid);
 121
 122         // Check defaults
 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());
 131
 132         // Alter
 133         s.setAlternativeExpression(false);
 134         s.setAlternativeFormula(false);
 135         s.setAutobreaks(true);
 136         s.setDialog(true);
 137         s.setDisplayGuts(true);
 138         s.setFitToPage(false);
 139         s.setRowSumsBelow(true);
 140         s.setRowSumsRight(true);
 141
 142         // Check
 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());
 159     }
 160
 161     public void testReadBooleans()
 162             throws Exception
 163     {
 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);
 174         stream.close();
 175
 176         FileInputStream readStream = new FileInputStream(tempFile);
 177         workbook = new HSSFWorkbook(readStream);
 178         sheet = workbook.getSheetAt(0);
 179         row = sheet.getRow(2);
 180         stream.close();
 181         tempFile.delete();
 182         assertNotNull(row);
 183         assertEquals(2, row.getPhysicalNumberOfCells());
 184     }
 185
 186     public void testRemoveRow()
 187     {
 188         HSSFWorkbook workbook = new HSSFWorkbook();
 189         HSSFSheet sheet = workbook.createSheet("Test boolean");
 190         HSSFRow row = sheet.createRow((short) 2);
 191         sheet.removeRow(row);
 192     }
 193
 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);
 201
 202         //Check for a good clone
 203         assertEquals(cloned.getRow((short)0).getCell((short)0).getStringCellValue(), "clone_test");
 204
 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");
 208     }
 209
 210     /** tests that the sheet name for multiple clones of the same sheet is unique
 211      * BUG 37416
 212      */
 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);
 222
 223         assertNotNull(workbook.getSheet("Test Clone"));
 224         assertNotNull(workbook.getSheet("Test Clone(1)"));
 225         assertNotNull(workbook.getSheet("Test Clone(2)"));
 226     }
 227
 228     /**
 229      * Setting landscape and portrait stuff on new sheets
 230      */
 231     public void testPrintSetupLandscapeNew() throws Exception {
 232         HSSFWorkbook workbook = new HSSFWorkbook();
 233         HSSFSheet sheetL = workbook.createSheet("LandscapeS");
 234         HSSFSheet sheetP = workbook.createSheet("LandscapeP");
 235
 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());
 241
 242         // Change one on each
 243         sheetL.getPrintSetup().setLandscape(true);
 244         sheetP.getPrintSetup().setCopies((short)3);
 245
 246         // Check taken
 247         assertTrue(sheetL.getPrintSetup().getLandscape());
 248         assertFalse(sheetP.getPrintSetup().getLandscape());
 249         assertEquals(0, sheetL.getPrintSetup().getCopies());
 250         assertEquals(3, sheetP.getPrintSetup().getCopies());
 251
 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()));
 256
 257         assertTrue(sheetL.getPrintSetup().getLandscape());
 258         assertFalse(sheetP.getPrintSetup().getLandscape());
 259         assertEquals(0, sheetL.getPrintSetup().getCopies());
 260         assertEquals(3, sheetP.getPrintSetup().getCopies());
 261     }
 262
 263     /**
 264      * Setting landscape and portrait stuff on existing sheets
 265      */
 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));
 271
 272         assertEquals(3, workbook.getNumberOfSheets());
 273
 274         HSSFSheet sheetL = workbook.getSheetAt(0);
 275         HSSFSheet sheetPM = workbook.getSheetAt(1);
 276         HSSFSheet sheetLS = workbook.getSheetAt(2);
 277
 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());
 285
 286         // Change one on each
 287         sheetL.getPrintSetup().setLandscape(true);
 288         sheetPM.getPrintSetup().setLandscape(false);
 289         sheetPM.getPrintSetup().setCopies((short)3);
 290
 291         // Check taken
 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());
 298
 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()));
 303
 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());
 310     }
 311
 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);
 320
 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());
 326
 327         s.groupRow(2,3);
 328
 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());
 334
 335         // Save and re-open
 336         ByteArrayOutputStream baos = new ByteArrayOutputStream();
 337         workbook.write(baos);
 338         workbook = new HSSFWorkbook(
 339                         new ByteArrayInputStream(baos.toByteArray())
 340         );
 341
 342         s = workbook.getSheetAt(0);
 343         r1 = s.getRow(0);
 344         r2 = s.getRow(1);
 345         r3 = s.getRow(2);
 346         r4 = s.getRow(3);
 347         r5 = s.getRow(4);
 348
 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());
 354     }
 355
 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));
 361
 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);
 369
 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());
 376
 377         // This used to complain about lacking guts records
 378         s.groupRow(2, 4);
 379
 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());
 386
 387         // Save and re-open
 388         ByteArrayOutputStream baos = new ByteArrayOutputStream();
 389         workbook.write(baos);
 390         workbook = new HSSFWorkbook(
 391                         new ByteArrayInputStream(baos.toByteArray())
 392         );
 393
 394         s = workbook.getSheetAt(0);
 395         r1 = s.getRow(0);
 396         r2 = s.getRow(1);
 397         r3 = s.getRow(2);
 398         r4 = s.getRow(3);
 399         r5 = s.getRow(4);
 400         r6 = s.getRow(5);
 401
 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());
 408     }
 409
 410         /**
 411          * Test that the ProtectRecord is included when creating or cloning a sheet
 412          */
 413         public void testProtect() {
 414                 HSSFWorkbook workbook = new HSSFWorkbook();
 415                 HSSFSheet hssfSheet = workbook.createSheet();
 416                 Sheet sheet = hssfSheet.getSheet();
 417                 ProtectRecord protect = sheet.getProtect();
 418
 419                 assertFalse(protect.getProtect());
 420
 421                 // This will tell us that cloneSheet, and by extension,
 422                 // the list forms of createSheet leave us with an accessible
 423                 // ProtectRecord.
 424                 hssfSheet.setProtect(true);
 425                 Sheet cloned = sheet.cloneSheet();
 426                 assertNotNull(cloned.getProtect());
 427                 assertTrue(hssfSheet.getProtect());
 428         }
 429
 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());
 442     }
 443
 444
 445     public void testZoom()
 446             throws Exception
 447     {
 448         HSSFWorkbook wb = new HSSFWorkbook();
 449         HSSFSheet sheet = wb.createSheet();
 450         assertEquals(-1, sheet.getSheet().findFirstRecordLocBySid(SCLRecord.sid));
 451         sheet.setZoom(3,4);
 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());
 456
 457         int sclLoc = sheet.getSheet().findFirstRecordLocBySid(SCLRecord.sid);
 458         int window2Loc = sheet.getSheet().findFirstRecordLocBySid(WindowTwoRecord.sid);
 459         assertTrue(sclLoc == window2Loc + 1);
 460
 461     }
 462
 463
 464         /**
 465          * When removing one merged region, it would break
 466          *
 467          */
 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);
 475
 476         sheet.removeMergedRegion(0);
 477
 478         region = sheet.getMergedRegionAt(0);
 479         assertEquals("Left over region should be starting at row 1", 1, region.getRowFrom());
 480
 481         sheet.removeMergedRegion(0);
 482
 483                 assertEquals("there should be no merged regions left!", 0, sheet.getNumMergedRegions());
 484
 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());
 490                 //add it again!
 491                 region.setRowTo(4);
 492
 493                 sheet.addMergedRegion(region);
 494                 assertEquals("there should now be one merged region!", 1, sheet.getNumMergedRegions());
 495
 496                 //should exist now!
 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());
 500
 501     }
 502
 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");
 509
 510                 row = sheet.createRow(1);
 511                 cell = row.createCell((short)1);
 512                 cell.setCellValue("second row, second cell");
 513
 514                 Region region = new Region(1, (short)0, 1, (short)1);
 515                 sheet.addMergedRegion(region);
 516
 517                 sheet.shiftRows(1, 1, 1);
 518
 519                 region = sheet.getMergedRegionAt(0);
 520                 assertEquals("Merged region not moved over to row 2", 2, region.getRowFrom());
 521
 522         }
 523
 524     /**
 525      * Tests the display of gridlines, formulas, and rowcolheadings.
 526      * @author Shawn Laubach (slaubach at apache dot org)
 527      */
 528     public void testDisplayOptions() throws Exception {
 529         HSSFWorkbook wb = new HSSFWorkbook();
 530         HSSFSheet sheet = wb.createSheet();
 531
 532         File tempFile = TempFile.createTempFile("display", "test.xls");
 533         FileOutputStream stream = new FileOutputStream(tempFile);
 534         wb.write(stream);
 535         stream.close();
 536
 537         FileInputStream readStream = new FileInputStream(tempFile);
 538         wb = new HSSFWorkbook(readStream);
 539         sheet = wb.getSheetAt(0);
 540         readStream.close();
 541
 542         assertEquals(sheet.isDisplayGridlines(), true);
 543         assertEquals(sheet.isDisplayRowColHeadings(), true);
 544         assertEquals(sheet.isDisplayFormulas(), false);
 545
 546         sheet.setDisplayGridlines(false);
 547         sheet.setDisplayRowColHeadings(false);
 548         sheet.setDisplayFormulas(true);
 549
 550         tempFile = TempFile.createTempFile("display", "test.xls");
 551         stream = new FileOutputStream(tempFile);
 552         wb.write(stream);
 553         stream.close();
 554
 555         readStream = new FileInputStream(tempFile);
 556         wb = new HSSFWorkbook(readStream);
 557         sheet = wb.getSheetAt(0);
 558         readStream.close();
 559
 560
 561         assertEquals(sheet.isDisplayGridlines(), false);
 562         assertEquals(sheet.isDisplayRowColHeadings(), false);
 563         assertEquals(sheet.isDisplayFormulas(), true);
 564     }
 565
 566
 567     /**
 568      * Make sure the excel file loads work
 569      *
 570      */
 571     public void testPageBreakFiles() throws Exception{
 572         FileInputStream fis = null;
 573         HSSFWorkbook wb     = null;
 574
 575         String filename = System.getProperty("HSSF.testdata.path");
 576
 577         filename = filename + "/SimpleWithPageBreaks.xls";
 578         fis = new FileInputStream(filename);
 579         wb = new HSSFWorkbook(fis);
 580         fis.close();
 581
 582         HSSFSheet sheet = wb.getSheetAt(0);
 583         assertNotNull(sheet);
 584
 585         assertEquals("1 row page break", 1, sheet.getRowBreaks().length);
 586         assertEquals("1 column page break", 1, sheet.getColumnBreaks().length);
 587
 588         assertTrue("No row page break", sheet.isRowBroken(22));
 589         assertTrue("No column page break", sheet.isColumnBroken((short)4));
 590
 591         sheet.setRowBreak(10);
 592         sheet.setColumnBreak((short)13);
 593
 594         assertEquals("row breaks number", 2, sheet.getRowBreaks().length);
 595         assertEquals("column breaks number", 2, sheet.getColumnBreaks().length);
 596
 597         File tempFile = TempFile.createTempFile("display", "testPagebreaks.xls");
 598         FileOutputStream stream = new FileOutputStream(tempFile);
 599         wb.write(stream);
 600         stream.close();
 601
 602         wb = new HSSFWorkbook(new FileInputStream(tempFile));
 603         sheet = wb.getSheetAt(0);
 604
 605         assertTrue("No row page break", sheet.isRowBroken(22));
 606         assertTrue("No column page break", sheet.isColumnBroken((short)4));
 607
 608
 609         assertEquals("row breaks number", 2, sheet.getRowBreaks().length);
 610         assertEquals("column breaks number", 2, sheet.getColumnBreaks().length);
 611
 612
 613     }
 614
 615     public void testDBCSName () throws Exception {
 616         FileInputStream fis = null;
 617         HSSFWorkbook wb     = null;
 618
 619         String filename = System.getProperty("HSSF.testdata.path");
 620
 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");
 627     }
 628
 629     /**
 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.
 633      */
 634     public void testTopRow() throws Exception
 635     {
 636         FileInputStream fis = null;
 637         HSSFWorkbook wb     = null;
 638
 639         String filename = System.getProperty("HSSF.testdata.path");
 640
 641         filename = filename + "/SimpleWithPageBreaks.xls";
 642         fis = new FileInputStream(filename);
 643         wb = new HSSFWorkbook(fis);
 644         fis.close();
 645
 646         HSSFSheet sheet = wb.getSheetAt(0);
 647         assertNotNull(sheet);
 648
 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());
 654     }
 655
 656     /** cell with formula becomes null on cloning a sheet*/
 657          public void test35084() {
 658
 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);
 665         r=s1.getRow(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");
 669    }
 670
 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());
 680     }
 681
 682
 683     /**
 684      *
 685      */
 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);
 691
 692         ByteArrayOutputStream out = new ByteArrayOutputStream();
 693         workbook.write(out);
 694         out.close();
 695
 696         workbook = new HSSFWorkbook(new ByteArrayInputStream(out.toByteArray()));
 697         assertTrue("No Exceptions while reading file", true);
 698
 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);
 703         in.close();
 704         assertTrue("No Exceptions while reading file", true);
 705
 706         sheet = workbook.getSheetAt(0);
 707         for (int i = 3; i < 10; i++) sheet.createRow(i);
 708
 709         out = new ByteArrayOutputStream();
 710         workbook.write(out);
 711         out.close();
 712
 713         workbook = new HSSFWorkbook(new ByteArrayInputStream(out.toByteArray()));
 714         assertTrue("No Exceptions while reading file", true);
 715
 716     }
 717
 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);
 726
 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;
 736
 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);
 742
 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();
 747                 wb.write(out);
 748                 out.close();
 749
 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);
 756
 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();
 761                 wb2.write(out);
 762                 out.close();
 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);
 767     }
 768
 769         public static void main(java.lang.String[] args) {
 770                  junit.textui.TestRunner.run(TestHSSFSheet.class);
 771         }
 772 }