How to get the JasperReports into Excel format with proper alignment

Question

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 the Eclipse project and run it on the server, it is getting misaligned 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>

narikkadan · Answer 1 · Sep 21, 2022

Remove at least one parameter:

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

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