0

I'm trying to write a simple program to add dropdown box in XSSFSheet in xlsm files. I've written the following code

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFCreationHelper;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;
import org.apache.poi.xssf.usermodel.XSSFDataValidationConstraint;
import org.apache.poi.xssf.usermodel.XSSFDataValidationHelper;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


public class XSSFDropDownTrial {

public static void main(String[] args) {
    // TODO Auto-generated method stub

    String fileName = "C:\\Ishan\\Test.xlsm";
    String fileName2 = "C:\\Ishan\\Test_Feb28.xlsm";
    FileOutputStream fout = null;
    FileInputStream fin = null;
     XSSFWorkbook workbook=null;
     XSSFCreationHelper createHelper = null;
     String[] dropDownList =  new String[5];
     dropDownList[0]="1";
     dropDownList[1]="2"; dropDownList[2]="3";
     dropDownList[3]="4"; dropDownList[4]="5";

    try {
        fin = new FileInputStream(new File(fileName));
        workbook = new XSSFWorkbook(OPCPackage.open(fin)
        );

//              createHelper = workbook.getCreationHelper();
           String [] listOfValues = new String [3];
           listOfValues[0] = "Ishan";
           listOfValues[1] = "IshanA";
           listOfValues[2] = "IshanB";
           CellRangeAddressList addressList = new CellRangeAddressList();
            addressList.addCellRangeAddress(1, 4, 100, 4);

            XSSFSheet sheet = workbook.createSheet("Data Validation");
            XSSFDataValidationHelper dvHelper = new 
            XSSFDataValidationHelper(sheet);
        XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint)dvHelper.createExplicitListConstraint(listOfValues);
        XSSFDataValidation validation = (XSSFDataValidation)dvHelper.createValidation(dvConstraint, addressList);
        validation.setShowErrorBox(true);
        validation.setSuppressDropDownArrow(true);
        sheet.addValidationData(validation);

             fout = new FileOutputStream(new File(fileName2));
            workbook.write(fout);
            fout.close();
            System.out.println("xlsm created successfully..");

        }catch(Exception e){
            e.printStackTrace();
        }finally{
            try{
                if(fout!=null){
                    fout.flush();
                    fout.close();
                    fout = null;
                }
                if(workbook!=null){
                    workbook = null;
                }
                if(fin!=null){
                    fin.close();
                    fin = null;
                }
                }catch(Exception e){
                    e.getMessage();
                }
            }

}

}

But it's giving error following error

The method addValidationData(XSSFDataValidation) is undefined for the type XSSFSheet

at the following line of code

sheet.addValidationData(validation);

I'm not sure why this is happening. I'm using apache-poi-3.8-20120326 JARs

But the thing is, there is one more java file in same project with same code, but there is no issue in that code. I've put that code also for reference

    package com;

    import java.io.File;
    import java.io.FileInputStream;
    import java.io.FileNotFoundException;
    import java.io.FileOutputStream;
    import java.io.IOException;


    import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
    import org.apache.poi.openxml4j.opc.OPCPackage;
    import org.apache.poi.ss.util.CellRangeAddressList;
    import org.apache.poi.xssf.usermodel.XSSFCreationHelper;
    import org.apache.poi.xssf.usermodel.XSSFDataValidation;
    import org.apache.poi.xssf.usermodel.XSSFDataValidationConstraint;
    import org.apache.poi.xssf.usermodel.XSSFDataValidationHelper;

    import org.apache.poi.xssf.usermodel.XSSFSheet;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;


    public class DemoXLSM {

        public static void main(String[] args) {
            // TODO Auto-generated method stub
            String fileName = "C:\\Ishan\\Test.xlsm";
            String fileName2 = "C:\\Ishan\\Test_Feb28.xlsm";
            FileOutputStream fout = null;
            FileInputStream fin = null;
             XSSFWorkbook workbook=null;
             XSSFCreationHelper createHelper = null;
             String[] dropDownList =  new String[5];
             dropDownList[0]="1";
             dropDownList[1]="2"; dropDownList[2]="3";
             dropDownList[3]="4"; dropDownList[4]="5";

            try {
                fin = new FileInputStream(new File(fileName));
                workbook = new XSSFWorkbook(OPCPackage.open(fin)
                );


                // dropdown
                String [] listOfValues = new String [3];
                   listOfValues[0] = "Ishan";
                   listOfValues[1] = "IshanA";
                   listOfValues[2] = "IshanB";
                   CellRangeAddressList addressList = new CellRangeAddressList();
                    addressList.addCellRangeAddress(1, 4, 100, 4);

                XSSFSheet sheet = workbook.createSheet("Data Validation");
                XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
                XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint)dvHelper.createExplicitListConstraint(listOfValues);
                XSSFDataValidation validation = (XSSFDataValidation)dvHelper.createValidation(dvConstraint, addressList);
                validation.setShowErrorBox(true);
                validation.setSuppressDropDownArrow(true);
                sheet.addValidationData(validation);
                // dropdown


                 fout = new FileOutputStream(new File(fileName2));
                workbook.write(fout);
                fout.close();
                System.out.println("xlsm created successfully..");

            } catch (FileNotFoundException e) {
                e.printStackTrace();
            } catch (InvalidFormatException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            }finally{
                try{
                    if(fout!=null){
                        fout.flush();
                        fout.close();
                        fout = null;
                    }
                    if(workbook!=null){
                        workbook = null;
                    }
                    if(fin!=null){
                        fin.close();
                        fin = null;
                    }
                    }catch(Exception e){
                        e.getMessage();
                    }
                }
        }

    }

There is no error in this code.

IshanAg24
  • 199
  • 2
  • 11
  • 1
    Don't use such an old version of Apache POI! Upgrade! – Gagravarr Feb 28 '18 at 18:59
  • @Gagravarr : I agree with you but the ongoing project I'm working on is using the same. So unfortunately I'll have to work with this, atleast till JAR migration occurs. Can you shed some light on why the problem maybe occuring ?. Because again, the same code is working in different java file. – IshanAg24 Mar 01 '18 at 04:33
  • 2
    "the same code is working in different java file": Since this is a compiler error, the compiler **must** run against different `poi-ooxml` - `jar`s containing different versions of the `XSSFSheet.class` then. Version `3.6` or lower for example. Do checking whether `poi-ooxml` version `3.6` or lower is in the class path and if so, do removing it. – Axel Richter Mar 01 '18 at 07:23
  • @Gagravarr : I took your advice and migrated to 3.17. – IshanAg24 Mar 08 '18 at 06:38
  • @AxelRichter : Your solution worked thanks. First I migrated to 3.17 as Gagravarr suggested. After importing new jars though, error was still there. So I tried removing the older jars from source code lib folder also and it worked. Thanks – IshanAg24 Mar 08 '18 at 06:40

0 Answers0