Use excel formula result in java jxl api

Question

I am trying to use the result of a formula in excel to divide other cells. My code is as follows:

import java.io.File;

import jxl.*;
import jxl.write.*;

public class CreateExcel
{

public static void main(String[] args) throws Exception
{
  Workbook workbook = Workbook.getWorkbook(new File("USA-IO-2005.xls"));
  WritableWorkbook copy = Workbook.createWorkbook(new File("output.xls"),
        workbook);
  copy.removeSheet(0);
  copy.removeSheet(2);
  WritableSheet domesticSheet = copy.getSheet(1);
  WritableSheet ASheet = copy.getSheet(0);

  for (int i = 8; i < 68; i++)
  {
     Formula f = new Formula(59, i - 1, "SUM(AX" + i + ":BE" + i
           + ") - BF" + i);
     domesticSheet.addCell(f);
  }

  double x;
  for (int i = 8; i < 68; i++)
  {
     NumberFormulaCell newCell = (NumberFormulaCell) domesticSheet.getCell(59, i);
     x = newCell.getValue();
     ASheet.addCell(new Label(1, i, String.valueOf(Double
           .valueOf(domesticSheet.getCell(1, i).getContents()) / x)));
  }
  copy.write();
  copy.close();
}
}

Unfortunately this is giving me the following exception:

Exception in thread "main" java.lang.ClassCastException: jxl.write.Formula cannot be cast to jxl.NumberFormulaCell at CreateExcel.main(CreateExcel.java:31)

Does anyone know how this is supposed to be done??

EDIT:

This is my revised code which essentially does what I want it to do just in a longer way. I calculate what the output of what formula f will equal and keep that rather then accessing the formula's result directly.

import java.io.File;

import jxl.*;
import jxl.write.*;
import jxl.write.Number;
import jxl.SheetSettings;

public class CreateExcel
{

public static void main(String[] args) throws Exception
{
  Workbook workbook = Workbook.getWorkbook(new File("USA-IO-2005.xls"));
  WritableWorkbook copy = Workbook.createWorkbook(new File("output.xls"), workbook);
  copy.removeSheet(3);
  WritableSheet domesticSheet = copy.getSheet(2);
  WritableSheet ASheet = copy.getSheet(1);
  WritableSheet RDSheet = copy.getSheet(0);
  ASheet.setName("A Matrix");
  double x = 1;
  double[] xVals = new double[60];



  //WritableCellFormat cellFormat = new WritableCellFormat(new NumberFormat("#.########"));
  WritableCellFormat arial8format = new WritableCellFormat (new WritableFont(WritableFont.ARIAL, 8)); 
  ASheet.addCell(new Label(1, 3, "A Matrix", arial8format));

  for (int i = 8; i < 56; i++)
  {

     //Workaround: Gets the same result as formula f. Just wont update as numbers in excel are changed in the future 
     for (int j = 49; j < 57; j++)
        xVals[i - 8] += Double.valueOf(domesticSheet.getCell(j, i - 1).getContents());
     xVals[i - 8] -= Double.valueOf(domesticSheet.getCell(57, i - 1).getContents());

     Number num = new Number(60, i - 1, xVals[i - 8], arial8format);
     Formula f = new Formula(59, i - 1, "SUM(AX" + i + ":BE" + i + ") - BF" + i, arial8format);
     domesticSheet.addCell(f);
     domesticSheet.addCell(num);

     for (int j = 1; j < 49; j++)
     {
        ASheet.setColumnView(j, 10);
        if (xVals[i - 8] != 0)
           x = Double.valueOf(domesticSheet.getCell(j, i - 1).getContents()) / xVals[i - 8];
        else
           x = 0;
        ASheet.addCell(new Number(j, i - 1, x, arial8format));
     }

  }

  SheetSettings s = new SheetSettings(ASheet);
  s.setVerticalFreeze(1);
  s.setHorizontalFreeze(1);

  ASheet.removeRow(55);
  ASheet.removeRow(56);
  for(int i = 0; i < 6; i ++)
     ASheet.removeRow(0);

  domesticSheet.removeRow(55);
  domesticSheet.removeRow(56);
  for(int i = 0; i < 6; i ++)
     domesticSheet.removeRow(0);

  copy.write();
  copy.close();
}
}

Answer 1

Well, the reason why the if case for CellType.Number isn't entered is that the formula cell is not a number cell. It is a formula cell. It has no 'value' othe rthan the formula itself (parsed for function names to tokenize them, but not evaluated). And it returns CellType.ERROR (at least in jxl 2.6.9). The library creates excel files with cells, it does not evaluate any formula or even check it for valid syntax. It just parses them for function names (tokens), so I18N for function names works. When reading a formula cell from an excel file that has been opened, calculated and saved by excel, then the foumula cell has the CellType.NUMBER_FORMULA and implements interfaces to read both, the formula and the formula result (as evaluated by excel).

You can't create a formula cell, add it to the sheet and then instantly read the result.

Answer 2

The exception is clear, you cannot cast return of method getCell() to NumberFormulaCell, it return Cell interface. They are different interface. I have source code changes below, I do not have your excel source file and so cannot really test it but that class cast exception should not be an issue now. With these changes below without the original excel source file, that line with Sheet.addCell give exception on my workstation but that is entirely different issue from the one you reported. Hope it works on your end.

import java.io.File;

import jxl.*;
import jxl.write.*;

public class CreateExcel
{

    public static void main(String[] args) throws Exception
    {
        Workbook workbook = Workbook.getWorkbook(new File("USA-IO-2005.xls"));
        WritableWorkbook copy = Workbook.createWorkbook(new File("output.xls"), workbook);
        copy.removeSheet(0);
        copy.removeSheet(2);
        WritableSheet domesticSheet = copy.getSheet(1);
        WritableSheet ASheet = copy.getSheet(0);

        for (int i = 8; i < 68; i++)
        {
            Formula f = new Formula(59, i - 1, "SUM(AX" + i + ":BE" + i + ") - BF" + i);
            domesticSheet.addCell(f);
        }

        double x = 1;
        for (int i = 8; i < 68; i++)
        {
            Cell cell1 = domesticSheet.getCell(3, i);

            if (cell1.getType() == CellType.ERROR)
            {
                System.out.println("this cell is not exist!");
            }

            if (cell1.getType() == CellType.NUMBER)
            {
                NumberCell nc = (NumberCell)cell1;
                x = nc.getValue();
                System.out.println(x);
                ASheet.addCell(new Label(1, i, String.valueOf(Double.valueOf(domesticSheet.getCell(1, i).getContents()) / x)));
            }                           
        }
        copy.write();
        copy.close();
    }
}