JExelApi read formulaNumberValue

[Frank Hamilton]

Hi ,

I have a problem with an write/read test with jexel api.
How can I get the formula cell value if I change its variables value with this api.

This is my test :

Formula value for A5 is =SUM(A1:A4)

Although the program change the value of A1,A2,A3,A4 , the A5 value that I get is the old value before change.

public static void main(String[] args) throws Exception{
		JExelTest jExelTest=new JExelTest();
		jExelTest.setOutputFile("/home/paco/Desktop/frank.xls");
		jExelTest.write();
		jExelTest.setOutputFile("/home/paco/Desktop/frankR.xls");
		jExelTest.read();
	}
public void write() throws IOException, WriteException, BiffException {
			File file = new File(inputFile);
			WorkbookSettings wbSettings = new WorkbookSettings();
			Workbook w = Workbook.getWorkbook(file);
			wbSettings.setLocale(new Locale("es", "ES"));
			WritableWorkbook workbook = Workbook.createWorkbook(new File("/home/paco/Desktop/frankR.xls"),w,wbSettings);
			WritableSheet excelSheet = workbook.getSheet(0);
			addNumber(excelSheet, 0, 0, 3);
			addNumber(excelSheet, 0, 1, 3);
			addNumber(excelSheet, 0, 2, 3);
			addNumber(excelSheet, 0, 3, 3);
			workbook.write();
			workbook.close();
		}

		public void read() throws IOException, FormulaException  {
			File inputWorkbook = new File(inputFile);
			Workbook w;
			try {
				WorkbookSettings wbSettings = new WorkbookSettings();
				wbSettings.setLocale(new Locale("en", "EN"));
				w = Workbook.getWorkbook(inputWorkbook,wbSettings);
				// Get the first sheet
				Sheet sheet = w.getSheet(0);
				Cell cell=sheet.getCell(0,4);
				if (cell.getType().equals(CellType.NUMBER_FORMULA)){
					FormulaCell fc=(FormulaCell)cell;
                                       System.out.println("I got a number "+ cell.getContents());
				}

				w.close();
			} catch (BiffException e) {
				e.printStackTrace();
			}
		}
	
	private void addNumber(WritableSheet sheet, int column, int row,
			Integer integer) throws WriteException, RowsExceededException {
		Number number;
		number = new Number(column, row, integer);
		sheet.addCell(number);
	}

Any idea,
thanks

[Ulf Dittmer]

jExcelApi is a library for reading and writing XLS files, not a spreadsheet engine that can evaluate formulas.

Apache POI (the other notable library for handling Excel files) has a formula evaluator that I think could be used for this.

[Frank Hamilton]

Thank so much.