Bug 40039 - Excel file repaired after being created
Summary: Excel file repaired after being created
Status: RESOLVED WONTFIX
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: unspecified
Hardware: Other Windows XP
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2006-07-13 17:12 UTC by Nuno Santos
Modified: 2009-02-18 10:48 UTC (History)
1 user (show)



Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Nuno Santos 2006-07-13 17:12:40 UTC
Using version poi-2.5.1-final-20040804, i tried writing an Excel file from a web
dynpro SAP component. When downloading to local pc, Excel opens the file by
repairing it. In the "show repairs" option of Excel, i got this:

"Damage to the file was so extensive that repairs were not possible. Excel
attempted to recover your formulas and values, but some data may have been lost
or corrupted."

The final sheet appears with one less column than i wanted. And what i want is
very simple: a sheet with a first row filled and first column filled with a
string code, second line with 10 columns with strings (headers), and then a
dynamic number of rows filling the first column with an integer value. 

Here's the code i use:

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

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

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

	  // Create a row (first header)
	  HSSFRow row = sheet.createRow((short) 0);
	  wdContext.currentResultadosElement().getCodigo();
	  row.createCell((short) 0).setCellValue("Levantamento Nº");
	  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("Código");
	  row.createCell((short) 1).setCellValue("Quantidade Último Ano");
	  row.createCell((short) 2).setCellValue("Prazo Médio Entrega");
	  row.createCell((short) 3).setCellValue("Data Última Aquisição");
	  row.createCell((short) 4).setCellValue("Quantidade Última Aquisição");
	  row.createCell((short) 5).setCellValue("Custo Unitário");
	  row.createCell((short) 6).setCellValue("Prev. Min. Mensal");
	  row.createCell((short) 7).setCellValue("Prev. Min. Anual");
	  row.createCell((short) 8).setCellValue("Prev. Max. Mensal");
	  row.createCell((short) 9).setCellValue("Prev. Max. Anual");
	 
	  	  
	  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());

	binaryType.setFileName("folha-levantamentos.xls");
	binaryType.setMimeType(WDWebResourceType.XLS);	  

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



The cell created by this line:
row.createCell((short) 9).setCellValue("Prev. Max. Anual");
never appears on the final Excel.

I allready tried changing POI versions to older versions, but the problem
maintains. Even worse, besides the "Corrupt file" messages, i loose one of the
rows now.
Comment 1 Nick Burch 2006-07-14 09:53:22 UTC
You should check to see if your problem still exists in 3.0 alpha 2 - that's the
version where development and bug fixes are happening against.
Comment 2 Jason Height 2006-07-26 11:58:37 UTC
Can you please retry with the alpha, if not upload a code snippet that fails but
doesnt include all of your custom classes.

Jason
Comment 3 Erik Pilz 2006-12-18 20:08:32 UTC
I just ran into this myself. The issue stems from using HFFSWorkbook#getBytes()
to create an input stream that eventually gets saved as a file. From the docs
for getBytes() I think it may just be a misunderstanding of what the method
returns. 

Here's what I was doing that caused the problem:

HFFSWorkbook wb = new HFFSWorkbook();
// add sheets, rows, and cells.
return new ByteArrayInputStream(wb.getBytes());

The way I ended up solving this is as follows:

    HFFSWorkbook wb = new HFFSWorkbook();
    // add sheets, rows, and cells.
    ByteArrayOutputStream baos = new ByteArrayOutputStream();
    try {
        workbook.write(baos);
        return new ByteArrayInputStream(baos.toByteArray());
    } catch (IOException e) {
        try { baos.close(); } catch (IOException ee) { }
    }

- Erik
Comment 4 Yegor Kozlov 2008-05-17 06:30:48 UTC
Please check if the problem still exists in 3.1-beta1.

Yegor

Comment 5 David Fisher 2009-02-18 10:48:53 UTC
Jason had this analyzed correctly. GetBytes is the Workbook part of the output file. All binary results must always be wrapped in a POIFS to be a valid file. The BIFF8 can look like other older Excel files enough that it can sometimes read most of the file.

Look at HSSFWorkbook and you can see how GetBytes is used:

    /**
     * Method write - write out this workbook to an Outputstream.  Constructs
     * a new POI POIFSFileSystem, passes in the workbook binary representation  and
     * writes it out.
     *
     * @param stream - the java OutputStream you wish to write the XLS to
     *
     * @exception IOException if anything can't be written.
     * @see org.apache.poi.poifs.filesystem.POIFSFileSystem
     */

    public void write(OutputStream stream)
            throws IOException
    {
        byte[] bytes = getBytes();
        POIFSFileSystem fs = new POIFSFileSystem();

        // For tracking what we've written out, used if we're
        //  going to be preserving nodes
        List excepts = new ArrayList(1);

        // Write out the Workbook stream
        fs.createDocument(new ByteArrayInputStream(bytes), "Workbook");

        // Write out our HPFS properties, if we have them
        writeProperties(fs, excepts);

        if (preserveNodes) {
            // Don't write out the old Workbook, we'll be doing our new one
            excepts.add("Workbook");
            // If the file had WORKBOOK instead of Workbook, we'll write it
            //  out correctly shortly, so don't include the old one
            excepts.add("WORKBOOK");

            // Copy over all the other nodes to our new poifs
            copyNodes(this.filesystem,fs,excepts);
        }
        fs.writeFilesystem(stream);
        //poifs.writeFilesystem(stream);
    }