如何通过适当的对齐将JasperReports转换为Excel格式 [英] How to get the JasperReports into Excel format with proper alignment

问题描述

在ireportDesigner 5.6.0中，当我将内部预览设置为XLS预览时，它会使报告正确对齐。图片如下：

Eclipse中的代码是：

 <％@ 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 =javacontentType =text / html; charset = ISO-8859-1
 pageEncoding =ISO-8859-1％> 
<！DOCTYPE html PUBLIC -  // W3C // DTD HTML 4.01 Transitional // ENhttp://www.w3.org/TR/html4/loose.dtd\"> 
< html> 
< head> 
< meta http-equiv =Content-Typecontent =text / html; charset = ISO-8859-1> 
< title> XLS Page< / title> 
< / head> 
< body> 
 
<％
 String sql = null; 
连接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.ServiceTa x，来自company_profile的invoice.CST，invoice，invoiceclient_details其中invoice.CId In（从invoiceclient_details中选择max（CId））和invoiceclient_details.CId In（从invoiceclient_details中选择max（CId））; 
 ServletContext context = request.getServletContext（）; 
 String fullPath = context.getRealPath（/ WEB-INF / reports / report5.jrxml）; 
 InputStream input = new FileInputStream（new File（fullPath））; 
 JasperDesign jasperDesign = JRXmlLoader.load（输入）; 
 
 System.out.println（编译报告设计）; 
 JasperReport jasperReport = JasperCompileManager.compileReport（jasperDesign）; 
 
 System.out.println（创建JasperPrint对象）; 
 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(\"字节数组的大小：+ 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(\"JasperPrint = 1后）; 
 response.setContentLength（xlsReport.size（））; 
 //System.out.println(\"JasperPrint = 2后）; 
 //xlsReport.close（）; 
 //System.out.println(\"JasperPrint = 3后）; 
 
 OutputStream outputStream = response.getOutputStream（）; 
 System.out.println（JasperPrint = 4之后）; 
 xlsReport.writeTo（outputStream）; 
 outputStream.flush（）; 
 //outputStream.close（）; 
} 
 catch（例外e）
 {e.printStackTrace（）;} 
 
％> 
< / body> 
< / html> 
  

解决方案

将评论转换为答案

至少删除参数：

  exporter.setParameter（JRXlsExporterParameter） .IS_COLLAPSE_ROW_SPAN，Boolean.TRUE）
  

并注意 JRXlsExportParameter 已弃用 版本5.6 的正确方式

  JRXlsExporter exporter = new JRXlsExporter（）; 
 exporter.setExporterInput（new SimpleExporterInput（jasperPrint））; 
 exporter.setExporterOutput（new SimpleOutputStreamExporterOutput（xlsReport））; 
 
 SimpleXlsReportConfiguration配置= new SimpleXlsReportConfiguration（）; 
 configuration.setOnePagePerSheet（true）; 
 configuration.setDetectCellType（true）; 
 configuration.set //你想要设置的其他属性
 exporter.setConfiguration（configuration）; 
 exporter.exportReport（）; 
  

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:

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

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>

解决方案

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(); 

这篇关于如何通过适当的对齐将JasperReports转换为Excel格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！