Error when exporting to EXCEL and open in MSExcel

[newtonarruda]

Hi,

 

i have a message(erro) when i try to open a xls file, that was exported to Excel with JXL, in MSExcel.

 

that is the message:

 

anyone help me!?!?

 

tnx. size=400]http://www.jasperforge.org/components/com_joomlaboard/uploaded/images/JXL_POI.JPG

Post edited by: newtonarruda, at: 2007/07/04 21:46

[szaharia]

Hi,

The error dialog you posted is just a generic one. We cannot extract significant information from that. More useful would be if you'd attach the jrxml sample you exported to excel, as simplified as possible (i.e using constants instead of query field values, because we cannot have access to your datasource).

 

Thank you,

sanda

[newtonarruda]

Sorry about that, I forgoth to put one mor information:

- when i export to XLS and before the reponse has been sent I convert the outputstream from the jasper to a Workbook (POI) and I set some columns width then I convert again to outputstream.

 

The error messagem does not apairs when a don´t do the conversions.

 

I´m going crazy because this messagem only apairs when my default program to open XLS is the MSExcel, this error does not apairs in OpenOffice.

 

I hope this addiction will help to explain my problem.

 

tnx

[szaharia]

It seems to be a matter of conversion you applied, rather than a Jasperreports one. After reading your second post, I only can suggest you to use the JExcelApiExporter instead of JRXlsExporter, because it is more adaptable than the last one.

About setting column width: couldn't you set it a priori in your jrxml, rather than programatically?

 

Regards,

sanda

[newtonarruda]

here is my code:

Code:

JExcelApiExporter exporter = new JExcelApiExporter();
ByteArrayOutputStream xlsReport = new ByteArrayOutputStream();
exporter.setParameter(JExcelApiExporterParameter.JASPER_PRINT,jasperPrint);
exporter.setParameter(JExcelApiExporterParameter.OUTPUT_STREAM,xlsReport);
exporter.setParameter(JExcelApiExporterParameter.IS_REMOVE_EMPTY_SPACE_BETWEEN_ROWS,Boolean.TRUE);
exporter.setParameter(JExcelApiExporterParameter.IS_WHITE_PAGE_BACKGROUND,Boolean.TRUE);
exporter.setParameter(JExcelApiExporterParameter.IS_ONE_PAGE_PER_SHEET,Boolean.TRUE);
exporter.setParameter(JExcelApiExporterParameter.IS_DETECT_CELL_TYPE,Boolean.TRUE);
exporter.exportReport();

InputStream inputStream = new ByteArrayInputStream(xlsReport.toByteArray());
HSSFWorkbook wb = new HSSFWorkbook(inputStream);

short[] columns         = {0   ,1   ,2     ,3   ,4   ,5   ,6   ,7   ,8   ,9   ,10  ,11  ,12  ,13  ,14  ,15  ,16  ,17};

short[] widthArray 	= {580 ,2124,1055,2322,1939,2375,2005,2045,1860,1939,2124,3799,4459,3694,1201,1201,3654,580};

int sheets = wb.getNumberOfSheets();
//    Auto Fit nos Sheets
for (int i = 0; i < sheets; i++) {

HSSFSheet sheet = wb.getSheetAt(i);
	for (int j = 0; j < columns.length; j++) {
		sheet.setColumnWidth(columns[j], widthArray[j]);
	}
}
 

 

Post edited by: newtonarruda, at: 2007/07/06 18:16

Post edited by: newtonarruda, at: 2007/07/06 18:39

[szaharia]

Hi,

 

The code you posted here contains a mix of JExcel and POI Api, which cannot lead to a good result. If JExcelApiExporter is your choice, you should use WritableWorkbook and WritableSheet instead of HSSF ones. If POI is the choice, use the JRXlsExporter and HSSF classes.

Another point of view is that your column dimensions seem to be known at compile time, and in this case you don't need to set them programatically. It's ok if you set the <box />'s element width for any field you want to be represented, in the jrxml file.

 

Regards,

sanda

[newtonarruda]

Thanks shertage, I try it but the message remains the same.

 

Is there any other way to set the column width after the exporter.exportReport() ?

 

Regards.

[newtonarruda]

Hi all,

I corrected the problem reorganizing my code and passed not to use Byte[] anymore. I believe that this caused the problem .

 

regards.

[Umakanth]

Hi. I am using ireport 2.0.0. I want some data to be exported to excel(2007 version). I

 

use the below code in my jsp using scriptlet. i am not able to do so. empty excel is

 

getting generated..

Can anyone help in this regard ?

 

File reportFile = new File(getServletConfig().getServletContext()

.getRealPath("/jasper/TrendAnalysisReportExcel.jrxml"));

InputStream input= new FileInputStream(reportFile);

JasperDesign design = JRXmlLoader.load(input);

JasperReport report = JasperCompileManager.compileReport(design);

String startDate = (String)request.getParameter("startDate");

String endDate = (String)request.getParameter("endDate");

String bUnit = (String)request.getParameter("bUnit");

String customer = (String)request.getParameter("customer");

Map<String,Object> parameters = new HashMap<String,Object>();

parameters.put("P_StartDate", new java.util.Date(startDate));

parameters.put("P_EndDate", new java.util.Date(endDate) );

parameters.put("P_BU_UNIT_CODE", bUnit );

parameters.put("P_CU_COMPANY_CODE", customer );

 

Connection conn = ConnectionManager.getConnection();

JasperPrint print = JasperManager.fillReport(report, parameters, conn);

 

ByteArrayOutputStream output = new ByteArrayOutputStream();

 

OutputStream outputfile= new FileOutputStream(new

 

File(getServletConfig().getServletContext()

.getRealPath("/spooler/Testing.xls")));

JExcelApiExporter exporterXLS = new JExcelApiExporter();

 

response.setContentType("application/vnd.ms-excel");

 

exporterXLS.setParameter(JExcelApiExporterParameter.JASPER_PRINT, print);

exporterXLS.setParameter(JExcelApiExporterParameter.OUTPUT_STREAM, output);

exporterXLS.setParameter(JExcelApiExporterParameter.IS_ONE_PAGE_PER_SHEET,

 

Boolean.FALSE);

exporterXLS.setParameter(JExcelApiExporterParameter.IS_DETECT_CELL_TYPE,

 

Boolean.TRUE);

exporterXLS.setParameter(JExcelApiExporterParameter.IS_WHITE_PAGE_BACKGROUND,

 

Boolean.FALSE);

exporterXLS.setParameter(JExcelApiExporterParameter.IS_REMOVE_EMPTY_SPACE_BETWEEN_ROWS,

 

Boolean.TRUE);

exporterXLS.exportReport();

 

cool regards,

uk