src/testcases/org/apache/poi/hssf/usermodel/TestHSSFCell.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.File;
  23 import java.io.FileInputStream;
  24 import java.io.FileOutputStream;
  25 import java.util.Date;
  26 import java.util.GregorianCalendar;
  27 import java.util.List;
  28
  29 import junit.framework.TestCase;
  30
  31 import org.apache.poi.hssf.model.Sheet;
  32 import org.apache.poi.hssf.record.HyperlinkRecord;
  33 import org.apache.poi.hssf.util.HSSFColor;
  34 import org.apache.poi.poifs.filesystem.POIFSFileSystem;
  35 import org.apache.poi.util.TempFile;
  36
  37 /**
  38  * Tests various functionity having to do with HSSFCell.  For instance support for
  39  * paticular datatypes, etc.
  40  * @author Andrew C. Oliver (andy at superlinksoftware dot com)
  41  * @author  Dan Sherman (dsherman at isisph.com)
  42  * @author Alex Jacoby (ajacoby at gmail.com)
  43  */
  44
  45 public class TestHSSFCell
  46 extends TestCase {
  47     public TestHSSFCell(String s) {
  48         super(s);
  49     }
  50
  51     /**
  52      * test that Boolean and Error types (BoolErrRecord) are supported properly.
  53      */
  54     public void testBoolErr()
  55             throws java.io.IOException {
  56         String readFilename = System.getProperty("HSSF.testdata.path");
  57
  58             File file = TempFile.createTempFile("testBoolErr",".xls");
  59             FileOutputStream out    = new FileOutputStream(file);
  60             HSSFWorkbook     wb     = new HSSFWorkbook();
  61             HSSFSheet        s      = wb.createSheet("testSheet1");
  62             HSSFRow          r      = null;
  63             HSSFCell         c      = null;
  64             r = s.createRow((short)0);
  65             c=r.createCell((short)1);
  66             //c.setCellType(HSSFCell.CELL_TYPE_BOOLEAN);
  67             c.setCellValue(true);
  68
  69             c=r.createCell((short)2);
  70             //c.setCellType(HSSFCell.CELL_TYPE_BOOLEAN);
  71             c.setCellValue(false);
  72
  73             r = s.createRow((short)1);
  74             c=r.createCell((short)1);
  75             //c.setCellType(HSSFCell.CELL_TYPE_ERROR);
  76             c.setCellErrorValue((byte)0);
  77
  78             c=r.createCell((short)2);
  79             //c.setCellType(HSSFCell.CELL_TYPE_ERROR);
  80             c.setCellErrorValue((byte)7);
  81
  82
  83             wb.write(out);
  84             out.close();
  85
  86             assertTrue("file exists",file.exists());
  87
  88             FileInputStream in = new FileInputStream(file);
  89             wb = new HSSFWorkbook(in);
  90             s = wb.getSheetAt(0);
  91             r = s.getRow(0);
  92             c = r.getCell((short)1);
  93             assertTrue("boolean value 0,1 = true",c.getBooleanCellValue());
  94             c = r.getCell((short)2);
  95             assertTrue("boolean value 0,2 = false",c.getBooleanCellValue()==false);
  96             r = s.getRow(1);
  97             c = r.getCell((short)1);
  98             assertTrue("boolean value 0,1 = 0",c.getErrorCellValue() == 0);
  99             c = r.getCell((short)2);
 100             assertTrue("boolean value 0,2 = 7",c.getErrorCellValue() == 7);
 101
 102             in.close();
 103     }
 104
 105     /**
 106      * Checks that the recognition of files using 1904 date windowing
 107      *  is working properly. Conversion of the date is also an issue,
 108      *  but there's a separate unit test for that.
 109      */
 110      public void testDateWindowingRead() throws Exception {
 111          GregorianCalendar cal = new GregorianCalendar(2000,0,1); // Jan. 1, 2000
 112          Date date = cal.getTime();
 113          String path = System.getProperty("HSSF.testdata.path");
 114
 115          // first check a file with 1900 Date Windowing
 116          String filename = path + "/1900DateWindowing.xls";
 117          FileInputStream stream   = new FileInputStream(filename);
 118          POIFSFileSystem fs       = new POIFSFileSystem(stream);
 119          HSSFWorkbook    workbook = new HSSFWorkbook(fs);
 120          HSSFSheet       sheet    = workbook.getSheetAt(0);
 121
 122          assertEquals("Date from file using 1900 Date Windowing",
 123                          date.getTime(),
 124                             sheet.getRow(0).getCell((short)0)
 125                                .getDateCellValue().getTime());
 126          stream.close();
 127
 128          // now check a file with 1904 Date Windowing
 129          filename = path + "/1904DateWindowing.xls";
 130          stream   = new FileInputStream(filename);
 131          fs       = new POIFSFileSystem(stream);
 132          workbook = new HSSFWorkbook(fs);
 133          sheet    = workbook.getSheetAt(0);
 134
 135          assertEquals("Date from file using 1904 Date Windowing",
 136                          date.getTime(),
 137                             sheet.getRow(0).getCell((short)0)
 138                                .getDateCellValue().getTime());
 139          stream.close();
 140      }
 141
 142      /**
 143       * Checks that dates are properly written to both types of files:
 144       * those with 1900 and 1904 date windowing.  Note that if the
 145       * previous test ({@link #testDateWindowingRead}) fails, the
 146       * results of this test are meaningless.
 147       */
 148       public void testDateWindowingWrite() throws Exception {
 149           GregorianCalendar cal = new GregorianCalendar(2000,0,1); // Jan. 1, 2000
 150           Date date = cal.getTime();
 151           String path = System.getProperty("HSSF.testdata.path");
 152
 153           // first check a file with 1900 Date Windowing
 154           String filename = path + "/1900DateWindowing.xls";
 155           writeCell(filename, 0, (short) 1, date);
 156           assertEquals("Date from file using 1900 Date Windowing",
 157                           date.getTime(),
 158                           readCell(filename, 0, (short) 1).getTime());
 159
 160           // now check a file with 1904 Date Windowing
 161           filename = path + "/1904DateWindowing.xls";
 162           writeCell(filename, 0, (short) 1, date);
 163           assertEquals("Date from file using 1900 Date Windowing",
 164                           date.getTime(),
 165                           readCell(filename, 0, (short) 1).getTime());
 166       }
 167
 168       /**
 169        * Sets cell value and writes file.
 170        */
 171       private void writeCell(String filename,
 172                  int rowIdx, short colIdx, Date date) throws Exception {
 173           FileInputStream stream   = new FileInputStream(filename);
 174           POIFSFileSystem fs       = new POIFSFileSystem(stream);
 175           HSSFWorkbook    workbook = new HSSFWorkbook(fs);
 176           HSSFSheet       sheet    = workbook.getSheetAt(0);
 177           HSSFRow         row      = sheet.getRow(rowIdx);
 178           HSSFCell        cell     = row.getCell(colIdx);
 179
 180           if (cell == null) {
 181                   cell = row.createCell(colIdx);
 182           }
 183           cell.setCellValue(date);
 184
 185           // Write the file
 186           stream.close();
 187           FileOutputStream oStream = new FileOutputStream(filename);
 188           workbook.write(oStream);
 189           oStream.close();
 190       }
 191
 192       /**
 193        * Reads cell value from file.
 194        */
 195       private Date readCell(String filename,
 196                  int rowIdx, short colIdx) throws Exception {
 197           FileInputStream stream   = new FileInputStream(filename);
 198           POIFSFileSystem fs       = new POIFSFileSystem(stream);
 199           HSSFWorkbook    workbook = new HSSFWorkbook(fs);
 200           HSSFSheet       sheet    = workbook.getSheetAt(0);
 201           HSSFRow         row      = sheet.getRow(rowIdx);
 202           HSSFCell        cell     = row.getCell(colIdx);
 203           return cell.getDateCellValue();
 204       }
 205
 206     /**
 207      * Tests that the active cell can be correctly read and set
 208      */
 209     public void testActiveCell() throws Exception
 210     {
 211         //read in sample
 212         String dir = System.getProperty("HSSF.testdata.path");
 213         File sample = new File(dir + "/Simple.xls");
 214         assertTrue("Simple.xls exists and is readable", sample.canRead());
 215         FileInputStream fis = new FileInputStream(sample);
 216         HSSFWorkbook book = new HSSFWorkbook(fis);
 217         fis.close();
 218
 219         //check initial position
 220         HSSFSheet umSheet = book.getSheetAt(0);
 221         Sheet s = umSheet.getSheet();
 222         assertEquals("Initial active cell should be in col 0",
 223             (short) 0, s.getActiveCellCol());
 224         assertEquals("Initial active cell should be on row 1",
 225             1, s.getActiveCellRow());
 226
 227         //modify position through HSSFCell
 228         HSSFCell cell = umSheet.createRow(3).createCell((short) 2);
 229         cell.setAsActiveCell();
 230         assertEquals("After modify, active cell should be in col 2",
 231             (short) 2, s.getActiveCellCol());
 232         assertEquals("After modify, active cell should be on row 3",
 233             3, s.getActiveCellRow());
 234
 235         //write book to temp file; read and verify that position is serialized
 236         File temp = TempFile.createTempFile("testActiveCell", ".xls");
 237         FileOutputStream fos = new FileOutputStream(temp);
 238         book.write(fos);
 239         fos.close();
 240
 241         fis = new FileInputStream(temp);
 242         book = new HSSFWorkbook(fis);
 243         fis.close();
 244         umSheet = book.getSheetAt(0);
 245         s = umSheet.getSheet();
 246
 247         assertEquals("After serialize, active cell should be in col 2",
 248             (short) 2, s.getActiveCellCol());
 249         assertEquals("After serialize, active cell should be on row 3",
 250             3, s.getActiveCellRow());
 251     }
 252
 253     /**
 254      * test that Cell Styles being applied to formulas remain intact
 255      */
 256     public void testFormulaStyle()
 257             throws java.io.IOException {
 258         String readFilename = System.getProperty("HSSF.testdata.path");
 259
 260             File file = TempFile.createTempFile("testFormulaStyle",".xls");
 261             FileOutputStream out    = new FileOutputStream(file);
 262             HSSFWorkbook     wb     = new HSSFWorkbook();
 263             HSSFSheet        s      = wb.createSheet("testSheet1");
 264             HSSFRow          r      = null;
 265             HSSFCell         c      = null;
 266             HSSFCellStyle cs = wb.createCellStyle();
 267             HSSFFont f = wb.createFont();
 268             f.setFontHeightInPoints((short) 20);
 269             f.setColor((short) HSSFColor.RED.index);
 270             f.setBoldweight(f.BOLDWEIGHT_BOLD);
 271             f.setFontName("Arial Unicode MS");
 272             cs.setFillBackgroundColor((short)3);
 273             cs.setFont(f);
 274             cs.setBorderTop((short)1);
 275             cs.setBorderRight((short)1);
 276             cs.setBorderLeft((short)1);
 277             cs.setBorderBottom((short)1);
 278
 279             r = s.createRow((short)0);
 280             c=r.createCell((short)0);
 281             c.setCellStyle(cs);
 282             c.setCellFormula("2*3");
 283
 284             wb.write(out);
 285             out.close();
 286
 287             assertTrue("file exists",file.exists());
 288
 289             FileInputStream in = new FileInputStream(file);
 290             wb = new HSSFWorkbook(in);
 291             s = wb.getSheetAt(0);
 292             r = s.getRow(0);
 293             c = r.getCell((short)0);
 294
 295             assertTrue("Formula Cell at 0,0", (c.getCellType()==c.CELL_TYPE_FORMULA));
 296             cs = c.getCellStyle();
 297
 298             assertNotNull("Formula Cell Style", cs);
 299             assertTrue("Font Index Matches", (cs.getFontIndex() == f.getIndex()));
 300             assertTrue("Top Border", (cs.getBorderTop() == (short)1));
 301             assertTrue("Left Border", (cs.getBorderLeft() == (short)1));
 302             assertTrue("Right Border", (cs.getBorderRight() == (short)1));
 303             assertTrue("Bottom Border", (cs.getBorderBottom() == (short)1));
 304
 305             in.close();
 306     }
 307
 308     public void BROKENtestWithHyperlink() throws Exception {
 309         String dir = System.getProperty("HSSF.testdata.path");
 310         File f = new File(dir, "WithHyperlink.xls");
 311         HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(f));
 312
 313         assertEquals(3, wb.getNumberOfSheets());
 314
 315         // Find our hyperlink record, and check they're
 316         //  as we'd expect
 317         List records = wb.getWorkbook().getHyperlinks();
 318         assertEquals(1, records.size());
 319
 320         HyperlinkRecord link = (HyperlinkRecord)
 321                 records.get(0);
 322         assertNotNull(link);
 323
 324         // Is in A5
 325         assertEquals("Foo", link.getLabel());
 326         assertEquals("http://poi.apache.org/", link.getUrlString());
 327         assertEquals(4, link.getRow());
 328         assertEquals(0, link.getColumn());
 329
 330         // Now check at the HSSFCell level
 331         assertEquals(3, wb.getNumberOfSheets());
 332
 333         HSSFSheet s = wb.getSheetAt(1);
 334         HSSFRow r = s.getRow(4);
 335         assertNotNull(r);
 336         HSSFCell c = r.getCell((short)0);
 337     }
 338
 339     public void BROKENtestWithTwoHyperlinks() throws Exception {
 340         String dir = System.getProperty("HSSF.testdata.path");
 341         File f = new File(dir, "WithTwoHyperLinks.xls");
 342         HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(f));
 343
 344         assertEquals(3, wb.getNumberOfSheets());
 345
 346         // Find our hyperlink record, and check they're
 347         //  as we'd expect
 348         List records = wb.getWorkbook().getHyperlinks();
 349         assertEquals(2, records.size());
 350
 351         HyperlinkRecord linkA = (HyperlinkRecord)
 352                 records.get(0);
 353         HyperlinkRecord linkB = (HyperlinkRecord)
 354                         records.get(1);
 355         assertNotNull(linkA);
 356         assertNotNull(linkB);
 357
 358         // Is in A5
 359         assertEquals("Foo", linkA.getLabel());
 360         assertEquals("http://poi.apache.org/", linkA.getUrlString());
 361         assertEquals(4, linkA.getRow());
 362         assertEquals(0, linkA.getColumn());
 363
 364         // Is in B9
 365         assertEquals("Bar", linkB.getLabel());
 366         assertEquals("http://poi.apache.org/", linkB.getUrlString());
 367         assertEquals(8, linkB.getRow());
 368         assertEquals(1, linkB.getColumn());
 369
 370         // Now check at the HSSFCell level
 371         assertEquals(3, wb.getNumberOfSheets());
 372
 373         HSSFSheet s = wb.getSheetAt(1);
 374         HSSFRow r = s.getRow(4);
 375         assertNotNull(r);
 376         HSSFCell c = r.getCell((short)0);
 377     }
 378
 379     /*tests the toString() method of HSSFCell*/
 380     public void testToString() throws Exception {
 381         HSSFWorkbook wb = new HSSFWorkbook();
 382         HSSFSheet s = wb.createSheet("Sheet1");
 383         HSSFRow r = s.createRow(0);
 384         HSSFCell c;
 385         c=r.createCell((short) 0); c.setCellValue(true);
 386         assertEquals("Boolean", "TRUE", c.toString());
 387         c=r.createCell((short) 1); c.setCellValue(1.5);
 388         assertEquals("Numeric", "1.5", c.toString());
 389         c=r.createCell((short)(2)); c.setCellValue("Astring");
 390         assertEquals("String", "Astring", c.toString());
 391         c=r.createCell((short) 3); c.setCellErrorValue((byte) 7);
 392         assertEquals("Error", "#ERR7", c.toString());
 393         c=r.createCell((short)4); c.setCellFormula("A1+B1");
 394         assertEquals("Formula", "A1+B1", c.toString());
 395
 396         //Write out the file, read it in, and then check cell values
 397         File f = File.createTempFile("testCellToString",".xls");
 398         wb.write(new FileOutputStream(f));
 399         wb = new HSSFWorkbook(new FileInputStream(f));
 400         assertTrue("File exists and can be read", f.canRead());
 401
 402         s = wb.getSheetAt(0);r=s.getRow(0);
 403         c=r.getCell((short) 0);
 404         assertEquals("Boolean", "TRUE", c.toString());
 405         c=r.getCell((short) 1);
 406         assertEquals("Numeric", "1.5", c.toString());
 407         c=r.getCell((short)(2));
 408         assertEquals("String", "Astring", c.toString());
 409         c=r.getCell((short) 3);
 410         assertEquals("Error", "#ERR7", c.toString());
 411         c=r.getCell((short)4);
 412         assertEquals("Formula", "A1+B1", c.toString());
 413     }
 414
 415     public static void main(String [] args) {
 416         System.out
 417         .println("Testing org.apache.poi.hssf.usermodel.TestHSSFCell");
 418         junit.textui.TestRunner.run(TestHSSFCell.class);
 419     }
 420
 421 }
 422