/* ==================================================================== | |
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. | |
==================================================================== */ | |
package org.apache.poi.ss.formula.functions; | |
import static org.junit.Assert.assertEquals; | |
import static org.junit.Assert.assertNotEquals; | |
import static org.junit.Assert.assertNotNull; | |
import static org.junit.Assert.assertTrue; | |
import static org.junit.Assert.fail; | |
import java.util.ArrayList; | |
import java.util.Collection; | |
import java.util.List; | |
import java.util.Locale; | |
import org.apache.poi.hssf.HSSFTestDataSamples; | |
import org.apache.poi.hssf.usermodel.HSSFCell; | |
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator; | |
import org.apache.poi.hssf.usermodel.HSSFRow; | |
import org.apache.poi.hssf.usermodel.HSSFSheet; | |
import org.apache.poi.hssf.usermodel.HSSFWorkbook; | |
import org.apache.poi.hssf.util.CellReference; | |
import org.apache.poi.ss.formula.eval.ErrorEval; | |
import org.apache.poi.ss.usermodel.CellType; | |
import org.apache.poi.ss.usermodel.CellValue; | |
import org.junit.Test; | |
import org.junit.runner.RunWith; | |
import org.junit.runners.Parameterized; | |
import org.junit.runners.Parameterized.Parameter; | |
@RunWith(Parameterized.class) | |
public abstract class BaseTestFunctionsFromSpreadsheet { | |
/** | |
* This class defines constants for navigating around the test data spreadsheet used for these tests. | |
*/ | |
interface SS { | |
/** Name of the test spreadsheet (found in the standard test data folder) */ | |
/** Name of the first sheet in the spreadsheet (contains comments) */ | |
String README_SHEET_NAME = "Read Me"; | |
/** Row (zero-based) in each sheet where the evaluation cases start. */ | |
int START_TEST_CASES_ROW_INDEX = 4; // Row '5' | |
/** Index of the column that contains the function names */ | |
int COLUMN_INDEX_MARKER = 0; // Column 'A' | |
int COLUMN_INDEX_EVALUATION = 1; // Column 'B' | |
int COLUMN_INDEX_EXPECTED_RESULT = 2; // Column 'C' | |
int COLUMN_ROW_COMMENT = 3; // Column 'D' | |
/** Used to indicate when there are no more test cases on the current sheet */ | |
String TEST_CASES_END_MARKER = "<end>"; | |
/** Used to indicate that the test on the current row should be ignored */ | |
String SKIP_CURRENT_TEST_CASE_MARKER = "<skip>"; | |
} | |
@Parameter(value = 0) | |
public String testName; | |
@Parameter(value = 1) | |
public String filename; | |
@Parameter(value = 2) | |
public HSSFSheet sheet; | |
@Parameter(value = 3) | |
public int formulasRowIdx; | |
@Parameter(value = 4) | |
public HSSFFormulaEvaluator evaluator; | |
protected static Collection<Object[]> data(Class<? extends BaseTestFunctionsFromSpreadsheet> clazz, String filename) throws Exception { | |
HSSFWorkbook workbook = HSSFTestDataSamples.openSampleWorkbook(filename); | |
confirmReadMeSheet(workbook, clazz); | |
List<Object[]> data = new ArrayList<Object[]>(); | |
int nSheets = workbook.getNumberOfSheets(); | |
for(int sheetIdx=1; sheetIdx< nSheets; sheetIdx++) { | |
HSSFSheet sheet = workbook.getSheetAt(sheetIdx); | |
processFunctionGroup(data, sheet, SS.START_TEST_CASES_ROW_INDEX, null, filename); | |
} | |
workbook.close(); | |
return data; | |
} | |
private static void processFunctionGroup(List<Object[]> data, HSSFSheet sheet, final int startRowIndex, String testFocusFunctionName, String filename) { | |
HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(sheet.getWorkbook()); | |
String currentGroupComment = ""; | |
final int maxRows = sheet.getLastRowNum()+1; | |
for(int rowIndex=startRowIndex; rowIndex<maxRows; rowIndex++) { | |
HSSFRow r = sheet.getRow(rowIndex); | |
if(r == null) { | |
continue; | |
} | |
String newMarkerValue = getCellTextValue(r, SS.COLUMN_INDEX_MARKER, "marker"); | |
if(SS.TEST_CASES_END_MARKER.equalsIgnoreCase(newMarkerValue)) { | |
// normal exit point | |
return; | |
} | |
if(SS.SKIP_CURRENT_TEST_CASE_MARKER.equalsIgnoreCase(newMarkerValue)) { | |
// currently disabled test case row | |
continue; | |
} | |
if(newMarkerValue != null) { | |
currentGroupComment = newMarkerValue; | |
} | |
HSSFCell evalCell = r.getCell(SS.COLUMN_INDEX_EVALUATION); | |
if (evalCell == null || evalCell.getCellTypeEnum() != CellType.FORMULA) { | |
continue; | |
} | |
String rowComment = getCellTextValue(r, SS.COLUMN_ROW_COMMENT, "row comment"); | |
String testName = (currentGroupComment+'\n'+rowComment).replace("null", "").trim().replace("\n", " - "); | |
if ("".equals(testName)) { | |
testName = evalCell.getCellFormula(); | |
} | |
data.add(new Object[]{testName, filename, sheet, rowIndex, evaluator}); | |
} | |
fail("Missing end marker '" + SS.TEST_CASES_END_MARKER + "' on sheet '" + sheet.getSheetName() + "'"); | |
} | |
@Test | |
public void processFunctionRow() throws Exception { | |
HSSFRow r = sheet.getRow(formulasRowIdx); | |
HSSFCell evalCell = r.getCell(SS.COLUMN_INDEX_EVALUATION); | |
HSSFCell expectedCell = r.getCell(SS.COLUMN_INDEX_EXPECTED_RESULT); | |
CellReference cr = new CellReference(sheet.getSheetName(), formulasRowIdx, evalCell.getColumnIndex(), false, false); | |
String msg = String.format(Locale.ROOT, "In %s %s {=%s} '%s'" | |
, filename, cr.formatAsString(), evalCell.getCellFormula(), testName); | |
CellValue actualValue = evaluator.evaluate(evalCell); | |
assertNotNull(msg + " - Bad setup data expected value is null", expectedCell); | |
assertNotNull(msg + " - actual value was null", actualValue); | |
if (expectedCell.getCellTypeEnum() == CellType.ERROR) { | |
int expectedErrorCode = expectedCell.getErrorCellValue(); | |
assertEquals(msg, CellType.ERROR, actualValue.getCellTypeEnum()); | |
assertEquals(msg, ErrorEval.getText(expectedErrorCode), actualValue.formatAsString()); | |
assertEquals(msg, expectedErrorCode, actualValue.getErrorValue()); | |
assertEquals(msg, ErrorEval.getText(expectedErrorCode), ErrorEval.getText(actualValue.getErrorValue())); | |
return; | |
} | |
// unexpected error | |
assertNotEquals(msg, CellType.ERROR, actualValue.getCellTypeEnum()); | |
assertNotEquals(msg, formatValue(expectedCell), ErrorEval.getText(actualValue.getErrorValue())); | |
// wrong type error | |
assertEquals(msg, expectedCell.getCellTypeEnum(), actualValue.getCellTypeEnum()); | |
final CellType expectedCellType = expectedCell.getCellTypeEnum(); | |
switch (expectedCellType) { | |
case BOOLEAN: | |
assertEquals(msg, expectedCell.getBooleanCellValue(), actualValue.getBooleanValue()); | |
break; | |
case FORMULA: // will never be used, since we will call method after formula evaluation | |
fail("Cannot expect formula as result of formula evaluation: " + msg); | |
case NUMERIC: | |
assertEquals(expectedCell.getNumericCellValue(), actualValue.getNumberValue(), 0.0); | |
break; | |
case STRING: | |
assertEquals(msg, expectedCell.getRichStringCellValue().getString(), actualValue.getStringValue()); | |
break; | |
default: | |
fail("Unexpected cell type: " + expectedCellType); | |
} | |
} | |
/** | |
* Asserts that the 'read me' comment page exists, and has this class' name in one of the | |
* cells. This back-link is to make it easy to find this class if a reader encounters the | |
* spreadsheet first. | |
*/ | |
private static void confirmReadMeSheet(HSSFWorkbook workbook, Class<? extends BaseTestFunctionsFromSpreadsheet> clazz) { | |
String firstSheetName = workbook.getSheetName(0); | |
assertTrue("First sheet's name was '" + firstSheetName + "' but expected '" + SS.README_SHEET_NAME + "'", | |
firstSheetName.equalsIgnoreCase(SS.README_SHEET_NAME)); | |
HSSFSheet sheet = workbook.getSheetAt(0); | |
String specifiedClassName = sheet.getRow(2).getCell(0).getRichStringCellValue().getString(); | |
assertEquals("Test class name in spreadsheet comment", clazz.getName(), specifiedClassName); | |
} | |
/** | |
* @return <code>null</code> if cell is missing, empty or blank | |
*/ | |
private static String getCellTextValue(HSSFRow r, int colIndex, String columnName) { | |
if(r == null) { | |
return null; | |
} | |
HSSFCell cell = r.getCell(colIndex); | |
if(cell == null) { | |
return null; | |
} | |
if(cell.getCellTypeEnum() == CellType.BLANK) { | |
return null; | |
} | |
if(cell.getCellTypeEnum() == CellType.STRING) { | |
return cell.getRichStringCellValue().getString(); | |
} | |
fail("Bad cell type for '" + columnName + "' column: (" | |
+ cell.getCellTypeEnum() + ") row (" + (r.getRowNum() +1) + ")"); | |
return ""; | |
} | |
private static String formatValue(HSSFCell expecedCell) { | |
switch (expecedCell.getCellTypeEnum()) { | |
case BLANK: return "<blank>"; | |
case BOOLEAN: return Boolean.toString(expecedCell.getBooleanCellValue()); | |
case NUMERIC: return Double.toString(expecedCell.getNumericCellValue()); | |
case STRING: return expecedCell.getRichStringCellValue().getString(); | |
default: fail("Unexpected cell type of expected value (" + expecedCell.getCellTypeEnum() + ")"); | |
} | |
return ""; | |
} | |
} |