albert's blog: [iReport] How to format numeric data with Excel exporter

2015/11/02

[iReport] How to format numeric data with Excel exporter

Problem
We use JasperReports API to export report with xls format.
But the problem is : data is numeric type, but in formula area its has been applied 1000 separator. It is String data type, no numeric data type.

We provide List of value object for this iReport file. The attributes of value object is as bellows (amount-related attributes' data type had been set to BigDecimal):

@Data
public class Dbm506rVo implements Serializable {
    /**
     * 
     */
    private static final long serialVersionUID = 1L;
    /** 年度 */
    private int year = 0;
    private String staYear = "";
    private BigDecimal gdp = BigDecimal.ZERO;
    private BigDecimal capitalAmount = BigDecimal.ZERO;
    private BigDecimal interestAmount = BigDecimal.ZERO;
    private BigDecimal yearExpend = BigDecimal.ZERO;
    private BigDecimal ttlAmt = BigDecimal.ZERO;
    private BigDecimal percentage1 = BigDecimal.ZERO;
    private BigDecimal percentage2 = BigDecimal.ZERO;
}

How-to
Set net.sf.jasperreports.export.xls.detect.cell.type to true (the default value is false) in this jrxml file.

Quote from document: Property whose value is used as default state of the IS_DETECT_CELL_TYPE export flag. Specifies whether the exporter should take into consideration the type of the original text field expressions and set the cell types and values accordingly.

<?xml version="1.0" encoding="UTF-8"?>
<jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="dbm506r1" pageWidth="842" pageHeight="595" orientation="Landscape" columnWidth="802" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="d1c3bf81-aa19-4e0e-ad82-0a34f472e482">
 <property name="net.sf.jasperreports.export.xls.detect.cell.type" value="true"/>
 <property name="ireport.zoom" value="1.3310000000000004"/>
 <property name="ireport.x" value="0"/>
 <property name="ireport.y" value="0"/>