Larger Summary Section Causing Detail Rows In CSV To Have Extra Commas

[david.alger]

I am creating a report with a Title, a Detail, and a Summary section.

• Title: 7 elements
• Detail:  4 elements (each row)
• Summary:  17 elements

When I fill and export to CSV, every row in every section has 17 elements.

I see nowhere in the documentation saying that in CSV export you are forced to the largest number of elements among the sections.

Here is my template:

<?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="MyReport" pageWidth="595" pageHeight="842" whenNoDataType="AllSectionsNoDetail" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="da0ebe1b-4e77-489b-a0c1-7d45b2e85b62">
    <property name="ireport.zoom" value="1.0"/>
    <property name="ireport.x" value="0"/>
    <property name="ireport.y" value="0"/>
    <property name="net.sf.jasperreports.print.keep.full.text" value="true"/>
    <parameter name="PHYSICAL_FILE_NAME" class="java.lang.String"/>
    <parameter name="FILE_TYPE_ID" class="java.lang.String"/>
    <parameter name="CLIENT_ID" class="java.lang.String"/>
    <parameter name="REPORT_DATE_TIME" class="java.util.Date"/>
    <parameter name="REPORT_DETAIL_COUNT" class="java.lang.String"/>
    <parameter name="CONTRIB_FILE_PROCESSED_DATE_TIME" class="java.util.Date"/>
    <parameter name="ADD_RECORD_COUNT" class="java.lang.String"/>
    <parameter name="CHANGE_RECORD_COUNT" class="java.lang.String"/>
    <parameter name="DELETE_RECORD_COUNT" class="java.lang.String"/>
    <parameter name="REJECTED_ADD_RECORD_COUNT" class="java.lang.String"/>
    <parameter name="REJECTED_CHANGE_RECORD_COUNT" class="java.lang.String"/>
    <parameter name="REJECTED_DELETE_RECORD_COUNT" class="java.lang.String"/>
    <parameter name="REJECTED_UNKNOWN_ACTION_RECORD_COUNT" class="java.lang.String"/>
    <parameter name="TOTAL_ADD_RECORD_COUNT" class="java.lang.String"/>
    <parameter name="TOTAL_CHANGE_RECORD_COUNT" class="java.lang.String"/>
    <parameter name="TOTAL_DELETE_RECORD_COUNT" class="java.lang.String"/>
    <parameter name="TOTAL_COUNT" class="java.lang.String"/>
    <field name="actionType" class="java.lang.String"/>
    <field name="line" class="java.lang.Integer"/>
    <field name="correlators" class="java.lang.String"/>
    <field name="errorCode" class="java.lang.String"/>
    <field name="errorText" class="java.lang.String"/>
    <title>
        <band height="23" splitType="Stretch">
            <staticText>
                <reportElement x="0" y="0" width="1" height="20"  uuid="096cf432-5b00-418a-a905-9ddae9f329fe"/>
                <text><![CDATA[H]]></text>
            </staticText>
            <textField isBlankWhenNull="true">
                <reportElement x="1" y="0" width="1" height="20" uuid="5d4ad69b-0a64-4604-8423-b46f82c30e34"/>
                <textFieldExpression><![CDATA[$P{CLIENT_ID}]]></textFieldExpression>
            </textField>
            <textField isBlankWhenNull="true">
                <reportElement x="2" y="0" width="1" height="20" uuid="5d4ad69b-0a64-4604-8423-b46f82c30e33"/>
                <textFieldExpression><![CDATA[new SimpleDateFormat("yyyyMMdd").format($P{REPORT_DATE_TIME})]]></textFieldExpression>
            </textField>
            <textField isBlankWhenNull="true">
                <reportElement x="3" y="0" width="1" height="20" uuid="5d4ad69b-0a64-4604-8423-b46f82c30e32"/>
                <textFieldExpression><![CDATA[new SimpleDateFormat("HHmmss").format($P{REPORT_DATE_TIME})]]></textFieldExpression>
            </textField>
            <textField isBlankWhenNull="true">
                <reportElement x="4" y="0" width="1" height="20" uuid="5d4ad69b-0a64-4604-8423-b46f82c30e31"/>
                <textFieldExpression><![CDATA[$P{FILE_TYPE_ID}]]></textFieldExpression>
            </textField>
            <textField isBlankWhenNull="true">
                <reportElement x="5" y="0" width="1" height="20" uuid="5d4ad69b-0a64-4604-8423-b46f82c30e30"/>
                <textFieldExpression><![CDATA[$P{PHYSICAL_FILE_NAME}]]></textFieldExpression>
            </textField>
        </band>
    </title>
    <detail>
        <band height="20" splitType="Stretch">
            <staticText>
                <reportElement x="0" y="0" width="1" height="20" uuid="42cbd8f7-80d2-4022-b124-2bf56051bb54"/>
                <text><![CDATA[D]]></text>
            </staticText>
            <textField isBlankWhenNull="true">
                <reportElement x="1" y="0" width="1" height="20"  uuid="76e02435-4e43-4c7f-b32b-f49d369aff05"/>
                <textFieldExpression><![CDATA[$F{actionType}]]></textFieldExpression>
            </textField>
            <textField isBlankWhenNull="true">
                <reportElement x="2" y="0" width="1" height="20"  uuid="76e02435-4e43-4c7f-b32b-f49d369aff04"/>
                <textFieldExpression><![CDATA[$F{line}]]></textFieldExpression>
            </textField>
            <textField isBlankWhenNull="true">
                <reportElement x="3" y="0" width="1" height="20"  uuid="76e02435-4e43-4c7f-b32b-f49d369aff03"/>
                <textFieldExpression><![CDATA[$F{correlators}]]></textFieldExpression>
            </textField>
            <textField isBlankWhenNull="true">
                <reportElement x="4" y="0" width="1" height="20"  uuid="76e02435-4e43-4c7f-b32b-f49d369aff02"/>
                <textFieldExpression><![CDATA[$F{errorCode}]]></textFieldExpression>
            </textField>
            <textField isBlankWhenNull="true">
                <reportElement x="5" y="0" width="1" height="20"  uuid="76e02435-4e43-4c7f-b32b-f49d369aff01"/>
                <textFieldExpression><![CDATA[$F{errorText}]]></textFieldExpression>
            </textField>
        </band>
    </detail>
    <lastPageFooter>
        <band height="21" splitType="Stretch">
            <staticText>
                <reportElement x="0" y="0" width="1" height="20" uuid="42cbd8f7-80d2-4022-b124-2bf56051bb54"/>
                <text><![CDATA[T]]></text>
            </staticText>
            <textField isBlankWhenNull="true">
                <reportElement x="1" y="0" width="9" height="20" uuid="865e2573-1e7e-4c06-88e5-e9805ff9004b"/>
                <textFieldExpression><![CDATA[$P{CLIENT_ID}]]></textFieldExpression>
            </textField>
            <textField isBlankWhenNull="true">
                <reportElement x="2" y="0" width="8" height="20" uuid="d0207498-953e-46fc-b6d8-62d235f21c99"/>
                <textFieldExpression><![CDATA[new SimpleDateFormat("yyyyMMdd").format($P{REPORT_DATE_TIME})]]></textFieldExpression>
            </textField>
            <textField isBlankWhenNull="true">
                <reportElement x="3" y="0" width="6" height="20" uuid="5d4ad69b-0a64-4604-8423-b46f82c30e1e"/>
                <textFieldExpression><![CDATA[new SimpleDateFormat("HHmmss").format($P{REPORT_DATE_TIME})]]></textFieldExpression>
            </textField>
            <textField isBlankWhenNull="true">
                <reportElement x="4" y="0" width="9" height="20" uuid="5d4ad69b-0a64-4604-8423-b46f82c30e11"/>
                <textFieldExpression><![CDATA[$P{REPORT_DETAIL_COUNT}]]></textFieldExpression>
            </textField>
            <textField isBlankWhenNull="true">
                <reportElement x="5" y="0" width="8" height="20" uuid="5d4ad69b-0a64-4604-8423-b46f82c30e12"/>
                <textFieldExpression><![CDATA[new SimpleDateFormat("yyyyMMdd").format($P{CONTRIB_FILE_PROCESSED_DATE_TIME})]]></textFieldExpression>
            </textField>
            <textField isBlankWhenNull="true">
                <reportElement x="6" y="0" width="6" height="20" uuid="5d4ad69b-0a64-4604-8423-b46f82c30e13"/>
                <textFieldExpression><![CDATA[new SimpleDateFormat("HHmmss").format($P{CONTRIB_FILE_PROCESSED_DATE_TIME})]]></textFieldExpression>
            </textField>
            <textField isBlankWhenNull="true">
                <reportElement x="7" y="0" width="9" height="20" uuid="5d4ad69b-0a64-4604-8423-b46f82c30e14"/>
                <textFieldExpression><![CDATA[$P{ADD_RECORD_COUNT}]]></textFieldExpression>
            </textField>
            <textField isBlankWhenNull="true">
                <reportElement x="8" y="0" width="9" height="20" uuid="5d4ad69b-0a64-4604-8423-b46f82c30e15"/>
                <textFieldExpression><![CDATA[$P{CHANGE_RECORD_COUNT}]]></textFieldExpression>
            </textField>
            <textField isBlankWhenNull="true">
                <reportElement x="9" y="0" width="9" height="20" uuid="5d4ad69b-0a64-4604-8423-b46f82c30e16"/>
                <textFieldExpression><![CDATA[$P{DELETE_RECORD_COUNT}]]></textFieldExpression>
            </textField>
            <textField isBlankWhenNull="true">
                <reportElement x="10" y="0" width="9" height="20" uuid="5d4ad69b-0a64-4604-8423-b46f82c30e17"/>
                <textFieldExpression><![CDATA[$P{REJECTED_ADD_RECORD_COUNT}]]></textFieldExpression>
            </textField>
            <textField isBlankWhenNull="true">
                <reportElement x="11" y="0" width="9" height="20" uuid="5d4ad69b-0a64-4604-8423-b46f82c30e18"/>
                <textFieldExpression><![CDATA[$P{REJECTED_CHANGE_RECORD_COUNT}]]></textFieldExpression>
            </textField>
            <textField isBlankWhenNull="true">
                <reportElement x="12" y="0" width="9" height="20" uuid="5d4ad69b-0a64-4604-8423-b46f82c30e19"/>
                <textFieldExpression><![CDATA[$P{REJECTED_DELETE_RECORD_COUNT}]]></textFieldExpression>
            </textField>
            <textField isBlankWhenNull="true">
                <reportElement x="13" y="0" width="9" height="20" uuid="5d4ad69b-0a64-4604-8423-b46f82c30e20"/>
                <textFieldExpression><![CDATA[$P{REJECTED_UNKNOWN_ACTION_RECORD_COUNT}]]></textFieldExpression>
            </textField>
            <textField isBlankWhenNull="true">
                <reportElement x="14" y="0" width="9" height="20" uuid="5d4ad69b-0a64-4604-8423-b46f82c30e21"/>
                <textFieldExpression><![CDATA[$P{TOTAL_ADD_RECORD_COUNT}]]></textFieldExpression>
            </textField>
            <textField isBlankWhenNull="true">
                <reportElement x="15" y="0" width="9" height="20" uuid="5d4ad69b-0a64-4604-8423-b46f82c30e22"/>
                <textFieldExpression><![CDATA[$P{TOTAL_CHANGE_RECORD_COUNT}]]></textFieldExpression>
            </textField>
            <textField isBlankWhenNull="true">
                <reportElement x="16" y="0" width="9" height="20" uuid="5d4ad69b-0a64-4604-8423-b46f82c30e23"/>
                <textFieldExpression><![CDATA[$P{TOTAL_DELETE_RECORD_COUNT}]]></textFieldExpression>
            </textField>
            <textField isBlankWhenNull="true">
                <reportElement x="17" y="0" width="9" height="20" uuid="5d4ad69b-0a64-4604-8423-b46f82c30e24"/>
                <textFieldExpression><![CDATA[$P{TOTAL_COUNT}]]></textFieldExpression>
            </textField>
        </band>
    </lastPageFooter>
</jasperReport>

Resulting exported CSV is like:

H,12345,20190101,134555,AAA,MyData.txt,,,,,,,,,,,,
D,,10,2199983,AL3602,Record contained value that is not numeric or incorrect length,,,,,,,,,,,,
D,,3,798987,AL3602,Record contained value that is not numeric or incorrect length,,,,,,,,,,,,
T,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0

My basic export code:

    public void generateReport() {
        BufferedInputStream bis;
        try {
            bis = new BufferedInputStream(new FileInputStream(new File(JR_TEMPLATE)));
            try {

                JasperDesign jasperDesign = JRXmlLoader.load(bis);
                JasperReport report = JasperCompileManager.compileReport(jasperDesign);
                Map<String, Object> jasperReportParams = new HashMap<String, Object>();
                List<ReportDetail> errorsList = this.createErrorList();
                JRDataSource jrDs = new JRBeanArrayDataSource(errorsList.toArray());
                JasperPrint jPrint = JasperFillManager.fillReport(report, jasperReportParams, jrDs);
                List<JasperPrint> jpl = new ArrayList<JasperPrint>();
                jpl.add(jPrint);
                SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd-HHmmss");
                JRCsvExporter exporterCSV = new JRCsvExporter();
                exporterCSV.setExporterInput(SimpleExporterInput.getInstance(jpl));
                exporterCSV.setExporterOutput(new SimpleWriterExporterOutput(new File(REPORT_OUTPUT_DIR+REPORT_FILE_PREFIX+sdf.format(new Date())+".txt")));
                exporterCSV.exportReport();
            }
            catch (JRException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        catch (FileNotFoundException e1) {
            // TODO Auto-generated catch block
            e1.printStackTrace();
        }
    }

 

 

 

[C-Box]

Have you already tried to exlude the whole (last)PageFooter band for your csv export:

There are some properties for exporting, that you should set at report level in your design.

net.sf.jasperreports.export.cvs.exclude.origin.band.1=pageFooternet.sf.jasperreports.export.cvs.exclude.origin.band.2=lastPageFooter[/code]

You could also set the "IgnorePagignation" flag to true, if you just want to get one fullfilled csv list of your data and no "printable" report.

not tried but could work

hth + regards 

C-Box

[david.alger]

It turns out this m x n layout behavior (with associated padding) is the natural behavior of JRCsvExporter:

CSV is a character-based file format whose content is structured in rows and columns, so the JRCsvExporter is a grid exporter because it must transform the free-form content of each page from the source document into a grid-like structure using the special grid layout algorithm.

See http://jasperreports.sourceforge.net/api/net/sf/jasperreports/engine/export/JRCsvExporter.html.

For our needs, we used the JRCsvExporter, but exported to a ByteArrayOutputStream, from which we trimmed each row.

                                 JasperPrint jPrint = asvReportJasperService.fillReport(
                                        jasperReport,
                                        jasperReportDataParams,
                                        new JRBeanCollectionDataSource(errorsList));

                                File outputFile = new File(<file path>);
                                JRCsvExporter exporterCSV = new JRCsvExporter();
                                SimpleExporterInput input = new SimpleExporterInput(jPrint);
                                exporterCSV.setExporterInput(input);
                                ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
                                SimpleWriterExporterOutput output = new SimpleWriterExporterOutput(outputStream);
                                exporterCSV.setExporterOutput(output);
                                exporterCSV.exportReport();
                                byte[] reportData = outputStream.toByteArray();
                                byte[] trimmedReportData = this.trimPipes(reportData);
                                FileUtils.writeByteArrayToFile(outputFile, trimmedReportData);

The trimPipes(byte[] data) method is:

    public byte[] trimPipes(byte[] data) {

        if (data == null || data.length == 0) {
            return data;
        }

        String report = new String(data);
        report = report.trim();
        char[] trimmedReportChars = new char[report.length()];
        char[] tempChars = new char[report.length()];

        int reportCharIdx = 0;
        int tempCharIdx = 0;
        boolean fillTemp = false;

        for (char reportChar : report.toCharArray()) {
            if (isReturnOrNewLine(reportChar)) {
                tempChars = new char[report.length()];
                fillTemp = false;
                tempCharIdx = 0;
                trimmedReportChars[reportCharIdx++] = reportChar;
            } else {
                if (isCsvDelimiter(reportChar)) {
                    tempChars[tempCharIdx++] = reportChar;
                    fillTemp = true;
                } else {
                    if (tempCharIdx > 0) {
                        for (int idx = 0; idx < tempCharIdx; idx++) {
                            trimmedReportChars[reportCharIdx++] = tempChars[idx];
                        }
                        tempChars = new char[report.length()];
                        fillTemp = false;
                        tempCharIdx = 0;
                    }
                    trimmedReportChars[reportCharIdx++] = reportChar;
                }
            }
        }

        String trimmedReport = String.valueOf(trimmedReportChars).trim();
        return trimmedReport.getBytes();
    }