diff options
Diffstat (limited to 'poi-excelant/src')
36 files changed, 3405 insertions, 0 deletions
diff --git a/poi-excelant/src/main/java/org/apache/poi/ss/excelant/ExcelAntEvaluateCell.java b/poi-excelant/src/main/java/org/apache/poi/ss/excelant/ExcelAntEvaluateCell.java new file mode 100644 index 0000000000..eb446dfde6 --- /dev/null +++ b/poi-excelant/src/main/java/org/apache/poi/ss/excelant/ExcelAntEvaluateCell.java @@ -0,0 +1,141 @@ +/* ==================================================================== + 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.excelant; + +import org.apache.poi.ss.excelant.util.ExcelAntEvaluationResult; +import org.apache.poi.ss.excelant.util.ExcelAntWorkbookUtil; +import org.apache.tools.ant.BuildException; +import org.apache.tools.ant.Project; +import org.apache.tools.ant.Task; + +/** + * Instances of this class are used to evaluate a single cell. This is usually + * after some values have been set. The evaluation is actually performed + * by a WorkbookUtil instance. The evaluate() method of the WorkbookUtil + * class returns an EvaluationResult which encapsulates the results and + * information from the evaluation. + * + * @author Jon Svede ( jon [at] loquatic [dot] com ) + * @author Brian Bush ( brian [dot] bush [at] nrel [dot] gov ) + + * + */ +public class ExcelAntEvaluateCell extends Task { + + private String cell ; + private double expectedValue ; + private double precision ; + private double globalPrecision ; + private boolean requiredToPass; + + + private ExcelAntEvaluationResult result ; + + private ExcelAntWorkbookUtil wbUtil ; + + private boolean showDelta; + + + public ExcelAntEvaluateCell() {} + + protected void setWorkbookUtil( ExcelAntWorkbookUtil wb ) { + wbUtil = wb ; + } + + public void setShowDelta( boolean value ) { + showDelta = value ; + } + + protected boolean showDelta() { + return showDelta ; + } + + public void setCell(String cell) { + this.cell = cell; + } + + public void setRequiredToPass( boolean val ) { + requiredToPass = val ; + } + + protected boolean requiredToPass() { + return requiredToPass ; + } + + public void setExpectedValue(double expectedValue) { + this.expectedValue = expectedValue; + } + + public void setPrecision(double precision) { + this.precision = precision; + } + + protected void setGlobalPrecision( double prec ) { + globalPrecision = prec ; + } + + protected String getCell() { + return cell; + } + + protected double getExpectedValue() { + return expectedValue; + } + + @SuppressWarnings("squid:S4275") + protected double getPrecision() { + // if there is a globalPrecision we will use it unless there is also + // precision set at the evaluate level, then we use that. If there + // is not a globalPrecision, we will use the local precision. + log( "test precision = " + precision + "\tglobal precision = " + globalPrecision, Project.MSG_VERBOSE ) ; + if( globalPrecision > 0 ) { + if( precision > 0 ) { + log( "Using evaluate precision of " + precision + " over the " + + "global precision of " + globalPrecision, Project.MSG_VERBOSE ) ; + return precision ; + } else { + log( "Using global precision of " + globalPrecision, Project.MSG_VERBOSE ) ; + return globalPrecision ; + } + } else { + log( "Using evaluate precision of " + precision, Project.MSG_VERBOSE ) ; + return precision ; + } + } + + @Override + public void execute() throws BuildException { + result = wbUtil.evaluateCell(cell, expectedValue, getPrecision() ) ; + + StringBuilder sb = new StringBuilder() ; + sb.append( "evaluation of cell " ) ; + sb.append( cell ) ; + sb.append( " resulted in " ) ; + sb.append( result.getReturnValue() ) ; + if(showDelta) { + sb.append(" with a delta of ").append(result.getDelta()); + } + + log( sb.toString(), Project.MSG_DEBUG) ; + + } + + public ExcelAntEvaluationResult getResult() { + return result ; + } +} diff --git a/poi-excelant/src/main/java/org/apache/poi/ss/excelant/ExcelAntHandlerTask.java b/poi-excelant/src/main/java/org/apache/poi/ss/excelant/ExcelAntHandlerTask.java new file mode 100644 index 0000000000..75892aa738 --- /dev/null +++ b/poi-excelant/src/main/java/org/apache/poi/ss/excelant/ExcelAntHandlerTask.java @@ -0,0 +1,76 @@ +/* ==================================================================== + 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.excelant; + +import org.apache.poi.ss.excelant.util.ExcelAntWorkbookUtil; +import org.apache.tools.ant.BuildException; +import org.apache.tools.ant.Project; +import org.apache.tools.ant.Task; + +/** + * This is the class that backs the <handler> tag in the Ant task. + * <p> + * Its purpose is to provide a way to manipulate a workbook in the course + * of an ExcelAnt task. The idea being to model a way for test writers to + * simulate the behaviors of the workbook. + * <p> + * Suppose, for example, you have a workbook that has a worksheet that + * reacts to values entered or selected by the user. It's possible in + * Excel to change other cells based on this but this isn't easily possible + * in POI. In ExcelAnt we handle this using the Handler, which is a Java + * class you write to manipulate the workbook. + * <p> + * In order to use this tag you must write a class that implements the + * <code>IExcelAntWorkbookHandler</code> interface. After writing the + * class you should package it and it's dependencies into a jar file to + * add as library in your Ant build file. + * + * @author Jon Svede ( jon [at] loquatic [dot] com ) + * @author Brian Bush ( brian [dot] bush [at] nrel [dot] gov ) + * + */ +public class ExcelAntHandlerTask extends Task { + + private String className ; + + private ExcelAntWorkbookUtil wbUtil ; + + public void setClassName( String cName ) { + className = cName ; + } + + protected void setEAWorkbookUtil( ExcelAntWorkbookUtil wkbkUtil ) { + wbUtil = wkbkUtil ; + } + + @Override + public void execute() throws BuildException { + log( "handling the workbook with class " + className, Project.MSG_INFO ) ; + try { + Class<?> clazz = Class.forName( className ) ; + Object handlerObj = clazz.getDeclaredConstructor().newInstance() ; + if( handlerObj instanceof IExcelAntWorkbookHandler ) { + IExcelAntWorkbookHandler iHandler = (IExcelAntWorkbookHandler)handlerObj ; + iHandler.setWorkbook( wbUtil.getWorkbook() ) ; + iHandler.execute() ; + } + } catch( Exception e ) { + throw new BuildException( e.getMessage(), e ) ; + } + } + } diff --git a/poi-excelant/src/main/java/org/apache/poi/ss/excelant/ExcelAntPrecision.java b/poi-excelant/src/main/java/org/apache/poi/ss/excelant/ExcelAntPrecision.java new file mode 100644 index 0000000000..0739ef5833 --- /dev/null +++ b/poi-excelant/src/main/java/org/apache/poi/ss/excelant/ExcelAntPrecision.java @@ -0,0 +1,39 @@ +/* ==================================================================== + 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.excelant; + +import org.apache.tools.ant.taskdefs.Typedef; + +/** + * + * @author Jon Svede ( jon [at] loquatic [dot] com ) + * @author Brian Bush ( brian [dot] bush [at] nrel [dot] gov ) + * + */ +public class ExcelAntPrecision extends Typedef { + + private double value ; + + public void setValue( double precision ) { + value = precision ; + } + + public double getValue() { + return value ; + } +} diff --git a/poi-excelant/src/main/java/org/apache/poi/ss/excelant/ExcelAntSet.java b/poi-excelant/src/main/java/org/apache/poi/ss/excelant/ExcelAntSet.java new file mode 100644 index 0000000000..e62f0623d9 --- /dev/null +++ b/poi-excelant/src/main/java/org/apache/poi/ss/excelant/ExcelAntSet.java @@ -0,0 +1,48 @@ +/* ==================================================================== + 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.excelant; + +import org.apache.poi.ss.excelant.util.ExcelAntWorkbookUtil; +import org.apache.tools.ant.Task; + +/** + * + * @author Jon Svede ( jon [at] loquatic [dot] com ) + * @author Brian Bush ( brian [dot] bush [at] nrel [dot] gov ) + * + */ +public abstract class ExcelAntSet extends Task { + + protected String cellStr ; + + protected ExcelAntWorkbookUtil wbUtil ; + + public void setCell( String cellName ) { + cellStr = cellName ; + } + + public String getCell() { + return cellStr ; + } + + + public void setWorkbookUtil( ExcelAntWorkbookUtil wb ) { + wbUtil = wb ; + } + +} diff --git a/poi-excelant/src/main/java/org/apache/poi/ss/excelant/ExcelAntSetDoubleCell.java b/poi-excelant/src/main/java/org/apache/poi/ss/excelant/ExcelAntSetDoubleCell.java new file mode 100644 index 0000000000..2451a61e68 --- /dev/null +++ b/poi-excelant/src/main/java/org/apache/poi/ss/excelant/ExcelAntSetDoubleCell.java @@ -0,0 +1,59 @@ +/* ==================================================================== + 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.excelant; + +import org.apache.tools.ant.BuildException; +import org.apache.tools.ant.Project; + +/** + * Class for use in an Ant build script that sets the value of an Excel + * sheet cell using the cell id ('Sheet Name'!cellId). + * + * @author Jon Svede ( jon [at] loquatic [dot] com ) + * @author Brian Bush ( brian [dot] bush [at] nrel [dot] gov ) + * + */ +public class ExcelAntSetDoubleCell extends ExcelAntSet { + private double cellValue; + + public ExcelAntSetDoubleCell() {} + + /** + * Set the value of the specified cell as the double passed in. + * @param value The double-value that should be set when this task is executed. + */ + public void setValue( double value ) { + cellValue = value ; + } + + /** + * Return the cell value as a double. + * @return The double-value of the cell as populated via setValue(), null + * if the value was not set yet. + */ + public double getCellValue() { + return cellValue; + } + + @Override + public void execute() throws BuildException { + wbUtil.setDoubleValue(cellStr, cellValue ) ; + + log( "set cell " + cellStr + " to value " + cellValue + " as double.", Project.MSG_DEBUG ) ; + } +} diff --git a/poi-excelant/src/main/java/org/apache/poi/ss/excelant/ExcelAntSetFormulaCell.java b/poi-excelant/src/main/java/org/apache/poi/ss/excelant/ExcelAntSetFormulaCell.java new file mode 100644 index 0000000000..8dc599b104 --- /dev/null +++ b/poi-excelant/src/main/java/org/apache/poi/ss/excelant/ExcelAntSetFormulaCell.java @@ -0,0 +1,53 @@ +/* ==================================================================== + 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.excelant; + +import org.apache.tools.ant.BuildException; +import org.apache.tools.ant.Project; + +/** + * Class for use in an Ant build script that sets the formula of an Excel + * sheet cell using the cell id ('Sheet Name'!cellId). + * + * @author Jon Svede ( jon [at] loquatic [dot] com ) + * @author Brian Bush ( brian [dot] bush [at] nrel [dot] gov ) + * + */ +public class ExcelAntSetFormulaCell extends ExcelAntSet { + + + private String cellValue ; + + public ExcelAntSetFormulaCell() {} + + public void setValue( String value ) { + cellValue = value ; + } + + protected String getCellValue() { + return cellValue; + } + + @Override + public void execute() throws BuildException { + + wbUtil.setFormulaValue( cellStr, cellValue ) ; + + log( "set cell " + cellStr + " to formula " + cellValue, Project.MSG_DEBUG ) ; + } +} diff --git a/poi-excelant/src/main/java/org/apache/poi/ss/excelant/ExcelAntSetStringCell.java b/poi-excelant/src/main/java/org/apache/poi/ss/excelant/ExcelAntSetStringCell.java new file mode 100644 index 0000000000..e1ab047fbf --- /dev/null +++ b/poi-excelant/src/main/java/org/apache/poi/ss/excelant/ExcelAntSetStringCell.java @@ -0,0 +1,59 @@ +/* ==================================================================== + 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.excelant; + +import org.apache.tools.ant.BuildException; +import org.apache.tools.ant.Project; + +/** + * Class for use in an Ant build script that sets the value of an Excel + * sheet cell using the cell id ('Sheet Name'!cellId). + * + * @author Jon Svede ( jon [at] loquatic [dot] com ) + * @author Brian Bush ( brian [dot] bush [at] nrel [dot] gov ) + * + */ +public class ExcelAntSetStringCell extends ExcelAntSet { + private String stringValue ; + + public ExcelAntSetStringCell() {} + + /** + * Set the value of the cell to the String passed in. + * @param value The string-value that should be set when this task is executed. + */ + public void setValue(String value ) { + stringValue = value ; + } + + /** + * Return the value that will be set into the cell. + * @return The string-value of the cell as populated via setValue(), null + * if the value was not set yet. + */ + public String getCellValue() { + return stringValue; + } + + @Override + public void execute() throws BuildException { + wbUtil.setStringValue(cellStr, stringValue ) ; + + log( "set cell " + cellStr + " to value " + stringValue + " as String.", Project.MSG_DEBUG ) ; + } +} diff --git a/poi-excelant/src/main/java/org/apache/poi/ss/excelant/ExcelAntTask.java b/poi-excelant/src/main/java/org/apache/poi/ss/excelant/ExcelAntTask.java new file mode 100644 index 0000000000..4fc7a80508 --- /dev/null +++ b/poi-excelant/src/main/java/org/apache/poi/ss/excelant/ExcelAntTask.java @@ -0,0 +1,155 @@ +/* ==================================================================== + 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.excelant; + +import java.text.SimpleDateFormat; +import java.util.Date; +import java.util.LinkedList; +import java.util.Locale; + +import org.apache.poi.ss.excelant.util.ExcelAntWorkbookUtil; +import org.apache.poi.ss.excelant.util.ExcelAntWorkbookUtilFactory; +import org.apache.tools.ant.BuildException; +import org.apache.tools.ant.Project; +import org.apache.tools.ant.Task; + +/** + * Ant task class for testing Excel workbook cells. + */ +public class ExcelAntTask extends Task { + + public static final String VERSION = "0.5.0" ; + + private String excelFileName ; + + private boolean failOnError; + + private ExcelAntWorkbookUtil workbookUtil ; + + private ExcelAntPrecision precision ; + + private LinkedList<ExcelAntTest> tests ; + private LinkedList<ExcelAntUserDefinedFunction> functions ; + + public ExcelAntTask() { + tests = new LinkedList<>() ; + functions = new LinkedList<>() ; + } + + public void addPrecision( ExcelAntPrecision prec ) { + precision = prec ; + } + + public void setFailOnError( boolean value ) { + failOnError = value ; + } + public void setFileName( String fileName ) { + excelFileName = fileName ; + } + + public void addTest( ExcelAntTest testElement ) { + tests.add( testElement ) ; + } + + public void addUdf( ExcelAntUserDefinedFunction def ) { + functions.add( def ) ; + } + + @Override + public void execute() throws BuildException { + checkClassPath(); + + int totalCount = 0 ; + int successCount = 0 ; + + StringBuilder versionBffr = new StringBuilder() ; + versionBffr.append( "ExcelAnt version " ) ; + versionBffr.append( VERSION ) ; + versionBffr.append( " Copyright 2011" ) ; + SimpleDateFormat sdf = new SimpleDateFormat( "yyyy", Locale.ROOT ) ; + double currYear = Double.parseDouble( sdf.format( new Date() ) ); + if( currYear > 2011 ) { + versionBffr.append( "-" ) ; + versionBffr.append( currYear ) ; + } + log( versionBffr.toString(), Project.MSG_INFO ) ; + + log( "Using input file: " + excelFileName, Project.MSG_INFO ) ; + + workbookUtil = ExcelAntWorkbookUtilFactory.getInstance(excelFileName); + + for (ExcelAntTest test : tests) { + log("executing test: " + test.getName(), Project.MSG_DEBUG); + + if (workbookUtil == null) { + workbookUtil = ExcelAntWorkbookUtilFactory.getInstance(excelFileName); + } + + for (ExcelAntUserDefinedFunction eaUdf : functions) { + try { + workbookUtil.addFunction(eaUdf.getFunctionAlias(), eaUdf.getClassName()); + } catch (Exception e) { + throw new BuildException(e.getMessage(), e); + } + } + test.setWorkbookUtil(workbookUtil); + + if (precision != null && precision.getValue() > 0) { + log("setting precision for the test " + test.getName(), Project.MSG_VERBOSE); + test.setPrecision(precision.getValue()); + } + + test.execute(); + + if (test.didTestPass()) { + successCount++; + } else { + if (failOnError) { + throw new BuildException("Test " + test.getName() + " failed."); + } + } + totalCount++; + + workbookUtil = null; + } + + if( !tests.isEmpty() ) { + log( successCount + "/" + totalCount + " tests passed.", Project.MSG_INFO ); + } + workbookUtil = null; + } + + + /** + * ExcelAnt depends on external libraries not included in the Ant distribution. + * Give user a sensible message if any if the required jars are missing. + */ + private void checkClassPath(){ + try { + Class.forName("org.apache.poi.hssf.usermodel.HSSFWorkbook"); + Class.forName("org.apache.poi.ss.usermodel.WorkbookFactory"); + } catch (Exception e) { + throw new BuildException( + "The <classpath> for <excelant> must include poi.jar and poi-ooxml.jar " + + "if not in Ant's own classpath. Processing .xlsx spreadsheets requires " + + "additional poi-ooxml-lite.jar, xmlbeans.jar" , + e, getLocation()); + } + + } +} diff --git a/poi-excelant/src/main/java/org/apache/poi/ss/excelant/ExcelAntTest.java b/poi-excelant/src/main/java/org/apache/poi/ss/excelant/ExcelAntTest.java new file mode 100644 index 0000000000..2fd45cab15 --- /dev/null +++ b/poi-excelant/src/main/java/org/apache/poi/ss/excelant/ExcelAntTest.java @@ -0,0 +1,210 @@ +/* ==================================================================== + 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.excelant; + +import java.util.Iterator; +import java.util.LinkedList; +import java.util.function.Supplier; + +import org.apache.poi.ss.excelant.util.ExcelAntEvaluationResult; +import org.apache.poi.ss.excelant.util.ExcelAntWorkbookUtil; +import org.apache.tools.ant.BuildException; +import org.apache.tools.ant.Project; +import org.apache.tools.ant.Task; + +/** + * This class represents a single test. In order for the test any and all + * ExcelAntEvaluateCell evaluations must pass. Therefore it is recommended + * that you use only 1 evaluator but you can use more if you choose. + * + * @author Jon Svede ( jon [at] loquatic [dot] com ) + * @author Brian Bush ( brian [dot] bush [at] nrel [dot] gov ) + * + */ +@SuppressWarnings("unused") +public class ExcelAntTest extends Task{ + private LinkedList<ExcelAntEvaluateCell> evaluators; + + private LinkedList<Task> testTasks; + + private String name; + + private double globalPrecision; + + private boolean showSuccessDetails; + + private boolean showFailureDetail; + LinkedList<String> failureMessages; + + + private ExcelAntWorkbookUtil workbookUtil; + + private boolean passed = true; + + + public ExcelAntTest() { + evaluators = new LinkedList<>(); + failureMessages = new LinkedList<>(); + testTasks = new LinkedList<>(); + } + + public void setPrecision( double precision ) { + globalPrecision = precision; + } + + public void setWorkbookUtil( ExcelAntWorkbookUtil wbUtil ) { + workbookUtil = wbUtil; + } + + + public void setShowFailureDetail( boolean value ) { + showFailureDetail = value; + } + + public void setName( String nm ) { + name = nm; + } + + public String getName() { + return name; + } + + public void setShowSuccessDetails( boolean details ) { + showSuccessDetails = details; + } + + public boolean showSuccessDetails() { + return showSuccessDetails; + } + + public void addSetDouble( ExcelAntSetDoubleCell setter ) { + addSetter( setter ); + } + + public void addSetString( ExcelAntSetStringCell setter ){ + addSetter( setter ); + } + + public void addSetFormula( ExcelAntSetFormulaCell setter ) { + addSetter( setter ); + } + + public void addHandler( ExcelAntHandlerTask handler ) { + testTasks.add( handler ); + } + + private void addSetter( ExcelAntSet setter ) { + testTasks.add( setter ); + } + + public void addEvaluate( ExcelAntEvaluateCell evaluator ) { + testTasks.add( evaluator ); + } + + protected LinkedList<ExcelAntEvaluateCell> getEvaluators() { + return evaluators; + } + + @Override + public void execute() throws BuildException { + + Iterator<Task> taskIt = testTasks.iterator(); + + int testCount = evaluators.size(); + int failureCount = 0; + + // roll over all sub task elements in one loop. This allows the + // ordering of the sub elements to be considered. + while( taskIt.hasNext() ) { + Task task = taskIt.next(); + + // log( task.getClass().getName(), Project.MSG_INFO ); + + if( task instanceof ExcelAntSet ) { + ExcelAntSet set = (ExcelAntSet) task; + set.setWorkbookUtil(workbookUtil); + set.execute(); + } + + if( task instanceof ExcelAntHandlerTask ) { + ExcelAntHandlerTask handler = (ExcelAntHandlerTask)task; + handler.setEAWorkbookUtil(workbookUtil ); + handler.execute(); + } + + if (task instanceof ExcelAntEvaluateCell ) { + ExcelAntEvaluateCell eval = (ExcelAntEvaluateCell)task; + eval.setWorkbookUtil( workbookUtil ); + + if( globalPrecision > 0 ) { + log( "setting globalPrecision to " + globalPrecision + " in the evaluator", Project.MSG_VERBOSE ); + eval.setGlobalPrecision( globalPrecision ); + } + + try { + eval.execute(); + ExcelAntEvaluationResult result = eval.getResult(); + + Supplier<String> details = () -> + result.getCellName() + ". It evaluated to " + + result.getReturnValue() + " when the value of " + + eval.getExpectedValue() + " with precision of " + + eval.getPrecision(); + + if( result.didTestPass() && !result.evaluationCompleteWithError()) { + if(showSuccessDetails) { + log("Succeeded when evaluating " + details.get(), Project.MSG_INFO ); + } + } else { + if(showFailureDetail) { + failureMessages.add( "\tFailed to evaluate cell " + details.get() + " was expected." ); + } + passed = false; + failureCount++; + + if(eval.requiredToPass()) { + throw new BuildException( "\tFailed to evaluate cell " + details.get() + " was expected." ); + } + } + } catch( NullPointerException npe ) { + // this means the cell reference in the test is bad. + log( "Cell assignment " + eval.getCell() + " in test " + getName() + + " appears to point to an empy cell. Please check the " + + " reference in the ant script.", Project.MSG_ERR ); + } + } + } + + if(!passed) { + log( "Test named " + name + " failed because " + failureCount + + " of " + testCount + " evaluations failed to " + + "evaluate correctly.", + Project.MSG_ERR ); + if(showFailureDetail && failureMessages.size() > 0 ) { + for (String failureMessage : failureMessages) { + log(failureMessage, Project.MSG_ERR); + } + } + } + } + + public boolean didTestPass() { + + return passed; + } + } diff --git a/poi-excelant/src/main/java/org/apache/poi/ss/excelant/ExcelAntUserDefinedFunction.java b/poi-excelant/src/main/java/org/apache/poi/ss/excelant/ExcelAntUserDefinedFunction.java new file mode 100644 index 0000000000..f96a01f996 --- /dev/null +++ b/poi-excelant/src/main/java/org/apache/poi/ss/excelant/ExcelAntUserDefinedFunction.java @@ -0,0 +1,62 @@ +/* ==================================================================== + 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.excelant; + +import org.apache.tools.ant.taskdefs.Typedef; + +/** + * This class encapsulates the Strings necessary to create the User Defined + * Function instances that will be passed to POI's Evaluator instance. + * + * @author Jon Svede ( jon [at] loquatic [dot] com ) + * @author Brian Bush ( brian [dot] bush [at] nrel [dot] gov ) + * + */ +public class ExcelAntUserDefinedFunction extends Typedef { + + + private String functionAlias ; + + private String className ; + + + public ExcelAntUserDefinedFunction() {} + + protected String getFunctionAlias() { + return functionAlias; + } + + public void setFunctionAlias(String functionAlias) { + this.functionAlias = functionAlias; + } + + protected String getClassName() { + // workaround for IBM JDK assigning the classname to the lowercase instance provided by Definer!?! + // I could not find out why that happens, the wrong assignment seems to be done somewhere deep inside Ant itself + // or even in IBM JDK as Oracle JDK does not have this problem. + if(className == null) { + return getClassname(); + } + + return className; + } + + public void setClassName(String className) { + this.className = className; + } +} diff --git a/poi-excelant/src/main/java/org/apache/poi/ss/excelant/IExcelAntWorkbookHandler.java b/poi-excelant/src/main/java/org/apache/poi/ss/excelant/IExcelAntWorkbookHandler.java new file mode 100644 index 0000000000..c8db7008d6 --- /dev/null +++ b/poi-excelant/src/main/java/org/apache/poi/ss/excelant/IExcelAntWorkbookHandler.java @@ -0,0 +1,42 @@ +/* ==================================================================== + 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.excelant; + +import org.apache.poi.ss.usermodel.Workbook; + + +/** + * In Excel there are many ways to handle manipulating a workbook based + * on some arbitrary user action (onChange, etc). You use this interface + * to create classes that will handle the workbook in whatever manner is needed + * that cannot be handled by POI. + * <p> + * For example, suppose that in Excel when you update a cell the workbook + * does some calculations and updates other cells based on that change. In + * ExcelAnt you would set the value of the cell then write your own handler + * then call that from your Ant task after the set task. + * + * @author Jon Svede ( jon [at] loquatic [dot] com ) + * @author Brian Bush ( brian [dot] bush [at] nrel [dot] gov ) + * + */ +public interface IExcelAntWorkbookHandler { + public void setWorkbook( Workbook workbook ) ; + + public void execute() ; +} diff --git a/poi-excelant/src/main/java/org/apache/poi/ss/excelant/util/ExcelAntEvaluationResult.java b/poi-excelant/src/main/java/org/apache/poi/ss/excelant/util/ExcelAntEvaluationResult.java new file mode 100644 index 0000000000..4eecd4faeb --- /dev/null +++ b/poi-excelant/src/main/java/org/apache/poi/ss/excelant/util/ExcelAntEvaluationResult.java @@ -0,0 +1,112 @@ +/* ==================================================================== + 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.excelant.util; + +/** + * A simple class that encapsulates information about a cell evaluation + * from POI. + * + * @author Jon Svede (jon [at] loquatic [dot] com) + * @author Brian Bush (brian [dot] bush [at] nrel [dot] gov) + * + */ +public class ExcelAntEvaluationResult { + + /** + * This boolean flag is used to determine if the evaluation completed + * without error. This alone doesn't ensure that the evaluation was + * successful. + */ + private boolean evaluationCompletedWithError ; + + /** + * This boolean flag is used to determine if the result was within + * the specified precision. + */ + private boolean didPass ; + + /** + * This is the actual value returned from the evaluation. + */ + private double returnValue ; + + /** + * Any error message String values that need to be returned. + */ + private String errorMessage ; + + /** + * Stores the absolute value of the delta for this evaluation. + */ + private double actualDelta ; + + /** + * This stores the fully qualified cell name (sheetName!cellId). + */ + private String cellName ; + + + + public ExcelAntEvaluationResult(boolean completedWithError, + boolean passed, + double retValue, + String errMessage, + double delta, + String cellId) { + + evaluationCompletedWithError = completedWithError; + didPass = passed; + returnValue = retValue; + errorMessage = errMessage; + actualDelta = delta ; + cellName = cellId ; + } + + public double getReturnValue() { + return returnValue; + } + + public String getErrorMessage() { + return errorMessage; + } + + public boolean didTestPass() { + return didPass ; + } + + public boolean evaluationCompleteWithError() { + return evaluationCompletedWithError ; + } + + public double getDelta() { + return actualDelta ; + } + + public String getCellName() { + return cellName ; + } + + @Override + public String toString() { + return "ExcelAntEvaluationResult [evaluationCompletedWithError=" + + evaluationCompletedWithError + ", didPass=" + didPass + + ", returnValue=" + returnValue + ", errorMessage=" + + errorMessage + ", actualDelta=" + actualDelta + ", cellName=" + + cellName + "]"; + } +} diff --git a/poi-excelant/src/main/java/org/apache/poi/ss/excelant/util/ExcelAntWorkbookUtil.java b/poi-excelant/src/main/java/org/apache/poi/ss/excelant/util/ExcelAntWorkbookUtil.java new file mode 100644 index 0000000000..ee3f443228 --- /dev/null +++ b/poi-excelant/src/main/java/org/apache/poi/ss/excelant/util/ExcelAntWorkbookUtil.java @@ -0,0 +1,384 @@ +/* ==================================================================== + 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.excelant.util; + +import java.io.FileInputStream; +import java.lang.reflect.InvocationTargetException; +import java.util.ArrayList; +import java.util.Date; +import java.util.HashMap; +import java.util.List; +import java.util.Map; + +import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator; +import org.apache.poi.hssf.usermodel.HSSFWorkbook; +import org.apache.poi.ss.formula.functions.FreeRefFunction; +import org.apache.poi.ss.formula.udf.AggregatingUDFFinder; +import org.apache.poi.ss.formula.udf.DefaultUDFFinder; +import org.apache.poi.ss.formula.udf.UDFFinder; +import org.apache.poi.ss.usermodel.Cell; +import org.apache.poi.ss.usermodel.CellValue; +import org.apache.poi.ss.usermodel.FormulaError; +import org.apache.poi.ss.usermodel.FormulaEvaluator; +import org.apache.poi.ss.usermodel.Row; +import org.apache.poi.ss.usermodel.Sheet; +import org.apache.poi.ss.usermodel.Workbook; +import org.apache.poi.ss.usermodel.WorkbookFactory; +import org.apache.poi.ss.util.CellReference; +import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator; +import org.apache.poi.xssf.usermodel.XSSFWorkbook; +import org.apache.tools.ant.BuildException; +import org.apache.tools.ant.Project; +import org.apache.tools.ant.taskdefs.Typedef; + +/** + * A general utility class that abstracts the POI details of loading the + * workbook, accessing and updating cells. + * + * @author Jon Svede (jon [at] loquatic [dot] com) + * @author Brian Bush (brian [dot] bush [at] nrel [dot] gov) + * + */ +public class ExcelAntWorkbookUtil extends Typedef { + + private String excelFileName; + + private Workbook workbook; + + private final Map<String, FreeRefFunction> xlsMacroList = new HashMap<>(); + + /** + * Constructs an instance using a String that contains the fully qualified + * path of the Excel file. This constructor initializes a Workbook instance + * based on that file name. + * + * @param fName The fully qualified path of the Excel file. + * @throws BuildException If the workbook cannot be loaded. + */ + protected ExcelAntWorkbookUtil(String fName) { + excelFileName = fName; + loadWorkbook(); + + } + + /** + * Constructs an instance based on a Workbook instance. + * + * @param wb The Workbook to use for this instance. + */ + protected ExcelAntWorkbookUtil(Workbook wb) { + workbook = wb; + } + + /** + * Loads the member variable workbook based on the fileName variable. + * @return The opened Workbook-instance + * @throws BuildException If the workbook cannot be loaded. + */ + private Workbook loadWorkbook() { + if (excelFileName == null) { + throw new BuildException("fileName attribute must be set!", getLocation()); + } + + try { + try (FileInputStream fis = new FileInputStream(excelFileName)) { + workbook = WorkbookFactory.create(fis); + } + } catch(Exception e) { + throw new BuildException("Cannot load file " + excelFileName + + ". Make sure the path and file permissions are correct.", e); + } + + return workbook; + } + + /** + * Used to add a UDF to the evaluator. + * @param name The name of the function to add + * @param clazzName The class which implements this function + * @throws ClassNotFoundException if the class cannot be found + * @throws InstantiationException if the class cannot be constructed + * @throws IllegalAccessException if the constructor or the class is not accessible + */ + public void addFunction(String name, String clazzName) throws ClassNotFoundException, InstantiationException, IllegalAccessException, NoSuchMethodException, InvocationTargetException { + Class<?> clazzInst = Class.forName(clazzName); + Object newInst = clazzInst.getDeclaredConstructor().newInstance(); + if(newInst instanceof FreeRefFunction) { + addFunction(name, (FreeRefFunction)newInst); + } + + } + + /** + * Updates the internal HashMap of functions with instance and alias passed + * in. + * + * @param name the name of the function to replace + * @param func the function to use + */ + protected void addFunction(String name, FreeRefFunction func) { + xlsMacroList.put(name, func); + } + + /** + * returns a UDFFinder that contains all of the functions added. + * + * @return An instance of {@link UDFFinder} which can be used to + * lookup functions + */ + protected UDFFinder getFunctions() { + + String[] names = new String[xlsMacroList.size()]; + FreeRefFunction[] functions = new FreeRefFunction[xlsMacroList.size()]; + + int x = 0; + for(Map.Entry<String, FreeRefFunction> entry : xlsMacroList.entrySet()) { + names[x] = entry.getKey(); + functions[x] = entry.getValue(); + } + + UDFFinder udff1 = new DefaultUDFFinder(names, functions); + + return new AggregatingUDFFinder(udff1); + + } + + /** + * Returns a formula evaluator that is loaded with the functions that + * have been supplied. + * + * @param fileName Specifies if XSSF or HSSF should be used for + * the evaluator + * @return A {@link FormulaEvaluator} constructed accordingly + */ + protected FormulaEvaluator getEvaluator(String fileName) { + FormulaEvaluator evaluator; + if (fileName.endsWith(".xlsx")) { + if(xlsMacroList.size() > 0) { + evaluator = XSSFFormulaEvaluator.create((XSSFWorkbook) workbook, + null, + getFunctions()); + } + evaluator = new XSSFFormulaEvaluator((XSSFWorkbook) workbook); + } else { + if(xlsMacroList.size() > 0) { + evaluator = HSSFFormulaEvaluator.create((HSSFWorkbook)workbook, + null, + getFunctions()); + } + + evaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook); + } + + return evaluator; + + } + + /** + * Returns the Workbook instance associated with this WorkbookUtil. + * + * @return + */ + public Workbook getWorkbook() { + return workbook; + } + + /** + * Returns the fileName that was used to initialize this instance. May + * return null if the instance was constructed from a Workbook object. + * + * @return + */ + public String getFileName() { + return excelFileName; + } + + /** + * Returns the list of sheet names. + * + * @return + */ + public List<String> getSheets() { + ArrayList<String> sheets = new ArrayList<>(); + + int sheetCount = workbook.getNumberOfSheets(); + + for(int x=0; x<sheetCount; x++) { + sheets.add(workbook.getSheetName(x)); + } + + return sheets; + } + + /** + * This method uses a String in standard Excel format (SheetName!CellId) to + * locate the cell and set it to the value of the double in value. + * + * @param cellName + * @param value + */ + public void setDoubleValue(String cellName, double value) { + log("starting setCellValue()", Project.MSG_DEBUG); + Cell cell = getCell(cellName); + log("working on cell: " + cell, Project.MSG_DEBUG); + cell.setCellValue(value); + log("after cell.setCellValue()", Project.MSG_DEBUG); + + log("set cell " + cellName + " to value " + value, Project.MSG_DEBUG); + } + + /** + * Utility method for setting the value of a Cell with a String. + * + * @param cellName + * @param value + */ + public void setStringValue(String cellName, String value) { + Cell cell = getCell(cellName); + cell.setCellValue(value); + } + + /** + * Utility method for setting the value of a Cell with a Formula. + * + * @param cellName + * @param formula + */ + public void setFormulaValue(String cellName, String formula) { + Cell cell = getCell(cellName); + cell.setCellFormula(formula); + } + + /** + * Utility method for setting the value of a Cell with a Date. + * @param cellName + * @param date + */ + public void setDateValue(String cellName, Date date) { + Cell cell = getCell(cellName); + cell.setCellValue(date); + } + /** + * Uses a String in standard Excel format (SheetName!CellId) to locate a + * cell and evaluate it. + * + * @param cellName + * @param expectedValue + * @param precision + */ + public ExcelAntEvaluationResult evaluateCell(String cellName, double expectedValue, + double precision) { + + ExcelAntEvaluationResult evalResults = null; + + Cell cell = getCell(cellName); + + FormulaEvaluator evaluator = getEvaluator(excelFileName); + + + CellValue resultOfEval = evaluator.evaluate(cell); + + if (resultOfEval.getErrorValue() == 0) { + // the evaluation did not encounter errors + double result = resultOfEval.getNumberValue(); + double delta = Math.abs(result - expectedValue); + if (delta > precision) { + evalResults = new ExcelAntEvaluationResult(false, false, + resultOfEval.getNumberValue(), + "Results was out of range based on precision " + " of " + + precision + ". Delta was actually " + delta, delta, cellName); + } else { + evalResults = new ExcelAntEvaluationResult(false, true, + resultOfEval.getNumberValue(), + "Evaluation passed without error within in range.", delta, cellName); + } + } else { + String errorMeaning = null; + try { + errorMeaning = FormulaError.forInt(resultOfEval.getErrorValue()).getString(); + } catch(IllegalArgumentException iae) { + errorMeaning = "unknown error code: " + + Byte.toString(resultOfEval.getErrorValue()); + } + + evalResults = new ExcelAntEvaluationResult(true, false, + resultOfEval.getNumberValue(), + "Evaluation failed due to an evaluation error of " + + resultOfEval.getErrorValue() + + " which is " + + errorMeaning, 0, cellName); + } + + return evalResults; + } + + /** + * Returns a Cell as a String value. + * + * @param cellName + * @return + */ + public String getCellAsString(String cellName) { + Cell cell = getCell(cellName); + return cell.getStringCellValue(); + } + + + /** + * Returns the value of the Cell as a double. + * + * @param cellName + * @return + */ + public double getCellAsDouble(String cellName) { + Cell cell = getCell(cellName); + return cell.getNumericCellValue(); + } + /** + * Returns a cell reference based on a String in standard Excel format + * (SheetName!CellId). This method will create a new cell if the + * requested cell isn't initialized yet. + * + * @param cellName + * @return + */ + private Cell getCell(String cellName) { + CellReference cellRef = new CellReference(cellName); + String sheetName = cellRef.getSheetName(); + Sheet sheet = workbook.getSheet(sheetName); + if(sheet == null) { + throw new BuildException("Sheet not found: " + sheetName); + } + + int rowIdx = cellRef.getRow(); + int colIdx = cellRef.getCol(); + Row row = sheet.getRow(rowIdx); + + if(row == null) { + row = sheet.createRow(rowIdx); + } + + Cell cell = row.getCell(colIdx); + + if(cell == null) { + cell = row.createCell(colIdx); + } + + return cell; + } +} diff --git a/poi-excelant/src/main/java/org/apache/poi/ss/excelant/util/ExcelAntWorkbookUtilFactory.java b/poi-excelant/src/main/java/org/apache/poi/ss/excelant/util/ExcelAntWorkbookUtilFactory.java new file mode 100644 index 0000000000..113f0390f7 --- /dev/null +++ b/poi-excelant/src/main/java/org/apache/poi/ss/excelant/util/ExcelAntWorkbookUtilFactory.java @@ -0,0 +1,60 @@ +/* ==================================================================== + 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.excelant.util; + +import java.util.HashMap; +import java.util.Map; + + +/** + * This is a factory class maps file names to WorkbookUtil instances. This + * helps ExcelAnt be more efficient when being run many times in an Ant build. + * + * @author Jon Svede (jon [at] loquatic [dot] com) + * @author Brian Bush (brian [dot] bush [at] nrel [dot] gov) + * + */ +public final class ExcelAntWorkbookUtilFactory { + + private static Map<String, ExcelAntWorkbookUtil> workbookUtilMap; + + private ExcelAntWorkbookUtilFactory() { + } + + /** + * Using the fileName, check the internal map to see if an instance + * of the WorkbookUtil exists. If not, then add an instance to the map. + * + * @param fileName The filename to use as key to look for the ExcelAntWorkbookUtil. + * @return An instance of ExcelAntWorkbookUtil associated with the filename or + * a freshly instantiated one if none did exist before. + */ + public static ExcelAntWorkbookUtil getInstance(String fileName) { + if(workbookUtilMap == null) { + workbookUtilMap = new HashMap<>(); + } + + if(workbookUtilMap.containsKey(fileName)) { + return workbookUtilMap.get(fileName); + } + + ExcelAntWorkbookUtil wbu = new ExcelAntWorkbookUtil(fileName); + workbookUtilMap.put(fileName, wbu); + return wbu; + } +} diff --git a/poi-excelant/src/main/java9/module-info.class b/poi-excelant/src/main/java9/module-info.class Binary files differnew file mode 100644 index 0000000000..9d9c4c142d --- /dev/null +++ b/poi-excelant/src/main/java9/module-info.class diff --git a/poi-excelant/src/main/java9/module-info.java b/poi-excelant/src/main/java9/module-info.java new file mode 100644 index 0000000000..43b64560a7 --- /dev/null +++ b/poi-excelant/src/main/java9/module-info.java @@ -0,0 +1,28 @@ +/* ==================================================================== + 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. +==================================================================== */ + +module org.apache.poi.excelant { + + requires ant; + requires org.apache.poi.ooxml; + requires org.apache.poi.scratchpad; + + exports org.apache.poi.ss.excelant; + exports org.apache.poi.ss.excelant.util; + + opens org.apache.poi.ss.excelant; +}
\ No newline at end of file diff --git a/poi-excelant/src/main/resources/org/apache/poi/ss/excelant/antlib.xml b/poi-excelant/src/main/resources/org/apache/poi/ss/excelant/antlib.xml new file mode 100644 index 0000000000..af13cc649b --- /dev/null +++ b/poi-excelant/src/main/resources/org/apache/poi/ss/excelant/antlib.xml @@ -0,0 +1,31 @@ +<?xml version="1.0"?> +<!-- +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. +--> +<antlib> + + <typedef name="excelant" classname="org.apache.poi.ss.excelant.ExcelAntTask" /> + <typedef name="test" classname="org.apache.poi.ss.excelant.ExcelAntTest"/> + <typedef name="setDouble" classname="org.apache.poi.ss.excelant.ExcelAntSetDoubleCell"/> + <typedef name="setString" classname="org.apache.poi.ss.excelant.ExcelAntSetStringCell"/> + <typedef name="setFormula" classname="org.apache.poi.ss.excelant.ExcelAntSetFormulaCell"/> + <typedef name="evaluate" classname="org.apache.poi.ss.excelant.ExcelAntEvaluateCell"/> + <typedef name="udf" classname="org.apache.poi.ss.excelant.ExcelAntUserDefinedFunction"/> + <typedef name="handler" classname="org.apache.poi.ss.excelant.ExcelAntHandlerTask"/> + +</antlib> diff --git a/poi-excelant/src/poi-ant-contrib/java/Bytes2Mega.java b/poi-excelant/src/poi-ant-contrib/java/Bytes2Mega.java new file mode 100644 index 0000000000..3bdafe18f5 --- /dev/null +++ b/poi-excelant/src/poi-ant-contrib/java/Bytes2Mega.java @@ -0,0 +1,42 @@ +/* ==================================================================== + 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. +==================================================================== */ + +import java.text.DecimalFormat; +import java.text.NumberFormat; + +import org.apache.tools.ant.Project; +import org.apache.tools.ant.Task; + +public class Bytes2Mega extends Task { + private final NumberFormat formatter = new DecimalFormat("#0.00"); + private String property; + private int bytes; + + public void setProperty(String property) { + this.property = property; + } + + public void setBytes(int bytes) { + this.bytes = bytes; + } + + public void execute() { + Project project = getProject(); + double mega = bytes/(1024.*1024.); + project.setProperty(property, formatter.format(mega)); + } +} diff --git a/poi-excelant/src/poi-ant-contrib/java/Junit5Progress.java b/poi-excelant/src/poi-ant-contrib/java/Junit5Progress.java new file mode 100644 index 0000000000..8e87ef064f --- /dev/null +++ b/poi-excelant/src/poi-ant-contrib/java/Junit5Progress.java @@ -0,0 +1,99 @@ +/* ==================================================================== +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. +==================================================================== */ + +import java.io.PrintStream; +import java.time.Duration; +import java.time.Instant; +import java.util.Optional; +import java.util.concurrent.atomic.AtomicInteger; + +import org.junit.platform.engine.TestExecutionResult; +import org.junit.platform.launcher.TestExecutionListener; +import org.junit.platform.launcher.TestIdentifier; + +/** + * Custom listener class for Ants junitlauncher, because it chomps the important running details + * + * @see <a href="https://bz.apache.org/bugzilla/show_bug.cgi?id=64836">Bug 64836 - junitlaucher poor summary</a> + **/ +public class Junit5Progress implements TestExecutionListener { + private final AtomicInteger numSkippedInTestSet = new AtomicInteger(); + private final AtomicInteger numAbortedInTestSet = new AtomicInteger(); + private final AtomicInteger numSucceededInTestSet = new AtomicInteger(); + private final AtomicInteger numFailedInTestSet = new AtomicInteger(); + private Instant testSetStartTime; + + final PrintStream out; + + public Junit5Progress() { + this.out = System.out; + } + + private void resetCountsForNewTestSet() { + this.numSkippedInTestSet.set(0); + this.numAbortedInTestSet.set(0); + this.numSucceededInTestSet.set(0); + this.numFailedInTestSet.set(0); + this.testSetStartTime = Instant.now(); + } + + @Override + public void executionStarted(TestIdentifier testIdentifier) { + Optional<String> parentId = testIdentifier.getParentId(); + if (parentId.isPresent() && parentId.get().indexOf('/') < 0) { + println("\nRunning " + testIdentifier.getLegacyReportingName()); + resetCountsForNewTestSet(); + } + } + + @Override + public void executionSkipped(TestIdentifier testIdentifier, String reason) { + this.numSkippedInTestSet.incrementAndGet(); + } + + @Override + public void executionFinished(TestIdentifier testIdentifier, TestExecutionResult testExecutionResult) { + Optional<String> parentId = testIdentifier.getParentId(); + if (parentId.isPresent() && parentId.get().indexOf('/') < 0) { + int totalTestsInClass = this.numSucceededInTestSet.get() + this.numAbortedInTestSet.get() + this.numFailedInTestSet.get() + + this.numSkippedInTestSet.get(); + Duration duration = Duration.between(this.testSetStartTime, Instant.now()); + double numSeconds = (double) duration.toMillis() / 1_000; + String summary = String.format("Tests run: %d, Failures: %d, Aborted: %d, Skipped: %d, Time elapsed: %f sec", totalTestsInClass, + this.numFailedInTestSet.get(), this.numAbortedInTestSet.get(), this.numSkippedInTestSet.get(), numSeconds); + println(summary); + } else if (testIdentifier.isTest()) { + switch (testExecutionResult.getStatus()) { + case SUCCESSFUL: + this.numSucceededInTestSet.incrementAndGet(); + break; + case ABORTED: + println(" Aborted: " + testIdentifier.getDisplayName()); + this.numAbortedInTestSet.incrementAndGet(); + break; + case FAILED: + println(" Failed: " + testIdentifier.getDisplayName()); + this.numFailedInTestSet.incrementAndGet(); + break; + } + } + } + + private void println(String str) { + this.out.println(str); + } +}
\ No newline at end of file diff --git a/poi-excelant/src/poi-ant-contrib/java/NextRelease.java b/poi-excelant/src/poi-ant-contrib/java/NextRelease.java new file mode 100644 index 0000000000..c0b6b3d864 --- /dev/null +++ b/poi-excelant/src/poi-ant-contrib/java/NextRelease.java @@ -0,0 +1,46 @@ +/* ==================================================================== + 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. +==================================================================== */ + +import java.util.regex.Matcher; +import java.util.regex.Pattern; + +import org.apache.tools.ant.Project; +import org.apache.tools.ant.Task; + +@SuppressWarnings("unused") +public class NextRelease extends Task { + private final Pattern pattern = Pattern.compile("(\\d+)\\.(\\d+)\\.(\\d+).*"); + private String property; + private int increment = 1; + + public void setProperty(String property) { + this.property = property; + } + + public void setIncrement(int increment) { + this.increment = increment; + } + + public void execute() { + Project project = getProject(); + String relCurr = project.getProperty("version.id"); + Matcher m = pattern.matcher(relCurr); + if (m.find()) { + project.setProperty(property, m.group(1) + "." + m.group(2) + "." + (Integer.parseInt(m.group(3)) + increment)); + } + } +} diff --git a/poi-excelant/src/poi-ant-contrib/java/PropertyReset.java b/poi-excelant/src/poi-ant-contrib/java/PropertyReset.java new file mode 100644 index 0000000000..4f569e67fd --- /dev/null +++ b/poi-excelant/src/poi-ant-contrib/java/PropertyReset.java @@ -0,0 +1,41 @@ +/* ==================================================================== + 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. +==================================================================== */ + +import org.apache.tools.ant.Project; +import org.apache.tools.ant.Task; + +public class PropertyReset extends Task { + private String name; + private String value; + + public void setName(String name) { + this.name = name; + } + + public void setValue(String value) { + this.value = value; + } + + public void execute() { + Project project = getProject(); + if (project.getUserProperty(name) != null) { + project.setUserProperty(name, value); + } else { + project.setProperty(name, value); + } + } +}
\ No newline at end of file diff --git a/poi-excelant/src/test/java/org/apache/poi/ss/excelant/CalculateMortgageFunction.java b/poi-excelant/src/test/java/org/apache/poi/ss/excelant/CalculateMortgageFunction.java new file mode 100644 index 0000000000..73eb984db7 --- /dev/null +++ b/poi-excelant/src/test/java/org/apache/poi/ss/excelant/CalculateMortgageFunction.java @@ -0,0 +1,93 @@ +/* ==================================================================== + 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.excelant; + +import org.apache.poi.ss.formula.OperationEvaluationContext; +import org.apache.poi.ss.formula.eval.ErrorEval; +import org.apache.poi.ss.formula.eval.EvaluationException; +import org.apache.poi.ss.formula.eval.NumberEval; +import org.apache.poi.ss.formula.eval.OperandResolver; +import org.apache.poi.ss.formula.eval.ValueEval; +import org.apache.poi.ss.formula.functions.FreeRefFunction; + +/** + * A simple user-defined function to calculate principal and interest. + * + * Used by {@link org.apache.poi.ss.excelant.util.TestExcelAntWorkbookUtil}. + * + * @author Jon Svede ( jon [at] loquatic [dot] com ) + * @author Brian Bush ( brian [dot] bush [at] nrel [dot] gov ) + * + */ +public class CalculateMortgageFunction implements FreeRefFunction { + + @Override + public ValueEval evaluate( ValueEval[] args, OperationEvaluationContext ec ) { + + // verify that we have enough data + if (args.length != 3) { + return ErrorEval.VALUE_INVALID; + } + + // declare doubles for values + double principal, rate, years, result; + try { + // extract values as ValueEval + ValueEval v1 = OperandResolver.getSingleValue( args[0], + ec.getRowIndex(), + ec.getColumnIndex() ) ; + ValueEval v2 = OperandResolver.getSingleValue( args[1], + ec.getRowIndex(), + ec.getColumnIndex() ) ; + ValueEval v3 = OperandResolver.getSingleValue( args[2], + ec.getRowIndex(), + ec.getColumnIndex() ) ; + + // get data as doubles + principal = OperandResolver.coerceValueToDouble( v1 ) ; + rate = OperandResolver.coerceValueToDouble( v2 ) ; + years = OperandResolver.coerceValueToDouble( v3 ) ; + + result = calculateMortgagePayment( principal, rate, years ) ; + System.out.println( "Result = " + result ) ; + + checkValue(result); + + } catch (EvaluationException e) { + return e.getErrorEval(); + } + + return new NumberEval( result ) ; + } + + public double calculateMortgagePayment( double p, double r, double y ) { + double i = r / 12 ; + double n = y * 12 ; + + return p * (( i * Math.pow((1 + i),n ) ) / ( Math.pow((1 + i),n) - 1)); + } + /** + * Excel does not support infinities and NaNs, rather, it gives a #NUM! error in these cases + * + * @throws EvaluationException (#NUM!) if <tt>result</tt> is <tt>NaN</> or <tt>Infinity</tt> + */ + private void checkValue(double result) throws EvaluationException { + if (Double.isNaN(result) || Double.isInfinite(result)) { + throw new EvaluationException(ErrorEval.NUM_ERROR); + } + } +} diff --git a/poi-excelant/src/test/java/org/apache/poi/ss/excelant/ExcelAntUserDefinedFunctionTestHelper.java b/poi-excelant/src/test/java/org/apache/poi/ss/excelant/ExcelAntUserDefinedFunctionTestHelper.java new file mode 100644 index 0000000000..48086fc22d --- /dev/null +++ b/poi-excelant/src/test/java/org/apache/poi/ss/excelant/ExcelAntUserDefinedFunctionTestHelper.java @@ -0,0 +1,34 @@ +/* ==================================================================== + 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.excelant; + +public class ExcelAntUserDefinedFunctionTestHelper extends + ExcelAntUserDefinedFunction { + + @Override + protected String getFunctionAlias() { + // TODO Auto-generated method stub + return super.getFunctionAlias(); + } + + @Override + protected String getClassName() { + // TODO Auto-generated method stub + return super.getClassName(); + } + +} diff --git a/poi-excelant/src/test/java/org/apache/poi/ss/excelant/MockExcelAntWorkbookHandler.java b/poi-excelant/src/test/java/org/apache/poi/ss/excelant/MockExcelAntWorkbookHandler.java new file mode 100644 index 0000000000..d6a3a74b7d --- /dev/null +++ b/poi-excelant/src/test/java/org/apache/poi/ss/excelant/MockExcelAntWorkbookHandler.java @@ -0,0 +1,39 @@ +/* + * 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.excelant; + +import static org.junit.jupiter.api.Assertions.assertNotNull; + +import org.apache.poi.ss.usermodel.Workbook; + +public class MockExcelAntWorkbookHandler implements IExcelAntWorkbookHandler { + public static boolean executed; + public static Workbook workbook; + + + @Override + public void setWorkbook(Workbook workbook) { + MockExcelAntWorkbookHandler.workbook = workbook; + } + + @Override + public void execute() { + executed = true; + assertNotNull(workbook); + } +} diff --git a/poi-excelant/src/test/java/org/apache/poi/ss/excelant/TestBuildFile.java b/poi-excelant/src/test/java/org/apache/poi/ss/excelant/TestBuildFile.java new file mode 100644 index 0000000000..2a2b36b827 --- /dev/null +++ b/poi-excelant/src/test/java/org/apache/poi/ss/excelant/TestBuildFile.java @@ -0,0 +1,375 @@ +/* + * 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.excelant; + +import static org.apache.poi.POITestCase.assertContains; +import static org.apache.poi.POITestCase.assertNotContained; +import static org.junit.jupiter.api.Assertions.assertNotNull; +import static org.junit.jupiter.api.Assertions.assertTrue; +import static org.junit.jupiter.api.Assertions.fail; + +import java.io.File; +import java.io.PrintStream; + +import org.apache.poi.POIDataSamples; +import org.apache.poi.util.NullPrintStream; +import org.apache.tools.ant.BuildEvent; +import org.apache.tools.ant.BuildListener; +import org.apache.tools.ant.Project; +import org.apache.tools.ant.ProjectHelper; +import org.junit.jupiter.api.AfterEach; +import org.junit.jupiter.api.BeforeEach; +import org.junit.jupiter.api.Test; + +/** + * JUnit test for the ExcelAnt tasks. + * Leverages Ant's test framework. + */ +public class TestBuildFile { + + protected Project project; + + private StringBuilder logBuffer; + private StringBuilder fullLogBuffer; + + + @BeforeEach + void setUp() { + String filename = TestBuildFile.getDataDir() + "/../poi-excelant/src/test/resources/tests.xml"; + int logLevel = Project.MSG_DEBUG; + + logBuffer = new StringBuilder(); + fullLogBuffer = new StringBuilder(); + project = new Project(); + project.init(); + project.setNewProperty("data.dir.name", getDataDir()); + File antFile = new File(System.getProperty("root"), filename); + project.setUserProperty("ant.file", antFile.getAbsolutePath()); + project.addBuildListener(new AntTestListener(logLevel)); + ProjectHelper.configureProject(project, antFile); + + } + + /** + * Automatically calls the target called "tearDown" + * from the build file tested if it exits. + * <p> + * This allows to use Ant tasks directly in the build file + * to clean up after each test. Note that no "setUp" target + * is automatically called, since it's trivial to have a + * test target depend on it. + */ + @AfterEach + void tearDown() { + if (project == null) { + /* + * Maybe the BuildFileTest was subclassed and there is + * no initialized project. So we could avoid getting a + * NPE. + * If there is an initialized project getTargets() does + * not return null as it is initialized by an empty + * HashSet. + */ + return; + } + final String tearDown = "tearDown"; + if (project.getTargets().containsKey(tearDown)) { + project.executeTarget(tearDown); + } + } + + /** + * run a target, expect for any build exception + * + * @param target target to run + * @param cause information string to reader of report + */ + void expectBuildException(String target, String cause) { + expectSpecificBuildException(target, cause, null); + } + + /** + * Assert that the given substring is in the log messages. + */ + void assertLogContaining(String substring) { + assertContains(getLog(), substring); + } + + /** + * Assert that the given substring is not in the log messages. + */ + void assertLogNotContaining(String substring) { + assertNotContained(getLog(), substring); + } + + /** + * Gets the log the BuildFileTest object. + * Only valid if configureProject() has been called. + * + * @return The log value + */ + public String getLog() { + return logBuffer.toString(); + } + + /** + * Executes a target we have set up + * + * @param targetName target to run + */ + void executeTarget(String targetName) { + PrintStream sysOut = System.out; + PrintStream sysErr = System.err; + try { + sysOut.flush(); + sysErr.flush(); + System.setOut(new NullPrintStream()); + System.setErr(new NullPrintStream()); + logBuffer = new StringBuilder(); + fullLogBuffer = new StringBuilder(); + project.executeTarget(targetName); + } finally { + System.setOut(sysOut); + System.setErr(sysErr); + } + + } + + /** + * Runs a target, wait for a build exception. + * + * @param target target to run + * @param cause information string to reader of report + * @param msg the message value of the build exception we are waiting + * for set to null for any build exception to be valid + */ + void expectSpecificBuildException(String target, String cause, String msg) { + try { + executeTarget(target); + } catch (org.apache.tools.ant.BuildException ex) { + assertTrue(msg == null || ex.getMessage().equals(msg), + "Should throw BuildException because '" + cause + "' with message '" + msg + "' (actual message '" + ex.getMessage() + "' instead)" + ); + return; + } + fail("Should throw BuildException because: " + cause); + } + + public static String getDataDir() { + String dataDirName = System.getProperty(POIDataSamples.TEST_PROPERTY); + return dataDirName == null ? "test-data" : dataDirName; + } + + /** + * Our own personal build listener. + */ + private class AntTestListener implements BuildListener { + private final int logLevel; + + /** + * Constructs a test listener which will ignore log events + * above the given level. + */ + public AntTestListener(int logLevel) { + this.logLevel = logLevel; + } + + /** + * Fired before any targets are started. + */ + @Override + public void buildStarted(BuildEvent event) { + } + + /** + * Fired after the last target has finished. This event + * will still be thrown if an error occurred during the build. + * + * @see BuildEvent#getException() + */ + @Override + public void buildFinished(BuildEvent event) { + } + + /** + * Fired when a target is started. + * + * @see BuildEvent#getTarget() + */ + @Override + public void targetStarted(BuildEvent event) { + //System.out.println("targetStarted " + event.getTarget().getName()); + } + + /** + * Fired when a target has finished. This event will + * still be thrown if an error occurred during the build. + * + * @see BuildEvent#getException() + */ + @Override + public void targetFinished(BuildEvent event) { + //System.out.println("targetFinished " + event.getTarget().getName()); + } + + /** + * Fired when a task is started. + * + * @see BuildEvent#getTask() + */ + @Override + public void taskStarted(BuildEvent event) { + //System.out.println("taskStarted " + event.getTask().getTaskName()); + } + + /** + * Fired when a task has finished. This event will still + * be throw if an error occurred during the build. + * + * @see BuildEvent#getException() + */ + @Override + public void taskFinished(BuildEvent event) { + //System.out.println("taskFinished " + event.getTask().getTaskName()); + } + + /** + * Fired whenever a message is logged. + * + * @see BuildEvent#getMessage() + * @see BuildEvent#getPriority() + */ + @Override + public void messageLogged(BuildEvent event) { + if (event.getPriority() > logLevel) { + // ignore event + return; + } + + if (event.getPriority() == Project.MSG_INFO || + event.getPriority() == Project.MSG_WARN || + event.getPriority() == Project.MSG_ERR) { + logBuffer.append(event.getMessage()); + } + fullLogBuffer.append(event.getMessage()); + } + } + + @Test + void testMissingFilename() { + expectSpecificBuildException("test-nofile", "required argument not specified", + "fileName attribute must be set!"); + } + + @Test + void testFileNotFound() { + expectSpecificBuildException("test-filenotfound", "required argument not specified", + "Cannot load file invalid.xls. Make sure the path and file permissions are correct."); + } + + @Test + void testEvaluate() { + executeTarget("test-evaluate"); + assertLogContaining("Using input file: " + TestBuildFile.getDataDir() + "/spreadsheet/excelant.xls"); + assertLogContaining("Succeeded when evaluating 'MortgageCalculator'!$B$4."); + } + + @Test + void testEvaluateNoDetails() { + executeTarget("test-evaluate-nodetails"); + assertLogContaining("Using input file: " + TestBuildFile.getDataDir() + "/spreadsheet/excelant.xls"); + assertLogNotContaining("Succeeded when evaluating 'MortgageCalculator'!$B$4."); + } + + @Test + void testPrecision() { + executeTarget("test-precision"); + + assertLogContaining("Using input file: " + TestBuildFile.getDataDir() + "/spreadsheet/excelant.xls"); + assertLogContaining("Succeeded when evaluating 'MortgageCalculator'!$B$4. " + + "It evaluated to 2285.5761494145563 when the value of 2285.576149 with precision of 1.0E-4"); + assertLogContaining("Succeeded when evaluating 'MortgageCalculator'!$B$4. " + + "It evaluated to 2285.5761494145563 when the value of 2285.576149 with precision of 1.0E-5"); + assertLogContaining("Failed to evaluate cell 'MortgageCalculator'!$B$4. " + + "It evaluated to 2285.5761494145563 when the value of 2285.576149 with precision of 1.0E-10 was expected."); + assertLogContaining("2/3 tests passed"); + } + + @Test + void testPrecisionFail() { + expectSpecificBuildException("test-precision-fails", "precision not matched", + "\tFailed to evaluate cell 'MortgageCalculator'!$B$4. It evaluated to 2285.5761494145563 when the value of 2285.576149 with precision of 1.0E-10 was expected."); + } + + @Test + void testPassOnError() { + executeTarget("test-passonerror"); + assertLogContaining("Using input file: " + TestBuildFile.getDataDir() + "/spreadsheet/excelant.xls"); + assertLogContaining("Test named failonerror failed because 1 of 0 evaluations failed to evaluate correctly."); + } + + @Test + void testFailOnError() { + expectBuildException("test-failonerror", "fail on error"); + assertLogContaining("Using input file: " + TestBuildFile.getDataDir() + "/spreadsheet/excelant.xls"); + assertLogNotContaining("failed because 1 of 0 evaluations failed to evaluate correctly. Failed to evaluate cell 'MortageCalculatorFunction'!$D$3"); + } + + @Test + void testFailOnErrorNoDetails() { + expectBuildException("test-failonerror-nodetails", "fail on error"); + assertLogNotContaining("Using input file: " + TestBuildFile.getDataDir() + "/spreadsheet/excelant.xls"); + assertLogNotContaining("failed because 1 of 0 evaluations failed to evaluate correctly. Failed to evaluate cell 'MortageCalculatorFunction'!$D$3"); + } + + @Test + void testUdf() { + executeTarget("test-udf"); + assertLogContaining("1/1 tests passed"); + } + + @Test + void testSetText() { + executeTarget("test-settext"); + assertLogContaining("1/1 tests passed"); + } + + @Test + void testAddHandler() { + executeTarget("test-addhandler"); + assertLogContaining("Using input file: " + TestBuildFile.getDataDir() + "/spreadsheet/excelant.xls"); + assertLogContaining("Succeeded when evaluating 'MortgageCalculator'!$B$4."); + + assertNotNull(MockExcelAntWorkbookHandler.workbook, "The workbook should have been passed to the handler"); + assertTrue(MockExcelAntWorkbookHandler.executed, "The handler should have been executed"); + } + + @Test + void testAddHandlerWrongClass() { + executeTarget("test-addhandler-wrongclass"); + assertLogContaining("Using input file: " + TestBuildFile.getDataDir() + "/spreadsheet/excelant.xls"); + assertLogContaining("Succeeded when evaluating 'MortgageCalculator'!$B$4."); + } + + @Test + void testAddHandlerFails() { + expectSpecificBuildException("test-addhandler-fails", "NullPointException", null); + } + +} diff --git a/poi-excelant/src/test/java/org/apache/poi/ss/excelant/TestExcelAntPrecision.java b/poi-excelant/src/test/java/org/apache/poi/ss/excelant/TestExcelAntPrecision.java new file mode 100644 index 0000000000..2902144fde --- /dev/null +++ b/poi-excelant/src/test/java/org/apache/poi/ss/excelant/TestExcelAntPrecision.java @@ -0,0 +1,54 @@ +/* ==================================================================== + 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.excelant; + +import static org.junit.jupiter.api.Assertions.assertEquals; +import static org.junit.jupiter.api.Assertions.assertTrue; + +import org.junit.jupiter.api.AfterEach; +import org.junit.jupiter.api.BeforeEach; +import org.junit.jupiter.api.Test; + +class TestExcelAntPrecision { + + private ExcelAntPrecision fixture ; + + @BeforeEach + void setUp() { + fixture = new ExcelAntPrecision() ; + } + + @AfterEach + void tearDown() { + fixture = null ; + } + + @Test + void testVerifyPrecision() { + + double value = 1.0E-1 ; + + fixture.setValue( value ) ; + + double result = fixture.getValue() ; + + assertTrue( result > 0 ) ; + + assertEquals( value, result, 0.0 ) ; + } + +} diff --git a/poi-excelant/src/test/java/org/apache/poi/ss/excelant/TestExcelAntSet.java b/poi-excelant/src/test/java/org/apache/poi/ss/excelant/TestExcelAntSet.java new file mode 100644 index 0000000000..42f6cd256c --- /dev/null +++ b/poi-excelant/src/test/java/org/apache/poi/ss/excelant/TestExcelAntSet.java @@ -0,0 +1,69 @@ +/* ==================================================================== + 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.excelant; + +import static org.junit.jupiter.api.Assertions.assertEquals; +import static org.junit.jupiter.api.Assertions.assertNotNull; + +import org.apache.poi.ss.excelant.util.ExcelAntWorkbookUtil; +import org.apache.poi.ss.excelant.util.ExcelAntWorkbookUtilFactory; +import org.junit.jupiter.api.AfterEach; +import org.junit.jupiter.api.BeforeEach; +import org.junit.jupiter.api.Test; + +class TestExcelAntSet { + + + // This is abstract in nature, so we'll use a + // concrete instance to test the set methods. + private ExcelAntSet fixture ; + + private static final String mortgageCalculatorFileName = + TestBuildFile.getDataDir() + "/spreadsheet/mortgage-calculation.xls" ; + + @BeforeEach + void setUp() { + fixture = new ExcelAntSetDoubleCell() ; + } + + @AfterEach + void tearDown() { + fixture = null ; + } + + @Test + void testSetter() { + String cell = "simpleCellRef!$F$1" ; + + fixture.setCell( cell ) ; + + String cellStr = fixture.getCell() ; + + assertNotNull( cellStr ) ; + assertEquals( cell, cellStr ) ; + } + + @Test + void testSetWorkbookUtil() { + ExcelAntWorkbookUtil util = ExcelAntWorkbookUtilFactory.getInstance( + mortgageCalculatorFileName ) ; + + assertNotNull( util ) ; + + fixture.setWorkbookUtil( util ) ; + } +} diff --git a/poi-excelant/src/test/java/org/apache/poi/ss/excelant/TestExcelAntSetDoubleCell.java b/poi-excelant/src/test/java/org/apache/poi/ss/excelant/TestExcelAntSetDoubleCell.java new file mode 100644 index 0000000000..45c4f53635 --- /dev/null +++ b/poi-excelant/src/test/java/org/apache/poi/ss/excelant/TestExcelAntSetDoubleCell.java @@ -0,0 +1,70 @@ +/* ==================================================================== + 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.excelant; + +import static org.junit.jupiter.api.Assertions.assertEquals; +import static org.junit.jupiter.api.Assertions.assertTrue; + +import org.apache.poi.ss.excelant.util.ExcelAntWorkbookUtil; +import org.apache.poi.ss.excelant.util.ExcelAntWorkbookUtilFactory; +import org.junit.jupiter.api.AfterEach; +import org.junit.jupiter.api.BeforeEach; +import org.junit.jupiter.api.Test; + +class TestExcelAntSetDoubleCell { + + private ExcelAntSetDoubleCell fixture ; + + private ExcelAntWorkbookUtil util ; + + private static final String mortgageCalculatorFileName = + TestBuildFile.getDataDir() + "/spreadsheet/mortgage-calculation.xls" ; + + @BeforeEach + void setUp() { + fixture = new ExcelAntSetDoubleCell() ; + util = ExcelAntWorkbookUtilFactory.getInstance(mortgageCalculatorFileName ) ; + fixture.setWorkbookUtil( util ) ; + } + + @AfterEach + void tearDown() { + fixture = null ; + } + + @Test + void testSetDouble() { + String cellId = "'Sheet3'!$A$1" ; + double testValue = 1.1 ; + + fixture.setCell( cellId ) ; + fixture.setValue( testValue ) ; + + double value = fixture.getCellValue() ; + + assertTrue( value > 0 ) ; + assertEquals( testValue, value, 0.0 ) ; + + fixture.execute() ; + + double setValue = util.getCellAsDouble( cellId ) ; + + assertEquals( setValue, testValue, 0.0 ) ; + } + + +} diff --git a/poi-excelant/src/test/java/org/apache/poi/ss/excelant/TestExcelAntUserDefinedFunction.java b/poi-excelant/src/test/java/org/apache/poi/ss/excelant/TestExcelAntUserDefinedFunction.java new file mode 100644 index 0000000000..52b580ac6f --- /dev/null +++ b/poi-excelant/src/test/java/org/apache/poi/ss/excelant/TestExcelAntUserDefinedFunction.java @@ -0,0 +1,57 @@ +/* ==================================================================== + 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.excelant; + +import static org.junit.jupiter.api.Assertions.assertEquals; +import static org.junit.jupiter.api.Assertions.assertNotNull; + +import org.junit.jupiter.api.BeforeEach; +import org.junit.jupiter.api.Test; + +class TestExcelAntUserDefinedFunction { + + private ExcelAntUserDefinedFunctionTestHelper fixture ; + + @BeforeEach + void setUp() { + fixture = new ExcelAntUserDefinedFunctionTestHelper() ; + } + + @Test + void testSetClassName() { + String className = "simple.class.name" ; + + fixture.setClassName( className ) ; + String value = fixture.getClassName() ; + + assertNotNull( value ) ; + assertEquals( className, value ) ; + } + + @Test + void testSetFunction() { + String functionAlias = "alias" ; + + fixture.setFunctionAlias( functionAlias ) ; + + String alias = fixture.getFunctionAlias() ; + + assertNotNull( alias ) ; + assertEquals( functionAlias, alias ) ; + } + +} diff --git a/poi-excelant/src/test/java/org/apache/poi/ss/excelant/util/ExcelAntWorkbookUtilTestHelper.java b/poi-excelant/src/test/java/org/apache/poi/ss/excelant/util/ExcelAntWorkbookUtilTestHelper.java new file mode 100644 index 0000000000..16189f1d50 --- /dev/null +++ b/poi-excelant/src/test/java/org/apache/poi/ss/excelant/util/ExcelAntWorkbookUtilTestHelper.java @@ -0,0 +1,50 @@ +/* ==================================================================== + 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.excelant.util; + +import org.apache.poi.ss.formula.udf.UDFFinder; +import org.apache.poi.ss.usermodel.FormulaEvaluator; +import org.apache.poi.ss.usermodel.Workbook; + +/** + * A helper class to allow testing of protected methods and constructors. + * + * @author jsvede + * + */ +public class ExcelAntWorkbookUtilTestHelper extends ExcelAntWorkbookUtil { + + public ExcelAntWorkbookUtilTestHelper(String fName) { + super(fName); + } + + public ExcelAntWorkbookUtilTestHelper(Workbook wb) { + super(wb); + } + + @Override + public UDFFinder getFunctions() { + return super.getFunctions(); + } + + @Override + public FormulaEvaluator getEvaluator(String excelFileName) { + return super.getEvaluator(excelFileName); + } + + +} diff --git a/poi-excelant/src/test/java/org/apache/poi/ss/excelant/util/TestExcelAntEvaluationResult.java b/poi-excelant/src/test/java/org/apache/poi/ss/excelant/util/TestExcelAntEvaluationResult.java new file mode 100644 index 0000000000..dcb044a2eb --- /dev/null +++ b/poi-excelant/src/test/java/org/apache/poi/ss/excelant/util/TestExcelAntEvaluationResult.java @@ -0,0 +1,76 @@ +/* ==================================================================== + 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.excelant.util; + +import static org.junit.jupiter.api.Assertions.assertEquals; +import static org.junit.jupiter.api.Assertions.assertNotNull; + +import org.junit.jupiter.api.AfterEach; +import org.junit.jupiter.api.BeforeEach; +import org.junit.jupiter.api.Test; + +class TestExcelAntEvaluationResult { + private ExcelAntEvaluationResult fixture; + + private boolean completedWithError; + private boolean passed; + private double retValue = 1.1; + private String errMessage = "error message"; + private double delta = 2.2; + private String cellId = "testCell!$F$1"; + + @BeforeEach + void setUp() { + fixture = new ExcelAntEvaluationResult(completedWithError, + passed, + retValue, + errMessage, + delta, + cellId); + } + + @AfterEach + void tearDown() { + fixture = null; + } + + @Test + void testCompletedWithErrorMessage() { + String errMsg = fixture.getErrorMessage(); + assertNotNull(errMsg); + assertEquals(errMsg, errMessage); + } + + @Test + void testPassed() { + boolean passedValue = fixture.didTestPass(); + assertEquals(passedValue, passed); + } + + @Test + void testDelta() { + double deltaValue = fixture.getDelta(); + assertEquals(deltaValue, delta, 0.0); + } + + @Test + void testCellId() { + String cellIdValue = fixture.getCellName(); + assertNotNull(cellIdValue); + assertEquals(cellIdValue, cellId); + } +} diff --git a/poi-excelant/src/test/java/org/apache/poi/ss/excelant/util/TestExcelAntWorkbookUtil.java b/poi-excelant/src/test/java/org/apache/poi/ss/excelant/util/TestExcelAntWorkbookUtil.java new file mode 100644 index 0000000000..4f46084c6a --- /dev/null +++ b/poi-excelant/src/test/java/org/apache/poi/ss/excelant/util/TestExcelAntWorkbookUtil.java @@ -0,0 +1,373 @@ +/* ==================================================================== + 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.excelant.util; + +import static org.junit.jupiter.api.Assertions.assertEquals; +import static org.junit.jupiter.api.Assertions.assertFalse; +import static org.junit.jupiter.api.Assertions.assertNotNull; +import static org.junit.jupiter.api.Assertions.assertNull; +import static org.junit.jupiter.api.Assertions.assertThrows; +import static org.junit.jupiter.api.Assertions.assertTrue; + +import java.io.File; +import java.io.FileInputStream; +import java.io.IOException; +import java.util.Date; +import java.util.List; + +import org.apache.poi.ss.excelant.CalculateMortgageFunction; +import org.apache.poi.ss.excelant.TestBuildFile; +import org.apache.poi.ss.formula.udf.UDFFinder; +import org.apache.poi.ss.usermodel.DateUtil; +import org.apache.poi.ss.usermodel.FormulaEvaluator; +import org.apache.poi.ss.usermodel.Workbook; +import org.apache.poi.ss.usermodel.WorkbookFactory; +import org.apache.tools.ant.BuildException; +import org.junit.jupiter.api.AfterEach; +import org.junit.jupiter.api.Test; + +class TestExcelAntWorkbookUtil { + + private static final String mortgageCalculatorFileName = + TestBuildFile.getDataDir() + "/spreadsheet/excelant.xls" ; + + private ExcelAntWorkbookUtilTestHelper fixture ; + + + @AfterEach + void tearDown() { + fixture = null ; + } + + @Test + void testStringConstructor() { + fixture = new ExcelAntWorkbookUtilTestHelper(mortgageCalculatorFileName); + + assertNotNull(fixture); + } + + @Test + void testLoadNotExistingFile() { + BuildException e = assertThrows(BuildException.class, () -> new ExcelAntWorkbookUtilTestHelper("notexistingFile")); + assertTrue(e.getMessage().contains("notexistingFile")); + } + + @Test + void testWorkbookConstructor() throws IOException { + File workbookFile = new File(mortgageCalculatorFileName); + FileInputStream fis = new FileInputStream(workbookFile); + Workbook workbook = WorkbookFactory.create(fis); + + fixture = new ExcelAntWorkbookUtilTestHelper(workbook); + + assertNotNull(fixture); + } + + @Test + void testAddFunction() { + fixture = new ExcelAntWorkbookUtilTestHelper( + mortgageCalculatorFileName); + + assertNotNull(fixture); + + fixture.addFunction("h2_ZFactor", new CalculateMortgageFunction()); + + UDFFinder functions = fixture.getFunctions(); + + assertNotNull(functions); + assertNotNull(functions.findFunction("h2_ZFactor")); + } + + @Test + void testAddFunctionClassName() throws Exception { + fixture = new ExcelAntWorkbookUtilTestHelper( + mortgageCalculatorFileName); + + assertNotNull(fixture); + + fixture.addFunction("h2_ZFactor", CalculateMortgageFunction.class.getName()); + + UDFFinder functions = fixture.getFunctions(); + + assertNotNull(functions); + assertNotNull(functions.findFunction("h2_ZFactor")); + } + + @Test + void testAddFunctionInvalidClassName() throws Exception { + fixture = new ExcelAntWorkbookUtilTestHelper( + mortgageCalculatorFileName); + + assertNotNull(fixture); + + fixture.addFunction("h2_ZFactor", String.class.getName()); + + UDFFinder functions = fixture.getFunctions(); + + assertNotNull(functions); + assertNull(functions.findFunction("h2_ZFactor")); + } + + @Test + void testGetWorkbook() { + fixture = new ExcelAntWorkbookUtilTestHelper( + mortgageCalculatorFileName); + + assertNotNull(fixture); + + Workbook workbook = fixture.getWorkbook(); + + assertNotNull(workbook); + } + + @Test + void testFileName() { + fixture = new ExcelAntWorkbookUtilTestHelper( + mortgageCalculatorFileName); + + assertNotNull(fixture); + + String fileName = fixture.getFileName(); + + assertNotNull(fileName); + + assertEquals(mortgageCalculatorFileName, fileName); + + } + + @Test + void testGetEvaluator() { + fixture = new ExcelAntWorkbookUtilTestHelper( + mortgageCalculatorFileName); + + FormulaEvaluator evaluator = fixture.getEvaluator( + mortgageCalculatorFileName); + + assertNotNull(evaluator); + } + + @Test + void testGetEvaluatorWithUDF() { + fixture = new ExcelAntWorkbookUtilTestHelper( + mortgageCalculatorFileName); + + fixture.addFunction("h2_ZFactor", new CalculateMortgageFunction()); + + FormulaEvaluator evaluator = fixture.getEvaluator( + mortgageCalculatorFileName); + + assertNotNull(evaluator); + } + + @Test + void testGetEvaluatorXLSX() { + fixture = new ExcelAntWorkbookUtilTestHelper( + TestBuildFile.getDataDir() + "/spreadsheet/sample.xlsx"); + + FormulaEvaluator evaluator = fixture.getEvaluator( + TestBuildFile.getDataDir() + "/spreadsheet/sample.xlsx"); + + assertNotNull(evaluator); + } + + @Test + void testGetEvaluatorXLSXWithFunction() { + fixture = new ExcelAntWorkbookUtilTestHelper( + TestBuildFile.getDataDir() + "/spreadsheet/sample.xlsx"); + + fixture.addFunction("h2_ZFactor", new CalculateMortgageFunction()); + + FormulaEvaluator evaluator = fixture.getEvaluator( + TestBuildFile.getDataDir() + "/spreadsheet/sample.xlsx"); + + assertNotNull(evaluator); + } + + @Test + void testEvaluateCell() { + String cell = "'MortgageCalculator'!B4" ; + double expectedValue = 790.79 ; + double precision = 0.1 ; + + fixture = new ExcelAntWorkbookUtilTestHelper( + mortgageCalculatorFileName); + + ExcelAntEvaluationResult result = fixture.evaluateCell(cell, + expectedValue, + precision); + + //System.out.println(result); + assertTrue( result.toString().contains("evaluationCompletedWithError=false"), "Had:" + result ); + assertTrue( result.toString().contains("returnValue=790.79"), "Had:" + result ); + assertTrue( result.toString().contains("cellName='MortgageCalculator'!B4"), "Had:" + result ); + assertFalse(result.toString().contains("#N/A")); + + assertFalse(result.evaluationCompleteWithError()); + assertTrue(result.didTestPass()); + } + + @Test + void testEvaluateCellFailedPrecision() { + String cell = "'MortgageCalculator'!B4" ; + double expectedValue = 790.79 ; + double precision = 0.0000000000001 ; + + fixture = new ExcelAntWorkbookUtilTestHelper( + mortgageCalculatorFileName); + + ExcelAntEvaluationResult result = fixture.evaluateCell(cell, + expectedValue, + precision); + + //System.out.println(result); + assertTrue( result.toString().contains("evaluationCompletedWithError=false"), "Had:" + result ); + assertTrue( result.toString().contains("returnValue=790.79"), "Had:" + result ); + assertTrue( result.toString().contains("cellName='MortgageCalculator'!B4"), "Had:" + result ); + assertFalse( result.toString().contains("#"), "Should not see an error, but had:" + result ); + + assertFalse(result.evaluationCompleteWithError()); + assertFalse(result.didTestPass()); + } + + @Test + void testEvaluateCellWithError() { + String cell = "'ErrorCell'!A1" ; + double expectedValue = 790.79 ; + double precision = 0.1 ; + + fixture = new ExcelAntWorkbookUtilTestHelper( + mortgageCalculatorFileName); + + ExcelAntEvaluationResult result = fixture.evaluateCell(cell, + expectedValue, + precision); + + System.out.println(result); + assertTrue( result.toString().contains("evaluationCompletedWithError=true"), "Had:" + result ); + assertTrue( result.toString().contains("returnValue=0.0"), "Had:" + result ); + assertTrue( result.toString().contains("cellName='ErrorCell'!A1"), "Had:" + result ); + assertTrue( result.toString().contains("#N/A"), "Had:" + result ); + + assertTrue(result.evaluationCompleteWithError()); + assertFalse(result.didTestPass()); + } + + @Test + void testGetSheets() { + fixture = new ExcelAntWorkbookUtilTestHelper( + mortgageCalculatorFileName); + + List<String> sheets = fixture.getSheets(); + + assertNotNull(sheets); + assertEquals(sheets.size(), 3); + } + + @Test + void testSetString() { + String cell = "'MortgageCalculator'!C14" ; + String cellValue = "testString" ; + + fixture = new ExcelAntWorkbookUtilTestHelper( + mortgageCalculatorFileName); + + fixture.setStringValue(cell, cellValue); + + String value = fixture.getCellAsString(cell); + + assertNotNull(value); + assertEquals(cellValue, value); + } + + @Test + void testSetNotExistingSheet() { + String cell = "'NotexistingSheet'!C14" ; + + fixture = new ExcelAntWorkbookUtilTestHelper(mortgageCalculatorFileName); + BuildException e = assertThrows(BuildException.class, () -> fixture.setStringValue(cell, "some")); + assertTrue(e.getMessage().contains("NotexistingSheet")); + } + + @Test + void testSetFormula() { + String cell = "'MortgageCalculator'!C14" ; + String cellValue = "SUM(B14:B18)" ; + + fixture = new ExcelAntWorkbookUtilTestHelper( + mortgageCalculatorFileName); + + fixture.setFormulaValue(cell, cellValue); + + double value = fixture.getCellAsDouble(cell); + + assertEquals(0.0, value, 0); + } + + @Test + void testSetDoubleValue() { + String cell = "'MortgageCalculator'!C14" ; + double cellValue = 1.2; + + fixture = new ExcelAntWorkbookUtilTestHelper( + mortgageCalculatorFileName); + + fixture.setDoubleValue(cell, cellValue); + + double value = fixture.getCellAsDouble(cell); + + assertEquals(cellValue, value, 0); + } + + @Test + void testSetDate() { + String cell = "'MortgageCalculator'!C14" ; + Date cellValue = new Date(); + + fixture = new ExcelAntWorkbookUtilTestHelper( + mortgageCalculatorFileName); + + fixture.setDateValue(cell, cellValue); + + double value = fixture.getCellAsDouble(cell); + + assertEquals(DateUtil.getExcelDate(cellValue, false), value, 0); + } + + @Test + void testGetNonexistingString() { + String cell = "'MortgageCalculator'!C33" ; + + fixture = new ExcelAntWorkbookUtilTestHelper( + mortgageCalculatorFileName); + + String value = fixture.getCellAsString(cell); + + assertEquals("", value); + } + + @Test + void testGetNonexistingDouble() { + String cell = "'MortgageCalculator'!C33" ; + + fixture = new ExcelAntWorkbookUtilTestHelper( + mortgageCalculatorFileName); + + double value = fixture.getCellAsDouble(cell); + + assertEquals(0.0, value, 0); + } +} diff --git a/poi-excelant/src/test/java/org/apache/poi/ss/excelant/util/TestExcelAntWorkbookUtilFactory.java b/poi-excelant/src/test/java/org/apache/poi/ss/excelant/util/TestExcelAntWorkbookUtilFactory.java new file mode 100644 index 0000000000..26e700fa8b --- /dev/null +++ b/poi-excelant/src/test/java/org/apache/poi/ss/excelant/util/TestExcelAntWorkbookUtilFactory.java @@ -0,0 +1,68 @@ +/* ==================================================================== + 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.excelant.util; + +import static org.junit.jupiter.api.Assertions.assertEquals; +import static org.junit.jupiter.api.Assertions.assertNotNull; + +import org.apache.poi.ss.excelant.TestBuildFile; +import org.junit.jupiter.api.Test; + + +/** + * Tests for the ExcelAntWorbookUtilFactory. + */ +class TestExcelAntWorkbookUtilFactory { + + private static final String mortgageCalculatorWorkbookFile = + TestBuildFile.getDataDir() + "/spreadsheet/mortgage-calculation.xls" ; + + + /** + * Simple test to determine if the factory properly returns an non-null + * instance of the ExcelAntWorkbookUtil class. + */ + @Test + void testGetNewWorkbookUtilInstance() { + ExcelAntWorkbookUtil util = ExcelAntWorkbookUtilFactory.getInstance( + mortgageCalculatorWorkbookFile) ; + + assertNotNull(util) ; + } + + + /** + * Test whether or not the factory will properly return the same reference + * to an ExcelAnt WorkbookUtil when two different Strings, that point to + * the same resource, are passed in. + */ + @Test + void testVerifyEquivalence() { + String sameFileName = TestBuildFile.getDataDir() + "/spreadsheet/mortgage-calculation.xls" ; + + ExcelAntWorkbookUtil util = ExcelAntWorkbookUtilFactory.getInstance( + mortgageCalculatorWorkbookFile) ; + + ExcelAntWorkbookUtil util2 = ExcelAntWorkbookUtilFactory.getInstance( + sameFileName) ; + + assertNotNull(util) ; + assertNotNull(util2) ; + + assertEquals(util, util2) ; + } +} diff --git a/poi-excelant/src/test/java9/module-info.class b/poi-excelant/src/test/java9/module-info.class Binary files differnew file mode 100644 index 0000000000..c4cef92e79 --- /dev/null +++ b/poi-excelant/src/test/java9/module-info.class diff --git a/poi-excelant/src/test/java9/module-info.java b/poi-excelant/src/test/java9/module-info.java new file mode 100644 index 0000000000..a26d9bd04d --- /dev/null +++ b/poi-excelant/src/test/java9/module-info.java @@ -0,0 +1,34 @@ +/* ==================================================================== + 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. +==================================================================== */ + +module org.apache.poi.excelant { + + requires ant; + requires org.apache.poi.ooxml; + requires org.apache.poi.scratchpad; + + exports org.apache.poi.ss.excelant; + exports org.apache.poi.ss.excelant.util; + + opens org.apache.poi.ss.excelant; + + // test specific exports + requires org.junit.jupiter.api; + requires org.junit.jupiter.params; + + opens org.apache.poi.ss.excelant.util to org.junit.platform.commons; +}
\ No newline at end of file diff --git a/poi-excelant/src/test/resources/tests.xml b/poi-excelant/src/test/resources/tests.xml new file mode 100644 index 0000000000..0882195551 --- /dev/null +++ b/poi-excelant/src/test/resources/tests.xml @@ -0,0 +1,226 @@ +<?xml version="1.0" encoding="UTF-8" standalone="yes"?> +<!-- +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. +--> +<project name="excelant-tests" basedir="." xmlns:poi="antlib:org.apache.poi.ss.excelant"> + + <path id="excelant.classpath"> + <pathelement location="build/classes"/> + <pathelement location="build/ooxml-classes"/> + <pathelement location="build/excelant-classes"/> + <pathelement location="build/excelant-test-classes"/> <!-- test udf is in the test classes --> + </path> + + <typedef resource="org/apache/poi/ss/excelant/antlib.xml" + classpathref="excelant.classpath" + uri="antlib:org.apache.poi.ss.excelant"/> + + <!-- Should fail because the fileName attribute is missing --> + <target name="test-nofile"> + <poi:excelant> + + </poi:excelant> + </target> + + <!-- Should fail because the specified file is invalid --> + <target name="test-filenotfound"> + <poi:excelant fileName="invalid.xls"> + + </poi:excelant> + </target> + + <!-- basic evaluation test --> + <target name="test-evaluate"> + <poi:excelant fileName="${data.dir.name}/spreadsheet/excelant.xls"> + <poi:test showSuccessDetails="true"> + <poi:setDouble cell="'MortgageCalculator'!$B$1" value="240000"/> + <poi:setDouble cell="'MortgageCalculator'!$B$2" value ="0.11"/> + <poi:setDouble cell="'MortgageCalculator'!$B$3" value ="30"/> + <poi:evaluate showDelta="true" cell="'MortgageCalculator'!$B$4" + expectedValue="2285.576149" precision="1.0E-4" /> + </poi:test> + </poi:excelant> + </target> + + <target name="test-evaluate-nodetails"> + <poi:excelant fileName="${data.dir.name}/spreadsheet/excelant.xls"> + <poi:test showSuccessDetails="false"> + <poi:setDouble cell="'MortgageCalculator'!$B$1" value="240000"/> + <poi:setDouble cell="'MortgageCalculator'!$B$2" value ="0.11"/> + <poi:setDouble cell="'MortgageCalculator'!$B$3" value ="30"/> + <poi:evaluate showDelta="true" cell="'MortgageCalculator'!$B$4" + expectedValue="2285.576149" precision="1.0E-4" /> + </poi:test> + </poi:excelant> + </target> + + <target name="test-precision"> + <poi:excelant fileName="${data.dir.name}/spreadsheet/excelant.xls"> + <poi:precision value="1.0E-4"/> + + <poi:test name="global-precision" showFailureDetail="true" showSuccessDetails="true"> + <poi:setDouble cell="'MortgageCalculator'!$B$1" value="240000"/> + <poi:setDouble cell="'MortgageCalculator'!$B$2" value ="0.11"/> + <poi:setDouble cell="'MortgageCalculator'!$B$3" value ="30"/> + <poi:evaluate showDelta="true" cell="'MortgageCalculator'!$B$4" + expectedValue="2285.576149"/> + </poi:test> + + <poi:test name="custom-precision" showFailureDetail="true" showSuccessDetails="true"> + <poi:setDouble cell="'MortgageCalculator'!$B$1" value="240000"/> + <poi:setDouble cell="'MortgageCalculator'!$B$2" value ="0.11"/> + <poi:setDouble cell="'MortgageCalculator'!$B$3" value ="30"/> + <poi:evaluate showDelta="true" cell="'MortgageCalculator'!$B$4" + expectedValue="2285.576149" precision="1.0E-5"/> + </poi:test> + + <poi:test name="tiny-precision" showFailureDetail="true" showSuccessDetails="true"> + <poi:setDouble cell="'MortgageCalculator'!$B$1" value="240000"/> + <poi:setDouble cell="'MortgageCalculator'!$B$2" value ="0.11"/> + <poi:setDouble cell="'MortgageCalculator'!$B$3" value ="30"/> + <poi:evaluate showDelta="true" cell="'MortgageCalculator'!$B$4" + expectedValue="2285.576149" precision="1.0E-10"/> + </poi:test> + + </poi:excelant> + </target> + + <target name="test-precision-fails"> + <poi:excelant fileName="${data.dir.name}/spreadsheet/excelant.xls"> + <poi:precision value="1.0E-4"/> + + <poi:test name="tiny-precision" showFailureDetail="true" showSuccessDetails="true"> + <poi:setDouble cell="'MortgageCalculator'!$B$1" value="240000"/> + <poi:setDouble cell="'MortgageCalculator'!$B$2" value ="0.11"/> + <poi:setDouble cell="'MortgageCalculator'!$B$3" value ="30"/> + <poi:evaluate showDelta="true" cell="'MortgageCalculator'!$B$4" + expectedValue="2285.576149" precision="1.0E-10" requiredToPass="true"/> + </poi:test> + + </poi:excelant> + </target> + + <!-- + By default ExcelAnt does not terminate execution if an error occurs + --> + <target name="test-passonerror"> + <poi:excelant fileName="${data.dir.name}/spreadsheet/excelant.xls"> + + <poi:test name="failonerror" showFailureDetail="true" showSuccessDetails="true"> + <poi:setDouble cell="'MortageCalculatorFunction'!$D$1" value="1"/> + <poi:setDouble cell="'MortageCalculatorFunction'!$D$2" value="2"/> + <poi:setFormula cell="'MortageCalculatorFunction'!$D$3" value ="SUM(D1:D2)"/> + <poi:evaluate showDelta="true" cell="'MortageCalculatorFunction'!$D$3" expectedValue="2"/> + </poi:test> + + </poi:excelant> + </target> + + <!-- + failOnError="true" forces ExcelAnt tot terminate execution if an error occurs + --> + <target name="test-failonerror"> + <poi:excelant fileName="${data.dir.name}/spreadsheet/excelant.xls" failOnError="true"> + + <poi:test name="failonerror" showFailureDetail="true" showSuccessDetails="true"> + <poi:setDouble cell="'MortageCalculatorFunction'!$D$1" value="1"/> + <poi:setDouble cell="'MortageCalculatorFunction'!$D$2" value="2"/> + <poi:setFormula cell="'MortageCalculatorFunction'!$D$3" value ="SUM(D1:D2)"/> + <poi:evaluate showDelta="true" cell="'MortageCalculatorFunction'!$D$3" expectedValue="2"/> + </poi:test> + + </poi:excelant> + </target> + + <target name="test-failonerror-notdetails"> + <poi:excelant fileName="${data.dir.name}/spreadsheet/excelant.xls" failOnError="true"> + + <poi:test name="failonerror" showFailureDetail="false" showSuccessDetails="false"> + <poi:setDouble cell="'MortageCalculatorFunction'!$D$1" value="1"/> + <poi:setDouble cell="'MortageCalculatorFunction'!$D$2" value="2"/> + <poi:setFormula cell="'MortageCalculatorFunction'!$D$3" value ="SUM(D1:D2)"/> + <poi:evaluate showDelta="true" cell="'MortageCalculatorFunction'!$D$3" expectedValue="2"/> + </poi:test> + + </poi:excelant> + </target> + + <!-- Evaluation of user-defined functions --> + <target name="test-udf"> + <poi:excelant fileName="${data.dir.name}/spreadsheet/excelant.xls"> + <poi:udf functionAlias="calculatePayment" + className="org.apache.poi.ss.excelant.CalculateMortgageFunction"/> + <poi:test> + <poi:setDouble cell="'MortageCalculatorFunction'!$B$1" value="240000"/> + <poi:setDouble cell="'MortageCalculatorFunction'!$B$2" value ="0.11"/> + <poi:setDouble cell="'MortageCalculatorFunction'!$B$3" value ="30"/> + <poi:evaluate showDelta="true" cell="'MortageCalculatorFunction'!$B$4" + expectedValue="2285.576149" precision="1.0E-4" /> + </poi:test> + </poi:excelant> + </target> + + <!-- basic evaluation test --> + <target name="test-settext"> + <poi:excelant fileName="${data.dir.name}/spreadsheet/excelant.xls"> + <poi:test showSuccessDetails="true"> + <poi:setString cell="'MortgageCalculator'!$B$1" value="sometext"/> + <!-- How can we evaluate text? --> + </poi:test> + </poi:excelant> + </target> + + <target name="test-addhandler"> + <poi:excelant fileName="${data.dir.name}/spreadsheet/excelant.xls"> + <poi:test showSuccessDetails="true"> + <poi:handler className="org.apache.poi.ss.excelant.MockExcelAntWorkbookHandler"/> + <poi:setDouble cell="'MortgageCalculator'!$B$1" value="240000"/> + <poi:setDouble cell="'MortgageCalculator'!$B$2" value ="0.11"/> + <poi:setDouble cell="'MortgageCalculator'!$B$3" value ="30"/> + <poi:evaluate showDelta="true" cell="'MortgageCalculator'!$B$4" + expectedValue="2285.576149" precision="1.0E-4" /> + </poi:test> + </poi:excelant> + </target> + + <target name="test-addhandler-wrongclass"> + <poi:excelant fileName="${data.dir.name}/spreadsheet/excelant.xls"> + <poi:test showSuccessDetails="true"> + <poi:handler className="java.lang.String"/> + <poi:setDouble cell="'MortgageCalculator'!$B$1" value="240000"/> + <poi:setDouble cell="'MortgageCalculator'!$B$2" value ="0.11"/> + <poi:setDouble cell="'MortgageCalculator'!$B$3" value ="30"/> + <poi:evaluate showDelta="true" cell="'MortgageCalculator'!$B$4" + expectedValue="2285.576149" precision="1.0E-4" /> + </poi:test> + </poi:excelant> + </target> + + <target name="test-addhandler-fails"> + <poi:excelant fileName="${data.dir.name}/spreadsheet/excelant.xls"> + <poi:test showSuccessDetails="true"> + <poi:handler/> + <poi:setDouble cell="'MortgageCalculator'!$B$1" value="240000"/> + <poi:setDouble cell="'MortgageCalculator'!$B$2" value ="0.11"/> + <poi:setDouble cell="'MortgageCalculator'!$B$3" value ="30"/> + <poi:evaluate showDelta="true" cell="'MortgageCalculator'!$B$4" + expectedValue="2285.576149" precision="1.0E-4" /> + </poi:test> + </poi:excelant> + </target> +</project> |