Set data type after creating a excel-report (.xls) with the "wizard"

[dev_34]

Regaring to my problem i need to know that now: https://community.jaspersoft.com/questions/1116991/unable-get-value-excel-field-classlangstring

Thank you very much.


Best regards, Jan

[swood_1]

OK - XLSX as a data source in Studio is messy.

The XLS/XLSX data adapter editor will show you the columns it detects in the file, and appears to do some guessing about whther the first row is the column labels. But it will not automatically set the "Skip the first line" checkbox. You have to set it yourself.

If you don't set the Skip first line and have column names, you will get an exception as it tries to parse the column name as a NUMERIC cell even if you have the column type in the report as String. In the stack trace, you will see at some point:

Caused by: java.text.ParseException: Unparseable number: "Price (USD)"
    at java.text.NumberFormat.parse(NumberFormat.java:385)

The Number and Date formats in the data adapter did not seem to have any effect on the parsing.

 

When you use your XLSX data adapter in a report, the Read Fields process in the Dataset and Query Dialog returns all columns as String. If you have columns in the file are NUMERIC in Excel, but have a String field type in the the field definition in the report, you will get:

net.sf.jasperreports.engine.JRException: Unable to get value for Excel field "Price (USD)" of class java.lang.String.
...

Caused by: java.lang.IllegalStateException: Cannot get a STRING value from a NUMERIC cell
    at org.apache.poi.xssf.usermodel.XSSFCell.typeMismatch(XSSFCell.java:1093)

So set the data type of NUMERIC columns to be a java.lang.Float, Integer, Double, BigDecimal, Short or Long in the report field definition.

Dates in the file also are treated as NUMERIC during the data parsing, so you will get the same exceptions as above if you leave a XLS Date column as with a field type of String. Set their field type in the report to be java.util.Date.

So:

1. Always manually set the "Skip the first line" checkbox in the XLSX data adapter.
2. Set the field type of any numeric column in the XLSX to be a Java numeric.
3. Set the field type of any data column in the XLSX to be a Java Date.

 

[dev_34]

Thank you very much, will look about it...

Regards, Jan

[dev_34]

It works now with that option, thank you very very much!! :-)