Is there a way to hide excel formula using Apache POI

Question

I want to fill in the value for column B using the choice made in column A's drop-down menu. With the aid of the VLOOKUP formula, all column B cells are functioning properly. However, in order to prevent user-caused formula changes, I now wish to hide the formula for column B cells (even by mistake).

Even after being hidden, the formula should still function as expected.

Is it possible to accomplish it using Apache POI. Or, is there another method to use Apache POI to achieve auto populating based on drop-down choices?

Answer 1

In Excel, hiding formulae is a feature of the cell style. Therefore, using  CellStyle.setHidden(true)..would be the simplest solution.

However, that will just mask the formula and not stop users from changing it. For this purpose, sheet protection is used. Therefore, a combination of the two would be required.

Following complete example shows that. Fomulas in C2:C4 are hidden and protected.

import java.io.FileOutputStream;

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

public class CreateExcelDefaultColumnStyleNotLockedAndLockedHideFormulas {

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

  Workbook workbook = new XSSFWorkbook(); String filePath = "./CreateExcelDefaultColumnStyleNotLockedAndLockedHideFormulas.xlsx";
  //Workbook workbook = new HSSFWorkbook(); String filePath = "./CreateExcelDefaultColumnStyleNotLockedAndLockedHideFormulas.xls";
  
  CellStyle lockedHideFormulas = workbook.createCellStyle();
  lockedHideFormulas.setLocked(true);
  lockedHideFormulas.setHidden(true);
  CellStyle notLocked = workbook.createCellStyle();
  notLocked.setLocked(false);

  Sheet sheet = workbook.createSheet();

  Row row = sheet.createRow(0);
  Cell cell = null;
  for (int c = 0; c < 3; c++) {
   cell = row.createCell(c);
   cell.setCellValue("Col " + (c+1));
  }

  for (int r = 1; r < 4; r++) {
   row = sheet.createRow(r);
   for (int c = 0; c < 2; c++) {
    cell = row.createCell(c);
    cell.setCellValue(r * (c+1));
    cell.setCellStyle(notLocked); 
   }
   cell = row.createCell(2);
   cell.setCellFormula("A" + (r+1) + "*B" + (r+1));
   cell.setCellStyle(lockedHideFormulas); 
  }

  sheet.setDefaultColumnStyle(0, notLocked);
  sheet.setDefaultColumnStyle(1, notLocked);
  sheet.setDefaultColumnStyle(2, notLocked);
  sheet.protectSheet("");

  FileOutputStream out = new FileOutputStream(filePath);
  workbook.write(out);
  out.close();
  workbook.close();

 }
}