Is there anywhere a Java library available that supports exactly the format strings of Excel?

Question

For a Java application that drags data together from some sources and does some calculation itself, we want to offer users the possibility to use their own format strings, and would prefer the format string syntax they know from Excel (e. g. "$ "#,###.,0) which happens to be the same used in .net and Analysis Services.

The closest thing to use that I found in Java is DecimalFormat which lacks some of the features (e. g. thousands and millions formatting by putting the thousands separator at the end, the floating point numbers NaN and infinity are displayed differently, etc., and probably depending on locale, there will be some additional small differences.

For now, just the numeric formatting would suffice. Maybe date and string formatting will become a requirement in future.

Is there a library, or would we have to develop that ourselves?

I cannot imagine we are the only ones who have this issue.

POI as suggested by Noel M does not seem to offer a solution. Any other ideas?

Answer 1

Apache POI might be able to do what you're looking for.

Answer 2

No library exist for Java that does this kind of parsing. Reverse-engineering Excel to cover all test cases would be time consuming for such a niche product.

Since .net will convert 100% of your test cases, I suggest you focus on calling .Net in your Java application.

Answer 3

_{(Adding another answer as you refer to my first in your question now)}

It seems that such a facility doesn't exist. One thing you might be able to explore is to create it yourself. You could possibly use JavaCC to express these formats yourself, and how they end up mapping to typical Java objects. JavaCC would definitely be able to give you the ability to use your own format strings.

Perhaps this would fit in nicely as an implementation of NumberFormat

Answer 4

for decimal format including locale and other stuff:

http://www.jdocs.com/javase/7.b12/java/text/DecimalFormat.html

http://www.freshsources.com/Format.htm

http://www.java2s.com/Code/Java/I18N/JavaI18NFormatNumberFormat.htm

You can tweak the decimalFormat output using your own custom DecimalFormatSymbols. It's posible to set the string used to represent NaN and a lot more.

http://download.oracle.com/javase/6/docs/api/java/text/DecimalFormatSymbols.html

for Date Format:

http://javatechniques.com/blog/dateformat-and-simpledateformat-examples/

for both in a JFormattedTextField

http://www.java2s.com/Code/Java/Swing-JFC/Formatted-TextField.htm

text format

http://www.java2s.com/Code/Java/Development-Class/MakecustomInputTextFormatterinJava.htm http://www.java2s.com/Code/Java/Development-Class/Formatter.htm http://www.java2s.com/Code/Java/Development-Class/ApplyamasktoString.htm

Answer 5

Is there a library, or would we have to develop that ourselves?

Check ExtenXLS, the Java Spreadsheet SDK, or its open source version OpenXLS (under GPLv.3). About OpenXLS:

OpenXLS is the open-source version of ExtenXLS -- the leading Java Spreadsheet SDK.

OpenXLS is the no-risk way to embed advanced Java spreadsheet functionality in your applications.

With the same industrial-strength code as ExtenXLS3, OpenXLS is a rock-solid performer which gives your Java applications the spreadsheet functionality your users demand -- and then some!

Give your users business intelligence applications and reports in the format they know and love, complete with Charts, images, VB code, and all of the features of Excel intact.

I had a quick look at the API and found this FormatHandle#convertFormatString(String) that converts an Excel-style format string to a Java Format string.

I don't know if it will match your constraints but it's definitely a serious product.