如何将数据库中的blob图像从jasper报告导出到excel？ [英] How to export an image that is a blob in database from a jasper reports to excel?
本文介绍了如何将数据库中的blob图像从jasper报告导出到excel？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
在ireportDesigner 5.6.0中，我正在从数据库添加图像。当我添加该图像时，在xml中显示为 java.lang.Object ，我已将其更改为 java.awt.Image 如下图所示
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
我已经将表达式类改为java.awt.Image一次，将另一次改为java.io.InputStream。现在，当我点击预览标签图片即将到来并且是完美的。
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.
现在出现问题。
当我将jrxml和jasper文件集成到eclipse并运行服务器时，它显示的错误如无法添加单元格。
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."
这是我的代码：
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>
推荐答案
使用您添加的其他信息，这就是我想要的执行：
With additional info that you added this is what I would do:
-
正确的字段类应为
java.sql。 Blob（与数据库映射相对应）
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>
图片表达应该是（在<$ c上） $ c> Blob 我们调用 getBinaryStream（）给我们一个 java.io.InputStream ）
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>
为了改善代码一些，它会很棒检查 $ F {companyLogo}！= null ，
To improve code some it would be great to check that $F{companyLogo}!=null,
一个小注释...为什么不把它传递给参数，因此将其加载到您的java中并将其作为java.io.InputStream或java.io.Image传递...这种方式也许您的jasper变得更清洁......
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...
这篇关于如何将数据库中的blob图像从jasper报告导出到excel？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
