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 :
  • I am getting this "NullPointerException" Error
  • How can I split apart a string that has different amount of spaces into two parts?
  • Splitting a String and then displaying a portion of that split (last name part)
  • String comparison from a list in for loop
  • Add method java
  • what is the difference between parameters and arguments?
  • Replace a reflective call with a dynamically generated class
  • Spring Boot - How to call method from a service class just by the provided service class name?
  • Difference between java.util.Date and java.time.LocalDate in java
  • Difference between Flink state and ordinary class variables
  • Split XML stream by XML documents
  • How can I look at the SQL query really used by myBatis?
  • Java setter does not change the value of an integer
  • Vaadin-14: checking if client is a mobile device
  • Exception occurred at Bank bank = new Bank(bankNaam);
  • List<Long> is not serialised to String in Java
  • Getting jackson cast exception while trying to start gemfire pulse with spring boot 2
  • Can I use JDK for commercial use?
  • What is wrong about this code using Transaction Management in Spring
  • Semicolon expected in java method
  • Spring Boot Security OAuth2 custom exception message
  • Transpose 2D Matrix in Java - Time & Space Complexity?
  • How to select grand-grand child (Like 5th OR 6th grand child) element using xpath which has class name only
  • cucumber throwing java.lang.NoClassDefFoundError: io/cucumber/core/gherkin/FeatureParser
  • Days 30/360 calendar calculator implementation
  • Why does comparison of objects return false in my snake game
  • System.currentTimeMillis() vs Timestamp.valueOf(LocalDateTime.now(UTC)).getTime()
  • How to select all text in AutoCompleteTextView Android
  • Why is the computational complexity O(n^4)?
  • Does the JVM use system calls to seek OS functionalities?
  • Can't use float value from dimen file by databinding
  • How can I implement a "promise like" thread-synchronization system in Java 7?
  • Is there any way to access a sub-type method without casting using generic
  • How to detect device name in Safari on iOS 13 using apache WebSession in Java?
  • Migrating Java 8 project to Java 11
  • Use return of a method for string compare in a main if statement
  • How to resolved OnClick method click issue , it is not working
  • How can I know an undecorated JFrame has been fully painted on the screen?
  • how to solve java.sql.SQLException: No suitable driver found for jdbc:derby:
  • Multiline lambda
  • How can I display custom view same as when long pressed on search icon on below image?
  • How to enclose the brackets around a percentage?
  • Java. Collections. Partition by page limit
  • Import error in android studio design and v7 - Android Studio -
  • How to run bat file with arguments from a java program? (Trying to automate using a Command Line Tool )
  • Spring-boot swagger throws java.lang.ClassNotFoundException on io.swagger.models.parameters.Parameter
  • Mockito: mocking a method call of a generic typed class
  • Would it be okay for me to assign variables then reference them right after?
  • Adding a number into a LinkedList, inside an ArrayList
  • How to parse incoming stream where one piece determines another's interpretation in Java?
  • calling particluar function after delay in java
  • Difference between newObject[]{...parameters..} and PreparedStatementSetter Interface implementation in Spring JDBC?
  • How to create an array of integers in char type?
  • Split function returning whitespace character at the begining
  • Where to put validation in java to check whether a product name already exists in the Database or not? Service Layer or
  • Java Math.min Math.max Errors
  • Java Char Array prints ASCII
  • How to parse JSR-310 date to Instant?
  • Vaadin Spring Boot: "Cannot access state in VaadinSession or UI without locking the session" when multiple use
  • How to convert christian year to thai year in Java?
  • shadow
    Privacy Policy - Terms - Contact Us © voile276.org