Getting #N/A Values After Calculating Formulas

I have a spreadsheet that calculates some formulas that reference input cells, the outputs are placed in some other cells. I read the excel as a Workbook, and update the input cells, then I read from my output cells but they are all #N/A results.

Here’s the Java code

	public static JsonNode calcRate(String calculator, String inputCsv) {
	ObjectNode response = Json.newObject();
	
	try {			
		File calcFile = getCalcFile(calculator);
		
		List<String> inputs = Arrays.asList(inputCsv.split(","));
		
		//create workbook
		Workbook workbook = new Workbook(calcFile.getAbsolutePath());
		
		//set input to standard input row
		Worksheet worksheet = workbook.getWorksheets().get("IO");
		Cells cells = worksheet.getCells();		
		for(int col = 0; col < inputs.size(); col++) {
			cells.get(1, col).setValue(inputs.get(col));
		}		
		
		workbook.calculateFormula();
		
		//get output from standard output row
		Cell lastCellOutput = cells.endCellInRow(3);
		for(int col = 0; col <= lastCellOutput.getColumn(); col++) {
			response.put(cells.get(2, col).getValue().toString(), cells.get(3, col).getValue().toString()); 
		}
								
	} catch (Exception e) {
		e.printStackTrace();
	}
	
	return response;
}

Do I actually need to save as another excel file after workbook.calculateFormula() ? Then reference that one?

Is there a more optimal way of doing this, ie. better performance?

@syedihuda,

Thanks for the code segment and details.

Could you provide us your template file and complete JAVA program (runnable) to reproduce the issue on our end. You may also simplify your code a bit to remove any inter dependency for other objects, methods, etc. to paste it here, so we could evaluate your issue precisely and help you out soon.

After looking at it a bit further, let me ask a separate but related question: when Aspose does “setValue” for a cell, it appears to save value as Text, and the formulas don’t seem to be triggered. In fact, if I open the excel file and hit F2 in one of those cells where it did setValue, the cell appears to be updated and the formula is triggered.

How do I set values in Aspose as general values, not Text, such that formulas are triggered (ie. how to F2 into the cell)?

See image below, the original and then after hitting F2 in the cells in 2nd row:

image.png (4.4 KB)
image.png (5.4 KB)

Well, I figured it out.

Using putValue instead of setValue did the trick, although the documentation isn’t very clear why. I needed to use the one with conversion boolean to true: putValue(string, boolean)

So, in my code, using this:
cells.get(1, col).putValue(inputs.get(col), true);

instead of:
cells.get(1, col).setValue(inputs.get(col));

Worked.

@syedihuda

Thanks for using Aspose APIs.

For formulas, please use the following code

//Set the formula of the cell
cell.setFormula("=A1+B1");

It is good to know that you were able to sort out your issue with putValue() method. Let us know if you encounter any other issue, we will be glad to look into it and help you further.