Search code examples
jasper-reportsblobexport-to-excel

How to export an image that is a blob in database from a jasper reports to excel?

In ireportDesigner 5.6.0, I am adding an image from database. When I add that image,in xml it is shown as java.lang.Object and I have changed it to java.awt.Image as in the image given below

enter image description here

I have changed expression class to "java.awt.Image" one time and another time to "java.io.InputStream". Now when i click on "preview" tab image is coming and is perfect.

Now the problem appears.

When i integrated jrxml and jasper files to eclipse and on running the server, it is showing an error like "The Cell Cannot be Added."

This is my code:

report1.jrxml

<?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="report1" language="groovy" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="d9b53979-ad44-4ad1-aedf-164f742a3c02">
    <queryString>
        <![CDATA[select company_profile.companyLogo,
company_profile.companyLetterHead
from
company_profile]]>
    </queryString>
    <field name="companyLogo" class="java.lang.Object">
        <fieldDescription><![CDATA[]]></fieldDescription>
    </field>
    <field name="companyLetterHead" class="java.lang.Object">
        <fieldDescription><![CDATA[]]></fieldDescription>
    </field>
    <title>
        <band height="79" splitType="Stretch">
            <image>
                <reportElement x="0" y="0" width="107" height="79" uuid="fba6c6a9-42a2-4ffb-9d80-f0721728a7b6"/>
                <imageExpression><![CDATA[$F{companyLogo}]]></imageExpression>
            </image>
            <image>
                <reportElement x="107" y="0" width="448" height="79" uuid="b0e3a3f8-424b-4780-926f-95d15f8e0cc5"/>
                <imageExpression><![CDATA[$F{companyLetterHead}]]></imageExpression>
            </image>
        </band>
    </title>
</jasperReport>

addCompanyProfile.jsp

<script language="javascript">
            function Checkfiles()
            {
                var fup = document.getElementById('filename');
                var fileName = fup.value;
                var ext = fileName.substring(fileName.lastIndexOf('.') + 1);
                if(ext == "gif" || ext == "GIF" || ext == "JPEG" || ext == "jpeg" || ext == "jpg" || ext == "JPG" || ext == "doc")
                {
                    return true;
                } 
                else
                {
                    alert("Upload an image");
                    fup.focus();
                    return false;
                }
            }
        </script>
    <body>
                        <td></td><br>
                        <td><html:submit value="Update"
                                style="width:70px;height:25px;" /></td>
                        </table>

                        </html:form>
                        <br/>
                       <%-- Photo edit --%>
                    <FORM ENCTYPE="multipart/form-data" ACTION="uploadCompanyLogo.jsp" METHOD=POST onsubmit="return Checkfiles();">
                        <table border="0">

                            <tr>
                                <td style="padding-left:100px;" colspan="2" align="center"><B>UPLOAD LOGO AND LETTER HEAD HERE:-</B><center></td>
                                </tr>

                                <tr>
                                    <td style="padding-left:100px;"><b>Add Logo:</b></td>
                                    <td><INPUT NAME="file" TYPE="file" id="filename" size="7"></td>
                                     <td colspan="2" align="center"><input type="submit" value="Upload Logo"> </td>
                                </tr>
                               </table>        
                    </FORM>

                    <FORM ENCTYPE="multipart/form-data" ACTION="uploadLetterHead.jsp" METHOD=POST onsubmit="return Checkfiles1();">
                        <table border="0"> 
                                <tr>
                                 <td style="padding-left:100px;"><b>Add LetterHead:</b></td>
                                    <td><INPUT NAME="file" TYPE="file" id="filename" size="7"></td>
                                    <td style="padding-left:50px;" colspan="2" align="center"><input type="submit" value="Upload LetterHead"> </td>

                                </tr>

                       </table> 
                       </FORM>
            </div>
             <div>


                    <%-- for further help <a href="/SalesPropeller/Profile/helpImg.jsp">Click Here</a> --%>
                </div>
</body>

uploadCompanyLogo.jsp

<%@ page import="java.io.*" %>
<%@ page import="java.sql.*" %>
<%@ page import="java.util.zip.*"%>
<%
    String email = (String) session.getAttribute("email");
    String saveFile = "";
    String contentType = request.getContentType();
    if ((contentType != null) && (contentType.indexOf("multipart/form-data") >= 0)) {
        DataInputStream in = new DataInputStream(request.getInputStream());
        int formDataLength = request.getContentLength();
        byte dataBytes[] = new byte[formDataLength];
        int byteRead = 0;
        int totalBytesRead = 0;
        while (totalBytesRead < formDataLength) {
            byteRead = in.read(dataBytes, totalBytesRead, formDataLength);
            totalBytesRead += byteRead;
        }
        String file = new String(dataBytes);
        saveFile = file.substring(file.indexOf("filename=\"") + 10);
        saveFile = saveFile.substring(0, saveFile.indexOf("\n"));
        saveFile = saveFile.substring(saveFile.lastIndexOf("\\") + 1, saveFile.indexOf("\""));
        int lastIndex = contentType.lastIndexOf("=");
        String boundary = contentType.substring(lastIndex + 1, contentType.length());
        int pos;
        pos = file.indexOf("filename=\"");
        pos = file.indexOf("\n", pos) + 1;
        pos = file.indexOf("\n", pos) + 1;
        pos = file.indexOf("\n", pos) + 1;
        int boundaryLocation = file.indexOf(boundary, pos) - 4;
        int startPos = ((file.substring(0, pos)).getBytes()).length;
        int endPos = ((file.substring(0, boundaryLocation)).getBytes()).length;
        File ff = new File(saveFile);
        FileOutputStream fileOut = new FileOutputStream(ff);
        fileOut.write(dataBytes, startPos, (endPos - startPos));
        fileOut.flush();
        fileOut.close();
%><Br><table border="2"><tr><td><b>You have successfully upload the file:</b>
            <%out.println(saveFile);%></td></tr></table>
<%
        Connection connection = null;
        ResultSet rs = null;
        PreparedStatement psmnt = null;
        FileInputStream fis;
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/marketing_database", "root", "root");
            File f = new File(saveFile);
            psmnt = connection.prepareStatement("UPDATE marketing_database.company_profile SET `companyLogo`=? WHERE `id`='1';");
            fis = new FileInputStream(f);
            psmnt.setBinaryStream(1, (InputStream) fis, (int) (f.length()));
            int s = psmnt.executeUpdate();
            if (s > 0) {
                System.out.println("Uploaded successfully !");
            } else {
                System.out.println("Error!");
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
    }
%>

<%
response.sendRedirect("/SalesPropeller/Admin/Profile/addCompanyProfile.jsp");
%>

QuotationXLS.jsp

<body>

<%
String sql=null;
Connection conn = null;
try 
{
    Class.forName("com.mysql.jdbc.Driver");
    conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/marketing_database","root","root");
    sql = "select company_profile.companyLogo,company_profile.companyLetterHead,company_profile.companyName AS 'cname',quotationclient_details.RefNo,quotationclient_details.Date,quotationclient_details.CustomerName,quotationclient_details.CompanyName,quotationclient_details.PaymentTerm,quotationclient_details.DeliveryTime,quotationclient_details.Validity,quotation.Item,quotation.id,quotation.Description,quotation.Quantity,quotation.PerUnitPrice,quotation.TotalPrice,quotation.VAT5,quotation.VAT14,quotation.CST,quotationclient_details.MobileNo,quotation.ServiceTax from quotation,quotationclient_details,company_profile where quotation.CId In (select max(CId) from quotationclient_details) and quotationclient_details.CId In (select max(CId) from quotationclient_details)";
    ServletContext context = request.getServletContext(); 
    String fullPath = context.getRealPath("/WEB-INF/reports/report9.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]; 

    JRXlsExporter exporter = new JRXlsExporter(); 
    ByteArrayOutputStream xlsReport = new ByteArrayOutputStream(); 
    exporter.setParameter(JRXlsExporterParameter.JASPER_PRINT, jasperPrint);
    exporter.setParameter(JRXlsExporterParameter.IS_WHITE_PAGE_BACKGROUND,Boolean.TRUE);
    exporter.setParameter(JRXlsExporterParameter.IS_DETECT_CELL_TYPE, Boolean.TRUE); 
    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.IGNORE_PAGE_MARGINS,Boolean.TRUE);
    exporter.setParameter(JRXlsExporterParameter.IS_ONE_PAGE_PER_SHEET,Boolean.TRUE);
    exporter.setParameter(JRXlsExporterParameter.OUTPUT_STREAM, xlsReport); 
    exporter.exportReport(); 
    String fileName = "QuotationReport.xls";
response.setHeader("Content-Disposition", "inline; filename="
+ fileName);
    response.setContentType("application/vnd.ms-excel"); 
    response.setContentLength(xlsReport.size()); 

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

        %>
</body>
</html>
Solution

  • With additional info that you added this is what I would do:

    1. The correct field class should be java.sql.Blob (corrispond to the database mapping) 

      <field name="companyLogo" class="java.sql.Blob">
   <fieldDescription><![CDATA[]]></fieldDescription>
</field>

    2. The image expression should be (On the Blob we call the getBinaryStream() that gives us a java.io.InputStream)

       <image>
    <reportElement x="0" y="0" width="107" height="79" uuid="fba6c6a9-42a2-4ffb-9d80-f0721728a7b6"/>
    <imageExpression class="java.io.InputStream"><![CDATA[$F{companyLogo}.getBinaryStream()]]></imageExpression>
 </image>

    To improve code some it would be great to check that $F{companyLogo}!=null,

    a small note... why not pass it as parameter, hence load it in your java and pass it as a java.io.InputStream or java.io.Image... this way maybe also your jasper gets cleaner...