Tags down


Datavalidation dropdown list - How to prohibit empty value?

By : hyoseok
Date : July 29 2020, 11:00 PM
I hope this helps . Using Excel's data validation lists it is not possible to prohibit empty cells. Set of Ignore blank checkbox unchecked only prevents an existing cell to get changed from any value to an empty value. But selecting a cell in Excel and then pressing Del deletes the whole cell and not only empties the cell value. But the data validation does not work on cells which are not already present. So this is an Excel issue and apache poi cannot do anything against this since apache poi cannot do what Excel cannot do.
The best we can do using Excel's data validation and using apache poi is telling the users as much and often as possible what they shall do. To do so we can show a input message when cell is selected additional to the error message.
code :
import java.io.FileOutputStream;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddressList;

class CreateExcelDataValidationList {

 public static void main(String[] args) throws Exception {

  //Workbook workbook = new HSSFWorkbook();
  Workbook workbook = new XSSFWorkbook();

  Sheet sheet = workbook.createSheet();

  DataValidationHelper dvHelper = sheet.getDataValidationHelper();
  DataValidationConstraint dvConstraint = dvHelper.createExplicitListConstraint(new String[]{"Checked", "Unchecked"}) ;
  int lastRow = workbook.getSpreadsheetVersion().getLastRowIndex();
  CellRangeAddressList addressList = new CellRangeAddressList(0, 10, 0, 0);

  DataValidation validation = dvHelper.createValidation(dvConstraint, addressList);
  validation.createPromptBox("Prompt", "Please select Cecked or Unchecked from dropdown list.");
  validation.createErrorBox("Error", "Please select Cecked or Unchecked from dropdown list.");
  if (workbook instanceof XSSFWorkbook) validation.setShowErrorBox(true);
  if (workbook instanceof XSSFWorkbook) validation.setShowPromptBox(true);

  FileOutputStream out = null;
  if (workbook instanceof HSSFWorkbook) {
   out = new FileOutputStream("CreateExcelDataValidationList.xls");
  } else if (workbook instanceof XSSFWorkbook) {
   out = new FileOutputStream("CreateExcelDataValidationList.xlsx");


Share : facebook icon twitter icon

Create dataValidation from named list in different sheet

By : Tate Norman
Date : March 29 2020, 07:55 AM
help you fix your problem Actually managed to solve my problem by creating an additional name to sheetIndex(0) and reference it to the name in sheetIndex(1).... The following is the code which works as desired:
code :
    Name name = dctmWorkSheet.getWorkbook().createName();
    name.setRefersToFormula(getRangeRefersToFormula(valueAssSheet, firstRow, rowIndex, insertCol));
    Name name2 = dctmWorkSheet.getWorkbook().createName();

    validationHelper = dctmWorkSheet.getDataValidationHelper();
    constraint = validationHelper.createFormulaListConstraint(name2.getNameName());
    dataValidation = validationHelper.createValidation(constraint, cral);
    dataValidation.createErrorBox("Error", "Please use Pick List Value");

OpenXml DataValidation set predefined List for columns

Date : March 29 2020, 07:55 AM
I wish this helpful for you Okay I got this solved. Added escaped double quotes to formula and done.
code :
DataValidation dataValidation = new DataValidation
    Type = DataValidationValues.List,
    AllowBlank = true,
    SequenceOfReferences = new ListValue<StringValue>() { InnerText = "B1" },
    Formula1 = new Formula1("\"True,False\"") // escape double quotes, this is what I was missing

DataValidations dvs = worksheet.GetFirstChild<DataValidations>(); //worksheet type => Worksheet
if (dvs != null)
    dvs.Count = dvs.Count + 1;
    DataValidations newDVs = new DataValidations();
    newDVs.Count = 1;

How to Prohibit a Particular Option of a Dropdown to Send Value?

By : Apoorv
Date : March 29 2020, 07:55 AM
it fixes the issue It turned out that all you needed to have a default prompt that sends null to db if no option is selected and sends value to db if an option is selected is to add ignore_date: true to f.time_select attributes. So I just turned
code :
<%= f.time_select :hours, {minute_step: 5, prompt: true, order: [:hour]} %>
<%= f.time_select :hours, {minute_step: 5, prompt: true, ignore_date: true, order: [:hour]} %>

Handle error in list DataValidation formula

By : kyntyn
Date : March 29 2020, 07:55 AM
Hope that helps I have a list DataValidation formula with depends on other column. , Knowing MATCH results in #N/A I wrapped it in IF(IFNA(...
code :
and whole formula is

Excel DataValidation dropdown value coming back null

By : Kripa Sindhu Singh
Date : March 29 2020, 07:55 AM
like below fixes the issue Your problem is not reproducible for me. This question shows why we always need a Minimal, Reproducible Example.
So let's have a such:
code :
import org.apache.poi.ss.usermodel.*;
import java.io.FileInputStream;

class ExcelParseCellValue {

 static void setSomething(Cell cell) {
  Object var = parseCellValue(cell);

 static Object parseCellValue(Cell cell) {
  Object var = null;
  switch (cell.getCellType()) {
   case NUMERIC:
    var = cell.getNumericCellValue();
   case STRING:
    var = cell.getStringCellValue();
   case BLANK:
    var = null;
    var = null;
  return var;

 public static void main(String[] args) throws Exception {

  Workbook workbook = WorkbookFactory.create(new FileInputStream("ExcelDataValidation.xlsx"));
  Sheet sheet = workbook.getSheetAt(0);

  for (Row row : sheet) {
   for (Cell cell : row) {
C:\java>c:\java\jdk-12.0.2\bin\java -cp .;.\*;poi-4.1.1\*;poi-4.1.1\lib\*;poi-4.1.1\ooxml-lib\* ExcelParseCellValue
Related Posts Related Posts :
  • IMB PCF agent returns error while inquiring channel status
  • Java EE 7 batch API (JSR-352): it's possible to stop a single step and not all the job?
  • Java multithread example - is object shared or not
  • Why String's replaceAll() method come at high performance cost at the first time and faster at the next time?
  • Lua script coverts empty array to object
  • Doesn't Access to external storage in API-29
  • How to add a button to the Android system navigation bar?
  • If inside for-each-loop to Stream
  • Shift array to the right by one slot
  • How to setup a Java development environment for Z3
  • how to make notify() works properly with wait()
  • IllegalFormatConversionException d != java.lang.Integer when trying to printf date?
  • Duplicate logic in backend and frontend with Domain Driven Design
  • How would I randomize an int[][]?
  • StringDate to Date coming in different Time in SimpleDateFormat in java
  • Get content uri from file path in android for video
  • function param issue in Scala 11, works in Scala 12 using resilience4j
  • Android unit testing : calling a method from fragment which uses context
  • How to remove E notation from Double value
  • Check if input is any of the values in an array
  • DateTimeParseException - could not be parsed at index 0
  • How to make a Java HTTP async request?
  • Make Javafx/Scenebuilder recognize tagged fields and methods when splitting the MainController into multiple files?
  • Why does this time fail to be parsed by DateTimeFormatter.ofPattern()?
  • Java - Merge objects of list given a condition
  • How to know if arraylist contains value in property of object?
  • Java modelling class if objects have same properties but they are fundamentally different
  • False Positive and False Negative
  • Java UnknownFormatConversionException = ','
  • Apache POI recording only 1 row in the spreadsheet
  • Getting Constructor from Java Class that would be called for argument types, not requiring exact argument & paramete
  • @JsonIgnore with Conditions
  • Does synchronizing on the static field that you are modifying make your code thread safe?
  • Compiler errors while trying to make a pop up
  • Trying to take two timestamps, subtract them, convert the difference to seconds and store in an int variable
  • How can I use Optionals instead of nested if statement?
  • How do I compare values in parallel arrays to eliminate duplicates?
  • make a method which returns the number of one digit numbers in an array
  • Does calling multiple times save() method of hibernate with same object insert new record in DB?
  • Single Number solving by Haspmap, return always be a "@"
  • What do you do about the JLabel classes? It says, "JLabel not a statement" for the error
  • Functional Interface call for a new Instance
  • When I tried to add ArrayList into ArrayList second ArrayList is repeating
  • How can I print the longest word from a user defined list?
  • enabling authentication in ignite
  • Providing OSGi Service Without Implementing Interface
  • Read manifest from JAR with gradle
  • Create one element Set the same way (if applicable) as create single element list
  • pass by reference an integer in java
  • how to generate the POI excel file in a specific location?
  • Issue trying to create a Bubble Sort using ArrayList<Integer>
  • How can I avoid typechecking in this situation
  • How to create a map<k,v> between first and last nested object using Java streams?
  • First time writing junit: Need suggestions
  • what is the best solution to split in chunk monthly given two dates startTime and EndTime in Java?
  • View Binding causes "The specified child already has a parent"
  • parse float in java with trailing junk
  • How to convert color from ColorPicker to String value in javaFX
  • Spring test integration not run how I deal with this error?
  • Java 8 Streams : Count the occurrence of elements(List<String> list1) from list of text data(List<String> li
  • shadow
    Privacy Policy - Terms - Contact Us © voile276.org