Excel formula gives error when write using Apache-poi library in Java

0 votes

I needed to write data to an excel file, which I was able to do. Another criterion is that if the status cell for the same row is "Received," the piece cell must be included in the summing.

Additionally, only visible cells should be included in the summing. I currently have two columns named "Status" (Column A2:A10) and "piece" (Column B2:B10), and I've added the following formula using the Apache-poi library:

SUMPRODUCT(SUBTOTAL(9,OFFSET(B2:B10,ROW(B2:B10)-(ROW(B2)),0,1)),(--(A2:A10="Recived")))

When the Java code is executed, the file is correctly written, but when I access the file, the formula cell displays as #VALUE! The value for the formula appears when I select the formula bar and press Enter.

Every time I open the Excel file, I want the formula's value to be visible.

Below is Java code

cell.setCellType(Cell.CELL_TYPE_FORMULA);
cell.setCellFormula("SUMPRODUCT(SUBTOTAL(9,OFFSET(B2:B10,ROW(B2:B10)-(ROW(B2)),0,1)),(--(A2:A10=\"Recived\")))");
Nov 5, 2022 in Others by Kithuzzz
• 28,900 points
246 views

1 answer to this question.

0 votes

I tested some sample code. The cell type must be set to Numeric (or whichever data type your excel formula will return).

See below example:

FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();     
Row row = sheet.getRow(0);
Cell cell2 = row.createCell(2);
//set cell formula
cell2.setCellFormula("SUM(A1:B1)+10");
//evaluate the formula
evaluator.evaluateFormulaCell(cell2);
//set the cell type to numeric
cell2.setCellType(Cell.CELL_TYPE_NUMERIC);
System.out.println(cell2.getNumericCellValue());

//write data to excel file
FileOutputStream out = new FileOutputStream(new File("res/Book1.xlsx"));
workbook.write(out);
out.close();
System.out.println("Excel written successfully..");
answered Nov 5, 2022 by narikkadan
• 53,520 points

Related Questions In Others

0 votes
1 answer

Writing large Excel in Java causing high CPU usage using apache-poi

Giving SXSSFWorkbook a window size appears to ...READ MORE

answered Jan 29 in Others by narikkadan
• 53,520 points
32 views
0 votes
1 answer

Is there a way to hide excel formula using Apache POI

In Excel, hiding formulae is a feature ...READ MORE

answered Oct 11, 2022 in Others by narikkadan
• 53,520 points
221 views
0 votes
0 answers

Data Driven Framework -- how to read and write in excel sheet using Selenium WebDriver with java

I'm using this code to read something, ...READ MORE

Oct 31, 2022 in Others by Kithuzzz
• 28,900 points
116 views
0 votes
0 answers

Can we write AES enryption in Javascript and Decryption in Java using a static SALT and IV

I'm working on AES encryption I want to ...READ MORE

Jun 4, 2019 in Others by Eighteen
• 120 points
1,441 views
0 votes
1 answer
0 votes
1 answer

What is the better API to Reading Excel sheets in java - JXL or Apache POI

Here are the things where both APIs ...READ MORE

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

How to get columns from Excel files using Apache POI?

The only way to see all the ...READ MORE

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

Formatting Excel Spreadsheet using Apache POI in JAVA

Try this: CellRangeAddress range= new CellRangeAddress(firstrow, lastrow, firstcol, ...READ MORE

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

How to create page borders using Apache POI in excel files with Java?

Microsoft Excel cannot do this. Libreoffice Calc ...READ MORE

answered Dec 13, 2022 in Others by narikkadan
• 53,520 points
92 views
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