Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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?

like image 804
Frank Avatar asked Jul 30 '10 14:07

Frank


4 Answers

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

like image 108
Noel M Avatar answered Nov 15 '22 05:11

Noel M


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.

like image 42
Thierry-Dimitri Roy Avatar answered Nov 15 '22 06:11

Thierry-Dimitri Roy


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.

like image 45
Pascal Thivent Avatar answered Nov 15 '22 04:11

Pascal Thivent


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

like image 1
hecvd Avatar answered Nov 15 '22 04:11

hecvd