Bug 26321

Summary: Array-entered formula (221h) not supported
Product: POI Reporter: Jennie Nguyen <jmnguyen>
Component: HSSFAssignee: POI Developers List <dev>
Status: RESOLVED FIXED    
Severity: major    
Priority: P3    
Version: 2.0-pre3   
Target Milestone: ---   
Hardware: PC   
OS: other   
Attachments: Initial XL file
POI Generated XL file

Description Jennie Nguyen 2004-01-21 19:19:58 UTC
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();
        }
    }
Comment 1 Marc Dworkin 2004-02-24 18:27:42 UTC
Created attachment 10520 [details]
Initial XL file
Comment 2 Marc Dworkin 2004-02-24 18:28:25 UTC
Created attachment 10521 [details]
POI Generated XL file
Comment 3 Marc Dworkin 2004-02-24 18:31:28 UTC
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();

Comment 4 Yegor Kozlov 2008-05-17 04:28:54 UTC
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
Comment 5 Josh Micich 2008-08-30 21:51:53 UTC
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.