Bug 50020 - IllegalStateException received when creating Data validation in sheet with macro
Summary: IllegalStateException received when creating Data validation in sheet with macro
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 3.7-dev
Hardware: PC Windows XP
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2010-09-28 11:43 UTC by Simon Kelly
Modified: 2010-10-04 04:47 UTC (History)
0 users


Note You need to log in before you can comment on or make changes to this bug.
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);

            HSSFName nameObj = wb.createName();

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

            HSSFDataValidation dataValidation = new HSSFDataValidation(
                new CellRangeAddressList(6, 6, 0, 0), 

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

        catch (IOException e) {

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