From b735d6736abf744ff16f70ae22ecb84bd621f5e0 Mon Sep 17 00:00:00 2001 From: Yegor Kozlov Date: Mon, 26 Jul 2010 12:13:07 +0000 Subject: [PATCH] support for POISSON function, see bug #49538 git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@979255 13f79535-47bb-0310-9956-ffa450edef68 --- src/documentation/content/xdocs/status.xml | 289 +++++++++--------- .../record/formula/eval/FunctionEval.java | 2 + .../formula/functions/NumericFunction.java | 107 ++++++- .../record/formula/functions/TestPoisson.java | 95 ++++++ 4 files changed, 344 insertions(+), 149 deletions(-) create mode 100644 src/testcases/org/apache/poi/hssf/record/formula/functions/TestPoisson.java diff --git a/src/documentation/content/xdocs/status.xml b/src/documentation/content/xdocs/status.xml index 29cb7a2b60..d6fe9299e5 100644 --- a/src/documentation/content/xdocs/status.xml +++ b/src/documentation/content/xdocs/status.xml @@ -34,158 +34,159 @@ + 49538 - Added implementation for POISSON() 49524 - Support for setting cell text to be vertically rotated, via style.setRotation(0xff) 49609 - Case insensitive matching of OOXML part names 49581 - Ability to add, modify and remove series from HSSF Charts 49185 - Support for HSSFNames where the comment is stored in a NameCommentRecord - 49599 - Correct writing of NoteRecord author text when switching between ASCII and Unicode - HWPF: Improve reading of auto-saved ("complex") documents - Paragraph level as well as whole-file text extraction for Word 6/95 files through HWPF - Text Extraction support for older Word 6 and Word 95 files via HWPF - 49508 - Allow the addition of paragraphs to XWPF Table Cells - 49446 - Don't consider 17.16.23 field codes as properly part of the paragraph's text - XSLFSlideShow shouldn't break on .thmx (theme) files. Support for them is still very limited though + 49599 - correct writing of noterecord author text when switching between ascii and unicode + hwpf: improve reading of auto-saved ("complex") documents + paragraph level as well as whole-file text extraction for word 6/95 files through hwpf + text extraction support for older word 6 and word 95 files via hwpf + 49508 - allow the addition of paragraphs to xwpf table cells + 49446 - don't consider 17.16.23 field codes as properly part of the paragraph's text + xslfslideshow shouldn't break on .thmx (theme) files. support for them is still very limited though - 49432 - Lazy caching of XSSFComment CTComment objects by reference, to make repeated comment searching faster - Better handling of Outlook messages in HSMF when there's no recipient email address - When formatting numbers with DataFormatter, handle brackets following colours - 48574 - further XWPF support for tables, paragraphs, including enhanced support for adding new ones - 48245 - tweak HWPF table cell detection to work across more files - 48996 - initial support for External Name References in HSSF formula evaluation - 46664 - fix up Tab IDs when adding new sheets, so that print areas don't end up invalid - 45269 - improve replaceText on HWPF ranges - 47815 - correct documentation on what happens when you request a String from a non-string Formula cell - 49386 - avoid NPE when extracting OOXML file properties which are dates - 49377 - only call DecimalFormat.setRoundingMode on Java 1.6 - it's needed to match excel's rendering of numbers - 49378 - correct 1.6ism - Parse the HSMF headers chunk if present, and use it to find Dates in text extraction if needed - 48494 - detect and support time formats like HH:MM;HH:MM - 48494 - have ExcelExtractor make use of HSSFDataFormatter, so that numbers and dates come out closer to how Excel would render them - 48494 - have EventBasedExcelExtractor make use of HSSFDataFormatter, so that numbers and dates come out closer to how Excel would render them - 49096 - add clone support to Chart begin and end records, to allow cloning of more Chart containing sheets - List attachment names in the output of OutlookTextExtractor (to get attachment contents, use ExtractorFactory as normal) - 48872 - allow DateFormatter.formatRawCellContents to handle 1904 as well as 1900 dates - 48872 - handle MMMMM and elapsed time formatting rules in DataFormatter - 48872 - handle zero formatting rules, and better color detection in DataFormatter - 48872 - support for more kinds of formatting in DataFormatter - 43161 - fixed construction of the DIB picture header - 49311 - initial support for reading AES-encrypted/write-protected OOXML files - 48718 - Make the creation of multiple, un-modified fonts in a row in XSSF match the old HSSF behaviour - 44916 - Allow access to the HSSFPatriarch from HSSFSheet once created - 48779 - Allow you to get straight from a CellStyle to a Color, irrespective of if the Color is indexed or inline-defined - 48924 - Allow access of the HWPF DateAndTime underlying date values - 48926 - Initial support for the HWPF revision marks authors list - 49160 - Ensure that CTDigSigBlob is included in poi-ooxml jar - 49189 - Detect w:tab and w:cr entries in XWPF paragraphs, even when the XSD is silly and maps them to CTEmpty - 49273 - Correct handling for Font Character Sets with indicies greater than 127 - 49334 - Track the ValueRangeRecords of charts in HSSFChart, to allow the basic axis operations - 49242 - Track the LinkDataRecords of charts in HSSFChart - Improved performance of XSSFWorkbook.write - 48846 - Avoid NPE when finding cell comments - 49325 - Ensure that CTPhoneticPr is included in poi-ooxml jar - 49191 - Fixed tests failing in non-english locales - 48432 - Support for XSSF themes - 49244 - Support for data validation for OOXML format - 49066 - Worksheet/cell formatting, with view and HTML converter - 49020 - Workaround Excel outputting invalid XML in button definitions by not closing BR tags - 49050 - Improve performance of AbstractEscherHolderRecord when there are lots of Continue Records - 49194 - Correct text size limit for OOXML .xlsx files - 49254 - Fix CellUtils.setFont to use the correct type internally - 49139 - Properly support 4k big block size in POIFS - 48936 - Avoid writing malformed CDATA blocks in sharedStrings.xml - 49026 - Added implementation for TEXT() - 49025 - Added implementation for TRUNC() - 49147 - Properly close internal InputStream in ExtractorFactory#createExtractor(File) - 49138 - Fixed locale-sensitive formatters in PackagePropertiesPart - 49153 - Ensure that CTVectorVariant is included in poi-ooxml-schemas.jar - 49146 - Added accessors to CoreProperties.Keywords - 48916 - Propagate parent to parent-aware records decoded from Escher - 48485 - Add extra paper size constans to PrintSetup, such as A3, B4 and B5 - Make poifs.filesystem.DirectoryNode preserve the original ordering of its files, which HSMF needs to be able to correctly match up chunks - Support evaluation of indirect defined names in INDIRECT - 43670 - Improve HDGF ChunkV11 separator detection, and short string detection, to solve the "Negative length of ChunkHeader" problem - 48617 - Optionally allow the overriding of the Locale used by DataFormatter to control how the default number and date formats should look - New event based xssf text extractor (XSSFEventBasedExcelExtractor) - ExtractorFactory can now be told to prefer Event Based extractors (current Excel only) on a per-thread or overall basis - 48544 - avoid failures in XLSX2CSV when shared string table is missing - 48571 - properly close all IO streams created in OPCPackage - 48572 - always copy all declared inner classes and interfaces when generating poi-ooxml-schemas - Low Level record support for the ExtRst (phonetic text) part of Unicode Strings. No usermodel access to it as yet though. - record.UnicodeString has moved to record.common.UnicodeString, to live with the other record-part classes, as it isn't a full record. - Avoid creating temporary files when opening OPC packages from input stream - Improved how HSMF handles multiple recipients - Add PublisherTextExtractor support to ExtractorFactory - Add XSLF support for text extraction from tables - Support attachments as embeded documents within the new OutlookTextExtractor - Add a text extractor (OutlookTextExtractor) to HSMF for simpler extraction of text from .msg files - Some improvements to HSMF parsing of .msg files - Initialise the link type of HSSFHyperLink, so that getType() on it works - 48425 - improved performance of DateUtil.isCellDateFormatted() - 47215 - fixed InterfaceEndRecord to tolerate unexpected record contents - 48415 - improved javadoc on HSSPicture.resize() - added Ant target to install artifacts in local repository - 48026 - fixed PageSettingsBlock to allow multiple HeaderFooterRecord records - 48202 - fixed CellRangeUtil.mergeCellRanges to work for adjacent cell regions - 48339 - fixed ExternalNameRecord to properly distinguish DDE data from OLE data items - 47920 - allow editing workbooks embedded into PowerPoint files - 48343 - added implementation of SUBTOTAL function - Switch to compiling the OOXML Schemas for Java 1.5 + 49432 - lazy caching of xssfcomment ctcomment objects by reference, to make repeated comment searching faster + better handling of outlook messages in hsmf when there's no recipient email address + when formatting numbers with dataformatter, handle brackets following colours + 48574 - further xwpf support for tables, paragraphs, including enhanced support for adding new ones + 48245 - tweak hwpf table cell detection to work across more files + 48996 - initial support for external name references in hssf formula evaluation + 46664 - fix up tab ids when adding new sheets, so that print areas don't end up invalid + 45269 - improve replacetext on hwpf ranges + 47815 - correct documentation on what happens when you request a string from a non-string formula cell + 49386 - avoid npe when extracting ooxml file properties which are dates + 49377 - only call decimalformat.setroundingmode on java 1.6 - it's needed to match excel's rendering of numbers + 49378 - correct 1.6ism + parse the hsmf headers chunk if present, and use it to find dates in text extraction if needed + 48494 - detect and support time formats like hh:mm;hh:mm + 48494 - have excelextractor make use of hssfdataformatter, so that numbers and dates come out closer to how excel would render them + 48494 - have eventbasedexcelextractor make use of hssfdataformatter, so that numbers and dates come out closer to how excel would render them + 49096 - add clone support to chart begin and end records, to allow cloning of more chart containing sheets + list attachment names in the output of outlooktextextractor (to get attachment contents, use extractorfactory as normal) + 48872 - allow dateformatter.formatrawcellcontents to handle 1904 as well as 1900 dates + 48872 - handle mmmmm and elapsed time formatting rules in dataformatter + 48872 - handle zero formatting rules, and better color detection in dataformatter + 48872 - support for more kinds of formatting in dataformatter + 43161 - fixed construction of the dib picture header + 49311 - initial support for reading aes-encrypted/write-protected ooxml files + 48718 - make the creation of multiple, un-modified fonts in a row in xssf match the old hssf behaviour + 44916 - allow access to the hssfpatriarch from hssfsheet once created + 48779 - allow you to get straight from a cellstyle to a color, irrespective of if the color is indexed or inline-defined + 48924 - allow access of the hwpf dateandtime underlying date values + 48926 - initial support for the hwpf revision marks authors list + 49160 - ensure that ctdigsigblob is included in poi-ooxml jar + 49189 - detect w:tab and w:cr entries in xwpf paragraphs, even when the xsd is silly and maps them to ctempty + 49273 - correct handling for font character sets with indicies greater than 127 + 49334 - track the valuerangerecords of charts in hssfchart, to allow the basic axis operations + 49242 - track the linkdatarecords of charts in hssfchart + improved performance of xssfworkbook.write + 48846 - avoid npe when finding cell comments + 49325 - ensure that ctphoneticpr is included in poi-ooxml jar + 49191 - fixed tests failing in non-english locales + 48432 - support for xssf themes + 49244 - support for data validation for ooxml format + 49066 - worksheet/cell formatting, with view and html converter + 49020 - workaround excel outputting invalid xml in button definitions by not closing br tags + 49050 - improve performance of abstractescherholderrecord when there are lots of continue records + 49194 - correct text size limit for ooxml .xlsx files + 49254 - fix cellutils.setfont to use the correct type internally + 49139 - properly support 4k big block size in poifs + 48936 - avoid writing malformed cdata blocks in sharedstrings.xml + 49026 - added implementation for text() + 49025 - added implementation for trunc() + 49147 - properly close internal inputstream in extractorfactory#createextractor(file) + 49138 - fixed locale-sensitive formatters in packagepropertiespart + 49153 - ensure that ctvectorvariant is included in poi-ooxml-schemas.jar + 49146 - added accessors to coreproperties.keywords + 48916 - propagate parent to parent-aware records decoded from escher + 48485 - add extra paper size constans to printsetup, such as a3, b4 and b5 + make poifs.filesystem.directorynode preserve the original ordering of its files, which hsmf needs to be able to correctly match up chunks + support evaluation of indirect defined names in indirect + 43670 - improve hdgf chunkv11 separator detection, and short string detection, to solve the "negative length of chunkheader" problem + 48617 - optionally allow the overriding of the locale used by dataformatter to control how the default number and date formats should look + new event based xssf text extractor (xssfeventbasedexcelextractor) + extractorfactory can now be told to prefer event based extractors (current excel only) on a per-thread or overall basis + 48544 - avoid failures in xlsx2csv when shared string table is missing + 48571 - properly close all io streams created in opcpackage + 48572 - always copy all declared inner classes and interfaces when generating poi-ooxml-schemas + low level record support for the extrst (phonetic text) part of unicode strings. no usermodel access to it as yet though. + record.unicodestring has moved to record.common.unicodestring, to live with the other record-part classes, as it isn't a full record. + avoid creating temporary files when opening opc packages from input stream + improved how hsmf handles multiple recipients + add publishertextextractor support to extractorfactory + add xslf support for text extraction from tables + support attachments as embeded documents within the new outlooktextextractor + add a text extractor (outlooktextextractor) to hsmf for simpler extraction of text from .msg files + some improvements to hsmf parsing of .msg files + initialise the link type of hssfhyperlink, so that gettype() on it works + 48425 - improved performance of dateutil.iscelldateformatted() + 47215 - fixed interfaceendrecord to tolerate unexpected record contents + 48415 - improved javadoc on hsspicture.resize() + added ant target to install artifacts in local repository + 48026 - fixed pagesettingsblock to allow multiple headerfooterrecord records + 48202 - fixed cellrangeutil.mergecellranges to work for adjacent cell regions + 48339 - fixed externalnamerecord to properly distinguish dde data from ole data items + 47920 - allow editing workbooks embedded into powerpoint files + 48343 - added implementation of subtotal function + switch to compiling the ooxml schemas for java 1.5 - 48332 - fixed XSSFSheet autoSizeColumn() to tolerate empty RichTextString - 48332 - fixed ColumnInfoRecord to tolerate missing reserved field - 47701 - fixed RecordFormatException when reading list subrecords (LbsDataSubRecord) - memory usage optimization in XSSF - avoid creating parentless xml beans - 47188 - avoid corruption of workbook when adding cell comments - 48106 - improved work with cell comments in XSSF - Add support for creating SummaryInformation and DocumentSummaryInformation properties - on POIDocuments that don't have them, via POIDocument.createInformationProperties() - 48180 - be more forgiving of short chart records, which skip some unused fields - 48274 - fix erronious wrapping of byte colours in HSSFPalette.findSimilarColor - 48269 - fix fetching of error codes from XSSF formula cells - 48229 - fixed javadoc for HSSFSheet.setColumnWidth and XSSFSheet setColumnWidth - 47757 - fixed XLSX2CSV to avoid exception when processing cells with multiple "t" elements - 48195 - short-circuit evaluation of IF() and CHOOSE() - 48161 - support for text extraction from PPT master slides - 47970 - added a method to set arabic mode in HSSFSheet - 48134 - release system resources when using Picture.resize() - 48087 - avoid NPE in XSSFChartSheet when calling methods of the superclass - 48038 - handle reading HWPF stylesheets from non zero offsets - When running the "compile-ooxml-xsds" ant task, also generate the source jar for the OOXML Schemas - 45672 - improve handling by MissingRecordAwareHSSFListener of records that cover multiple cells (MulBlankRecord and MulRKRecord) - 48096 - relaxed validation check in RecalcIdRecord - 48085 - improved error checking in BlockAllocationTableReader to trap unreasonable field values - 47924 - fixed logic for matching cells and comments in HSSFCell.getCellComment() - 47942 - added implementation of protection features to XLSX and DOCX files - 48070 - preserve leading and trailing white spaces in XSSFRichTextString - 48044 - added implementation for CountBlank function - 48036 - added IntersectionEval to allow evaluation of the intersection formula operator - 47999 - avoid un-needed call to the JVM Garbage Collector when working on OOXML OPC Packages - 47922 - added example HSMF application that converts a .msg file to text and extracts attachments - 47903 - added Ant target to compile scratchpad examples - 47839 - improved API for OOXML custom properties - 47862 - fixed XSSFSheet.setColumnWidth to handle columns included in a column span - 47804 - fixed XSSFSheet.setColumnHidden to handle columns included in a column span - 47889 - fixed XSSFCell.getStringCellValue() to properly handle cached formula results - - - 47747 - fixed logic for locating shared formula records - 47809 - Improved work with user-defined functions - 47581 - fixed XSSFSheet.setColumnWidth to produce XML compatible with Mac Excel 2008 - 47734 - removed unnecessary svn:executable flag from files in SVN trunk - 47543 - added javadoc how to avoid Excel crash when creating too many HSSFRichTextString cells - 47813 - fixed problems with XSSFWorkbook.removeSheetAt when workbook contains chart - 47737 - adjust sheet indices of named ranges when deleting sheets - 47770 - built-in positive formats don't need starting '(' - 47771 - Added method setFunction(boolean) for defined names - 47768 - Implementation of Excel "Days360" and "Npv" functions - 47751 - Do not allow HSSF's cell text longer than 32,767 characters - 47757 - Added an example demonstrating how to convert an XLSX workbook to CSV - 44770 - Fixed PPT parser to tolerate Comment2000 containers with missing comment text - 47773 - Fix for extraction paragraphs and sections from headers/footers with XWPFWordExtractor - 47727 - Support for extraction of header / footer images in HWPF - moved all test data to a top-level directory + 48332 - fixed xssfsheet autosizecolumn() to tolerate empty richtextstring + 48332 - fixed columninforecord to tolerate missing reserved field + 47701 - fixed recordformatexception when reading list subrecords (lbsdatasubrecord) + memory usage optimization in xssf - avoid creating parentless xml beans + 47188 - avoid corruption of workbook when adding cell comments + 48106 - improved work with cell comments in xssf + add support for creating summaryinformation and documentsummaryinformation properties + on poidocuments that don't have them, via poidocument.createinformationproperties() + 48180 - be more forgiving of short chart records, which skip some unused fields + 48274 - fix erronious wrapping of byte colours in hssfpalette.findsimilarcolor + 48269 - fix fetching of error codes from xssf formula cells + 48229 - fixed javadoc for hssfsheet.setcolumnwidth and xssfsheet setcolumnwidth + 47757 - fixed xlsx2csv to avoid exception when processing cells with multiple "t" elements + 48195 - short-circuit evaluation of if() and choose() + 48161 - support for text extraction from ppt master slides + 47970 - added a method to set arabic mode in hssfsheet + 48134 - release system resources when using picture.resize() + 48087 - avoid npe in xssfchartsheet when calling methods of the superclass + 48038 - handle reading hwpf stylesheets from non zero offsets + when running the "compile-ooxml-xsds" ant task, also generate the source jar for the ooxml schemas + 45672 - improve handling by missingrecordawarehssflistener of records that cover multiple cells (mulblankrecord and mulrkrecord) + 48096 - relaxed validation check in recalcidrecord + 48085 - improved error checking in blockallocationtablereader to trap unreasonable field values + 47924 - fixed logic for matching cells and comments in hssfcell.getcellcomment() + 47942 - added implementation of protection features to xlsx and docx files + 48070 - preserve leading and trailing white spaces in xssfrichtextstring + 48044 - added implementation for countblank function + 48036 - added intersectioneval to allow evaluation of the intersection formula operator + 47999 - avoid un-needed call to the jvm garbage collector when working on ooxml opc packages + 47922 - added example hsmf application that converts a .msg file to text and extracts attachments + 47903 - added ant target to compile scratchpad examples + 47839 - improved api for ooxml custom properties + 47862 - fixed xssfsheet.setcolumnwidth to handle columns included in a column span + 47804 - fixed xssfsheet.setcolumnhidden to handle columns included in a column span + 47889 - fixed xssfcell.getstringcellvalue() to properly handle cached formula results + + + 47747 - fixed logic for locating shared formula records + 47809 - improved work with user-defined functions + 47581 - fixed xssfsheet.setcolumnwidth to produce xml compatible with mac excel 2008 + 47734 - removed unnecessary svn:executable flag from files in svn trunk + 47543 - added javadoc how to avoid excel crash when creating too many hssfrichtextstring cells + 47813 - fixed problems with xssfworkbook.removesheetat when workbook contains chart + 47737 - adjust sheet indices of named ranges when deleting sheets + 47770 - built-in positive formats don't need starting '(' + 47771 - added method setfunction(boolean) for defined names + 47768 - implementation of excel "days360" and "npv" functions + 47751 - do not allow hssf's cell text longer than 32,767 characters + 47757 - added an example demonstrating how to convert an xlsx workbook to csv + 44770 - fixed ppt parser to tolerate comment2000 containers with missing comment text + 47773 - fix for extraction paragraphs and sections from headers/footers with xwpfwordextractor + 47727 - support for extraction of header / footer images in hwpf + moved all test data to a top-level directory 47721 - Added implementation for INDIRECT() 45583 - Avoid exception when reading ClipboardData packet in OLE property sets 47652 - Added support for reading encrypted workbooks diff --git a/src/java/org/apache/poi/hssf/record/formula/eval/FunctionEval.java b/src/java/org/apache/poi/hssf/record/formula/eval/FunctionEval.java index a50ac59973..d7be348650 100644 --- a/src/java/org/apache/poi/hssf/record/formula/eval/FunctionEval.java +++ b/src/java/org/apache/poi/hssf/record/formula/eval/FunctionEval.java @@ -187,6 +187,8 @@ public final class FunctionEval { retval[298] = new Odd(); + retval[300] = NumericFunction.POISSON; + retval[303] = new Sumxmy2(); retval[304] = new Sumx2my2(); retval[305] = new Sumx2py2(); diff --git a/src/java/org/apache/poi/hssf/record/formula/functions/NumericFunction.java b/src/java/org/apache/poi/hssf/record/formula/functions/NumericFunction.java index bf69e6181f..8315159be2 100644 --- a/src/java/org/apache/poi/hssf/record/formula/functions/NumericFunction.java +++ b/src/java/org/apache/poi/hssf/record/formula/functions/NumericFunction.java @@ -17,11 +17,7 @@ package org.apache.poi.hssf.record.formula.functions; -import org.apache.poi.hssf.record.formula.eval.ErrorEval; -import org.apache.poi.hssf.record.formula.eval.EvaluationException; -import org.apache.poi.hssf.record.formula.eval.NumberEval; -import org.apache.poi.hssf.record.formula.eval.OperandResolver; -import org.apache.poi.hssf.record.formula.eval.ValueEval; +import org.apache.poi.hssf.record.formula.eval.*; /** * @author Amol S. Deshmukh < amolweb at ya hoo dot com > @@ -395,4 +391,105 @@ public abstract class NumericFunction implements Function { return new NumberEval(Math.random()); } }; + public static final Function POISSON = new Fixed3ArgFunction() { + + private final static double DEFAULT_RETURN_RESULT =1; + + /** + * This checks is x = 0 and the mean = 0. + * Excel currently returns the value 1 where as the + * maths common implementation will error. + * @param x The number. + * @param mean The mean. + * @return If a default value should be returned. + */ + private boolean isDefaultResult(double x, double mean) { + + if ( x == 0 && mean == 0 ) { + return true; + } + return false; + } + + private boolean checkArgument(double aDouble) throws EvaluationException { + + NumericFunction.checkValue(aDouble); + + // make sure that the number is positive + if (aDouble < 0) { + throw new EvaluationException(ErrorEval.NUM_ERROR); + } + + return true; + } + + private double probability(int k, double lambda) { + return Math.pow(lambda, k) * Math.exp(-lambda) / factorial(k); + } + + private double cumulativeProbability(int x, double lambda) { + double result = 0; + for(int k = 0; k <= x; k++){ + result += probability(k, lambda); + } + return result; + } + + /** All long-representable factorials */ + private final long[] FACTORIALS = new long[] { + 1l, 1l, 2l, + 6l, 24l, 120l, + 720l, 5040l, 40320l, + 362880l, 3628800l, 39916800l, + 479001600l, 6227020800l, 87178291200l, + 1307674368000l, 20922789888000l, 355687428096000l, + 6402373705728000l, 121645100408832000l, 2432902008176640000l }; + + + public long factorial(final int n) { + if (n < 0 || n > 20) { + throw new IllegalArgumentException("Valid argument should be in the range [0..20]"); + } + return FACTORIALS[n]; + } + + public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1, ValueEval arg2) { + + // arguments/result for this function + double mean=0; + double x=0; + boolean cumulative = ((BoolEval)arg2).getBooleanValue(); + double result=0; + + try { + x = NumericFunction.singleOperandEvaluate(arg0, srcRowIndex, srcColumnIndex); + mean = NumericFunction.singleOperandEvaluate(arg1, srcRowIndex, srcColumnIndex); + + // check for default result : excel implementation for 0,0 + // is different to Math Common. + if (isDefaultResult(x,mean)) { + return new NumberEval(DEFAULT_RETURN_RESULT); + } + // check the arguments : as per excel function def + checkArgument(x); + checkArgument(mean); + + // truncate x : as per excel function def + if ( cumulative ) { + result = cumulativeProbability((int)x, mean); + } else { + result = probability((int)x, mean); + } + + // check the result + NumericFunction.checkValue(result); + + } catch (EvaluationException e) { + return e.getErrorEval(); + } + + return new NumberEval(result); + + } + }; } diff --git a/src/testcases/org/apache/poi/hssf/record/formula/functions/TestPoisson.java b/src/testcases/org/apache/poi/hssf/record/formula/functions/TestPoisson.java new file mode 100644 index 0000000000..e086b0e22a --- /dev/null +++ b/src/testcases/org/apache/poi/hssf/record/formula/functions/TestPoisson.java @@ -0,0 +1,95 @@ +package org.apache.poi.hssf.record.formula.functions; + +import junit.framework.TestCase; + +import org.apache.poi.hssf.record.formula.eval.BoolEval; +import org.apache.poi.hssf.record.formula.eval.ErrorEval; +import org.apache.poi.hssf.record.formula.eval.NumberEval; +import org.apache.poi.hssf.record.formula.eval.ValueEval; + + +/** + * Tests for Excel function POISSON(x,mean,cumulative) + * @author Kalpesh Parmar + */ +public class TestPoisson extends TestCase { + + private static final double DELTA = 1E-15; + + private static ValueEval invokePoisson(double x, double mean, boolean cumulative) + { + + ValueEval[] valueEvals = new ValueEval[3]; + valueEvals[0] = new NumberEval(x); + valueEvals[1] = new NumberEval(mean); + valueEvals[2] = BoolEval.valueOf(cumulative); + + return NumericFunction.POISSON.evaluate(valueEvals,-1,-1); + } + + public void testCumulativeProbability() + { + double x = 1; + double mean = 0.2; + double result = 0.9824769036935787; // known result + + NumberEval myResult = (NumberEval)invokePoisson(x,mean,true); + + assertEquals(myResult.getNumberValue(), result, DELTA); + } + + public void testNonCumulativeProbability() + { + double x = 0; + double mean = 0.2; + double result = 0.8187307530779818; // known result + + NumberEval myResult = (NumberEval)invokePoisson(x,mean,false); + + assertEquals(myResult.getNumberValue(), result, DELTA); + } + + public void testNegativeMean() + { + double x = 0; + double mean = -0.2; + + ErrorEval myResult = (ErrorEval)invokePoisson(x,mean,false); + + assertEquals(ErrorEval.NUM_ERROR.getErrorCode(), myResult.getErrorCode()); + } + + public void testNegativeX() + { + double x = -1; + double mean = 0.2; + + ErrorEval myResult = (ErrorEval)invokePoisson(x,mean,false); + + assertEquals(ErrorEval.NUM_ERROR.getErrorCode(), myResult.getErrorCode()); + } + + + + public void testXAsDecimalNumber() + { + double x = 1.1; + double mean = 0.2; + double result = 0.9824769036935787; // known result + + NumberEval myResult = (NumberEval)invokePoisson(x,mean,true); + + assertEquals(myResult.getNumberValue(), result, DELTA); + } + + public void testXZeroMeanZero() + { + double x = 0; + double mean = 0; + double result = 1; // known result in excel + + NumberEval myResult = (NumberEval)invokePoisson(x,mean,true); + + assertEquals(myResult.getNumberValue(), result, DELTA); + } +} -- 2.39.5