cancel
Showing results for 
Search instead for 
Did you mean: 

Downloading Excel file using Jakarta POI - HSSF

Former Member
0 Kudos

Hello everyone,

I'm downloading a UI Table element to an excel file using HSSF features to create an Excel sheet and move it to a file.

HSSF can be found here:

<a href="http://jakarta.apache.org/poi/index.html">http://jakarta.apache.org/poi/index.html</a>

Everything seems to work fine until the moment Excel tries to open my newly created file.

What happens is that Excel warns me that it has "encountered a problem and needs to close" (the usual message).

Afterwards, Excel tries to repair the file and it does retrieve most of the data, except for one column. Has anyone else had an error like this ? Can anyone help me?

Here i enclose my code:

  public byte[] exportToExcel( )
  {
    //@@begin exportToExcel()

	  //Create a workbook
	  HSSFWorkbook wb = new HSSFWorkbook();

	  //Create an sheet attached to the workbook
	  HSSFSheet sheet = wb.createSheet("Form Values");

	  // Create a row (first header)
	  HSSFRow row = sheet.createRow((short) 0);
	  wdContext.currentResultadosElement().getCodigo();
	  row.createCell((short) 0).setCellValue("Invoice Number: ");
	  row.createCell((short) 1).setCellValue(wdContext.currentResultadosElement().getCodigo());

	  // Create a row (second header)
	  row = sheet.createRow((short) 1);
	  // Create all cells and put all value in it.
	  row.createCell((short) 0).setCellValue("Code");
	  row.createCell((short) 1).setCellValue("Amount Last Year");
	  row.createCell((short) 2).setCellValue("Due Date");
	  row.createCell((short) 3).setCellValue("Last Purchase Date");
	  row.createCell((short) 4).setCellValue("Amount Last Purchase");
	  row.createCell((short) 5).setCellValue("Unit Price");
	  row.createCell((short) 6).setCellValue("Prev. Min. Month");
	  row.createCell((short) 7).setCellValue("Prev. Min. Year");
	  row.createCell((short) 8).setCellValue("Prev. Max. Month");
	  row.createCell((short) 9).setCellValue("Prev. Max. Year");
	  	  
	  int count = wdContext.nodeNecValores_excel().size();
	  
	  for (int i = 0; i < count; i++){
		
		  IPublicLevValores.INecValores_excelElement elem=
			  wdContext.nodeNecValores_excel().getNecValores_excelElementAt(i);
		
		  double prod_id = Double.parseDouble( (elem.getProd_id()).trim() );
		
		  //Create a row 
		  row = sheet.createRow((short) i+2);
		  // Create all cells and put all value in it.
		  row.createCell((short) 0).setCellValue(prod_id);
	  }
	  
	//	get attribute info for context attribute 'FileResource'
	IWDAttributeInfo attributeInfo =
	wdContext.getNodeInfo().getAttribute(IPublicLevValores.IContextElement.FILE_RESOURCE);
	//	create a modifiable binary type for the context attribute
	//	which stores the MIME-object.

	IWDModifiableBinaryType binaryType =
	(IWDModifiableBinaryType) attributeInfo.getModifiableSimpleType();  
 	
	wdContext.currentContextElement().setFileResource(wb.getBytes());

///////// Testes 	
 	wdComponentAPI.getMessageManager().reportWarning("Get Bytes: "+ wb.getBytes().toString());
	ByteArrayInputStream result = new ByteArrayInputStream(wb.getBytes());
	wdComponentAPI.getMessageManager().reportWarning("ByteArrayInputStream: " + result.available() );	
///////// 	
	binaryType.setFileName("folha-levantamentos.xls");
	binaryType.setMimeType(WDWebResourceType.XLS);	  

	return wb.getBytes();
	
    //@@end
  }

Line

row.createCell((short) 9).setCellValue("Prev. Max. Year");

has the column that doesn't appear. But i also would like to try and avoid that error message.

Thank you in advance for any help.

Best regards,

Nuno Santos

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hello Nuno,

I have done a fair bit of work with POI myself.

I have found the exact same problem as you, and for some reason, it has to do with the getBytes() method of the Workbook not returning the content correctly (maybe this has to do with the encoding? - it probably needs to be reported anyway, as clearly, the byte array returned by this method is not the same what workbook.write() produces).

What I do is the following:

ByteArrayOutputStream baos = new ByteArrayOutputStream();

workBook.write(baos);

baos.toByteArray();

(the last statement returns the byte array, and this is successfully opened by Excel).

It works for me. Hope it works for you.

Cheers,

Martin

Answers (1)

Answers (1)

Former Member
0 Kudos

No ideas ? Has anyone had any contact with the POI API ?