Support
Forums
Contact Me
Download
Demo
Apache POI list of Excel supported functions
- Details
- Category: Framework
- Published on Monday, 15 April 2013 09:33
- Written by Administrator
- Hits: 383
The Apache POI Project's mission is to create and maintain Java APIs for manipulating various file formats based upon the Office Open XML standards (OOXML) and Microsoft's OLE 2 Compound Document format (OLE2). In short, you can read and write MS Excel files using Java. In addition, you can read and write MS Word and MS PowerPoint files using Java. Apache POI is your Java Excel solution (for Excel 97-2008). We have a complete API for porting other OOXML and OLE2 formats and welcome others to participate.
From Apache POI 4.1Beta1
List of 148 functions that Apache POI can evaluate
[ABS, ACOS, ACOSH, ADDRESS, AND, ASIN, ASINH, ATAN, ATAN2, ATANH, AVEDEV, AVERAGE, CEILING, CHAR, CHOOSE, CLEAN, COLUMN, COLUMNS, COMBIN, CONCATENATE, COS, COSH, COUNT, COUNTA, COUNTBLANK, COUNTIF, DATE, DAY, DAYS360, DEGREES, DEVSQ, DOLLAR, EDATE, ERROR.TYPE, EVEN, EXACT, EXP, FACT, FALSE, FIND, FLOOR, FV, HLOOKUP, HOUR, HYPERLINK, IF, IFERROR, INDEX, INDIRECT, INT, INTERCEPT, IPMT, IRR, ISBLANK, ISERROR, ISEVEN, ISLOGICAL, ISNA, ISNONTEXT, ISNUMBER, ISODD, ISREF, ISTEXT, LARGE, LEFT, LEN, LN, LOG, LOG10, LOOKUP, LOWER, MATCH, MAX, MAXA, MEDIAN, MID, MIN, MINA, MINUTE, MOD, MODE, MONTH, MROUND, NA, NETWORKDAYS, NOT, NOW, NPER, NPV, ODD, OFFSET, OR, PI, PMT, POISSON, POWER, PPMT, PRODUCT, PV, RADIANS, RAND, RANDBETWEEN, RANK, RATE, REPLACE, RIGHT, ROUND, ROUNDDOWN, ROUNDUP, ROW, ROWS, SEARCH, SECOND, SIGN, SIN, SINH, SLOPE, SMALL, SQRT, STDEV, SUBSTITUTE, SUBTOTAL, SUM, SUMIF, SUMIFS, SUMPRODUCT, SUMSQ, SUMX2MY2, SUMX2PY2, SUMXMY2, T, TAN, TANH, TEXT, TIME, TODAY, TRIM, TRUE, TRUNC, UPPER, VALUE, VAR, VARP, VLOOKUP, WEEKDAY, WORKDAY, YEAR, YEARFRAC]
Can be obtained by running
Collection<String> supportedFuncs = WorkbookEvaluator.getSupportedFunctionNames(); System.out.println(supportedFuncs);
List of 207 functions that are not supported by Apache POI
[ACCRINT, ACCRINTM, AMORDEGRC, AMORLINC, AREAS, ASC, AVERAGEA, AVERAGEIF, AVERAGEIFS, BAHTTEXT, BESSELI, BESSELJ, BESSELK, BESSELY, BETADIST, BETAINV, BIN2DEC, BIN2HEX, BIN2OCT, BINOMDIST, CELL, CHIDIST, CHIINV, CHITEST, CODE, COMPLEX, CONFIDENCE, CONVERT, CORREL, COUNTIFS, COUPDAYBS, COUPDAYS, COUPDAYSNC, COUPNCD, COUPNUM, COUPPCD, COVAR, CRITBINOM, CUBEKPIMEMBER, CUBEMEMBER, CUBEMEMBERPROPERTY, CUBERANKEDMEMBER, CUBESET, CUBESETCOUNT, CUBEVALUE, CUMIPMT, CUMPRINC, DATEDIF, DATESTRING, DATEVALUE, DAVERAGE, DB, DBCS, DCOUNT, DCOUNTA, DDB, DEC2BIN, DEC2HEX, DEC2OCT, DELTA, DGET, DISC, DMAX, DMIN, DOLLARDE, DOLLARFR, DPRODUCT, DSTDEV, DSTDEVP, DSUM, DURATION, DVAR, DVARP, EFFECT, EOMONTH, ERF, ERFC, EXPONDIST, FACTDOUBLE, FDIST, FINDB, FINV, FISHER, FISHERINV, FIXED, FORECAST, FREQUENCY, FTEST, FVSCHEDULE, GAMMADIST, GAMMAINV, GAMMALN, GCD, GEOMEAN, GESTEP, GETPIVOTDATA, GROWTH, HARMEAN, HEX2BIN, HEX2DEC, HEX2OCT, HYPGEOMDIST, IMABS, IMAGINARY, IMARGUMENT, IMCONJUGATE, IMCOS, IMDIV, IMEXP, IMLN, IMLOG10, IMLOG2, IMPOWER, IMPRODUCT, IMREAL, IMSIN, IMSQRT, IMSUB, IMSUM, INFO, INTRATE, ISERR, ISPMT, JIS, KURT, LCM, LEFTB, LENB, LINEST, LOGEST, LOGINV, LOGNORMDIST, MDETERM, MDURATION, MIDB, MINVERSE, MIRR, MMULT, MULTINOMIAL, N, NEGBINOMDIST, NOMINAL, NORMDIST, NORMINV, NORMSDIST, NORMSINV, NUMBERSTRING, OCT2BIN, OCT2DEC, OCT2HEX, ODDFPRICE, ODDFYIELD, ODDLPRICE, ODDLYIELD, PEARSON, PERCENTILE, PERCENTRANK, PERMUT, PHONETIC, PRICE, PRICEDISC, PRICEMAT, PROB, PROPER, QUARTILE, QUOTIENT, RECEIVED, REPLACEB, REPT, RIGHTB, ROMAN, RSQ, RTD, SEARCHB, SERIESSUM, SKEW, SLN, SQRTPI, STANDARDIZE, STDEVA, STDEVP, STDEVPA, STEYX, SYD, TBILLEQ, TBILLPRICE, TBILLYIELD, TDIST, TIMEVALUE, TINV, TRANSPOSE, TREND, TRIMMEAN, TTEST, TYPE, USDOLLAR, VARA, VARPA, VDB, WEEKNUM, WEIBULL, XIRR, XNPV, YIELD, YIELDDISC, YIELDMAT, ZTEST]
Can be obtained by running
Collection<String> unsupportedFuncs = WorkbookEvaluator.getNotSupportedFunctionNames(); System.err.println(unsupportedFuncs);
Apache POI Speed Optimizations
- Details
- Category: Framework
- Published on Monday, 29 October 2012 23:39
- Written by Administrator
- Hits: 1359
The Apache POI Project's mission is to create and maintain Java APIs for manipulating various file formats based upon the Office Open XML standards (OOXML) and Microsoft's OLE 2 Compound Document format (OLE2). In short, you can read and write MS Excel files using Java. In addition, you can read and write MS Word and MS PowerPoint files using Java. Apache POI is your Java Excel solution (for Excel 97-2008). We have a complete API for porting other OOXML and OLE2 formats and welcome others to participate.
Switch Off logging
From the documentation at http://poi.apache.org/utils/logging.html
Logging in POI is used only as a debugging mechanism, not a normal runtime logging system. Logging is ONLY for autopsie type debugging, and should NEVER be enabled on a production system. Enabling logging will reduce performance by at least a factor of 100. If you are not developing POI or trying to debug why POI isn't reading a file correctly, then DO NOT enable logging. You've been warned.
In order to effectively disable the logging functionality in Apache POI you must use an alternative logger. This is accomplished by providing a property to the POILogFactory to override the default logger. You can add one of these –D to your JVM settings
-Dorg.apache.poi.util.POILogger=org.apache.poi.util.NullLogger
-Dorg.apache.poi.util.POILogger=org.apache.commons.logging.impl.NoOpLog
I found Apache POI to slightly better perform with the NoOpLog of apache common!
Recompile poi with more adapted settings
You can create a custom build of Apache POI 3.8 and alter the following properties to better match the size of the excel files you are generating or reading:
http://poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/HSSFWorkbook.html#INITIAL_CAPACITY
Don’t use xlsx, prefer xls!
This will only work if you do not reach xls limitations which may avoid you to go to that extreme solution. XLS is not compressed (XLSX is xml based and compressed) and your workbook may double size in memory as a result!
For example, data beyond 256 (IV) columns by 65,536 rows will not be saved in xls! In Excel 2010 and Excel 2007, the worksheet size is 16,384 columns by 1,048,576 rows, but the worksheet size of Excel 97-2003 is only 256 columns by 65,536 rows. Data in cells outside of this column and row limit is lost in Excel 97-2003. But there is a lot more limitations listed at office.com…
The biggest side effect was that my excel file went from 354kb to 967kb, but the speed increase was quite interesting: more than 44% less evaluation time.
Small localized optimization
I don’t think these bring a lot of speed, JIT should optimize this bad piece of code for us but it is always worth trying Speeding up org.apache.poi.hssf.usermodel.HSSFRow.compareTo() and http://affy.blogspot.ch/2004/04/poi-optimization-speeding-_108265938673224937.html
Playing with Axis C++ and Apache 1.3.1
- Details
- Category: Framework
- Published on Wednesday, 05 April 2006 21:54
- Written by Administrator
- Hits: 10084
Here is a How to since it take me a very long time to install something which should have been trivial....
For the benefit of the community, I am publishing it here on my free time :-) ... Enjoy...
Apache Axis and Apache Axis C++ are implementation of the SOAP ("Simple Object Access Protocol") submission to W3C. From the W3C draft specification:
SOAP is a lightweight protocol for exchanging structured information in a decentralized, distributed environment. It is an XML based protocol that consists of three parts: an envelope that defines a framework for describing what is in a message and how to process it, a set of encoding rules for expressing instances of application-defined datatypes, and a convention for representing remote procedure calls and responses.
Axis C/C++ (Axis CPP) is a non-Java implementation of Axis. At its core Axis CPP has a C++ runtime engine. The provided tooling allows you to create C++ client-side stubs and server-side skeletons. The server skeletons can be deployed to either a full Apache web server using the supplied apache module or a "simple_axis_server" - which is a simple HTTP listener (designed to help you test your services).
Fully Time Deterministic Java
- Details
- Category: Framework
- Published on Wednesday, 03 October 2007 20:49
- Written by Administrator
- Hits: 7179
The AIAA (American Institute of Aeronautics and Astronautics) paper [.pdf] plan to use java (For cost reasons) for safety-critical missions. The first fully time-deterministic and open-source library for Java: Javolution is ready to fight!
Javolution real-time goals are simple: To make your application faster and more time predictable!
That being accomplished through:
- High performance and time-deterministic (real-time) util / lang / text / io / xml base classes.
- Context programming in order to achieve true separation of concerns (logging, performance, etc).
- A testing framework addressing not only unit tests but also performance and regression tests as well.
- Straightforward and low-level parallel computing capabilities with ConcurrentContext.
- Struct and Union base classes for direct interfacing with native applications (e.g. C/C++).
- World's fastest and first hard real-time XML marshalling/unmarshalling facility.
- Simple yet flexible configuration management of your application.
Top 10 Reason to make a try:
- Javolution classes
are simple to use, even simpler than most JDK classes.
You don't need to guess the capacity of a
TextBuilder,
FastTable or a
FastMap, their
size expand gently without ever incurring expensive resize/copy or rehash
operations (unlike
StringBuilder,ArrayListorHashMap). - Developers may achieve true separation of concerns (e.g. logging, configuration) through Context Programming or by using classes such as Configurable.
- Javolution classes
are fast, very fast (e.g. Text
insertion/deletion in
O[Log(n)]instead ofO[n]for standardStringBuffer/StringBuilder). - All Javolution classes are hard real-time compliant and have highly deterministic behavior (in the microsecond range). Furthermore (unlike the standard library), Javolution is RTSJ safe (no memory clash or memory leak when used with Java Real-Time extension).
- Javolution makes it easy for concurrent algorithms to take advantage of multi-processors systems.
- Javolution's real-time collection classes (map, list, table and set) can be used in place of most standard collection classes and provide numerous additional capabilities.
- Any Java class can be serialized/deserialized in XML format in any form you may want, also no need to implement Serializable or for the platform to support serialization
- Javolution provides Struct and Union classes for direct interoperability with C/C++ applications.
- Javolution runs on any platform from the simplest J2ME CLDC 1.0 with no garbage collector to the latest J2EE 5.0 with parameterized types.
- Javolution is a pure Java Solution (no native code), small (less than 300 KBytes jar file) and free; permission to use, copy, modify, and distribute this software is freely granted, provided that copyright notices are preserved (BSD License).
Open source libraries to read, write, change PDF files
- Details
- Category: Framework
- Published on Tuesday, 14 June 2005 00:00
- Written by Administrator
- Hits: 10428
More Articles...
Subcategories
-
Apache Maven
Maven is a software tool for Java project management and build automation created by Jason van Zyl in 2002. It is similar in functionality to the Apache Ant tool (and to a lesser extent, PHP's PEAR and Perl's CPAN), but has a simpler build configuration model, based on an XML format. Maven is hosted by the Apache Software Foundation, where it was formerly part of the Jakarta Project.
Maven uses a construct known as a Project Object Model (POM) to describe the software project being built, its dependencies on other external modules and components, and the build order. It comes with pre-defined targets for performing certain well defined tasks such as compilation of code and its packaging.
A key feature of Maven is that it is network-ready. The core engine can dynamically download plug-ins from a repository, the same repository that provides access to many versions of different Open Source Java projects, from Apache and other organisations and developers. This repository and its reorganized successor, the Maven 2 repository, strives to be the de facto distribution mechanism for Java applications, but its adoption has been slow. Maven provides built in support not just for retrieving files from this repository, but to upload artifacts at the end of the build. A local cache of downloaded artifacts acts as the primary means of synchronizing the output of projects on a local system.
Maven is based on a plugin-based architecture that allows it to make use of any application controllable through standard input. Theoretically, this would allow anyone to write plugins to interface with build tools (compilers, unit test tools, etc.) for any other language. In reality, support and use for languages other than Java has been minimal. Currently a plugin for the .Net framework exists and is maintained, and a C/C++ native plugin was at one time maintained for Maven 1. -
Apache Ant
- Apache Ant is a cross-platform Java-based build tool.
- Configuration files are XML-based
- I am presenting here ready to use scripts or some tips/tricks
-
Java Server Faces
JavaServer Faces or JSF simplify the development of user interfaces for J2EE applications using JavaServer Pages. The JSF specification is defined by JSR 127 of the Java Community Process. WikiPedia
