Bug 50020

Summary: IllegalStateException received when creating Data validation in sheet with macro
Product: POI Reporter: Simon Kelly <sjk1471>
Component: HSSFAssignee: POI Developers List <dev>
Status: RESOLVED FIXED    
Severity: normal    
Priority: P2    
Version: 3.7-dev   
Target Milestone: ---   
Hardware: PC   
OS: Windows XP   

Description Simon Kelly 2010-09-28 11:43:01 UTC
Add a cell drop-down (using a formula list constraint) in a worksheet where there is already a macro attached.

Expected result:
The chosen cell has a validation list applied to it.

Actual result:
IllegalStateException occurs.

Stack trace:
Exception in thread "main" java.lang.IllegalStateException: Unexpected (org.apache.poi.hssf.record.UnknownRecord) while looking for DV Table insert pos
	at org.apache.poi.hssf.model.RecordOrderer.findDataValidationTableInsertPos(RecordOrderer.java:310)
	at org.apache.poi.hssf.model.RecordOrderer.findSheetInsertPos(RecordOrderer.java:98)
	at org.apache.poi.hssf.model.RecordOrderer.addNewSheetRecord(RecordOrderer.java:92)
	at org.apache.poi.hssf.model.Sheet.getOrCreateDataValidityTable(Sheet.java:1579)
	at org.apache.poi.hssf.usermodel.HSSFSheet.addValidationData(HSSFSheet.java:364)
	at com.du.sjk.scrap.POIBug.main(POIBug.java:42)


Code to reproduce:
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFName;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddressList;

public class POIBug
{
    /**
     * filename which points to any existing .xls file with 
     * a macro attached to the first sheet.
     */
    private static String filename = "C:\\scrap\\test.xls";


    public static void main(String[] args)
    {
        try {
            HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(filename));
            HSSFSheet sheet = wb.getSheetAt(0);
            HSSFCell cell;
            for (int i = 0; i < 5; i++) {
                cell = sheet.createRow(i).createCell(0);
                cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                cell.setCellValue(i);
            }

            HSSFName nameObj = wb.createName();
            nameObj.setNameName("list");
            nameObj.setRefersToFormula("$A$1:$A$6");

            cell = sheet.createRow(6).createCell(0);

            HSSFDataValidation dataValidation = new HSSFDataValidation(
                new CellRangeAddressList(6, 6, 0, 0), 
                DVConstraint.createFormulaListConstraint("list")
             );
            cell.getSheet().addValidationData(dataValidation);

            wb.write(new FileOutputStream("C:\\scrap\\out.xls"));
        }

        catch (IOException e) {
            e.printStackTrace();
        }
    }
}

Build where bug occurred: poi-3.6-20091214
Comment 1 Simon Kelly 2010-09-28 12:35:34 UTC
This bug occurs with the simplest of macros. Here is the VB Macro I used to reproduce this (added in first sheet but infact can be on any sheet or in separate 'module'):

Sub AnyOldMacro()
    MsgBox "Oh Hi!"
End Sub
Comment 2 Yegor Kozlov 2010-10-04 04:47:42 UTC
Fixed in r1004143, junit added

Yegor