Hi, I am working on a workbook with Macros in it. I'm not sure if this is the problem. I am currently on Windows 2000, using Excel 2000. When I execute my code, I get "Unknown PTG" warnings, but I am not modifying or inserting any Formulas at all. The formula cells are left alone. When I open the workbook, I get a "File Error: Data may have been lost" message. Some of the cells have #NAME? values in them, and if I re-save the file, almost every cell that had values changes to either #NAME? or #REF!, and sometimes it would not allow me to save the file at all ("File not saved"). I don't know what I am doing wrong, any help is greatly appreciated! Below is the code the causes this problem: //this method writes to the xsl workbook -- Gets File error when opening and //re-saving private void set_intros_values() { FileInputStream fin = null; FileOutputStream fout = null; POIFSFileSystem poifs; HSSFWorkbook wb; HSSFName hssfname; HSSFSheet hssfsheet; HSSFCell cell; int index = 0, i = 0; int rownum = 0, colnum = 0; String name, cell_ref = null, sheet_name = null; StringTokenizer st, prev_str; Object new_val; double prev; try { fin = new FileInputStream(intros_filename); poifs = new POIFSFileSystem(fin); wb = new HSSFWorkbook(poifs); //go through the names and see if any of the named ranges for (Enumeration e = name_to_vals.keys(); e.hasMoreElements();) { name = (String)e.nextElement(); index = wb.getNameIndex(name); hssfname = wb.getNameAt(index); cell_ref = hssfname.getReference(); //format -- sheet_name!$col$row st = new StringTokenizer(cell_ref, "$ "); st.nextToken(); //get cell column and row colnum = get_col(st.nextToken().toLowerCase()); rownum = Integer.valueOf(st.nextToken()).intValue() - 1; //the cell to write new value to hssfsheet = wb.getSheet(hssfname.getSheetName()); cell = (HSSFCell)(hssfsheet.getRow((short)rownum).getCell ((short)colnum)); new_val = name_to_vals.get(name); int cell_type = cell.getCellType(); //check for cell type switch(cell_type){ //Double case HSSFCell.CELL_TYPE_NUMERIC: cell.setCellValue(((Double)new_val).doubleValue()); break; //Formula -- *** DO NOTHING IF FORMULA !! ***** case HSSFCell.CELL_TYPE_FORMULA: break; default: break; } } } //write out results to intros workbook fout = new FileOutputStream(intros_filename); wb.write(fout); fout.close(); fin.close(); } catch (Exception exc){ exc.printStackTrace(); } }
Created attachment 10520 [details] Initial XL file
Created attachment 10521 [details] POI Generated XL file
I had a similar issue. It only arose on array formulas. I have attached the initial excel file and the POI generated file. The code is simply: POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream("c:\test\before.xls")); HSSFWorkbook wb = new HSSFWorkbook(fs); FileOutputStream out = new FileOutputStream("c:\test\after.xls"); wb.write( out ); out.close();
Still have it in 3.1-beta2. Array-Entered Formula (221h) are not yet supported and it might be a clue. The attached workbook contains a couple of such ARRAY records mixed with FORMULA records. After serialization the order if records is changed and it seems to be the problem. Yegor
Fixed in svn r690636. POI now does round trip re-serialization of workbooks with ArrayRecords and/or TableRecords without introducing errors. However, POI is still not able to create/edit Array or Table formulas.