View | Details | Raw Unified | Return to bug 46410
Collapse All | Expand All

(-)src/java/org/apache/poi/hssf/record/formula/functions/Time.java (-1 / +48 lines)
Lines 20-25 Link Here
20
 */
20
 */
21
package org.apache.poi.hssf.record.formula.functions;
21
package org.apache.poi.hssf.record.formula.functions;
22
22
23
public class Time extends NotImplementedFunction {
23
import org.apache.poi.hssf.record.formula.eval.ErrorEval;
24
import org.apache.poi.hssf.record.formula.eval.EvaluationException;
24
25
26
/**
27
 * @author Steven Butler (sebutler @ gmail dot com)
28
 *
29
 * Based on POI org.apache.hssf.record.formula.DateFunc.java
30
 */
31
public final class Time extends NumericFunction.MultiArg {
32
33
    public Time() {
34
        super(3, 3);
35
    }
36
    /**
37
     *  Approximate value of 1 second in Excel
38
     */
39
    private static final double EXCEL_SECOND = 0.000011574074074074074074;
40
41
    /**
42
     * Converts the supplied hours, minutes and seconds to an Excel time value.
43
     *
44
     *
45
     * @param ds array of 3 doubles containing hours, minutes and seconds.
46
     * Non-integer inputs are truncated to an integer before further calculation
47
     * of the time value.
48
     * @return An Excel representation of a time of day.
49
     * If the time value represents more than a day, the days are removed from
50
     * the result, leaving only the time of day component.
51
     * @throws org.apache.poi.hssf.record.formula.eval.EvaluationException
52
     * If any of the arguments are greater than 32767 or the hours
53
     * minutes and seconds when combined form a time value less than 0, the function
54
     * evaluates to an error.
55
     */
56
    protected double evaluate(double[] ds) throws EvaluationException {
57
        // Excel silently truncates double values to integers
58
        long hour = (int) ds[0];
59
        long minutes = (int) ds[1];
60
        long seconds = (int) ds[2];
61
62
        if (hour > 32767 || minutes > 32767 || seconds > 32767) {
63
            throw new EvaluationException(ErrorEval.VALUE_INVALID);
64
        }
65
        seconds = (hour * 3600L + minutes * 60L + seconds);
66
67
        if (seconds < 0) {
68
            throw new EvaluationException(ErrorEval.VALUE_INVALID);
69
        }
70
        return (seconds % 86400L) * EXCEL_SECOND;
71
    }
25
}
72
}
(-)src/testcases/org/apache/poi/hssf/record/formula/functions/AllIndividualFunctionEvaluationTests.java (+1 lines)
Lines 32-37 Link Here
32
		result.addTestSuite(TestAverage.class);
32
		result.addTestSuite(TestAverage.class);
33
		result.addTestSuite(TestCountFuncs.class);
33
		result.addTestSuite(TestCountFuncs.class);
34
		result.addTestSuite(TestDate.class);
34
		result.addTestSuite(TestDate.class);
35
		result.addTestSuite(TestTime.class);
35
		result.addTestSuite(TestFind.class);
36
		result.addTestSuite(TestFind.class);
36
		result.addTestSuite(TestFinanceLib.class);
37
		result.addTestSuite(TestFinanceLib.class);
37
		result.addTestSuite(TestIndex.class);
38
		result.addTestSuite(TestIndex.class);
(-)src/testcases/org/apache/poi/hssf/record/formula/functions/TestTime.java (+148 lines)
Line 0 Link Here
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
package org.apache.poi.hssf.record.formula.functions;
19
20
import junit.framework.TestCase;
21
22
import org.apache.poi.hssf.usermodel.HSSFCell;
23
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
24
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
25
import org.apache.poi.hssf.usermodel.HSSFDataFormatter;
26
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
27
import org.apache.poi.hssf.usermodel.HSSFSheet;
28
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
29
30
/**
31
 * @author @author Steven Butler (sebutler @ gmail dot com)
32
 */
33
public final class TestTime extends TestCase {
34
35
    private HSSFCell cell11;
36
    private HSSFFormulaEvaluator evaluator;
37
    private HSSFWorkbook wb;
38
    HSSFDataFormatter form;
39
    HSSFCellStyle style;
40
41
    public void setUp() {
42
        wb = new HSSFWorkbook();
43
        HSSFSheet sheet = wb.createSheet("new sheet");
44
        style = wb.createCellStyle();
45
        HSSFDataFormat fmt = wb.createDataFormat();
46
        style.setDataFormat(fmt.getFormat("hh:mm:ss"));
47
48
        cell11 = sheet.createRow(0).createCell(0);
49
        cell11.setCellType(HSSFCell.CELL_TYPE_FORMULA);
50
        form = new HSSFDataFormatter();
51
52
        evaluator = new HSSFFormulaEvaluator(wb);
53
    }
54
55
    /**
56
     * Test disabled pending a fix in the formula evaluator
57
     * TODO - create MissingArgEval and modify the formula evaluator to handle this
58
     */
59
    public void DISABLEDtestSomeArgumentsMissing() {
60
        confirm("TIME(, 0, 0)", 0.0);
61
        confirm("TIME(, 0, 0)", 1.0);
62
    }
63
64
    public void testValid() {
65
        confirm("TIME(0,0,1)", 0.000011574074074074100000);
66
        confirmTime("TIME(0,0,1)", "00:00:01");
67
        confirm("TIME(0,1,0)", 0.000694444444444444000000);
68
        confirmTime("TIME(0,1,0)", "00:01:00");
69
70
        confirm("TIME(0,0,0)", 0.000000000000000000000000);
71
        confirmTime("TIME(0,0,0)", "00:00:00");
72
73
        confirm("TIME(1,0,0)", 0.041666666666666700000000);
74
        confirmTime("TIME(1,0,0)", "01:00:00");
75
        confirm("TIME(12,0,0)", 0.500000000000000000000000);
76
        confirmTime("TIME(12,0,0)", "12:00:00");
77
        confirm("TIME(23,0,0)", 0.958333333333333000000000);
78
        confirmTime("TIME(23,0,0)", "23:00:00");
79
        confirm("TIME(24,0,0)", 0.000000000000000000000000);
80
        confirmTime("TIME(24,0,0)", "00:00:00");
81
        confirm("TIME(25,0,0)", 0.041666666666666700000000);
82
        confirmTime("TIME(25,0,0)", "01:00:00");
83
        confirm("TIME(48,0,0)", 0.000000000000000000000000);
84
        confirmTime("TIME(48,0,0)", "00:00:00");
85
        confirm("TIME(6,0,0)", 0.250000000000000000000000);
86
        confirmTime("TIME(6,0,0)", "06:00:00");
87
        confirm("TIME(6,1,0)", 0.250694444444444000000000);
88
        confirmTime("TIME(6,1,0)", "06:01:00");
89
        confirm("TIME(6,30,0)", 0.270833333333333000000000);
90
        confirmTime("TIME(6,30,0)", "06:30:00");
91
        confirm("TIME(6,59,0)", 0.290972222222222000000000);
92
        confirmTime("TIME(6,59,0)", "06:59:00");
93
        confirm("TIME(6,60,0)", 0.291666666666667000000000);
94
        confirmTime("TIME(6,60,0)", "07:00:00");
95
        confirm("TIME(6,61,0)", 0.292361111111111000000000);
96
        confirmTime("TIME(6,61,0)", "07:01:00");
97
        confirm("TIME(6,120,0)", 0.333333333333333000000000);
98
        confirmTime("TIME(6,120,0)", "08:00:00");
99
        confirm("TIME(6,1440,0)", 0.250000000000000000000000);
100
        confirmTime("TIME(6,1440,0)", "06:00:00");
101
        confirm("TIME(18,49,0)", 0.784027777777778000000000);
102
        confirmTime("TIME(18,49,0)", "18:49:00");
103
        confirm("TIME(18,49,1)", 0.784039351851852000000000);
104
        confirmTime("TIME(18,49,1)", "18:49:01");
105
        confirm("TIME(18,49,30)", 0.784375000000000000000000);
106
        confirmTime("TIME(18,49,30)", "18:49:30");
107
        confirm("TIME(18,49,59)", 0.784710648148148000000000);
108
        confirmTime("TIME(18,49,59)", "18:49:59");
109
        confirm("TIME(18,49,60)", 0.784722222222222000000000);
110
        confirmTime("TIME(18,49,60)", "18:50:00");
111
        confirm("TIME(18,49,61)", 0.784733796296296000000000);
112
        confirmTime("TIME(18,49,61)", "18:50:01");
113
        confirm("TIME(18,49,119)", 0.785405092592593000000000);
114
        confirmTime("TIME(18,49,119)", "18:50:59");
115
        confirm("TIME(18,49,120)", 0.785416666666667000000000);
116
        confirmTime("TIME(18,49,120)", "18:51:00");
117
        confirm("TIME(18,49,32767)", 0.163275462962963000000000);
118
        confirmTime("TIME(18,49,32767)", "03:55:07");
119
        confirm("TIME(18,32767,61)", 0.505567129629629000000000);
120
        confirmTime("TIME(18,32767,61)", "12:08:01");
121
        confirm("TIME(32767,49,61)", 0.326400462962965000000000);
122
        confirmTime("TIME(32767,49,61)", "07:50:01");
123
124
    }
125
126
    private void confirmTime(String formulaText, String expectedResult) {
127
        cell11.setCellFormula(formulaText);
128
        cell11.setCellStyle(style);
129
130
        evaluator.clearAllCachedResultValues();
131
        String cellValue = form.formatCellValue(cell11, evaluator);
132
133
134
        assertEquals(expectedResult, cellValue);
135
    }
136
137
    private void confirm(String formulaText, double expectedResult) {
138
        cell11.setCellFormula(formulaText);
139
        cell11.setCellStyle(style);
140
141
        evaluator.clearAllCachedResultValues();
142
		// Excel seems to have more internal precision than can be displayed - we fudge the numeric tests passing
143
		// against the Excel displayed values  to 14 decimal places
144
        double actualValue = evaluator.evaluate(cell11).getNumberValue();
145
        assertEquals(expectedResult, actualValue, 0.00000000000001);
146
    }
147
}
148

Return to bug 46410