How to remove the data validation of excel from using apache POI. I have following code. On first run, the dropdowns are getting created properly, but the same is not getting updated once I run the program again with different values in the String List.
FileInputStream fsIP= new FileInputStream(new File("D:\\template3.xls")); //Read the spreadsheet that needs to be updated
HSSFWorkbook wb = new HSSFWorkbook(fsIP); //Access the workbook
HSSFSheet worksheet = wb.getSheetAt(0); //Access the worksheet, so that we can update / modify it.
// System.out.println(worksheet.getRow(1).getCell(2));
Cell cell = null; // declare a Cell object
DataValidation dataValidation = null;
DataValidationConstraint constraint = null;
DataValidationHelper validationHelper = null;
CellRangeAddressList addressList = new CellRangeAddressList(0,5,0,5);
//cell = worksheet.getRow(2).getCell(2); // Access the second cell in second row to update the value
DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(new String[]{"124", "20", "30"});
dataValidation = new HSSFDataValidation(addressList, dvConstraint);
dataValidation.setSuppressDropDownArrow(false);
worksheet.addValidationData(dataValidation);
// cell.setCellValue("OverRide Last Name"); // Get current cell value value and overwrite the value
fsIP.close(); //Close the InputStream
FileOutputStream output_file =new FileOutputStream(new File("D:\\template3.xls")); //Open FileOutputStream to write updates
wb.write(output_file); //write changes
output_file.close(); //close the stream
I had to remove/edit DataValidation inside sheet and couldn't find answer anywhere, here is what I did. (using apache-poi:3.14)
First, you shouldn't use this method except for reading concerns :
worksheet.getSheet("MySheet").getDataValidations()
It is always sending copies of DataValidation so that editing them will do nothing.
I had to go with this :
worksheet.getSheet("MySheet").getCTWorksheet().getDataValidations().getDataValidationList()
Then you will be able to edit or remove DataValidation from here using get/set/remove methods.
Regards.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With