src/scratchpad/testcases/org/apache/poi/hssf/usermodel/TestBug44410.java

   1 package org.apache.poi.hssf.usermodel;
   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 import junit.framework.TestCase;
  20
  21 import java.io.IOException;
  22 import java.io.FileInputStream;
  23 import java.io.File;
  24 import java.util.List;
  25
  26 import org.apache.poi.hssf.record.aggregates.FormulaRecordAggregate;
  27 import org.apache.poi.hssf.record.formula.AreaPtg;
  28 import org.apache.poi.hssf.record.formula.FuncVarPtg;
  29
  30 /**
  31  * Bug 44410: SUM(C:C) is valid in excel, and means a sum
  32  *  of all the rows in Column C
  33  *
  34  * @author Nick Burch
  35  */
  36
  37 public class TestBug44410 extends TestCase {
  38     protected String cwd = System.getProperty("HSSF.testdata.path");
  39
  40     public void test44410() throws IOException {
  41         FileInputStream in = new FileInputStream(new File(cwd, "SingleLetterRanges.xls"));
  42         HSSFWorkbook wb = new HSSFWorkbook(in);
  43         in.close();
  44
  45         HSSFSheet sheet   = wb.getSheetAt(0);
  46
  47         HSSFFormulaEvaluator eva = new HSSFFormulaEvaluator(sheet, wb);
  48
  49         // =index(C:C,2,1)   -> 2
  50         HSSFRow rowIDX = (HSSFRow)sheet.getRow(3);
  51         // =sum(C:C)         -> 6
  52         HSSFRow rowSUM = (HSSFRow)sheet.getRow(4);
  53         // =sum(C:D)         -> 66
  54         HSSFRow rowSUM2D = (HSSFRow)sheet.getRow(5);
  55
  56         // Test the sum
  57         HSSFCell cellSUM = rowSUM.getCell((short)0);
  58
  59         FormulaRecordAggregate frec =
  60                 (FormulaRecordAggregate)cellSUM.getCellValueRecord();
  61         List ops = frec.getFormulaRecord().getParsedExpression();
  62         assertEquals(2, ops.size());
  63         assertEquals(AreaPtg.class, ops.get(0).getClass());
  64         assertEquals(FuncVarPtg.class, ops.get(1).getClass());
  65
  66         // Actually stored as C1 to C65536
  67         //  (last row is -1 === 65535)
  68         AreaPtg ptg = (AreaPtg)ops.get(0);
  69         assertEquals(2, ptg.getFirstColumn());
  70         assertEquals(2, ptg.getLastColumn());
  71         assertEquals(0, ptg.getFirstRow());
  72         assertEquals(65535, ptg.getLastRow());
  73         assertEquals("C:C", ptg.toFormulaString(wb.getWorkbook()));
  74
  75         // Will show as C:C, but won't know how many
  76         //  rows it covers as we don't have the sheet
  77         //  to hand when turning the Ptgs into a string
  78         assertEquals("SUM(C:C)", cellSUM.getCellFormula());
  79         eva.setCurrentRow(rowSUM);
  80
  81         // But the evaluator knows the sheet, so it
  82         //  can do it properly
  83         assertEquals(6, eva.evaluate(cellSUM).getNumberValue(), 0);
  84
  85
  86         // Test the index
  87         // Again, the formula string will be right but
  88         //  lacking row count, evaluated will be right
  89         HSSFCell cellIDX = rowIDX.getCell((short)0);
  90         assertEquals("INDEX(C:C,2,1)", cellIDX.getCellFormula());
  91         eva.setCurrentRow(rowIDX);
  92         assertEquals(2, eva.evaluate(cellIDX).getNumberValue(), 0);
  93
  94         // Across two colums
  95         HSSFCell cellSUM2D = rowSUM2D.getCell((short)0);
  96         assertEquals("SUM(C:D)", cellSUM2D.getCellFormula());
  97         eva.setCurrentRow(rowSUM2D);
  98         assertEquals(66, eva.evaluate(cellSUM2D).getNumberValue(), 0);
  99     }
 100 }