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 in Others by Kithuzzz
• 20,660 points
77 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 by narikkadan
• 37,660 points

Related Questions In Others

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 in Others by narikkadan
• 37,660 points
60 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 in Others by narikkadan
• 37,660 points
74 views
0 votes
1 answer
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16 in Others by Edureka
• 13,640 points
202 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,768 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,554 views
0 votes
1 answer
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