How to get the JasperReports into Excel format wit

2019-07-07 14:55发布

站内问答 / 后端开发
900 1 4

In ireportDesigner 5.6.0, when I set "internal Preview" as "XLS Preview",it is getting the report with proper alignment. The image is given below:

enter image description here

When I integrated the report into Eclipse project and run on server, it is getting mis-aligned as shown:

enter image description here

enter image description here

Code in Eclipse is:

<%@ 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.FileNotFoundException"%>
<%@ page import="java.io.InputStream"%>
<%@ page import="java.sql.SQLException"%>
<%@ page import="java.io.ByteArrayOutputStream"%>
<%@ page import="java.io.OutputStream"%>
<%@ page import="net.sf.jasperreports.engine.util.*"%> 
<%@ page import="net.sf.jasperreports.engine.export.*"%>
<%@ page import="net.sf.jasperreports.engine.design.JRDesignQuery"%>
<%@ page import="net.sf.jasperreports.engine.xml.JRXmlLoader"%>
<%@ page import="net.sf.jasperreports.engine.design.JasperDesign"%>
<%@ page import="net.sf.jasperreports.view.JasperViewer"%>
<%@ page import="net.sf.jasperreports.engine.JRException"%>
<%@ page import="net.sf.jasperreports.engine.JasperCompileManager"%>
<%@ page import="net.sf.jasperreports.engine.JasperExportManager"%>
<%@ page import="net.sf.jasperreports.engine.JasperFillManager"%>
<%@ page import="net.sf.jasperreports.engine.JasperPrint"%>
<%@ page import="net.sf.jasperreports.engine.JasperReport"%>
<%@ page import="net.sf.jasperreports.engine.export.JRXlsExporter"%>
<%@ page import="net.sf.jasperreports.engine.export.JRXlsExporterParameter"%>
<%@ page import="net.sf.jasperreports.engine.export.ooxml.JRXlsxExporter"%>
<%@ page import="net.sf.jasperreports.engine.JRExporterParameter"%>
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>XLS Page</title>
</head>
<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.companyName,company_profile.branchLocationAddress,company_profle.mobileNumber,company_profile.landLineNumber,company_profile.emailID,company_profile.vatOrTin,company_profile.serviceTaxNo,company_profile.panNo,company_profile.CIN,company_profile.bankDetailswithBankName,company_profile.accNo,company_profile.branch,company_profile.IFSCCode,invoiceclient_details.OrganizationName,invoiceclient_details.InvoiceNo,invoiceclient_details.InvoiceDate,invoiceclient_details.DeliveryNote,invoiceclient_details.TermsofPayment,invoiceclient_details.EsugamNo,invoiceclient_details.BuyerOrderNo,invoiceclient_details.BuyDate,invoiceclient_details.DispatchDocumentNo,invoiceclient_details.Dated,invoiceclient_details.DispatchThrough,invoiceclient_details.Destination,invoiceclient_details.TermsofDelivery,invoiceclient_details.BuyerTin,invoice.id,invoice.DescriptionOfGoods,invoice.Quantity,invoice.PerUnitPrice,invoice.TotalPrice,invoice.VAT14,invoice.VAT5,invoice.ServiceTax,invoice.CST from company_profile,invoice,invoiceclient_details where invoice.CId In (select max(CId) from invoiceclient_details) and invoiceclient_details.CId In (select max(CId) from invoiceclient_details)";
    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); 



    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.IS_COLLAPSE_ROW_SPAN,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(); 
    //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();} 

        %>
</body>
</html>

1条回答
对你真心纯属浪费
2楼-- · 2019-07-07 15:29

Converted comment in to answer

Remove at least parameter:

exporter.setParameter(JRXlsExporterParameter.IS_COLLAPSE_ROW_SPAN,Boolean.TRUE)

and note that JRXlsExportParameter is deprecated the correct way from version 5.6 is

JRXlsExporter exporter = new JRXlsExporter();
exporter.setExporterInput(new SimpleExporterInput(jasperPrint));
exporter.setExporterOutput(new SimpleOutputStreamExporterOutput(xlsReport));

SimpleXlsReportConfiguration configuration = new SimpleXlsReportConfiguration();
configuration.setOnePagePerSheet(true);
configuration.setDetectCellType(true);
configuration.set //The other properties you like to set
exporter.setConfiguration(configuration);
exporter.exportReport();
查看更多
0人赞 添加讨论(0) 举报
登录 后发表回答
相关问题
查看全部
相关文章
查看全部
收藏的人(4)