CODEWITHSUNDEEP
javaexcelvalidationapache-poi
tala d
tala d

Reputation: 119

How to validate all the column of a particular header name in excel using POI api

I have a excel with headers, empName, empId, Department. I need to provide dropdown for user to enter department Name. User should be able to entry only the values present in drop down. I tried below code but it is not validating department column. CellRangeAddressList addressList = new CellRangeAddressList(2, 2, 2, 2); DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(new String[] { "ACCOUNTS", "ISE", "SALES" }); DataValidation dataValidation = new HSSFDataValidation(addressList, dvConstraint); dataValidation.setSuppressDropDownArrow(true); sheet.addValidationData(dataValidation); font.setFontHeightInPoints((short) 16);

Upvotes: 0

Views: 2556

Answers (1)

Axel Richter
Axel Richter

Reputation: 61975

Following code creates a workbook having a sheet having the header cells 

  |    A    |   B   |     C      |
1 | empName | empId | Department |

in A1:C1 and data validation list in C2:C65536 in HSSF or C2:C1048576 in XSSF.

The code works for binary *.xls files (HSSF) as well as for Office Open XML *.xlsx files (XSSF).

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 CreateExcelDataValidationListsWholeColumn {

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

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

  Sheet sheet = workbook.createSheet("Sheet1");

  String[] headers = new String[]{"empName", "empId", "Department"};
  Row row = sheet.createRow(0);
  for (int c = 0 ; c < headers.length; c++) {
   row.createCell(c).setCellValue(headers[c]);
  }

  //data validation in column B, except first row:
  DataValidationHelper dvHelper = sheet.getDataValidationHelper();
  DataValidationConstraint dvConstraint = dvHelper.createExplicitListConstraint(new String[]{"ACCOUNTS", "ISE", "SALES"}) ;
  int lastRow = workbook.getSpreadsheetVersion().getLastRowIndex();
  CellRangeAddressList addressList = new CellRangeAddressList(1, lastRow, 2, 2); // C2:C65536 in HSSF or C2:C1048576 in XSSF   
  DataValidation validation = dvHelper.createValidation(dvConstraint, addressList);
  validation.setShowErrorBox(true);
  sheet.addValidationData(validation); // data validation for C2:C65536 in HSSF or C2:C1048576 in XSSF

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

 }
}

Upvotes: 1

Related Questions