Mailing-List: contact dev-help@poi.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "POI Developers List" <dev@poi.apache.org>
Received-SPF: pass (nike.apache.org: local policy)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws;
  s=s1024; d=yahoo.com;
  h=X-YMail-OSG:Received:Date:From:Subject:To:MIME-Version:Content-Type:Content-Transfer-Encoding:Message-ID;
  b=M/D/i/4mLeaQ2butdqdZVzHFKi62xUuyY6kX3/eeZLcGx/4o5P4HJYkxjZMUrwGTCdTfJNULlpuSDwtV/qDmL6HBkIaBa8u9Ml6N10+WDgsBqSmKbE3HQyEASLgunbM4FDqwIWpfb1Ukybu0+p5nLS8NrZ2t/f34MSLvlGzt6Ac=;
Date: Mon, 24 Sep 2007 13:23:35 -0700 (PDT)
From: Pavel Krupets <pkrupets@yahoo.com>
Subject: Please add Date(...) function (code attached)...
To: dev@poi.apache.org
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="0-134813300-1190665415=:70215"
Content-Transfer-Encoding: 8bit
Message-ID: <83844.70215.qm@web54008.mail.re2.yahoo.com>

--0-134813300-1190665415=:70215
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit
Content-Id: 
Content-Disposition: inline

Hello,

Attached you can find DATE function implementation and tests.

With regards,
Pavel Krupets
--0-134813300-1190665415=:70215
Content-Type: text/java; name="Date.java"
Content-Description: 2239400980-Date.java
Content-Disposition: inline; filename="Date.java"

/*
* Licensed to the Apache Software Foundation (ASF) under one or more
* contributor license agreements.  See the NOTICE file distributed with
* this work for additional information regarding copyright ownership.
* The ASF licenses this file to You under the Apache License, Version 2.0
* (the "License"); you may not use this file except in compliance with
* the License.  You may obtain a copy of the License at
*
*     http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
/*
 * Created on May 15, 2005
 *
 */
package org.apache.poi.hssf.record.formula.functions;

import java.util.Calendar;
import java.util.GregorianCalendar;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;

import org.apache.poi.hssf.record.formula.eval.Eval;
import org.apache.poi.hssf.record.formula.eval.RefEval;
import org.apache.poi.hssf.record.formula.eval.BlankEval;
import org.apache.poi.hssf.record.formula.eval.ErrorEval;
import org.apache.poi.hssf.record.formula.eval.ValueEval;
import org.apache.poi.hssf.record.formula.eval.NumberEval;
import org.apache.poi.hssf.record.formula.eval.NumericValueEval;

/**
 * @author Pavel Krupets (pkrupets at palmtreebusiness dot com)
 */
public class Date extends NumericFunction {
    /**
     * @see org.apache.poi.hssf.record.formula.functions.Function#evaluate(org.apache.poi.hssf.record.formula.eval.Eval[], int, short)
     */
    public Eval evaluate(Eval[] operands, int srcCellRow, short srcCellCol) {
        if (operands.length == 3) {
            ValueEval ve[] = new ValueEval[3];
            
            ve[0] = singleOperandEvaluate(operands[0], srcCellRow, srcCellCol);
            ve[1] = singleOperandEvaluate(operands[1], srcCellRow, srcCellCol);
            ve[2] = singleOperandEvaluate(operands[2], srcCellRow, srcCellCol);
            
            if (validValues(ve)) {
                int year = getYear(ve[0]);
                int month = (int) ((NumericValueEval) ve[1]).getNumberValue() - 1;
                int day = (int) ((NumericValueEval) ve[2]).getNumberValue();
                
                if (year < 0 || month < 0 || day < 0) {
                    return ErrorEval.VALUE_INVALID;
                }
                
                if (year == 1900 && month == Calendar.FEBRUARY && day == 29) {
                    return new NumberEval(60.0);
                }
                
                if (year == 1900) {
                    if ((month == Calendar.JANUARY && day >= 60) ||
                        (month == Calendar.FEBRUARY && day >= 30))
                    {
                        day--;
                    }
                }
                
                Calendar c = new GregorianCalendar();
                
                c.set(year, month, day, 0, 0, 0);
                c.set(Calendar.MILLISECOND, 0);
                
                return new NumberEval(HSSFDateUtil.getExcelDate(c.getTime()));
            }
        }
        
        return ErrorEval.VALUE_INVALID;
    }
    
    private int getYear(ValueEval ve) {
        int year = (int) ((NumericValueEval) ve).getNumberValue();
        
        if (year < 0) {
            return -1;
        }
        
        return year < 1900 ? 1900 + year : year;
    }
    
    private boolean validValues(ValueEval[] values) {
        for (int i = 0; i < values.length; i++) {
            ValueEval value =  values[i];
            
            if (value instanceof RefEval) {
                RefEval re = (RefEval) value;
                ValueEval ive = re.getInnerValueEval();
                
                if (ive instanceof BlankEval) {
                    value = new NumberEval(0);
                } else if (ive instanceof NumericValueEval) {
                    value = ive;
                } else {
                    return false;
                }
            }
            
            if (!(value instanceof NumericValueEval)) {
                return false;
            }
        }
        
        return true;
    }
}

--0-134813300-1190665415=:70215
Content-Type: text/java; name="DateTest.java"
Content-Description: 262204861-DateTest.java
Content-Disposition: inline; filename="DateTest.java"

/*
 * Created on Sep 11, 2007
 * 
 * The Copyright statements and Licenses for the commons application may be
 * found in the file LICENSE.txt
 */

package org.apache.poi.hssf.record.formula.functions;

import junit.framework.TestCase;

import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;

/**
 * @author Pavel Krupets (pkrupets at palmtreebusiness dot com)
 */
public class DateTest extends TestCase {
    public void setUp() {
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("new sheet");
        HSSFRow row1 = sheet.createRow((short) 0);
        
        this.cell11 = row1.createCell((short) 0);
        
        this.evaluator = new HSSFFormulaEvaluator(sheet, wb);
        this.evaluator.setCurrentRow(row1);
    }
    
    public void testSomeArgumentsMissing() throws Exception {
        this.cell11.setCellFormula("DATE(, 1, 0)");
        assertEquals(0.0, this.evaluator.evaluate(this.cell11).getNumberValue(), 0);
        
        this.cell11.setCellFormula("DATE(, 1, 1)");
        assertEquals(1.0, this.evaluator.evaluate(this.cell11).getNumberValue(), 0);
    }
    
    public void testValid() throws Exception {
        this.cell11.setCellType(HSSFCell.CELL_TYPE_FORMULA);
        
        this.cell11.setCellFormula("DATE(1900, 1, 1)");
        assertEquals(1, this.evaluator.evaluate(this.cell11).getNumberValue(), 0);
        
        this.cell11.setCellFormula("DATE(1900, 1, 32)");
        assertEquals(32, this.evaluator.evaluate(this.cell11).getNumberValue(), 0);
        
        this.cell11.setCellFormula("DATE(1900, 222, 1)");
        assertEquals(6727, this.evaluator.evaluate(this.cell11).getNumberValue(), 0);
        
        this.cell11.setCellFormula("DATE(1900, 2, 0)");
        assertEquals(31, this.evaluator.evaluate(this.cell11).getNumberValue(), 0);
        
        this.cell11.setCellFormula("DATE(2000, 1, 222)");
        assertEquals(36747.00, this.evaluator.evaluate(this.cell11).getNumberValue(), 0);
        
        this.cell11.setCellFormula("DATE(2007, 1, 1)");
        assertEquals(39083, this.evaluator.evaluate(this.cell11).getNumberValue(), 0);
    }
    
    public void testBugDate() {
        this.cell11.setCellFormula("DATE(1900, 2, 29)");
        assertEquals(60, this.evaluator.evaluate(this.cell11).getNumberValue(), 0);
        
        this.cell11.setCellFormula("DATE(1900, 2, 30)");
        assertEquals(61, this.evaluator.evaluate(this.cell11).getNumberValue(), 0);
        
        this.cell11.setCellFormula("DATE(1900, 1, 222)");
        assertEquals(222, this.evaluator.evaluate(this.cell11).getNumberValue(), 0);
        
        this.cell11.setCellFormula("DATE(1900, 1, 2222)");
        assertEquals(2222, this.evaluator.evaluate(this.cell11).getNumberValue(), 0);
        
        this.cell11.setCellFormula("DATE(1900, 1, 22222)");
        assertEquals(22222, this.evaluator.evaluate(this.cell11).getNumberValue(), 0);
    }
    
    public void testPartYears() {
        this.cell11.setCellFormula("DATE(4, 1, 1)");
        assertEquals(1462.00, this.evaluator.evaluate(this.cell11).getNumberValue(), 0);
        
        this.cell11.setCellFormula("DATE(14, 1, 1)");
        assertEquals(5115.00, this.evaluator.evaluate(this.cell11).getNumberValue(), 0);
        
        this.cell11.setCellFormula("DATE(104, 1, 1)");
        assertEquals(37987.00, this.evaluator.evaluate(this.cell11).getNumberValue(), 0);
        
        this.cell11.setCellFormula("DATE(1004, 1, 1)");
        assertEquals(366705.00, this.evaluator.evaluate(this.cell11).getNumberValue(), 0);
    }
    
    private HSSFCell cell11;
    private HSSFFormulaEvaluator evaluator;
}


--0-134813300-1190665415=:70215
Content-Type: text/plain; charset=us-ascii

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
--0-134813300-1190665415=:70215--