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.