Is there a way to hide excel formula using Apache POI

0 votes
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?
Oct 10, 2022 in Others by Kithuzzz
• 28,900 points
221 views

1 answer to this question.

0 votes

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();

 }
}
answered Oct 11, 2022 by narikkadan
• 53,520 points

Related Questions In Others

0 votes
1 answer
0 votes
1 answer

Is there a way in Microsoft Excel to give specific bins different bin widths when making a histogram plot?

Excel built-in histogram tool only allows equal ...READ MORE

answered Oct 2, 2022 in Others by narikkadan
• 53,520 points
164 views
0 votes
1 answer

Is there a way to lock cells after editing an excel sheet that's on sharepoint?

The Excel Web version (which, based on ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 53,520 points
174 views
0 votes
1 answer

Is there any way in python to auto-correct spelling mistake in multiple rows of an excel files of a single column?

Use Spellchecker for doing your stuff: import pandas ...READ MORE

answered Oct 14, 2022 in Others by narikkadan
• 53,520 points
324 views
0 votes
1 answer
0 votes
1 answer

Can anyone show how to modify an existing excel sheet using Apache POI?

Hello Prakash, to modify an existing excel ...READ MORE

answered Jul 17, 2019 in Selenium by Anvi
• 14,170 points
2,891 views
0 votes
1 answer

How can I read numeric data from an Excel sheet using Selenium Webdriver?

Hey Jignesh, for reading numeric data from ...READ MORE

answered Jul 17, 2019 in Selenium by Abha
• 28,120 points
6,802 views
0 votes
1 answer

Is there a way to test a formula result in excel and type it only once, all within one cell and not using a user defined function?

Use the Let function: =LET(Value,A1+B2+C4+G3+B4,IF(Value>10,"No",Value)) I hope this helps ...READ MORE

answered Jan 9 in Others by narikkadan
• 53,520 points
41 views
0 votes
1 answer
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP