Hot questions for Using JasperReports in excel

Question:

I trying to display barcode in excel sheet using API's like Apache POI but didn't found any solution.

I also tried using JasperReports (as we can generate barcode in it) and then convert the compiled report to excel using JRXlsExporter.

Below is my jrxml for the excel export

<?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="customer_export" language="groovy" pageWidth="755" pageHeight="842" columnWidth="715" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20">
<parameter name="siteId" class="java.lang.String">
    <defaultValueExpression><![CDATA[]]></defaultValueExpression>
</parameter>
<parameter name="companyId" class="java.lang.String">
    <defaultValueExpression><![CDATA[]]></defaultValueExpression>
</parameter>
<queryString>
    <![CDATA[SELECT c.NAME ,c.EXTERNAL_CODE ,a.ADDRESS,CASE  WHEN c.LOCAL_MAIL IS NOT NULL THEN c.LOCAL_MAIL when C.MAIL IS NOT NULL then c.MAIL when c.MAIL IS Null then NULL when c.LOCAL_MAIL is NULL then NULL ELSE NULL END as MAIL, ru.BARCODE, c.PHONE FROM CUSTOMER c, SITE csite, RACK_USER ru, ADDRESS a WHERE c.COMPANY_SITE_ID = csite.ID_SITE AND c.RACK_USER_ID = ru.ID_RACK_USER AND ru.ID_RACK_USER = a.RACK_USER_ID AND csite.ID_SITE=$P{companyId} AND c.SITE_ID=$P{siteId} AND a.MAIN_FOR_COMPANY_SITE=1 AND a.COMPANY_SITE_ID =$P{companyId} ORDER BY c.NAME ASC]]>
</queryString>
<field name="NAME" class="java.lang.String"/>
<field name="EXTERNAL_CODE" class="java.lang.String"/>
<field name="ADDRESS" class="java.lang.String"/>
<field name="MAIL" class="java.lang.String"/>
<field name="BARCODE" class="java.lang.String"/>
<field name="PHONE" class="java.lang.String"/>
<title>
    <band height="20">
        <staticText>
            <reportElement mode="Opaque" x="0" y="0" width="119" height="20" backcolor="#CCCCCC"/>
            <textElement textAlignment="Center" verticalAlignment="Middle"/>
            <text><![CDATA[Customer Name]]></text>
        </staticText>
        <staticText>
            <reportElement mode="Opaque" x="119" y="0" width="112" height="20" backcolor="#CCCCCC"/>
            <textElement textAlignment="Center" verticalAlignment="Middle"/>
            <text><![CDATA[Customer Code]]></text>
        </staticText>
        <staticText>
            <reportElement mode="Opaque" x="231" y="0" width="157" height="20" backcolor="#CCCCCC"/>
            <textElement textAlignment="Center" verticalAlignment="Middle"/>
            <text><![CDATA[Address]]></text>
        </staticText>
        <staticText>
            <reportElement mode="Opaque" x="388" y="0" width="121" height="20" backcolor="#CCCCCC"/>
            <textElement textAlignment="Center" verticalAlignment="Middle"/>
            <text><![CDATA[Email]]></text>
        </staticText>
        <staticText>
            <reportElement mode="Opaque" x="509" y="0" width="100" height="20" backcolor="#CCCCCC"/>
            <textElement textAlignment="Center" verticalAlignment="Middle"/>
            <text><![CDATA[Barcode]]></text>
        </staticText>
        <staticText>
            <reportElement mode="Opaque" x="609" y="0" width="106" height="20" backcolor="#CCCCCC"/>
            <textElement textAlignment="Center" verticalAlignment="Middle"/>
            <text><![CDATA[Phone Number]]></text>
        </staticText>
    </band>
</title>
<detail>
    <band height="24" splitType="Stretch">
        <textField>
            <reportElement positionType="Float" stretchType="RelativeToTallestObject" x="0" y="0" width="119" height="24"/>
            <textElement textAlignment="Center" verticalAlignment="Middle"/>
            <textFieldExpression><![CDATA[$F{NAME}]]></textFieldExpression>
        </textField>
        <textField>
            <reportElement positionType="Float" stretchType="RelativeToTallestObject" x="119" y="0" width="112" height="24"/>
            <textElement textAlignment="Center" verticalAlignment="Middle"/>
            <textFieldExpression><![CDATA[$F{EXTERNAL_CODE}]]></textFieldExpression>
        </textField>
        <textField isStretchWithOverflow="true">
            <reportElement positionType="Float" stretchType="RelativeToTallestObject" x="231" y="0" width="157" height="24"/>
            <textElement textAlignment="Center" verticalAlignment="Middle"/>
            <textFieldExpression><![CDATA[$F{ADDRESS}]]></textFieldExpression>
        </textField>
        <textField isStretchWithOverflow="true">
            <reportElement positionType="Float" stretchType="RelativeToTallestObject" x="388" y="0" width="121" height="24"/>
            <textElement textAlignment="Center" verticalAlignment="Middle"/>
            <textFieldExpression><![CDATA[$F{MAIL}]]></textFieldExpression>
        </textField>
        <textField isBlankWhenNull="true">
            <reportElement positionType="Float" stretchType="RelativeToTallestObject" x="609" y="0" width="106" height="24"/>
            <textElement textAlignment="Center" verticalAlignment="Middle"/>
            <textFieldExpression><![CDATA[$F{PHONE}]]></textFieldExpression>
        </textField>
        <componentElement>
            <reportElement positionType="Float" stretchType="RelativeToTallestObject" x="509" y="0" width="100" height="24"/>
            <jr:barbecue xmlns:jr="http://jasperreports.sourceforge.net/jasperreports/components" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports/components http://jasperreports.sourceforge.net/xsd/components.xsd" type="Code39" drawText="true" checksumRequired="false" evaluationTime="Report">
                <jr:codeExpression><![CDATA["1234"]]></jr:codeExpression>
            </jr:barbecue>
        </componentElement>
    </band>
</detail>

Java code to export excel:

JRXlsExporter exporterXLS = new JRXlsExporter();
exporterXLS.setParameter(JRXlsExporterParameter.JASPER_PRINT, jprint);
exporterXLS.setParameter(JRXlsExporterParameter.OUTPUT_STREAM, reportos);
exporterXLS.setParameter(JRXlsExporterParameter.IS_DETECT_CELL_TYPE, Boolean.TRUE);
exporterXLS.setParameter(JRXlsExporterParameter.IGNORE_PAGE_MARGINS, Boolean.TRUE);
exporterXLS.setParameter(JRXlsExporterParameter.IS_WHITE_PAGE_BACKGROUND, Boolean.FALSE);
exporterXLS.setParameter(JRXlsExporterParameter.IS_IGNORE_GRAPHICS, Boolean.TRUE);
exporterXLS.setParameter(JRXlsExporterParameter.IS_REMOVE_EMPTY_SPACE_BETWEEN_COLUMNS, Boolean.TRUE);
exporterXLS.setParameter(JRXlsExporterParameter.IS_REMOVE_EMPTY_SPACE_BETWEEN_ROWS, Boolean.TRUE);
exporterXLS.setParameter(JRXlsExporterParameter.MAXIMUM_ROWS_PER_SHEET, 50000);
exporterXLS.setParameter(JRXlsExporterParameter.OUTPUT_FILE, filename);
exporterXLS.exportReport();

But in Barcode column I'm getting empty when excel is exported.

Is there any attribute I need to apply in JasperReports or in Java or anything else?

Or is there any other API's or can we display barcode using Apache POI?


Answer:

Using the barcode4j or barbecue you can generate the barcode as an image in java and then pass it to jasper-reports

Example with barcode4j

Java code

public class MyBarcodeGenerator {
  public static Image getGS1_128(String code) {
    if (code==null){
        return null;
    }
    EAN128Bean c = new EAN128Bean();
    c.setChecksumMode(ChecksumMode.CP_AUTO);
    c.setOmitBrackets(false);
    c.setCodeset(Code128Constants.CODESET_C);
    c.setMsgPosition(HumanReadablePlacement.HRP_BOTTOM);
    c.doQuietZone(true);
    c.setQuietZone(5);
    c.setFontSize(2d);

    int dpi = 200;
    boolean antiAlias = false;
    int orientation = 0;
    BitmapCanvasProvider canvas = new BitmapCanvasProvider(dpi, BufferedImage.TYPE_BYTE_BINARY, antiAlias, orientation);
    c.generateBarcode(canvas, code);
    return canvas.getBufferedImage();
  }
}

jrxml, displaying the image

<image scaleImage="Clip" hAlign="Center" vAlign="Middle" isUsingCache="false" onErrorType="Blank">
    <reportElement positionType="Float" x="112" y="67" width="315" height="95" uuid="b90b1ec8-1483-4677-8db4-4556ecdad6b3">
        <printWhenExpression><![CDATA[new Boolean($F{GS1_128}!=null)]]></printWhenExpression>
    </reportElement>
    <imageExpression><![CDATA[my.package.MyBarcodeGenerator.getGS1_128($F{GS1_128})]]></imageExpression>
</image>

For correct image size change the dpi settings or use the batik libraries to generate svg images, it's a bit more complex but with much better resolution see barcode4j svg dom

Question:

i have already managed to export a pdf report with jasperreports but i cannot export to excel format.

The xls output file my program produces doesn't open with Excel(corrupted), and is totally empty if i open it with LibreOffice.

Here is the jrxml code(a test example):

<?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="xls" language="groovy" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="d8281a37-1a28-43e6-824c-0d39b69f7d23">
<property name="net.sf.jasperreports.export.xls.detect.cell.type" value="true"/>
<property name="net.sf.jasperreports.print.keep.full.text" value="true"/>
<property name="net.sf.jasperreports.export.xls.wrap.text" value="false"/>
<property name="net.sf.jasperreports.export.xls.auto.fit.row" value="true"/>
<property name="net.sf.jasperreports.export.xls.auto.fit.column" value="true"/>
<style name="table">
    <box>
        <pen lineWidth="1.0" lineColor="#000000"/>
    </box>
</style>
<style name="table_TH" mode="Opaque" backcolor="#F0F8FF">
    <box>
        <pen lineWidth="0.5" lineColor="#000000"/>
    </box>
</style>
<style name="table_CH" mode="Opaque" backcolor="#BFE1FF">
    <box>
        <pen lineWidth="0.5" lineColor="#000000"/>
    </box>
</style>
<style name="table_TD" mode="Opaque" backcolor="#FFFFFF">
    <box>
        <pen lineWidth="0.5" lineColor="#000000"/>
    </box>
</style>
<subDataset name="Table Dataset 1" uuid="3897caef-b047-4ba2-8b62-bd46ec43553d"/>
<field name="credit" class="java.lang.Double"/>
<field name="time" class="java.lang.String"/>
<title>
    <band height="50"/>
</title>
<pageHeader>
    <band height="50">
        <staticText>
            <reportElement x="0" y="30" width="100" height="20" uuid="4a091ece-05dc-48a7-8390-bf3722715ca8"/>
            <text><![CDATA[Nikos]]></text>
        </staticText>
    </band>
</pageHeader>
<columnHeader>
    <band height="50">
        <staticText>
            <reportElement x="0" y="30" width="100" height="20" uuid="39c43e60-ee74-4373-bafb-b78647870be5"/>
            <text><![CDATA[Nikos]]></text>
        </staticText>
    </band>
</columnHeader>
<detail>
    <band height="153">
        <textField>
            <reportElement x="0" y="0" width="100" height="20" uuid="ec837765-6e78-417f-b6ea-1cec760fba54"/>
            <textFieldExpression><![CDATA[$F{credit}]]></textFieldExpression>
        </textField>
        <textField>
            <reportElement x="100" y="0" width="100" height="20" uuid="23e21ff0-e776-4a04-a5d0-a4188694446e"/>
            <textFieldExpression><![CDATA[$F{time}]]></textFieldExpression>
        </textField>
    </band>
</detail>
<summary>
    <band height="229">
        <textField>
            <reportElement x="0" y="0" width="100" height="20" uuid="a5015437-e944-41c1-88bf-9a0d9f88e2d0"/>
            <textFieldExpression><![CDATA[$F{credit}]]></textFieldExpression>
        </textField>
        <textField>
            <reportElement x="100" y="0" width="100" height="20" uuid="066120ca-8df1-40fb-8dea-b93bf5ef30a9"/>
            <textFieldExpression><![CDATA[$F{time}]]></textFieldExpression>
        </textField>
    </band>
</summary>

And this is my java program (contains also pdf successful export):

public static void main(String[] args) {
    String inPdfFileName = "src/test/javabeans.jasper";
    String inXlsFileName = "src/test/xls.jasper";
    String outPdfName = "test.pdf";
    String outXlsName = "test.xls";
    HashMap pdfParams = new HashMap();
    HashMap xlsParams = new HashMap();

    Collection<FundBean> fundbeans = FundBeanFactory.getBeanCollection();
    pdfParams.put("HighTime", FundBeanFactory.getMax().getTime());
    pdfParams.put("LowTime", FundBeanFactory.getMin().getTime());

    pdfParams.put("Highest", FundBeanFactory.getMax().getCredit().toString());
    pdfParams.put("Lowest", FundBeanFactory.getMin().getCredit().toString());

    JRBeanCollectionDataSource beanCollectionDataSource = new JRBeanCollectionDataSource(fundbeans);
    try {
        JasperPrint PdfPrint = JasperFillManager.fillReport(
                inPdfFileName,
                pdfParams,
                beanCollectionDataSource);

        JasperPrint xlsPrint = JasperFillManager.fillReport(
                inXlsFileName,
                xlsParams,
                beanCollectionDataSource);

        JRPdfExporter pdfExporter = new JRPdfExporter();
        pdfExporter.setExporterInput(new SimpleExporterInput(PdfPrint));
        pdfExporter.setExporterOutput(new SimpleOutputStreamExporterOutput(outPdfName));
        SimplePdfExporterConfiguration configuration = new SimplePdfExporterConfiguration();
        configuration.setCreatingBatchModeBookmarks(true);
        pdfExporter.setConfiguration(configuration);
        pdfExporter.exportReport();

        JRXlsExporter xlsExporter = new JRXlsExporter();

        xlsExporter.setExporterInput(new SimpleExporterInput(xlsPrint));
        xlsExporter.setExporterOutput(new SimpleOutputStreamExporterOutput(outXlsName));
        SimpleXlsReportConfiguration xlsReportConfiguration = new SimpleXlsReportConfiguration();
        xlsReportConfiguration.setOnePagePerSheet(false);
        xlsReportConfiguration.setRemoveEmptySpaceBetweenRows(true);
        xlsReportConfiguration.setDetectCellType(false);
        xlsReportConfiguration.setWhitePageBackground(false);
        xlsExporter.setConfiguration(xlsReportConfiguration);

        xlsExporter.exportReport();
    } catch (JRException e) {
        e.printStackTrace();
    }
  }

When i preview in xls format with iReport Designer i get the desired result. So i guess the problem lies in my java code...


Answer:

Thanks for all help, i figured out what caused the problem, i was using the same bean List both for the pdf report and for the xls,and for some reason when i was passing the list to fillReport() to create the print object for the pdf, all beans where removed from the list. I created a second JRBeanCollection for the xls Print object and everything works fine!

Question:

I have a jasper report with output in excel, I would like to disable the "grid line" option in excel for the background to be all white.

How I can do this in jasper report?

EDIT: The following examples worked for me !!

You set this property directly in your jrxml

net.sf.jasperreports.export.xls.show.gridlines=false, the default value is true

jrxml example

<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="reputation" printOrder="Horizontal" pageWidth="595" pageHeight="842" whenNoDataType="AllSectionsNoDetail" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" isSummaryNewPage="true" uuid="a88bd694-4f90-41fc-84d0-002b90b2d73e">
     <property name="net.sf.jasperreports.export.xls.show.gridlines" value="false"/>
     ....
</jasperReport>

or if you are exporting from java set the SimpleXlsReportConfiguration.setShowGridLines(false)

java example

JRXlsExporter exporterXls = new JRXlsExporter();
exporterXls.setExporterInput(new SimpleExporterInput(jasperPrint));
exporterXls.setExporterOutput(new SimpleOutputStreamExporterOutput(new File("excelTest.xls")));
SimpleXlsReportConfiguration configXls = new SimpleXlsReportConfiguration();
configXls.setShowGridLines(false);
//set your additional settings
exporterXls.setConfiguration(configXls);
exporterXls.exportReport();

Note: The code shows xls (since tagged xls) example but the same property can be used for the SimpleXlsxReportConfiguration (xlsx)


Answer:

You set this property directly in your jrxml

net.sf.jasperreports.export.xls.show.gridlines=false, the default value is true

jrxml example

<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="reputation" printOrder="Horizontal" pageWidth="595" pageHeight="842" whenNoDataType="AllSectionsNoDetail" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" isSummaryNewPage="true" uuid="a88bd694-4f90-41fc-84d0-002b90b2d73e">
     <property name="net.sf.jasperreports.export.xls.show.gridlines" value="false"/>
     ....
</jasperReport>

or if you are exporting from java set the SimpleXlsReportConfiguration.setShowGridLines(false)

java example

JRXlsExporter exporterXls = new JRXlsExporter();
exporterXls.setExporterInput(new SimpleExporterInput(jasperPrint));
exporterXls.setExporterOutput(new SimpleOutputStreamExporterOutput(new File("excelTest.xls")));
SimpleXlsReportConfiguration configXls = new SimpleXlsReportConfiguration();
configXls.setShowGridLines(false);
//set your additional settings
exporterXls.setConfiguration(configXls);
exporterXls.exportReport();

Note: The code shows xls (since tagged xls) example but the same property can be used for the SimpleXlsxReportConfiguration (xlsx)

Question:

I have to upgrade jasperreport-4.0.2 to jasperreport-6.5.1 I found some differences regarding export reports to excel format as

net.sf.jasperreports.engine.export.JExcelApiExporter

Is deprecated and replaced by

net.sf.jasperreports.engine.export.JRXlsExporter

And many others things are also changed. As in jasperreport-4.0.2 I can change excel sheet names as by this code

JRAbstractExporter exporter = new net.sf.jasperreports.engine.export.JExcelApiExporter();
exporter.setParameter(JRXlsExporterParameter.IS_DETECT_CELL_TYPE, Boolean.TRUE);

What is alternate for naming excel sheets in jasperreport-6.5.1 is?


Answer:

In accordance with the new API (comparing with 4.x version) you can solve the task in several ways.

Using ReportExportConfiguration

We can set sheet name using SimpleXlsxReportConfiguration, in thit case we should override getSheetNames() method.

Java code:

Map<String, Object> params = new HashMap<>();
JasperPrint jasperPrint = JasperFillManager.fillReport(jasperReport, params);

JRXlsxExporter exporter = new JRXlsxExporter();

exporter.setConfiguration(new SimpleXlsxReportConfiguration() {

    @Override
    public String[] getSheetNames() {
        return new String[]{"MyName"};
    }
});
exporter.setExporterInput(new SimpleExporterInput(jasperPrint));
SimpleOutputStreamExporterOutput exporterOutput = null;
try (OutputStream outputStream = new FileOutputStream(file)) {
    exporterOutput = new SimpleOutputStreamExporterOutput(outputStream);
    exporter.setExporterOutput(exporterOutput);
    exporter.exportReport();
} finally {
    if (exporterOutput != null) {
        exporterOutput.close();
    }
}

Report 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="Set sheet name via exporter's configuration" pageWidth="595" pageHeight="200" whenNoDataType="AllSectionsNoDetail" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20">
    <title>
        <band height="30">
            <staticText>
                <reportElement x="0" y="0" width="100" height="30"/>
                <text><![CDATA[Set sheet name via exporter's configuration]]></text>
            </staticText>
        </band>
    </title>
</jasperReport>

The generated result will be:

Using report's parameter and net.sf.jasperreports.export.xls.sheet.name property

The sheet name can be set with help of net.sf.jasperreports.export.xls.sheet.name property. In case we want set this name dynamically we can use report's parameter for passing the name.

Java code:

Map<String, Object> params = new HashMap<>();
params.put("stringParam", "ZZZ");
JasperPrint jasperPrint = JasperFillManager.fillReport(jasperReport, params);

JRXlsxExporter exporter = new JRXlsxExporter();

exporter.setConfiguration(new SimpleXlsxReportConfiguration());
exporter.setExporterInput(new SimpleExporterInput(jasperPrint));
SimpleOutputStreamExporterOutput exporterOutput = null;
try (OutputStream outputStream = new FileOutputStream(file)) {
    exporterOutput = new SimpleOutputStreamExporterOutput(outputStream);
    exporter.setExporterOutput(exporterOutput);
    exporter.exportReport();
} finally {
    if (exporterOutput != null) {
        exporterOutput.close();
    }
}

The report's 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="Set sheet name via parameter" pageWidth="595" pageHeight="200" whenNoDataType="AllSectionsNoDetail" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20">
    <parameter name="name" class="java.lang.String"/>
    <title>
        <band height="30">
            <staticText>
                <reportElement x="0" y="0" width="100" height="30">
                    <propertyExpression name="net.sf.jasperreports.export.xls.sheet.name"><![CDATA[$P{name}]]></propertyExpression>
                </reportElement>
                <text><![CDATA[Set sheet name via parameter]]></text>
            </staticText>
        </band>
    </title>
</jasperReport>

The result will be:


More information about using xls/xlsx exporters:

Question:

I have written some code to show excel in a website.

But I'm getting this exception

net.sf.jasperreports.engine.JRRuntimeException: No input source supplied to the exporter.

My code

import in jsp

<%@ page import="java.io.*"%>
<%@ page import="java.sql.Connection"%>
<%@ page import="java.sql.DriverManager"%>
<%@ page import="java.util.HashMap"%>
<%@ page import="java.util.Map"%>
<%@ page import="net.sf.jasperreports.engine.*"%>
<%@ page import="java.io.ByteArrayOutputStream"%>
<%@ page import="net.sf.jasperreports.view.JasperViewer"%>
<%@ page import="net.sf.jasperreports.engine.export.*"%>

jsp code to export to excel

        <%
        Connection conn = null;
        String no1 = request.getParameter("no1");
        String no2 = request.getParameter("no2");

        System.out.println("get value " + no1 + " " +no2);
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/ams2"
                                                ,"root","passwd1234");
            File reportFile = new File (application.getRealPath("//jasper//report//Blank_A4_2.jasper"));
            Map parameters = new HashMap();

            parameters.put("no1",no1);
            parameters.put("no2",no2);
            System.out.println("123 "+parameters);

            ByteArrayOutputStream xlsReport = new ByteArrayOutputStream();
            JRXlsExporter exporter = new JRXlsExporter();
            exporter.setParameter(JRExporterParameter.OUTPUT_STREAM, xlsReport);
            exporter.setParameter(JRExporterParameter.OUTPUT_FILE, "C:\\");
            exporter.setParameter(JRExporterParameter.OUTPUT_FILE_NAME, "sample.xls");
            exporter.exportReport();

            byte bytes[] = new byte[10];
            bytes = xlsReport.toByteArray();
            response.setContentType("application/vnd.ms-excel");

            response.setContentLength(bytes.length);
            xlsReport.close();
            ServletOutputStream outStream = response.getOutputStream();
            outStream.write(bytes,0,bytes.length);
            outStream.flush();
            outStream.close();

        } catch (Exception ex) {
            out.println("Error " + ex);
        }
    %>

How can this be fixed?


Answer:

If you are not using a very old version of JasperReports, you are using deprecated methods and most importantly you are not passing the JasperPrint to the exporter.

No input source supplied to the exporter.

You need to fill the report, using the JasperFillManager.fillReport

Example code (jasper report v5 or above)

JasperDesign jasperDesign = JRXmlLoader.load(new FileInputStream(reportFile));
JasperPrint jasperPrint = JasperFillManager.fillReport(jasperDesign, parameters, conn);

JRXlsExporter exporter = new JRXlsExporter();
exporter.setExporterInput(new SimpleExporterInput(jasperPrint)); //The JasperPrint, filled report
exporter.setExporterOutput(new SimpleOutputStreamExporterOutput(xlsReport)); //Your ByteArrayOutputStream 

SimpleXlsReportConfiguration configuration = new SimpleXlsReportConfiguration();
configuration.setOnePagePerSheet(true);
configuration.setDetectCellType(true);
configuration.set //The other properties you like to set
exporter.setConfiguration(configuration);

exporter.exportReport();

Question:

I am using Dynamic Reports to create Reports and am able to create it. But the issue is that when i convert it to excel, the cell in the excel showing a warning as Number Stored as Text. Because of this no operation is possible.

Here is my code

File file = new File("c:/report.xls");

JasperXlsExporterBuilder xlsExporter = export.xlsExporter(file).setDetectCellType(true).setIgnorePageMargins(true)
                .setWhitePageBackground(false).setRemoveEmptySpaceBetweenColumns(true);

report.addProperty(JasperProperty.EXPORT_XLS_FREEZE_ROW, "2").ignorePageWidth().ignorePagination().toXls(xlsExporter);

How to remove this error.


Answer:

Add the following line to your code:

<property name="net.sf.jasperreports.export.xls.detect.cell.type" value="true"/>

Source

EDIT

In the Dynamic Reports samples, the report variable also sets the following. This could help depending on the column.

.columns(
    itemColumn,
    col.column("Quantity",   "quantity",  type.integerType()),
    col.column("Unit price", "unitprice", type.bigDecimalType()))

Question:

I have an ireport with static fields and text fields.The database consists of large part of data and for single row in a database there is multiple line data.So when i am exporting the jasper report through java code to PDF format it is stretching and elaborating and printing the entire data without any missing of lines but when i am exporting to XLS it is missing the data and moreover stretching of the cell is not happening.I will show an image for better understanding.

As you can see in the above the data is printing very clearly in PDF.

In the above,that is XLS format data is hiding or in the sense missing.So my question is how to display the entire data in a cell when reports are exported to XLS format.I am posting java code below.

    ServletContext context = request.getServletContext(); 
    String fullPath = context.getRealPath("/WEB-INF/reports/report5.jrxml");
    InputStream input = new FileInputStream(new File(fullPath));
    JasperDesign jasperDesign = JRXmlLoader.load(input);

    System.out.println("Compiling Report Designs");
    JasperReport jasperReport = JasperCompileManager.compileReport(jasperDesign);

    System.out.println("Creating JasperPrint Object");
    HashMap<String,Object> map = new HashMap<String,Object>();
    map.put("sql",sql);
    JasperPrint jasperPrint = JasperFillManager.fillReport (jasperReport,map,conn); 
    byte bytes[] = new byte[10000]; 
    //String result = JasperRunManager.runReportToHtmlFile("./usertemplates/test.jasper" , parameters, conn); 



    JRXlsxExporter exporter = new JRXlsxExporter();
    ByteArrayOutputStream xlsReport = new ByteArrayOutputStream(); 
exporter.setExporterInput(new SimpleExporterInput(jasperPrint));
exporter.setExporterOutput(new SimpleOutputStreamExporterOutput(xlsReport));
SimpleXlsxReportConfiguration configuration = new SimpleXlsxReportConfiguration();
configuration.setWhitePageBackground(true);
configuration.setDetectCellType(true);
configuration.setFontSizeFixEnabled(true);
exporter.setConfiguration(configuration);
exporter.exportReport();
    //System.out.println("Size of byte array:"+xlsReport.size()); 
    //bytes = xlsReport.toByteArray();
    String fileName = "InvoiceReport.xls";
response.setHeader("Content-Disposition", "inline; filename="
+ fileName);
    response.setContentType("application/vnd.ms-excel"); 
    //System.out.println("After JasperPrint = 1"); 
    response.setContentLength(xlsReport.size()); 
    //System.out.println("After JasperPrint = 2"); 
    //xlsReport.close(); 
    //System.out.println("After JasperPrint = 3"); 

    OutputStream outputStream = response.getOutputStream(); 
    System.out.println("After JasperPrint = 4"); 
    xlsReport.writeTo(outputStream); 
    outputStream.flush(); 
    //outputStream.close(); 
    } 
    catch(Exception e) 
    {e.printStackTrace();} 

        %>

and the jrxml code is below.

report5.jrxml

<detail>
        <band height="20" splitType="Prevent">
            <textField isStretchWithOverflow="true">
                <reportElement positionType="Float" stretchType="RelativeToTallestObject" x="0" y="0" width="13" height="20" isPrintWhenDetailOverflows="true" uuid="24b49d3f-5c78-4a19-a4d2-e2cbff95a40d">
                    <property name="net.sf.jasperreports.export.xls.auto.fit.row" value="true"/>
                    <property name="net.sf.jasperreports.export.xls.auto.fit.column" value="true"/>
                </reportElement>
                <box>
                    <topPen lineWidth="0.5"/>
                    <leftPen lineWidth="0.5"/>
                    <bottomPen lineWidth="0.5"/>
                    <rightPen lineWidth="0.5"/>
                </box>
                <textElement textAlignment="Center" verticalAlignment="Middle">
                    <font fontName="Times New Roman" pdfFontName="Times-Roman" isPdfEmbedded="true"/>
                </textElement>
                <textFieldExpression><![CDATA[$V{serial number}]]></textFieldExpression>
            </textField>
            <textField isStretchWithOverflow="true">
                <reportElement positionType="Float" stretchType="RelativeToTallestObject" x="13" y="0" width="242" height="20" isPrintWhenDetailOverflows="true" uuid="4332ae75-6371-4879-b805-9d07254f0784">
                    <property name="net.sf.jasperreports.export.xls.auto.fit.row" value="true"/>
                    <property name="net.sf.jasperreports.export.xls.auto.fit.column" value="true"/>
                </reportElement>
                <box>
                    <topPen lineWidth="0.5"/>
                    <leftPen lineWidth="0.5"/>
                    <bottomPen lineWidth="0.5"/>
                    <rightPen lineWidth="0.5"/>
                </box>
                <textElement textAlignment="Center" verticalAlignment="Middle">
                    <font fontName="Times New Roman" pdfFontName="Times-Roman" isPdfEmbedded="true"/>
                </textElement>
                <textFieldExpression><![CDATA[$F{DescriptionOfGoods}]]></textFieldExpression>
            </textField>
            <textField isStretchWithOverflow="true">
                <reportElement positionType="Float" stretchType="RelativeToTallestObject" x="309" y="0" width="86" height="20" isPrintWhenDetailOverflows="true" uuid="54828e8d-bae0-4669-873e-36de8bf54446">
                    <property name="net.sf.jasperreports.export.xls.auto.fit.row" value="true"/>
                    <property name="net.sf.jasperreports.export.xls.auto.fit.column" value="true"/>
                </reportElement>
                <box>
                    <topPen lineWidth="0.5"/>
                    <leftPen lineWidth="0.5"/>
                    <bottomPen lineWidth="0.5"/>
                    <rightPen lineWidth="0.5"/>
                </box>
                <textElement textAlignment="Center" verticalAlignment="Middle">
                    <font fontName="Times New Roman" pdfFontName="Times-Roman" isPdfEmbedded="true"/>
                </textElement>
                <textFieldExpression><![CDATA[$F{PerUnitPrice}]]></textFieldExpression>
            </textField>
            <staticText>
                <reportElement positionType="Float" stretchType="RelativeToTallestObject" x="395" y="0" width="64" height="20" isPrintWhenDetailOverflows="true" uuid="e02408a1-2bcd-4fb9-94d3-ed0c0c1d8ff6">
                    <property name="net.sf.jasperreports.export.xls.auto.fit.row" value="true"/>
                    <property name="net.sf.jasperreports.export.xls.auto.fit.column" value="true"/>
                </reportElement>
                <box>
                    <topPen lineWidth="0.5"/>
                    <leftPen lineWidth="0.5"/>
                    <bottomPen lineWidth="0.5"/>
                    <rightPen lineWidth="0.5"/>
                </box>
                <textElement textAlignment="Center" verticalAlignment="Middle" markup="none">
                    <font fontName="Times New Roman" pdfFontName="Times-Roman" isPdfEmbedded="true"/>
                </textElement>
                <text><![CDATA[Nos]]></text>
            </staticText>
            <textField isStretchWithOverflow="true">
                <reportElement positionType="Float" stretchType="RelativeToTallestObject" x="459" y="0" width="96" height="20" isPrintWhenDetailOverflows="true" uuid="00ba44d4-b287-4a29-8bf3-1ad07da760e8">
                    <property name="net.sf.jasperreports.export.xls.auto.fit.row" value="true"/>
                    <property name="net.sf.jasperreports.export.xls.auto.fit.column" value="true"/>
                </reportElement>
                <box>
                    <topPen lineWidth="0.5"/>
                    <leftPen lineWidth="0.5"/>
                    <bottomPen lineWidth="0.5"/>
                    <rightPen lineWidth="0.5"/>
                </box>
                <textElement textAlignment="Center" verticalAlignment="Middle">
                    <font fontName="Times New Roman" pdfFontName="Times-Roman" isPdfEmbedded="true"/>
                </textElement>
                <textFieldExpression><![CDATA[$F{Quantity} *$F{PerUnitPrice}]]></textFieldExpression>
            </textField>
            <textField isStretchWithOverflow="true">
                <reportElement positionType="Float" stretchType="RelativeToTallestObject" x="255" y="0" width="54" height="20" isPrintWhenDetailOverflows="true" uuid="d74b9bd4-9608-44a7-b46e-55f39ed0c852">
                    <property name="net.sf.jasperreports.export.xls.auto.fit.row" value="true"/>
                    <property name="net.sf.jasperreports.export.xls.auto.fit.column" value="true"/>
                </reportElement>
                <box>
                    <topPen lineWidth="0.5"/>
                    <leftPen lineWidth="0.5"/>
                    <bottomPen lineWidth="0.5"/>
                    <rightPen lineWidth="0.5"/>
                </box>
                <textElement textAlignment="Center" verticalAlignment="Middle">
                    <font fontName="Times New Roman" pdfFontName="Times-Roman" isPdfEmbedded="true"/>
                </textElement>
                <textFieldExpression><![CDATA[$F{Quantity}]]></textFieldExpression>
            </textField>
        </band>
    </detail>

Answer:

First you are using deprecated method in jasper report, consider to update your code:

JRXlsxExporter exporter = new JRXlsxExporter();
exporter.setExporterInput(new SimpleExporterInput(jasperPrint));
xporter.setExporterOutput(new SimpleOutputStreamExporterOutput(xlsReport));
SimpleXlsxReportConfiguration configuration = new SimpleXlsxReportConfiguration();
configuration.setWhitePageBackground(true);
configuration.setDetectCellType(true);
\\... set all your configuration like above
exporter.setConfiguration(configuration);
exporter.exportReport();

You need to set the properties on the reportElement's in textField's containing the text the need to bit fit.

<property name="net.sf.jasperreports.export.xls.auto.fit.row" value="true"/>
<property name="net.sf.jasperreports.export.xls.auto.fit.column" value="true"/>

If this is not working as desired your solutions are:

  1. Make excel change font size (to fit)

    configuration.setFontSizeFixEnabled(true)
    

Flag for decreasing font size so that texts fit into the specified cell height.

  1. Set manually the column width in excel

net.sf.jasperreports.export.xls.column.width, property with an integer value measured in pixels

If your still not happy turn to apache-poi, load the workbook and modify it as you like....

Question:

I have two subreports, which are in separate worksheets. The problem is, that the column segmantation in the second worksheet is the same as in the first and so there are some merged cells. With merged cells you could not sort the columns ("This operation requires the merged cells to be identically sized").

How could I enforce a new style/segmentation of the columns?

These are my settings for the exporter:

reportBuilder.title(cmp.subreport(criteriaReportBuilder), cmp.subreport(secondReportBuilder));
JasperXlsxExporterBuilder xlsxExporter = DynamicReports.export.xlsxExporter(outputStream);
        xlsxExporter.setCollapseRowSpan(false);
        xlsxExporter.setRemoveEmptySpaceBetweenColumns(true);
        xlsxExporter.setRemoveEmptySpaceBetweenRows(false);
        xlsxExporter.setDetectCellType(true);
        xlsxExporter.setWhitePageBackground(false);
        xlsxExporter.setIgnoreGraphics(false);
        xlsxExporter.setOnePagePerSheet(false);

        reportBuilder.toXlsx(xlsxExporter);

Output from second report (cmp.subreport(secondReportBuilder)) I would expect, that there are only two columns (A-B) and not A-G.


Answer:

Converting comment to answer:

Generate multiple JasperPrint from DynamicJasper and then use the standard JRXlsExporter

JRXlsExporter exporter = new JRXlsExporter();
List<JasperPrint> sheets = new ArrayList<JasperPrint>();
sheets.add(criteriaReportBuilder.toJasperPrint());
sheets.add(reportBuilder.toJasperPrint());
exporter.setExporterInput(SimpleExporterInput.getInstance(sheets));
...

Question:

I'm new in JasperSoft and I'm finding issues exporting a report into multi-tabs (multi-sheets) excel file when I upload the jrxml and try to execute it on a web application that runs with Apache Tomcat 8 . I used iReport 4.6.0 CE to build this report where I'm using two subreports contained into two headergroups separated with page breaks to determine the end of the first tab and the begining of the second tab of the excel file . When I ran this report using iReport , I get the result I wanted in the first place However, When I run the same report on the web application , I get all the data from the two subreports under the same tab. Any body could help me here?

Here is my main report xml code

<?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="reportGlobalDOD" language="groovy" pageWidth="1310" pageHeight="842" columnWidth="1270" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" isIgnorePagination="true" uuid="3eb83bbf-8ce6-4d69-b25a-288780da1894">
<property name="net.sf.jasperreports.export.xls.one.page.per.sheet" value="true"/>
<property name="ireport.zoom" value="0.75"/>
<property name="ireport.x" value="0"/>
<property name="ireport.y" value="0"/>
<property name="net.sf.jasperreports.export.xls.sheet.names.1" value="Broker"/>
<property name="net.sf.jasperreports.page.break.no.pagination" value="apply"/>
<property name="net.sf.jasperreports.export.xls.sheet.names.2" value="PMA"/>

<parameter name="EffDate" class="java.util.Date">
    <defaultValueExpression><![CDATA[]]></defaultValueExpression>
</parameter>
<parameter name="SUBREPORT_DIR" class="java.lang.String" isForPrompting="false">
    <defaultValueExpression><![CDATA[]]></defaultValueExpression>
</parameter>
<queryString>


<![CDATA[SET LANGUAGE ENGLISH;
Declare     @CurrentDay smalldatetime = NULL

select @CurrentDay =$P{EffDate}
select @CurrentDay as effective_date]]>
    </queryString>
    <field name="effective_date" class="java.sql.Timestamp"/>
    <group name="subreport1">
        <groupExpression><![CDATA[dummy1]]></groupExpression>
        <groupHeader>
            <band height="152">
                <subreport>
                    <reportElement uuid="22345c46-948d-435f-99de-dbec39e586e2" x="-20" y="0" width="1310" height="134"/>
                    <subreportParameter name="EffDate">
                        <subreportParameterExpression><![CDATA[$P{EffDate}]]></subreportParameterExpression>
                    </subreportParameter>
                    <connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression>
                    <subreportExpression><![CDATA[$P{SUBREPORT_DIR} + "/BrokerDayOverDayReconciliation.jasper"]]></subreportExpression>
                </subreport>
                <break>
                    <reportElement uuid="c23c613d-444f-4b61-84f2-ee14d39df15c" x="0" y="140" width="100" height="1"/>
                </break>
            </band>
        </groupHeader>
    </group>
    <group name="subreport2">
        <groupExpression><![CDATA[dummy1]]></groupExpression>
        <groupHeader>
            <band height="115">
                <subreport>
                    <reportElement uuid="1a03a62a-4372-4a42-8910-bd8e79cfb318" x="-20" y="0" width="1310" height="115"/>
                    <subreportParameter name="EffDate">
                        <subreportParameterExpression><![CDATA[$P{EffDate}]]></subreportParameterExpression>
                    </subreportParameter>
                    <connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression>
                    <subreportExpression><![CDATA[$P{SUBREPORT_DIR} + "/PMADayOverDayReconciliation.jasper"]]></subreportExpression>
                </subreport>
            </band>
        </groupHeader>
    </group>
</jasperReport>

PS : I've used all related or similar question to mine in building this report , and as indicated it worked in local using iReport but not when I try to run the report on the web application

Also please find bellow my Java code part of Excel export

 JRXlsExporter exporterXLS = new JRXlsExporter();

         exporterXLS.setParameter(JRXlsExporterParameter.JASPER_PRINT, jasperPrint);
         exporterXLS.setParameter(JRXlsExporterParameter.OUTPUT_STREAM, outputByte);
         exporterXLS.setParameter(JRXlsExporterParameter.IS_ONE_PAGE_PER_SHEET, Boolean.TRUE);
        //               exporterXLS.setParameter(JRXlsExporterParameter.IS_AUTO_DETECT_CELL_TYPE, Boolean.TRUE);
         exporterXLS.setParameter(JRXlsExporterParameter.IS_WHITE_PAGE_BACKGROUND, Boolean.FALSE);
         exporterXLS.setParameter(JRXlsExporterParameter.IS_REMOVE_EMPTY_SPACE_BETWEEN_ROWS, Boolean.TRUE);
         exporterXLS.setParameter(JRXlsExporterParameter.IS_REMOVE_EMPTY_SPACE_BETWEEN_COLUMNS, Boolean.TRUE);
         exporterXLS.setParameter(JExcelApiExporterParameter.IS_DETECT_CELL_TYPE,Boolean.TRUE);
         exporterXLS.exportReport();

Also, I tried to execute the same report using a newer version of ireport (specifically 5.0.0) and it is bringing the 2 sheets data into the same one (the result is an excel file with a unique tab combining the 2 subreports data extract). Please help me what I'm missing here .


Answer:

My Solution was to set "ignorePagination" to false (I know this could cause some issues since it could create more sheets than wanted) and to increase the height of the 2 subreports and the two bands that contain them.This could not be a stable solution but for know it is working as a charm .

<?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="reportGlobalDOD" language="groovy" pageWidth="1600" pageHeight="15000" columnWidth="1560" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="3eb83bbf-8ce6-4d69-b25a-288780da1894">
	<property name="net.sf.jasperreports.export.xls.one.page.per.sheet" value="true"/>
	<property name="net.sf.jasperreports.page.break.no.pagination" value="apply"/>
	<property name="ireport.zoom" value="0.75"/>
	<property name="ireport.x" value="0"/>
	<property name="ireport.y" value="0"/>
	<property name="net.sf.jasperreports.export.xls.sheet.names.1" value="Broker"/>
	<property name="net.sf.jasperreports.export.xls.sheet.names.2" value="PMA"/>
	<parameter name="EffDate" class="java.util.Date">
		<defaultValueExpression><![CDATA[]]></defaultValueExpression>
	</parameter>
	<parameter name="SUBREPORT_DIR" class="java.lang.String" isForPrompting="false">
		<defaultValueExpression><![CDATA["C:\\Users\\user1\\Documents\\Reports\\"]]></defaultValueExpression>
	</parameter>
	<queryString>
		<![CDATA[SET LANGUAGE ENGLISH;
Declare     @CurrentDay smalldatetime = NULL

select @CurrentDay =$P{EffDate}
select @CurrentDay as effective_date]]>
	</queryString>
	<field name="effective_date" class="java.sql.Timestamp"/>
	<group name="subreport1" isStartNewPage="true">
		<groupExpression><![CDATA[dummy1]]></groupExpression>
		<groupHeader>
			<band height="300">
				<subreport isUsingCache="false" runToBottom="false">
					<reportElement uuid="22345c46-948d-435f-99de-dbec39e586e2" x="-20" y="0" width="1599" height="299"/>
					<subreportParameter name="EffDate">
						<subreportParameterExpression><![CDATA[$P{EffDate}]]></subreportParameterExpression>
					</subreportParameter>
					<connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression>
					<subreportExpression><![CDATA[$P{SUBREPORT_DIR} + "/BrokerDayOverDayReconciliation.jasper"]]></subreportExpression>
				</subreport>
				<break>
					<reportElement uuid="c23c613d-444f-4b61-84f2-ee14d39df15c" x="0" y="299" width="138" height="1">
						<property name="net.sf.jasperreports.export.xls.break.after.row" value="true"/>
					</reportElement>
				</break>
			</band>
		</groupHeader>
	</group>
	<group name="subreport2" isStartNewPage="true">
		<groupExpression><![CDATA[dummy1]]></groupExpression>
		<groupHeader>
			<band height="300">
				<break>
					<reportElement uuid="81337467-5a85-43e4-a23a-ba567317e3ce" x="0" y="0" width="100" height="1">
						<property name="net.sf.jasperreports.export.xls.break.before.row" value="true"/>
					</reportElement>
				</break>
				<subreport>
					<reportElement uuid="1a03a62a-4372-4a42-8910-bd8e79cfb318" x="-20" y="1" width="1599" height="299"/>
					<subreportParameter name="EffDate">
						<subreportParameterExpression><![CDATA[$P{EffDate}]]></subreportParameterExpression>
					</subreportParameter>
					<connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression>
					<subreportExpression><![CDATA[$P{SUBREPORT_DIR} + "/PMADayOverDayReconciliation.jasper"]]></subreportExpression>
				</subreport>
			</band>
		</groupHeader>
	</group>
</jasperReport>

Question:

I used this codes to export report as Excel file with JasperReports 4.6:

  File reportFile = new File(externalContextAuthenticationConfiguration.getReportTempFolderUrl());
    File outputFile = File.createTempFile("reportOutput", ".XLS", reportFile);
    JRXlsExporter exporterXLS = new JRXlsExporter();
    exporterXLS.setParameter(JRXlsExporterParameter.JASPER_PRINT, print);
    exporterXLS.setParameter(JRXlsExporterParameter.OUTPUT_FILE, outputFile);
    exporterXLS.setParameter(JRXlsExporterParameter.IS_ONE_PAGE_PER_SHEET, Boolean.FALSE);
    exporterXLS.setParameter(JRXlsExporterParameter.IS_COLLAPSE_ROW_SPAN, Boolean.TRUE);
    exporterXLS.setParameter(JRXlsExporterParameter.IS_WHITE_PAGE_BACKGROUND, Boolean.TRUE);
    exporterXLS.setParameter(JRXlsExporterParameter.IS_REMOVE_EMPTY_SPACE_BETWEEN_ROWS, Boolean.TRUE);
    exporterXLS.setParameter(JRXlsExporterParameter.IS_REMOVE_EMPTY_SPACE_BETWEEN_COLUMNS, Boolean.TRUE);

    exporterXLS.setParameter(JRXlsExporterParameter.CHARACTER_ENCODING, "UTF-8");
    exporterXLS.exportReport();
    return outputFile.getAbsolutePath();

This code is working well in Windows but when the project goes to OpenSuse Linux the created Excel file is like this picture :

Does anybody know what the problem is?


Answer:

Finally I found the problem : The problem was not in JasperReport. It creates the report in Excel extension correctly but when the app server sends it to client the problem happens

We used this code for sending the report to client :

    response.setHeader("Content-Transfer-Encoding", "Cp1256");
    response.setContentType("application/vnd.ms-excel-download");

but application/vnd.ms-excel-download is just for Windows Excel and it destroys the Excel file report in Linux.

Now, I am using this code to zip it instead and then send it to the client so it works both on Windows and Linux server :

        File zipFile = new File(fileName.replace("XLS", "ZIP"));
        FileOutputStream fileOutputStream = new FileOutputStream(zipFile);
        ZipOutputStream zipOutputStream = new ZipOutputStream(fileOutputStream);
        addFileToZip("", fileName, zipOutputStream, false);
        zipOutputStream.flush();
        zipOutputStream.close();

Question:

I have some reports which I export them using JasperReports library. It is all fine except excel files (xls). For example when I try to export an exact report with 751 rows as an excel file, there are no problems and the excel file will be downloaded correctly, but when it exceeds 800 rows, it will show some unrelated characters. The row size mentioned above is just for this report and for other reports the limit is on different numbers. This problem does not exist before I upgrade the libraries from version 2.3 to 6.1.0 and now I tried 6.2.2.

This is how I create my excel file:

    JasperPrint jasperPrint;
    try {
        jasperPrint = getReportPrint(request, baseForm, "excel");
    } catch (ReportNoDataException e) {
        return handleNoData(mapping, request, e);
    }

    try {
        JRXlsExporter jrXlsExporter = new JRXlsExporter();
        try {
            jrXlsExporter.setExporterInput(new SimpleExporterInput(jasperPrint));
            jrXlsExporter.setExporterOutput(new SimpleOutputStreamExporterOutput(response.getOutputStream()));

            SimpleXlsReportConfiguration configuration = new SimpleXlsReportConfiguration();
            configuration.setIgnoreCellBorder(true);
            configuration.setWrapText(true);
            configuration.setWhitePageBackground(false);
            configuration.setRemoveEmptySpaceBetweenColumns(true);
            configuration.setRemoveEmptySpaceBetweenRows(true);
            configuration.setAutoFitPageHeight(true);
            configuration.setDetectCellType(true);
            configuration.setSheetDirection(RunDirectionEnum.RTL);

            jrXlsExporter.setConfiguration(configuration);
            jrXlsExporter.exportReport();
        } catch (IOException e) {
            log.error("IOException occured" + e);
            e.printStackTrace();
        } catch (JRException e) {
            log.error("JRException occured while exporting for xls format", e);
            e.printStackTrace();
        }
    } catch (Exception e) {
        throw e;
    }
    String filename="report";
    response.setHeader("Content-disposition","attachment; filename=\""+filename+".xls\"");
    putSearchForm(request, baseForm);
    return null;

here is the report code:

<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="mtdTankhahEdaratMojoodiExcelReport" pageWidth="595" pageHeight="60" columnWidth="595" leftMargin="0" rightMargin="0" topMargin="0" bottomMargin="0" scriptletClass="report.webapp.tlmReportScriptlet" uuid="4354b1d9-63b2-4236-b190-ac5b2fb0f464">
<property name="com.jaspersoft.studio.unit." value="pixel"/>
<queryString>
    <![CDATA[]]>
</queryString>
<field name="no" class="java.lang.String">
    <fieldDescription><![CDATA[no]]></fieldDescription>
</field>
<field name="baseDate" class="java.lang.String">
    <fieldDescription><![CDATA[baseDate]]></fieldDescription>
</field>
<field name="pardakht" class="java.lang.Double">
    <fieldDescription><![CDATA[pardakht]]></fieldDescription>
</field>
<field name="daryaft" class="java.lang.Double">
    <fieldDescription><![CDATA[daryaft]]></fieldDescription>
</field>
<field name="zinaf" class="java.lang.String">
    <fieldDescription><![CDATA[zinaf]]></fieldDescription>
</field>
<field name="sanadType" class="java.lang.String">
    <fieldDescription><![CDATA[sanadType]]></fieldDescription>
</field>
<variable name="mande" class="java.lang.Double">
    <variableExpression><![CDATA[($F{pardakht}==null || $F{daryaft}==null) ? new Double(0) :
(new Double(($F{daryaft}.doubleValue()-$F{pardakht}.doubleValue())+
$V{mande}.doubleValue()))]]></variableExpression>
    <initialValueExpression><![CDATA[new Double("0")]]></initialValueExpression>
</variable>
<title>
    <band height="30" splitType="Stretch">
        <staticText>
            <reportElement key="staticText-4" x="0" y="0" width="30" height="30" uuid="36cd81f4-073b-4023-b5b8-716d43a4d377">
                <property name="com.jaspersoft.studio.unit.y" value="pixel"/>
                <property name="com.jaspersoft.studio.unit.x" value="pixel"/>
            </reportElement>
            <box>
                <topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
                <leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
                <bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
                <rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
            </box>
            <textElement textAlignment="Center" verticalAlignment="Middle">
                <font fontName="bnazanin" size="12" isBold="true"/>
            </textElement>
            <text><![CDATA[ردیف]]></text>
        </staticText>
        <staticText>
            <reportElement key="staticText-8" x="275" y="0" width="80" height="30" uuid="0827df09-4353-424e-94fa-dd8cc7d5c6ac">
                <property name="com.jaspersoft.studio.unit.width" value="pixel"/>
            </reportElement>
            <box>
                <topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
                <leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
                <bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
                <rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
            </box>
            <textElement textAlignment="Center" verticalAlignment="Middle">
                <font fontName="bnazanin" size="12" isBold="true"/>
            </textElement>
            <text><![CDATA[نوع سند]]></text>
        </staticText>
        <staticText>
            <reportElement key="staticText-9" x="190" y="0" width="85" height="30" uuid="13490ca9-0ba0-4315-b5a0-745ce020f9e1">
                <property name="com.jaspersoft.studio.unit.width" value="pixel"/>
            </reportElement>
            <box>
                <topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
                <leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
                <bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
                <rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
            </box>
            <textElement textAlignment="Center" verticalAlignment="Middle">
                <font fontName="bnazanin" size="12" isBold="true"/>
            </textElement>
            <text><![CDATA[تاریخ سند]]></text>
        </staticText>
        <staticText>
            <reportElement key="staticText-12" x="110" y="0" width="80" height="30" uuid="544625a2-83d3-405d-a117-c0fa6820813a">
                <property name="com.jaspersoft.studio.unit.width" value="pixel"/>
            </reportElement>
            <box>
                <topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
                <leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
                <bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
                <rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
            </box>
            <textElement textAlignment="Center" verticalAlignment="Middle">
                <font fontName="bnazanin" size="12" isBold="true"/>
            </textElement>
            <text><![CDATA[شماره سند]]></text>
        </staticText>
        <staticText>
            <reportElement key="staticText-14" x="355" y="0" width="80" height="30" uuid="fc76869a-b6b1-4223-8ab3-26a870a23e08">
                <property name="com.jaspersoft.studio.unit.width" value="pixel"/>
            </reportElement>
            <box>
                <topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
                <leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
                <bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
                <rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
            </box>
            <textElement textAlignment="Center" verticalAlignment="Middle">
                <font fontName="bnazanin" size="12" isBold="true"/>
            </textElement>
            <text><![CDATA[دریافت]]></text>
        </staticText>
        <staticText>
            <reportElement key="staticText-14" x="515" y="0" width="80" height="30" uuid="f53ebc1b-eaaa-487b-bd23-ee62a1534caa"/>
            <box>
                <topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
                <leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
                <bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
                <rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
            </box>
            <textElement textAlignment="Center" verticalAlignment="Middle">
                <font fontName="bnazanin" size="12" isBold="true"/>
            </textElement>
            <text><![CDATA[مانده]]></text>
        </staticText>
        <staticText>
            <reportElement key="staticText-14" x="435" y="0" width="80" height="30" uuid="aa3bda32-9b4c-4e53-927a-f6750256fb51"/>
            <box>
                <topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
                <leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
                <bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
                <rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
            </box>
            <textElement textAlignment="Center" verticalAlignment="Middle">
                <font fontName="bnazanin" size="12" isBold="true"/>
            </textElement>
            <text><![CDATA[پرداخت]]></text>
        </staticText>
        <staticText>
            <reportElement key="staticText-12" x="30" y="0" width="80" height="30" uuid="b8c6b055-1b5e-4923-954b-a4eed5bab543">
                <property name="com.jaspersoft.studio.unit.width" value="pixel"/>
            </reportElement>
            <box>
                <topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
                <leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
                <bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
                <rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
            </box>
            <textElement textAlignment="Center" verticalAlignment="Middle">
                <font fontName="bnazanin" size="12" isBold="true"/>
            </textElement>
            <text><![CDATA[ذینفع]]></text>
        </staticText>
    </band>
</title>
<detail>
    <band height="30" splitType="Stretch">
        <property name="com.jaspersoft.studio.unit.height" value="pixel"/>
        <textField isStretchWithOverflow="true" isBlankWhenNull="true">
            <reportElement key="textField-1" stretchType="RelativeToTallestObject" x="0" y="0" width="30" height="30" isPrintWhenDetailOverflows="true" uuid="263cfc8f-7812-4797-843c-42fe00045f98">
                <property name="com.jaspersoft.studio.unit.width" value="pixel"/>
            </reportElement>
            <box>
                <topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
                <leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
                <bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
                <rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
            </box>
            <textElement textAlignment="Center" verticalAlignment="Middle">
                <font fontName="bnazanin" size="12"/>
            </textElement>
            <textFieldExpression><![CDATA[$V{REPORT_COUNT}]]></textFieldExpression>
        </textField>
        <textField isStretchWithOverflow="true" isBlankWhenNull="true">
            <reportElement key="textField-5" stretchType="RelativeToTallestObject" x="355" y="0" width="80" height="30" isPrintWhenDetailOverflows="true" uuid="baaa6584-8b9d-4523-a698-a68d065b8892"/>
            <box>
                <topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
                <leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
                <bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
                <rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
            </box>
            <textElement textAlignment="Center" verticalAlignment="Middle">
                <font fontName="bnazanin" size="12"/>
            </textElement>
            <textFieldExpression><![CDATA[($F{daryaft} == null)? new Double("0"): $F{daryaft}]]></textFieldExpression>
        </textField>
        <textField isStretchWithOverflow="true" isBlankWhenNull="true">
            <reportElement key="textField-6" stretchType="RelativeToTallestObject" x="190" y="0" width="85" height="30" isPrintWhenDetailOverflows="true" uuid="073a34ff-2e81-4265-a580-beb8fb293ae0"/>
            <box>
                <topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
                <leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
                <bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
                <rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
            </box>
            <textElement textAlignment="Center" verticalAlignment="Middle">
                <font fontName="bnazanin" size="12"/>
            </textElement>
            <textFieldExpression><![CDATA[($F{baseDate} == null)? "---" : $F{baseDate}]]></textFieldExpression>
        </textField>
        <textField isStretchWithOverflow="true" isBlankWhenNull="true">
            <reportElement key="textField-9" stretchType="RelativeToTallestObject" x="110" y="0" width="80" height="30" isPrintWhenDetailOverflows="true" uuid="e1c072ca-5d06-453c-a739-c797507a92b0"/>
            <box>
                <topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
                <leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
                <bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
                <rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
            </box>
            <textElement textAlignment="Center" verticalAlignment="Middle">
                <font fontName="bnazanin" size="12"/>
            </textElement>
            <textFieldExpression><![CDATA[($F{no} == null)? "---" : $F{no}]]></textFieldExpression>
        </textField>
        <textField isStretchWithOverflow="true" isBlankWhenNull="true">
            <reportElement key="textField-11" stretchType="RelativeToTallestObject" x="435" y="0" width="80" height="30" isPrintWhenDetailOverflows="true" uuid="a5778bf8-71da-4c84-82b8-219f27f04065"/>
            <box>
                <topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
                <leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
                <bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
                <rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
            </box>
            <textElement textAlignment="Center" verticalAlignment="Middle">
                <font fontName="bnazanin" size="12"/>
            </textElement>
            <textFieldExpression><![CDATA[($F{pardakht} == null)? new Double("0"): $F{pardakht}]]></textFieldExpression>
        </textField>
        <textField isStretchWithOverflow="true" isBlankWhenNull="true">
            <reportElement key="textField-11" stretchType="RelativeToTallestObject" x="515" y="0" width="80" height="30" isPrintWhenDetailOverflows="true" uuid="a762bdfa-4d3f-4975-b70d-d25875d15af8"/>
            <box>
                <topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
                <leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
                <bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
                <rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
            </box>
            <textElement textAlignment="Center" verticalAlignment="Middle">
                <font fontName="bnazanin" size="12"/>
            </textElement>
            <textFieldExpression><![CDATA[($V{mande} == null)? new Double("0"): $V{mande}]]></textFieldExpression>
        </textField>
        <textField isStretchWithOverflow="true" isBlankWhenNull="true">
            <reportElement key="textField-5" stretchType="RelativeToTallestObject" x="275" y="0" width="80" height="30" isPrintWhenDetailOverflows="true" uuid="d8c12462-e172-47eb-a301-905e356465b2"/>
            <box>
                <topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
                <leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
                <bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
                <rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
            </box>
            <textElement textAlignment="Center" verticalAlignment="Middle">
                <font fontName="bnazanin" size="12"/>
            </textElement>
            <textFieldExpression><![CDATA[($F{sanadType} == null)? "---": $F{sanadType}]]></textFieldExpression>
        </textField>
        <textField isStretchWithOverflow="true" isBlankWhenNull="true">
            <reportElement key="textField-9" stretchType="RelativeToTallestObject" x="30" y="0" width="80" height="30" isPrintWhenDetailOverflows="true" uuid="7eae6f3a-173e-479d-90d7-38b6c5a5b99f"/>
            <box>
                <topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
                <leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
                <bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
                <rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
            </box>
            <textElement textAlignment="Center" verticalAlignment="Middle">
                <font fontName="bnazanin" size="12"/>
            </textElement>
            <textFieldExpression><![CDATA[($F{zinaf} == null)? "---" : $F{zinaf}]]></textFieldExpression>
        </textField>
    </band>
</detail>
</jasperReport>

and here is the screenshot from the report:

Is there any idea how can I fix the problem?


Answer:

The output you are looking at is the Excel output. This is what an Excel file looks like if you open it in a text editor (I took a random XLS file and opened it in Notepad++):

This means, that your response is not providing the content as a Excel file but as a direct download, which is shown directly in the Browser.

Try adding additional parameters to your response (from here and set to fit Java) to be more explicit:

response.setHeader("Content-Type:   application/vnd.ms-excel; charset=utf-8");
response.setHeader("Expires: 0");
response.setHeader("Cache-Control: must-revalidate, post-check=0, pre-check=0");
response.setHeader("Cache-Control: private");

EDIT

If this happens to bigger files, take a look at this answer which describes the same problem. In general it says, you should send response headers as early as possible and then afterwards generate the report file.

Put short: You should first write all contents to the header first (I suspect you do have more on response higher up) and then try to generate the file. So the browser gets a result, sees that an application file is coming and will provide a download. Otherwise, as far as I understand it, the response header is set after the file is generated - which could be too long for the browser to wait for the Content-Type.

Question:

Im pretty pretty new to Dynamic-Jasper, but due to work i had to add a new feature to our already implemented solution.

My Problem

The Goal is to add a Column to a report that consists only out of a background-color based on some Information. I managed to do that, but while testing I stumbled upon a Problem. While all my Columns in the html and pdf view had the right color, the Excel one only colored the fields in the last Color.

While debugging i noticed, that the same colored Fields had the same templateId, but while all Views run through mostly the same Code the Excel one showed different behavior and had the same ID in all fields.

My Code where I manipulate the template

for(JRPrintElement elemt : jasperPrint.getPages().get(0).getElements()) {
         if(elemt instanceof JRTemplatePrintText) {
                   JRTemplatePrintText text = (JRTemplatePrintText) elemt;
                   (...)
                   if (text.getFullText().startsWith("COLOR_IDENTIFIER")) {
                            String marker = text.getFullText().substring(text.getFullText().indexOf('#') + 1);
                            text.setText("ID = " + ((JRTemplatePrintText) elemt).getTemplate().getId());
                            int rgb = TypeConverter.string2int(Integer.parseInt(marker, 16) + "", 0);
                            ((JRTemplatePrintText) elemt).getTemplate().setBackcolor(new Color(rgb));
                   }
         }
}

The html view

The Excel view

Temporary Conclusion

The same styles uses the same Objects in the background and the JR-Excel export messes something up by assigning the same Object to all the Fields that I manipulated there. If anyone knows of a misstake by me or potential Solutions to change something different to result the same thing please let me know.

Something different I tried earlier, was trying to set the field in an evaluate Method that was called by Jasper. In that method we assign the textvalue of each field. It contained a map with JRFillFields, but unfortunatelly the Map-Implementation denied access to them and just retuned the Value of those. The map was provided by dj and couldn't be switched with a different one.

Edit

We are using JasperReports 6.7.1


Answer:

I found a Solution, where I replaced each template with a new one that was supposed to look exactly alike. That way every Field has its own ID guaranteed and its not up to chance, how JasperReports handles its Data internaly.

JRTemplateElement custom = 
   new JRTemplateText(((JRTemplatePrintText) elemt).getTemplate().getOrigin(), 
   ((JRTemplatePrintText) elemt).getTemplate().getDefaultStyleProvider());
custom.setBackcolor(new Color(rgb));
custom.setStyle(((JRTemplatePrintText) elemt).getTemplate().getStyle());
((JRTemplatePrintText) elemt).setTemplate(custom);

Question:

I am trying to export jasperreport to xls format but failed.

following are my code:

<%@page import="net.sf.jasperreports.engine.export.JRXlsExporterParameter"%>
<%@page import="net.sf.jasperreports.engine.export.JRXlsExporter"%>
<%@page import="net.sf.jasperreports.engine.export.JRPdfExporter"%>
<%@page import="net.sf.jasperreports.engine.export.JRPdfExporterParameter"%>
<%@page import="net.sf.jasperreports.engine.JRExporter"%>
<%@page import="net.sf.jasperreports.engine.JRExporterParameter"%>
<%@page import="net.sf.jasperreports.engine.JasperFillManager"%>
<%@page import="net.sf.jasperreports.engine.JasperPrint"%>
 <%
    String RptName = (request.getParameter("RptName")) != null? request.getParameter("RptName"):"";    
    String iReportPath = utilHandler.GetResBundle("IREPORT_PATH");
    String jasperReportPath    = iReportPath + RptName + ".jasper"; 
    response.setContentType("application/"+strReportFormat);
    os = response.getOutputStream();

    if(strReportFormat.equals("pdf")) {
    exporter = new JRPdfExporter();    
    exporter.setParameter(JRPdfExporterParameter.CHARACTER_ENCODING, "UTF-8");    
    print = JasperFillManager.fillReport(jasperReportPath,map, connection);
    exporter.setParameter(JRExporterParameter.JASPER_PRINT,print);     
    exporter.setParameter(JRExporterParameter.OUTPUT_STREAM,os);    
    exporter.exportReport();    

    } else if(strReportFormat.equals("msexcel")) {
      strReportFileFormat = "xls";
      response.setHeader("Content-Disposition"," inline; filename="+RptName+"."+strReportFileFormat);      
      exporter = new JRXlsExporter();
      exporter.setParameter(JRXlsExporterParameter.JASPER_PRINT, print);
      exporter.setParameter(JRXlsExporterParameter.OUTPUT_STREAM, os);
      exporter.setParameter(JRXlsExporterParameter.IS_WHITE_PAGE_BACKGROUND,Boolean.FALSE);
      exporter.setParameter(JRXlsExporterParameter.IS_REMOVE_EMPTY_SPACE_BETWEEN_ROWS,Boolean.TRUE);
      exporter.setParameter(JRXlsExporterParameter.IS_REMOVE_EMPTY_SPACE_BETWEEN_COLUMNS,Boolean.TRUE);
      exporter.setParameter(JRXlsExporterParameter.IS_COLLAPSE_ROW_SPAN,Boolean.TRUE);
      exporter.setParameter(JRXlsExporterParameter.IGNORE_PAGE_MARGINS,Boolean.TRUE);
      exporter.setParameter(JRXlsExporterParameter.IS_ONE_PAGE_PER_SHEET,Boolean.FALSE);
      System.out.println("generate Excel");
        print = JasperFillManager.fillReport(jasperReportPath,map, connection);
        exporter.setParameter(JRExporterParameter.JASPER_PRINT,print);     
        exporter.setParameter(JRExporterParameter.OUTPUT_STREAM,os);    
        exporter.exportReport();      
    }
 %>

i get Failed - Network error on chrome browser,

i use the .jasper file to produce pdf and excel, xls are failed but PDF work fine and display fine on chrome browser. i checked the tomcat 8 log, could not find any error log on catalina.out and also the tomcat console (windows bat). Any idea what could be the problem?


Answer:

the response Content-Disposition is wrong, i replaced to:

response.setHeader("Content-Disposition", "attachment;filename=\"" + new String(RptName.getBytes("utf-8"),"ISO-8859-1") + ".pdf\"");
response.setHeader("Content-Disposition", "attachment;filename=\"" + new String(RptName.getBytes("utf-8"),"ISO-8859-1") + ".xls\"");

for both pdf and xls, now working fine.

Question:

How to set Excel print page margins when using JRParameter.IS_IGNORE_PAGINATION?

My Jasper-Report contains a header and a table with many rows. It is created with the setting JRParameter.IS_IGNORE_PAGINATION ...

    params.put(JRParameter.IS_IGNORE_PAGINATION, Boolean.TRUE);
    return JasperFillManager.fillReport(inputStream, params, new JRBeanCollectionDataSource(myDtoList));

... and then exported via JRXlsExporter.

The result is how I want it - an Excel sheet with one heading and a table without any margins or headings in between.

But when printing the sheet, there are no margins on the paper. The table starts right at the top edge.

How to set the margins for printing in this case?

EDIT: the setting of JasperPrint.setTopMargin, JasperPrint.setBottomMargin seems to be ignored by Jasper in this special case.


Answer:

You need to understand that once you take an export of your report, jasper server no longer has control over the exported file. So ideally you should try to set the page margin before calling export.

You can set the page margins on JasperDesign object (before compiling it) or on the JasperPrint object using setLeftMargin, setRightMargin, setTopMargin, setBottomMargin methods.

Code sample with JasperPrint:

JasperPrint jasperPrint = JasperFillManager.fillReport(inputStream, params, new JRBeanCollectionDataSource(myDtoList));
jasperPrint.setLeftMargin(20);
jasperPrint.setRightMargin(20);
jasperPrint.setTopMargin(20);
jasperPrint.setBottomMargin(20);
exporter.setParameter(JRExporterParameter.JASPER_PRINT, jasperPrint);
exporter.setParameter(JRExporterParameter.OUTPUT_STREAM, stream);
exporter.exportReport();

Question:

I am not sure why this is happening but timezone and all settings are correct but the datetime which is in datebase is not getting reflected over report and showing something different.

Note : This excel report is getting exported by jasper report tool.

jrxml

<?xml version="1.0" encoding="UTF-8"  ?>
<!-- Created with iReport - A designer for JasperReports -->
<!DOCTYPE jasperReport PUBLIC "//JasperReports//DTD Report Design//EN" "http://jasperreports.sourceforge.net/dtds/jasperreport.dtd">
<jasperReport
         name="groundingReport"
         columnCount="1"
         printOrder="Vertical"
         orientation="Landscape"
         pageWidth="3600"
         pageHeight="842"
         columnWidth="3600"
         columnSpacing="0"
         leftMargin="0"
         rightMargin="0"
         topMargin="0"
         bottomMargin="0"
         whenNoDataType="AllSectionsNoDetail"
         isTitleNewPage="false"
         isSummaryNewPage="false"
         isIgnorePagination="true">

    <property name="ireport.encoding" value="UTF-8" />
    <import value="java.util.*" />
    <import value="net.sf.jasperreports.engine.*" />
    <import value="net.sf.jasperreports.engine.data.*" />
    <parameter name="SIMPLE_DATE_FORMAT_WITH_TIME_ZONE" isForPrompting="false" class="com.adesa.framework.report.util.SimpleDateFormatWithTimeZone">
        <defaultValueExpression ><![CDATA[new com.adesa.framework.report.util.SimpleDateFormatWithTimeZone("MM/dd/yyyy HH:mm:ss a", TimeZone.getTimeZone("US/Eastern"))]]></defaultValueExpression>
    </parameter>
    <parameter name="INSPECTION_PROVIDER" isForPrompting="false" class="java.lang.String"/>

    <field name="inspectionOrderedDate" class="java.sql.Timestamp"/>
    <field name="inspectionQCCompleteDate" class="java.sql.Timestamp"/>
    <field name="inspectionCompleteDate" class="java.sql.Timestamp"/>
    <field name="inspectionProvider" class="java.lang.String"/>

    <variable name="Test" class="java.lang.String" resetType="Report" calculation="Nothing">
    </variable>
        <background>
            <band height="0"  isSplitAllowed="true" >
            </band>
        </background>
        <title>
            <band height="50"  isSplitAllowed="true" >
            </band>
        </title>
        <pageHeader>
            <band height="75"  isSplitAllowed="true" >
                <textField isStretchWithOverflow="true" isBlankWhenNull="true" evaluationTime="Now" hyperlinkType="None"  hyperlinkTarget="Self" >

                <textFieldExpression   class="java.lang.String"><![CDATA[$P{JRXML_DT_FRM}]]></textFieldExpression>
                </textField>
                <textField isStretchWithOverflow="true" isBlankWhenNull="true" evaluationTime="Now" hyperlinkType="None"  hyperlinkTarget="Self" >
                    <reportElement
                        mode="Transparent"
                        x="162"
                        y="30"
                        width="162"
                        height="15"
                        key="textField-40"
                        stretchType="RelativeToTallestObject"
                        positionType="Float"
                        isPrintWhenDetailOverflows="true"/>
                    <box>                   <pen lineWidth="0.0"/>
                    <topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
                    <leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
                    <bottomPen lineWidth="0.0" lineColor="#000000"/>
                    <rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
                    <textElement verticalAlignment="Bottom">
                        <font fontName="Arial" pdfFontName="Helvetica" isItalic="false"/>
                    </textElement>
                <textFieldExpression   class="java.lang.String"><![CDATA[$P{JRXML_DT_TO}]]></textFieldExpression>
                </textField>
                <staticText>

                    <textElement textAlignment="Left" verticalAlignment="Bottom">
                        <font fontName="Arial" pdfFontName="Helvetica-Bold" isBold="true"/>
                    </textElement>
                <text><![CDATA[Inspection Provider]]></text>
                </staticText>
                <textField isStretchWithOverflow="true" isBlankWhenNull="true" evaluationTime="Now" hyperlinkType="None"  hyperlinkTarget="Self" >
                    <textElement verticalAlignment="Bottom">
                        <font fontName="Arial" pdfFontName="Helvetica" isItalic="false"/>
                    </textElement>
                <textFieldExpression   class="java.lang.String"><![CDATA[$P{INSPECTION_PROVIDER}.equals("AUTOVIN LIVE") ? "AutoVIN Live" : $P{INSPECTION_PROVIDER}]]></textFieldExpression>
                </textField>
            </band>
        </pageHeader>
        <columnHeader>
            <band height="24"  isSplitAllowed="true" >
                    <textElement textAlignment="Center" verticalAlignment="Top">
                        <font fontName="Arial" pdfFontName="Helvetica-Bold" isBold="true"/>
                    </textElement>
                <text><![CDATA[Inspection Provider]]></text>
                </staticText>
                <staticText>
                    <reportElement
                        mode="Opaque"
                        x="162"
                        y="0"
                        width="162"
                        height="24"
                        backcolor="#CCCCFF"
                        key="staticText-2"/>
                    <box>                   <pen lineWidth="0.0"/>
                    <topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
                    <leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
                    <bottomPen lineWidth="0.0" lineColor="#000000"/>
                    <rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
</box>
                    <textElement textAlignment="Center" verticalAlignment="Top">
                        <font fontName="Arial" pdfFontName="Helvetica-Bold" isBold="true"/>
                    </textElement>
                <text><![CDATA[VIN]]></text>
                </staticText>
                <staticText>
                    <textElement textAlignment="Center" verticalAlignment="Top">
                        <font fontName="Arial" pdfFontName="Helvetica-Bold" isBold="true"/>
                    </textElement>
                <text><![CDATA[Inspection Ordered]]></text>
                </staticText>
                <staticText>
                    <textElement textAlignment="Center" verticalAlignment="Top">
                        <font fontName="Arial" pdfFontName="Helvetica-Bold" isBold="true"/>
                    </textElement>
                <text><![CDATA[Inspection Complete]]></text>
                </staticText>
                <staticText>
                    <textElement textAlignment="Center" verticalAlignment="Top">
                        <font fontName="Arial" pdfFontName="Helvetica-Bold" isBold="true"/>
                    </textElement>
                <text><![CDATA[ Days to QC Complete]]></text>
                </staticText>
                <staticText>
                    <textElement textAlignment="Center" verticalAlignment="Top">
                        <font fontName="Arial" pdfFontName="Helvetica-Bold" isBold="true"/>
                    </textElement>
                <text><![CDATA[Inspection Incomplete]]></text>
                </staticText>
                <staticText>
                    <textElement textAlignment="Center" verticalAlignment="Top">
                        <font fontName="Arial" pdfFontName="Helvetica-Bold" isBold="true"/>
                    </textElement>
                <text><![CDATA[Incomplete Inspection Reason]]></text>
                </staticText>
                <staticText>
                    <textElement textAlignment="Center" verticalAlignment="Top">
                        <font fontName="Arial" pdfFontName="Helvetica-Bold" isBold="true"/>
                    </textElement>
                <text><![CDATA[QC Complete]]></text>
                </staticText>
                <staticText>
                    <textElement textAlignment="Center" verticalAlignment="Top">
                        <font fontName="Arial" pdfFontName="Helvetica-Bold" isBold="true"/>
                    </textElement>
                <text><![CDATA[Inspection Not Inspected ]]></text>
                </staticText>
                <staticText>
                    <textElement textAlignment="Center" verticalAlignment="Top">
                        <font fontName="Arial" pdfFontName="Helvetica-Bold" isBold="true"/>
                    </textElement>
                <text><![CDATA[Inspection ID]]></text>
                </staticText>
                <staticText>

                    <textElement textAlignment="Center" verticalAlignment="Top">
                        <font fontName="Arial" pdfFontName="Helvetica-Bold" isBold="true"/>
                    </textElement>
                <text><![CDATA[Inspection Cancelled]]></text>
                </staticText>
            </band>
        </columnHeader>
        <detail>
            <band height="15"  isSplitAllowed="true" >
                <textField isStretchWithOverflow="true" isBlankWhenNull="true" evaluationTime="Now" hyperlinkType="None"  hyperlinkTarget="Self" >

                    <textElement verticalAlignment="Bottom">
                        <font fontName="Arial" pdfFontName="Helvetica" isItalic="false"/>
                    </textElement>
                <textFieldExpression   class="java.lang.String"><![CDATA[$F{inspectionProvider}]]></textFieldExpression>
                </textField>
                <textField isStretchWithOverflow="true" isBlankWhenNull="true" evaluationTime="Now" hyperlinkType="None"  hyperlinkTarget="Self" >

                    <textElement verticalAlignment="Bottom">
                        <font fontName="Arial" pdfFontName="Helvetica" isItalic="false"/>
                    </textElement>
                <textFieldExpression   class="java.lang.String"><![CDATA[$F{vin}]]></textFieldExpression>
                </textField>
                <textField isStretchWithOverflow="true" pattern="MM/dd/yyyy hh:mm:ss a" isBlankWhenNull="true" evaluationTime="Now" hyperlinkType="None"  hyperlinkTarget="Self" >

                    <textElement textAlignment="Center" verticalAlignment="Bottom">
                        <font fontName="Arial" pdfFontName="Helvetica" isItalic="false"/>
                    </textElement>
                <textFieldExpression   class="java.util.Date"><![CDATA[new SimpleDateFormat("MM/dd/yyyy hh:mm:ss a").parse($P{SIMPLE_DATE_FORMAT_WITH_TIME_ZONE}.format($F{inspectionOrderedDate}))]]></textFieldExpression>
                </textField>
                <textField isStretchWithOverflow="true" pattern="MM/dd/yyyy hh:mm:ss a" isBlankWhenNull="true" evaluationTime="Now" hyperlinkType="None"  hyperlinkTarget="Self" >

                    <textElement textAlignment="Center" verticalAlignment="Bottom">
                        <font fontName="Arial" pdfFontName="Helvetica" isItalic="false"/>
                    </textElement>
                <textFieldExpression   class="java.util.Date"><![CDATA[new SimpleDateFormat("MM/dd/yyyy HH:mm:ss a").parse($P{SIMPLE_DATE_FORMAT_WITH_TIME_ZONE}.format($F{inspectionCompleteDate}))]]></textFieldExpression>
                </textField>
                <textField isStretchWithOverflow="true" pattern="###0;-###0" isBlankWhenNull="true" evaluationTime="Now" hyperlinkType="None"  hyperlinkTarget="Self" >

                    <textElement textAlignment="Center" verticalAlignment="Bottom">
                        <font fontName="Arial" pdfFontName="Helvetica" isItalic="false"/>
                    </textElement>
                <textFieldExpression   class="java.lang.Integer"><![CDATA[$F{daysToQCComplete} != null ? Integer.valueOf($F{daysToQCComplete}): null]]></textFieldExpression>
                </textField>
                <textField isStretchWithOverflow="true" isBlankWhenNull="true" evaluationTime="Now" hyperlinkType="None"  hyperlinkTarget="Self" >

                    <textElement verticalAlignment="Bottom">
                        <font fontName="Arial" pdfFontName="Helvetica" isItalic="false"/>
                    </textElement>
                <textFieldExpression   class="java.lang.String"><![CDATA[$P{SIMPLE_DATE_FORMAT_WITH_TIME_ZONE}.format($F{inspectionIncompleteDate})]]></textFieldExpression>
                </textField>
                <textField isStretchWithOverflow="true" isBlankWhenNull="true" evaluationTime="Now" hyperlinkType="None"  hyperlinkTarget="Self" >

                    <textElement verticalAlignment="Bottom">
                        <font fontName="Arial" pdfFontName="Helvetica" isItalic="false"/>
                    </textElement>
                <textFieldExpression   class="java.lang.String"><![CDATA[$F{incompleteInspectionReason}]]></textFieldExpression>
                </textField>
                <textField isStretchWithOverflow="true" pattern="MM/dd/yyyy hh:mm:ss a" isBlankWhenNull="true" evaluationTime="Now" hyperlinkType="None"  hyperlinkTarget="Self" >

                    <textElement textAlignment="Center" verticalAlignment="Bottom">
                        <font fontName="Arial" pdfFontName="Helvetica" isItalic="false"/>
                    </textElement>
                <textFieldExpression   class="java.util.Date"><![CDATA[new SimpleDateFormat("MM/dd/yyyy HH:mm:ss a").parse($P{SIMPLE_DATE_FORMAT_WITH_TIME_ZONE}.format($F{inspectionQCCompleteDate}))]]></textFieldExpression>
                </textField>

            </band>
        </detail>

</jasperReport>

Date is in oracle db :

02-JAN-18 11.22.09 PM

Date is in excel reoprt

1/2/2018 6:22:09 PM

What is the problem with this I really don't know.

But the timezone is correct from excel report perspective as well as db . and the timezone is "US/Eastern" only.

And the columns which are coming wrong are these fields :

inspectionQCCompleteDate, inspectionCompleteDate

please someone help me to figure this out.


Answer:

To resolve the datetime difference, define textFieldExpression for inspectionQCCompleteDate & inspectionCompleteDate column in your jrxml as follows:

 <textFieldExpression class="java.util.Date">
    <![CDATA[$P{SIMPLE_DATE_FORMAT_WITH_TIME_ZONE}.parse($P{SIMPLE_DATE_FORMAT_WITH_TIME_ZONE}.format($F{inspectionQCCompleteDate}))]]>
</textFieldExpression>


<textFieldExpression class="java.util.Date">
    <![CDATA[$P{SIMPLE_DATE_FORMAT_WITH_TIME_ZONE}.parse($P{SIMPLE_DATE_FORMAT_WITH_TIME_ZONE}.format($F{inspectionCompleteDate}))]]>
</textFieldExpression>

Question:

I'm new with Jasper Reports. I'm using 5.6.0 version and I can export some data to PDF and all fields are filled correct but when I try to export the same data to Excel, some fields are missing. The code I use it's the some on both clases (I have one class for each file export)

I also read that the template to export to PDF or Excel it's the same, so I don't know why fills all in PDF and not in Excel.

When I generate the PDF I pass all the data to:

JasperExportManager.exportReportToPdfStream(jasperPrint,fileOutputStream);

And when I try to generate the Excel I call this funcion:

private void generateExcel(JasperPrint jasperPrint) throws IOException, JRException {
    JRXlsExporter exporterXLS = new JRXlsExporter();
    JFileChooser jFileChooser = new JFileChooser(".");
    jFileChooser.setFileSelectionMode(JFileChooser.DIRECTORIES_ONLY);
    int status = jFileChooser.showOpenDialog(SIXACOPWorkbench.getWorkbenchContext().getWorkbench().getFrame());
    if (status == JFileChooser.APPROVE_OPTION) {
        if (jFileChooser.getSelectedFile() != null) {
            String pathPDFs = (jFileChooser.getSelectedFile().getAbsolutePath());
            File ficheroXLS = new File(pathPDFs+"//testing.xls");
            FileOutputStream fos = new FileOutputStream(ficheroXLS);
            exporterXLS.setExporterInput(new SimpleExporterInput(jasperPrint));
            exporterXLS.setExporterOutput(new SimpleOutputStreamExporterOutput(fos));
            SimpleXlsReportConfiguration configuration = new SimpleXlsReportConfiguration();
            configuration.setRemoveEmptySpaceBetweenRows(true);
            configuration.setDetectCellType(true);
            configuration.setWhitePageBackground(false);
            exporterXLS.setConfiguration(configuration);
            exporterXLS.exportReport();     
            fos.close();
        }
    }
}

¿What I'm doing wrong when exporting to Excel?

Regards.

--- Update ---

I thoguht that if the PDF was generating well the Excel should do it but after read Dave Jarvis comment I'm updating with the jrxml:

1 - There are 3 informs: Inform A - Inform B - Inform C. 2 - Inform A has a header and footer and each row it's filled by Inform B. 3 - On Inform B the field that it's nos displayed on Excel it's numberIndex:

<?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="SubinformeAtribucionesPorPropietario1" pageWidth="813" pageHeight="842" columnWidth="813" leftMargin="0" rightMargin="0" topMargin="0" bottomMargin="0" uuid="5af05b05-4f4d-4ff8-8bcb-ef8fa6c2b067">
    <property name="ireport.scriptlethandling" value="0"/>
    <property name="ireport.encoding" value="UTF-8"/>
    <property name="ireport.zoom" value="1.4641000000000008"/>
    <property name="ireport.x" value="178"/>
    <property name="ireport.y" value="0"/>
    <import value="net.sf.jasperreports.engine.*"/>
    <import value="java.util.*"/>
    <import value="net.sf.jasperreports.engine.data.*"/>
    <parameter name="Inform C" class="net.sf.jasperreports.engine.JasperReport" isForPrompting="false"/>
    <parameter name="useNumberIndex" class="java.lang.Boolean" isForPrompting="false"/>
    <field name="fieldA" class="java.util.List"/>
    <field name="fieldB" class="java.lang.String"/>
    <field name="fieldC" class="java.lang.String"/>
    <field name="fieldD" class="java.lang.String"/>
    <field name="fieldF" class="java.lang.String"/>
    <field name="numberIndex" class="java.lang.String"/>
    <background>
        <band splitType="Stretch"/>
    </background>
    <title>
        <band splitType="Stretch"/>
    </title>
    <pageHeader>
        <band splitType="Stretch"/>
    </pageHeader>
    <columnHeader>
        <band splitType="Stretch"/>
    </columnHeader>
    <detail>
        <band height="21" splitType="Stretch">
            <textField pattern="" isBlankWhenNull="false">
                <reportElement key="textField-1" mode="Transparent" x="1" y="1" width="53" height="19" forecolor="#000000" backcolor="#FFFFFF" uuid="974d00c4-7438-4d1f-92ff-6b720e113ecd"/>
                <box>
                    <topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
                    <leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
                    <bottomPen lineWidth="0.0" lineColor="#000000"/>
                    <rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
                </box>
                <textElement textAlignment="Center" verticalAlignment="Middle" rotation="None">
                    <font fontName="Arial" size="7" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
                    <paragraph lineSpacing="Single"/>
                </textElement>
                <textFieldExpression><![CDATA[$P{useNumberIndex}.booleanValue() ? $F{numberIndex} : $F{fieldC}]]></textFieldExpression>
            </textField>
            <textField pattern="" isBlankWhenNull="false">
                <reportElement key="textField-2" mode="Transparent" x="53" y="1" width="297" height="19" forecolor="#000000" backcolor="#FFFFFF" uuid="6dee9995-5734-4e42-b7e1-4c2cbe02739b"/>
                <box>
                    <topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
                    <leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
                    <bottomPen lineWidth="0.0" lineColor="#000000"/>
                    <rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
                </box>
                <textElement textAlignment="Center" verticalAlignment="Middle" rotation="None">
                    <font fontName="Arial" size="7" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
                    <paragraph lineSpacing="Single"/>
                </textElement>
                <textFieldExpression><![CDATA[$F{fieldB}]]></textFieldExpression>
            </textField>
            <subreport isUsingCache="true">
                <reportElement key="subreport-1" mode="Opaque" x="350" y="1" width="463" height="18" forecolor="#000000" backcolor="#FFFFFF" uuid="a40d7385-072d-4cfc-a952-88b23a906b85"/>
                <subreportParameter name="name1">
                    <subreportParameterExpression><![CDATA[$F{fieldD}]]></subreportParameterExpression>
                </subreportParameter>
                <subreportParameter name="name2">
                    <subreportParameterExpression><![CDATA[$F{fieldE}]]></subreportParameterExpression>
                </subreportParameter>
                <subreportParameter name="Inform C">
                    <subreportParameterExpression><![CDATA[$P{Inform C}]]></subreportParameterExpression>
                </subreportParameter>
                <dataSourceExpression><![CDATA[new net.sf.jasperreports.engine.data.JRBeanCollectionDataSource((Collection) $F{fieldA})]]></dataSourceExpression>
                <subreportExpression><![CDATA[$P{Inform C}]]></subreportExpression>
            </subreport>
        </band>
    </detail>
    <columnFooter>
        <band splitType="Stretch"/>
    </columnFooter>
    <pageFooter>
        <band splitType="Stretch"/>
    </pageFooter>
    <summary>
        <band splitType="Stretch"/>
    </summary>
</jasperReport>

4 - On Inform B there is a section filled by Inform C and on Inform C, the field that isn't display it's extension:

<?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="SubinformeAtribucionesPorPropietarios" pageWidth="463" pageHeight="842" columnWidth="463" leftMargin="0" rightMargin="0" topMargin="0" bottomMargin="0" uuid="92989482-f12d-409d-bbbc-5168396c7b22">
    <property name="ireport.scriptlethandling" value="0"/>
    <property name="ireport.encoding" value="UTF-8"/>
    <property name="ireport.zoom" value="1.9487171000000016"/>
    <property name="ireport.x" value="0"/>
    <property name="ireport.y" value="0"/>
    <import value="net.sf.jasperreports.engine.*"/>
    <import value="java.util.*"/>
    <import value="net.sf.jasperreports.engine.data.*"/>
    <parameter name="paramA" class="java.lang.String" isForPrompting="false"/>
    <parameter name="paramB" class="java.lang.String" isForPrompting="false"/>
    <field name="fieldA" class="java.lang.String"/>
    <field name="fieldB" class="java.lang.String"/>
    <field name="fieldC" class="java.lang.String"/>
    <field name="fieldD" class="java.lang.String"/>
    <field name="extension" class="java.lang.String"/>
    <field name="fieldE" class="java.lang.String"/>
    <field name="fieldF" class="java.lang.String"/>
    <background>
        <band splitType="Stretch"/>
    </background>
    <title>
        <band splitType="Stretch"/>
    </title>
    <pageHeader>
        <band splitType="Stretch"/>
    </pageHeader>
    <columnHeader>
        <band splitType="Stretch"/>
    </columnHeader>
    <detail>
        <band height="17" splitType="Stretch">
            <textField pattern="" isBlankWhenNull="false">
                <reportElement key="textField-1" mode="Transparent" x="0" y="0" width="109" height="16" forecolor="#000000" backcolor="#FFFFFF" uuid="4e1802bf-4ad3-4ed8-a378-e451866ef365"/>
                <box>
                    <topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
                    <leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
                    <bottomPen lineWidth="0.0" lineColor="#000000"/>
                    <rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
                </box>
                <textElement textAlignment="Center" verticalAlignment="Middle" rotation="None">
                    <font fontName="Arial" size="7" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
                    <paragraph lineSpacing="Single"/>
                </textElement>
                <textFieldExpression><![CDATA[$F{fieldA}]]></textFieldExpression>
            </textField>
            <textField pattern="" isBlankWhenNull="false">
                <reportElement key="textField-2" mode="Transparent" x="109" y="0" width="71" height="16" forecolor="#000000" backcolor="#FFFFFF" uuid="a87c767c-8b6e-4dcf-b397-2412a752acaa"/>
                <box>
                    <topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
                    <leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
                    <bottomPen lineWidth="0.0" lineColor="#000000"/>
                    <rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
                </box>
                <textElement textAlignment="Center" verticalAlignment="Middle" rotation="None">
                    <font fontName="Arial" size="7" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
                    <paragraph lineSpacing="Single"/>
                </textElement>
                <textFieldExpression><![CDATA[$F{fieldD}]]></textFieldExpression>
            </textField>
            <textField pattern="" isBlankWhenNull="false">
                <reportElement key="textField-3" mode="Transparent" x="180" y="0" width="71" height="16" forecolor="#000000" backcolor="#FFFFFF" uuid="82e3a926-8f8f-41af-b478-db4eed521356"/>
                <box>
                    <topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
                    <leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
                    <bottomPen lineWidth="0.0" lineColor="#000000"/>
                    <rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
                </box>
                <textElement textAlignment="Center" verticalAlignment="Middle" rotation="None">
                    <font fontName="Arial" size="7" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
                    <paragraph lineSpacing="Single"/>
                </textElement>
                <textFieldExpression><![CDATA[$F{fieldC}]]></textFieldExpression>
            </textField>
            <textField pattern="" isBlankWhenNull="false">
                <reportElement key="textField-4" mode="Transparent" x="251" y="0" width="71" height="16" forecolor="#000000" backcolor="#FFFFFF" uuid="b8954bd1-387e-4a5d-a762-9b1c19a86a78"/>
                <box>
                    <topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
                    <leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
                    <bottomPen lineWidth="0.0" lineColor="#000000"/>
                    <rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
                </box>
                <textElement textAlignment="Center" verticalAlignment="Middle" rotation="None">
                    <font fontName="Arial" size="7" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
                    <paragraph lineSpacing="Single"/>
                </textElement>
                <textFieldExpression><![CDATA[$F{fieldB} + $F{fieldF}]]></textFieldExpression>
            </textField>
            <textField pattern="" isBlankWhenNull="false">
                <reportElement key="textField-6" mode="Transparent" x="322" y="0" width="71" height="16" forecolor="#000000" backcolor="#FFFFFF" uuid="80594584-7236-407f-99b1-a5bd8b7cb91f"/>
                <box>
                    <topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
                    <leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
                    <bottomPen lineWidth="0.0" lineColor="#000000"/>
                    <rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
                </box>
                <textElement textAlignment="Center" verticalAlignment="Middle" rotation="None">
                    <font fontName="Arial" size="7" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
                    <paragraph lineSpacing="Single"/>
                </textElement>
                <textFieldExpression><![CDATA[$F{extension}]]></textFieldExpression>
            </textField>
            <textField pattern="" isBlankWhenNull="false">
                <reportElement key="textField-7" mode="Transparent" x="392" y="0" width="71" height="16" forecolor="#000000" backcolor="#FFFFFF" uuid="4792a89e-5852-4fcb-9f34-510d411a3cc3"/>
                <box>
                    <topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
                    <leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
                    <bottomPen lineWidth="0.0" lineColor="#000000"/>
                    <rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
                </box>
                <textElement textAlignment="Center" verticalAlignment="Middle" rotation="None">
                    <font fontName="Arial" size="7" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
                    <paragraph lineSpacing="Single"/>
                </textElement>
                <textFieldExpression><![CDATA[$F{fieldE}]]></textFieldExpression>
            </textField>
        </band>
    </detail>
    <columnFooter>
        <band splitType="Stretch"/>
    </columnFooter>
    <pageFooter>
        <band splitType="Stretch"/>
    </pageFooter>
    <lastPageFooter>
        <band height="22" splitType="Stretch">
            <staticText>
                <reportElement x="221" y="3" width="101" height="17" uuid="45817d74-5748-40d9-ac2a-f07baa316e7e"/>
                <box>
                    <pen lineWidth="0.5"/>
                    <topPen lineWidth="0.5"/>
                    <leftPen lineWidth="0.5"/>
                    <bottomPen lineWidth="0.5"/>
                    <rightPen lineWidth="0.5"/>
                </box>
                <textElement textAlignment="Center" verticalAlignment="Middle">
                    <font fontName="Arial" size="8" isBold="true"/>
                </textElement>
                <text><![CDATA[Text]]></text>
            </staticText>
            <textField pattern="" isBlankWhenNull="false">
                <reportElement key="textField-8" mode="Transparent" x="322" y="3" width="70" height="17" forecolor="#000000" backcolor="#FFFFFF" uuid="2d0cc5e5-1e50-4196-867f-a729d1c5a2f9"/>
                <box>
                    <pen lineWidth="0.5"/>
                    <topPen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                    <leftPen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                    <bottomPen lineWidth="0.5" lineColor="#000000"/>
                    <rightPen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                </box>
                <textElement textAlignment="Center" verticalAlignment="Middle" rotation="None">
                    <font fontName="Arial" size="7" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
                    <paragraph lineSpacing="Single"/>
                </textElement>
                <textFieldExpression><![CDATA[$P{paramA}]]></textFieldExpression>
            </textField>
            <textField pattern="" isBlankWhenNull="false">
                <reportElement key="textField-9" mode="Transparent" x="392" y="3" width="71" height="17" forecolor="#000000" backcolor="#FFFFFF" uuid="19d787cb-e1df-473d-9f52-3c3aa7bed427"/>
                <box>
                    <pen lineWidth="0.5"/>
                    <topPen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                    <leftPen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                    <bottomPen lineWidth="0.5" lineColor="#000000"/>
                    <rightPen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                </box>
                <textElement textAlignment="Center" verticalAlignment="Middle" rotation="None">
                    <font fontName="Arial" size="7" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
                    <paragraph lineSpacing="Single"/>
                </textElement>
                <textFieldExpression><![CDATA[$P{paramB}]]></textFieldExpression>
            </textField>
        </band>
    </lastPageFooter>
    <summary>
        <band splitType="Stretch"/>
    </summary>
</jasperReport>

Answer:

I don't know if was a bug, but I copied the line of other textFieldExpression of the jrxml that was working, rename it to the value that wasn't displaying and now the value it's displayed.

What the hell...

Question:

I have created, using a jasper designer (not iReport but a plugin for eclipse), a report that uses an excel file as datasource. the report works fine, in the designer, and read the data from the file without problems but after compiling the file to file.jasper and giving him the excel file's path nothing apears in the JasperViewer! This is my code:

try{
      Map<String, Object> parameters = new HashMap<String, Object>();
      parameters.put("DataFile", "jasper_export.xls");
      JasperPrint jasperPrint = JasperFillManager.fillReport(new FileInputStream(new File("file.jasper")), parameters,conn);

      JasperViewer jv = new JasperViewer(jasperPrint, false);
      jv.setVisible(true);
    } catch (Exception ex) {
        ex.printStackTrace();
    }

Answer:

SOLUTION: This code woks perfect:

try{
    Map<String, Object> parameters = new HashMap<String, Object>();
    parameters.put("param_name", paramValue);

    ExcelDataSource ds = new ExcelDataSource(JRLoader.getLocationInputStream(excelFilePath));
    String[] columnNames = new String[]{"id", "nom", "iden", "adress", "activity", "compta"};
    ds.setColumnNames(columnNames);
    JasperPrint jasperPrint = JasperFillManager.fillReport(new FileInputStream(new File(yourJasperFilePath)), parameters, ds);
    JasperPrintManager.printReport(jasperPrint, false);
   } catch (Exception ex) {
      ex.printStackTrace();

   }

Question:

I am currently working in JasperReports. I have one method to generate report in PDF format. It is working fine but I want the same report in excel format.

Here is my method:

public void generateFleetReportPdf(List<DailyTrakingReportBean> listOfDailyBean) throws JRException, IOException {
    /*
     Convert List to JRBeanCollectionDataSource 
     */
    JRBeanCollectionDataSource beanCollectionDataSource = new JRBeanCollectionDataSource(listOfDailyBean);

    /* 
        Map to hold Jasper Report parameters
    */

    Map<String,Object> parameters = new HashMap<String, Object>();
    parameters.put("ItemDataSource", beanCollectionDataSource);

    String reportPath = FacesContext.getCurrentInstance().getExternalContext().getRealPath("/reports/sadaqatReport.jasper");
    JasperPrint jasperPrint = JasperFillManager.fillReport(reportPath, parameters, beanCollectionDataSource);
    HttpServletResponse httpServletResponse = (HttpServletResponse) FacesContext.getCurrentInstance().getExternalContext().getResponse();
    httpServletResponse.addHeader("Content-disposition", "attachment; filename=report.pdf");
    ServletOutputStream outputStream = httpServletResponse.getOutputStream();
    JasperExportManager.exportReportToPdfStream(jasperPrint, outputStream);
    FacesContext.getCurrentInstance().responseComplete();
}

Answer:

Please try the below code. It should export the Jasper report to .xlsx format. If you prefer .xls, then replace net.sf.jasperreports.engine.export.ooxml.JRXlsxExporter with net.sf.jasperreports.engine.export.JRXlsExporter

public void generateFleetReportXlsx(List listOfDailyBean) throws JRException, IOException {
    /*
     Convert List to JRBeanCollectionDataSource 
     */
    JRBeanCollectionDataSource beanCollectionDataSource = new JRBeanCollectionDataSource(listOfDailyBean);

    /* 
        Map to hold Jasper Report parameters
    */

    Map<String,Object> parameters = new HashMap<String, Object>();
    parameters.put("ItemDataSource", beanCollectionDataSource);

    String reportPath = FacesContext.getCurrentInstance().getExternalContext().getRealPath("/reports/sadaqatReport.jasper");
    JasperPrint jasperPrint = JasperFillManager.fillReport(reportPath, parameters, beanCollectionDataSource);
    HttpServletResponse httpServletResponse = (HttpServletResponse) FacesContext.getCurrentInstance().getExternalContext().getResponse();
    httpServletResponse.addHeader("Content-Type", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    httpServletResponse.addHeader("Content-disposition", "attachment; filename=report.xlsx");
    ServletOutputStream outputStream = httpServletResponse.getOutputStream();
    net.sf.jasperreports.engine.export.ooxml.JRXlsxExporter exporter = new net.sf.jasperreports.engine.export.ooxml.JRXlsxExporter();
    exporter.setParameter(JRExporterParameter.JASPER_PRINT, jasperPrint);
    exporter.setParameter(JRExporterParameter.OUTPUT_STREAM,outputStream);
    exporter.exportReport();
    outputStream.flush();
    outputStream.close();
    FacesContext.getCurrentInstance().responseComplete();
}

Question:

I am using Jasper reports to create Excel file programmatically. I am using Java Data Source - a java class that implements JRDataSource interface. Up until now My Datasource returned a List of class instances that looked something like this:

public Class MyDataSource implements JRDataSource {
  private Integer prop1;
  private String prop2;
  private String prop3;
  ...
  // getters and setters omitted to save space
}

With this Data Source I was able to create a very nice excel table that looked:

prop1-Header prop2-Header prop3-Header...
----------------------------------------
prop1-value  prop2-value  prop3-value...
prop1-value  prop2-value  prop3-value...
...

But now MyDataSource class has additional property List<String>

public Class MyDataSource implements JRDataSource {
  private Integer prop1;
  private String prop2;
  private String prop3;
  private List<String> subvalues;
  ...
  // getters and setters omitted to save space
}

So I need my excel to look like this

prop1-Header prop2-Header prop3-Header...
----------------------------------------
prop1-value  prop2-value  prop3-value...
                                         Sub-header1 Sub-header2...
                                         -----------------------
                                         sub-value1  sub-value2....
                                         ....
prop1-value  prop2-value  prop3-value...
                                         Sub-header1 Sub-header2...
                                         -----------------------
                                         sub-value1  sub-value2....
                                         ....
...

I managed to do that by concatenating The list into a single string, and it looks very similar to what I need. But I have no way of sorting and filtering on sub-value data. So, I need to actually make it as sub-list or sub-table. And this is my question - how to do this?


Answer:

After searching for a while I found the solution. First of all, the is a very nice youtube video

How to fill Jasper Report Table using Collection of data in Java?

This video shows in greate detail how to pass from java code into report an additional collection besides your Datasource java class. This is done by doing something like this:

    JRBeanCollectionDataSource detailBean = new JRBeanCollectionDataSource(getMySubvaluesINstancesList());
    Map<String, Object> params = new HashMap<>();
    params.put("DetailDataSource", detailBean);
    jasperPrint = JasperFillManager.fillReport(jasperReport, params, ds);

Note the name "DetailDataSource". In your report, you will need to create a parameter with that name and declare it's type as net.sf.jasperreports.engine.data.JRBeanCollectionDataSource. After that, you build a table based on a dataset that you create based on that parameter. But this is a short description of the video referenced above. However, that alone doesn't solve the problem yet. The remaining problem that collection passed as a parameter will only be rendered once and will not be rendered for each record of your datasource. So, what we need to do is to add our list property into our datasource class, the same way as described in the question:

public Class MyDataSource implements JRDataSource {
  private Integer prop1;
  private String prop2;
  private String prop3;
  private List<String> subvalues;
  ...
  // getters and setters omitted to save space
}

After that in your report you will need to declare a field named "subvalues" and declare its type as java.util.List. And then change the definition of your Dataset that you created following the video referenced above. You will need to change JRDatasource expression from $P{yourParameterName} to new net.sf.jasperreports.engine.data.JRBeanCollectionDataSource($F{subvalues}) And bingo! your collection is part of a datasource and will be rendered for each record

Question:

I have two methods to export a pdf and an excel from jasper reports with primefaces. Exporting a pdf method works perfectly. But exporting excel exports a .xlxs though there are html codes inside the excel file and no any required data. The code is as follows.

ChatListBean.java

public void printExcel(){
    String reportPath = JsfUtil.getAbsolutePath("/resources/reports/chat.jasper");
    String subReportPath = JsfUtil.getAbsolutePath("/resources/reports");
    String realPath = JsfUtil.getAbsolutePath("");
    Map<String, Object> params = new HashMap<String, Object>();
    params.put("realPath", realPath);
    params.put("SUBREPORT_DIR", subReportPath);
    // params.put("location", selectedLocation.getName());
    JRBeanCollectionDataSource beanCollectionDataSource = new JRBeanCollectionDataSource(findChats);

    try {
        JasperReportUtil.toEXCEL(beanCollectionDataSource, reportPath, params, "Chat_History");

    } catch (Exception ex) {
        Logger.getLogger(ChatListBean.class.getName()).log(Level.SEVERE, null, ex);
    }
}

JasperReportUtil.java

public static void toEXCEL(JRBeanCollectionDataSource beanCollectionDataSource, String reportPath, Map<String, Object> params, String reportName) throws Exception{
    JasperPrint jasperPrint = JasperFillManager.fillReport(reportPath, params, beanCollectionDataSource);
    HttpServletResponse httpServletResponse = (HttpServletResponse) FacesContext.getCurrentInstance().getExternalContext().getResponse();
    httpServletResponse.addHeader("Content-disposition", "attachment; filename=" + reportName + ".xlsx");

    JRXlsExporter xlsExporter = new JRXlsExporter();
    xlsExporter.setExporterInput(new SimpleExporterInput(jasperPrint));
    xlsExporter.setExporterOutput(new SimpleOutputStreamExporterOutput(reportName));
    xlsExporter.exportReport();
    FacesContext.getCurrentInstance().responseComplete();

}

This is an exported Excel file

How do i fix this? I'm using following maven dependencies.

<groupId>net.sf.jasperreports</groupId>
<artifactId>jasperreports</artifactId>
<version>6.5.1</version>

and

<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.16</version>

Answer:

I have done a mistake with xlsExporter.setExporterOutput method and working code is

public static void toEXCEL(JRBeanCollectionDataSource beanCollectionDataSource, String reportPath, Map<String, Object> params, String reportName) throws Exception{
    JasperPrint jasperPrint = JasperFillManager.fillReport(reportPath, params, beanCollectionDataSource);
    HttpServletResponse httpServletResponse = (HttpServletResponse) FacesContext.getCurrentInstance().getExternalContext().getResponse();
    httpServletResponse.addHeader("Content-disposition", "attachment; filename=" + reportName + ".xlsx");
    ServletOutputStream servletOutputStream = httpServletResponse.getOutputStream();
    JRXlsExporter xlsExporter = new JRXlsExporter();
    xlsExporter.setExporterInput(new SimpleExporterInput(jasperPrint));
    xlsExporter.setExporterOutput(new SimpleOutputStreamExporterOutput(servletOutputStream));
    xlsExporter.exportReport();

    FacesContext.getCurrentInstance().responseComplete();
}