I am using jasperReport 1.3.0 that have not an ability to limit number of rows MAXIMUM_ROW_PER_SHEET while generating File in Office(Excel 2003-97)
Any one can figure out this problem as OR or any other to handle this situation
As i know about it latest version of jasper report have an option to limit Rows by using this property MAXIMUM_ROW_PER_SHEET. I have some limitation that i can.t update jasper report version below is my method that i am using.
import net.sf.jasperreports.engine.*;
import net.sf.jasperreports.engine.export.JRCsvExporter;
import net.sf.jasperreports.engine.export.JRXlsExporter;
import net.sf.jasperreports.engine.export.JRHtmlExporter;
import net.sf.jasperreports.engine.export.JRHtmlExporterParameter;
import net.sf.jasperreports.engine.export.JRXlsExporterParameter;
import net.sf.jasperreports.engine.export.JExcelApiExporter;
import net.sf.jasperreports.engine.export.JRXlsExporterParameter;
import net.sf.jasperreports.engine.export.JRXlsAbstractExporterParameter;
import java.util.ArrayList;
import net.sf.jasperreports.engine.util.JRLoader;
import net.sf.jasperreports.engine.design.JasperDesign;
import net.sf.jasperreports.engine.design.JasperDesign;
import net.sf.jasperreports.engine.export.JExcelApiExporter;
import javax.servlet.ServletContext;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.HashMap;
import java.util.Map;
import jxl.write.NumberFormat;
import org.apache.catalina.connector.ClientAbortException;
import org.apache.log4j.Logger;
import com.sibisoft.northstar.pos.CurrencySymbol;
import com.sibisoft.northstar.servlet.NSCommonServlet;
import com.sibisoft.northstar.util.i18n.NSDisplayFormats;
import net.sf.jasperreports.engine.export.JRXlsExporterParameter;
import net.sf.jasperreports.engine.export.JRXlsAbstractExporterParameter;
public void createXLSQuickReports(HttpServletResponse response) throws Exception {
//==============================
String reportfilename = getDownloadableFileNameFor()+".xls";
String sheetName=getDownloadableFileNameFor();
if(sheetName.length()>MAX_NAME_LENGTH_FOR_EXCEL_SHEET)
{
sheetName=sheetName.substring(0,MAX_NAME_LENGTH_FOR_EXCEL_SHEET);
}
String [] sheetNames = {sheetName};
final JExcelApiExporter exporterXLS = new JExcelApiExporter(){
private Map numberFormats = new HashMap();
@Override
protected jxl.write.NumberFormat getNumberFormat(String pattern){
String convertedPattern = getConvertedPattern(pattern);
jxl.write.NumberFormat cellFormat = (jxl.write.NumberFormat) numberFormats.get(convertedPattern);
if (cellFormat == null){
cellFormat = new jxl.write.NumberFormat(convertedPattern,NumberFormat.COMPLEX_FORMAT);
numberFormats.put(convertedPattern, cellFormat);
}
return cellFormat;
}
private String getConvertedPattern(String pattern){
if( pattern != null ){
if ( pattern.equals( "$#,##0.00;($#,##0.00)" )
|| pattern.equals( "$#,##0.00;$(#,##0.00)" )
|| pattern.equals( "$#,##0.00;(#,##0.00)" )
|| pattern.equals( "$#0.00;($#0.00)" )
|| pattern.equals( "$#,##0.00")
){
return NSDisplayFormats.JASPER_EXCEL_CURRENCY_PATTERN;
}else{
System.out.println( "pattern that is not in if condition=" + pattern );
}
}
return pattern;
}
};
//exporterXLS.setParameter(net.sf.jasperreports.engine.export.JRXlsExporterParameter.MAXIMUM_ROWS_PER_SHEET,Integer.decode("65000"));
exporterXLS.setParameter(JRXlsExporterParameter.IS_ONE_PAGE_PER_SHEET,false);
exporterXLS.setParameter(JRXlsExporterParameter.JASPER_PRINT, jasperPrint);
//exporterXLS.setParameter(JRXlsExporterParameter.IS_DETECT_CELL_TYPE, Boolean.TRUE);
//exporterXLS.setParameter(JRXlsExporterParameter.IS_ONE_PAGE_PER_SHEET, Boolean.FALSE);
exporterXLS.setParameter(JRXlsAbstractExporterParameter.SHEET_NAMES, sheetNames);
exporterXLS.setParameter(JRXlsExporterParameter.IS_WHITE_PAGE_BACKGROUND, Boolean.FALSE);
exporterXLS.setParameter(JRXlsExporterParameter.IS_REMOVE_EMPTY_SPACE_BETWEEN_ROWS, Boolean.TRUE);
//exporterXLS.setParameter(JRXlsExporterParameter.OUTPUT_STREAM, response.getOutputStream());
if(true || getIS_DETECT_CELL_TYPE_FOR_EXCEL().equals("1")){
exporterXLS.setParameter( JRXlsExporterParameter.IS_DETECT_CELL_TYPE,Boolean.TRUE);
}
/*Map numberFormats = new HashMap();
numberFormats.put("INR #,##0.00", "$#,##0.00;-$#,##0.00");
exporterXLS.setParameter(JRXlsExporterParameter.FORMAT_PATTERNS_MAP, numberFormats);*/
response.setHeader("Content-Disposition", "attachment;filename=" + reportfilename);
response.setContentType("application/vnd.ms-excel");
ByteArrayOutputStream xlsReport = new ByteArrayOutputStream();
exporterXLS.setParameter(JRXlsExporterParameter.OUTPUT_STREAM, xlsReport);
exporterXLS.exportReport();
byte[] bytes = xlsReport.toByteArray();
response.setContentLength(bytes.length);
ServletOutputStream ouputStream = response.getOutputStream();
try
{
ouputStream.write(bytes);
ouputStream.flush();
ouputStream.close();
}
catch(ClientAbortException cae)
{
logger.debug("User Cancel the report ("+getDownloadableFileNameFor()+") or connection is dropped.");
}
}
Recommended Comments