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

Related Questions In Others

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

Runtime error 438 while importing data in excel from secured website using VBA

Replace With ieDoc.forms(0) .userType.Value = "1" ...READ MORE

answered Sep 23 in Others by narikkadan
• 37,660 points
104 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 in Others by narikkadan
• 37,660 points
343 views
0 votes
1 answer

Apache POI. Setup data filters in Excel

It's already enabled in Apache POI 3.7. ...READ MORE

answered Oct 21 in Others by narikkadan
• 37,660 points
78 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 in Others by narikkadan
• 37,660 points
74 views
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 in Others by narikkadan
• 37,660 points
196 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