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.
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.
Can you please retry with the alpha, if not upload a code snippet that fails but doesnt include all of your custom classes. Jason
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
Please check if the problem still exists in 3.1-beta1. Yegor
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); }