Cell Expansion is not happening while exporting jasper reports to XLS format

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>

Solution

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:

Make excel change font size (to fit)

configuration.setFontSizeFixEnabled(true)

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

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....