CODEWITHSUNDEEP
javaapache-poi
Napstablook
Napstablook

Reputation: 614

How to disable editing in a cell according to the value in another cell in Apache POI?

I have a requirement where I need to only ask the user for a flag and if that flag is true, I need him to input a number in another cell in that row. If it's false then that cell should be un-editable. I know how to put restrictions on cells but I have no idea about this kind of conditional restrictions. enter image description here

Upvotes: 4

Views: 3043

Answers (1)

Axel Richter
Axel Richter

Reputation: 61860

If the flags in column A would be already present at the time apache poi is parsing the sheet, then the CellStyle.setLocked could be set to False if there is True in column A. So if the sheet is protected, those cells will be not locked and be editable.

But if the True or False in column A will be changed in Excel GUI, then this is not possible since it will not conditional changing while changing the value in column A. For this there would must be a possibility for conditional formatting the cells locking. But this possibility does not exists, at least not without using VBA.

Then only data validation is possible which checks whether A is true when B gets a value and alerts if not. Data Validations are possible to set using apache poi.

Example:

import java.io.FileOutputStream;

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

public class CreateExcelDataValidationIfATrueThenB {

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

  Workbook workbook = new XSSFWorkbook();
  Sheet sheet = workbook.createSheet("Sheet1");

  DataValidationHelper dvHelper = sheet.getDataValidationHelper();
  DataValidationConstraint dvConstraint = dvHelper.createCustomConstraint("AND(A1, B1<>\"\")");

  CellRangeAddressList addressList = new CellRangeAddressList(-1, -1, 1, 1);
  DataValidation validation = dvHelper.createValidation(dvConstraint, addressList);

  validation.createPromptBox("For column B:", "If column A is True, then please put content in column B, else not.");
  validation.setShowPromptBox(true);
  validation.createErrorBox("Bad Value", "Please put content in column B only if column A is True, else not!");
  validation.setShowErrorBox(true);

  sheet.addValidationData(validation);

  sheet.createRow(0).createCell(0).setCellValue(true);
  sheet.createRow(1).createCell(0).setCellValue(false);
  sheet.createRow(2).createCell(0).setCellValue(false);
  sheet.createRow(3).createCell(0).setCellValue(true);

  workbook.write(new FileOutputStream("CreateExcelDataValidationIfATrueThenB.xlsx"));
  workbook.close();

 }

}

As said above, conditional formatting the cells locking would be possible using VBA events while changing the cells in column A. But apache poi not provides creating VBA code. So if this would be the requirement, then a Excel template must be used which contains those macros already.

Upvotes: 3

Related Questions