3

I have created a worksheet, out.xls which had cell D6=D5*2 and D5 set to 1. My issue is that when I plug a value into D5 in jxl, D6 never calculates. D6 simply holds on the value it initially calculated when I plugged in 1 to D5 in excel.

note: I have a much larger programming problem that I am trying to tackle, this is just a very scaled down version to reduce error.

This is my first time ever use Jexcel and I only just learned java this last year, so any help would be appreciated. I spent 6 hours yesterday trying to find an answer on the web, but to no avail.

the output is attached below the code
Code: (left out the main and imports)

        WorkbookSettings custom= new WorkbookSettings();
        custom.setRationalization(true);
        custom.setRefreshAll(true);
        custom.setUseTemporaryFileDuringWrite(true);
        Workbook workbook = Workbook.getWorkbook(new File("out.xls"),custom); 


        WritableWorkbook copy = Workbook.createWorkbook(new File("output.xls"), workbook); 

        WritableSheet sheet2 = copy.getSheet(0); 
        SheetSettings customsheetsettings=new SheetSettings(sheet2);
        customsheetsettings.setAutomaticFormulaCalculation(true); 
        Number number = new Number(3, 4, 3);
        sheet2.addCell(number); 


        copy.write();
        copy.close(); 
        workbook.close();


        Workbook workbook2 = Workbook.getWorkbook(new File("output.xls")); 
        Sheet sheet=workbook2.getSheet(0);


        System.out.println("D5:"+sheet.getCell(3,4).getContents());
        FormulaCell formula5=(FormulaCell) sheet.getCell(3,5);
        System.out.println("Formula:"+formula5.getFormula());
        System.out.println("D6:"+formula5.getContents()); 

        NumberFormulaCell nfc=(NumberFormulaCell)sheet.getCell(3, 5); 
        System.out.println(nfc.getValue());  
        workbook2.close();

output:

D5:3
Formula:D5*2.0
D6:2
2.0
Kuitsi
  • 1,675
  • 2
  • 28
  • 48
user1433764
  • 31
  • 2

2 Answers2

2

JExcel only allows to read or write Excel files, and give a Java representation of the content of an Excel file. It will not substitute for Excel.

When you add a formula, this formula will be computed, but only in Excel, when you open the generated file in Excel. The cell is not computed by JExcel.

tofcoder
  • 2,352
  • 1
  • 20
  • 28
  • Alright, thank you. Is there anyway to get the computed results? For the larger project I mentioned I have been trying to take sets of data, insert them into a sheet that does some calculations to them, then put the results elsewhere in the sheet. Is the above project even possible with Jexcel? I could try to use macros, but I would prefer to use java if possible. – user1433764 Jun 03 '12 at 17:52
  • I am afraid not. JExcel does not make any computation. It seems that macros is a better alternative. – tofcoder Jun 03 '12 at 18:15
  • @Teetoo I am modifying an excel but the cells which have formulaes are not getting updates as per the new modified data. What is the alternative? How to use Macros for this purpose? – Code Enthusiastic Mar 11 '13 at 08:18
0

If anyone else wants to know, POI does that using the FormulaEvaluator class.

Att

user2390097
  • 1