Bug 26321 - Array-entered formula (221h) not supported
Summary: Array-entered formula (221h) not supported
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 2.0-pre3
Hardware: PC other
: P3 major (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2004-01-21 19:19 UTC by Jennie Nguyen
Modified: 2008-08-31 09:39 UTC (History)
0 users

Initial XL file (18.00 KB, application/vnd.ms-excel)
2004-02-24 18:27 UTC, Marc Dworkin
POI Generated XL file (17.50 KB, application/vnd.ms-excel)
2004-02-24 18:28 UTC, Marc Dworkin

Note You need to log in before you can comment on or make changes to this bug.
Description Jennie Nguyen 2004-01-21 19:19:58 UTC
 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
    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, "$ ");
                    //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

                    new_val = name_to_vals.get(name);
                    int cell_type = cell.getCellType();
                    //check for cell type
                        case HSSFCell.CELL_TYPE_NUMERIC:

                        //Formula -- *** DO NOTHING IF FORMULA !! *****
                        case HSSFCell.CELL_TYPE_FORMULA:
            //write out results to intros workbook
            fout = new FileOutputStream(intros_filename);
        catch (Exception exc){
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 );

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. 
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.