java - 在java jxl api中使用excel公式结果

Question

我正在尝试使用 excel 中公式的结果来划分其他单元格。我的代码如下:

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();
}
}

不幸的是，这给了我以下异常:

线程“main”中的异常 java.lang.ClassCastException: jxl.write.Formula 无法转换为 jxl.NumberFormulaCell 在 CreateExcel.main(CreateExcel.java:31)

有谁知道应该怎么做？

编辑:

这是我修改后的代码，它基本上以更长的方式完成了我想要它做的事情。我计算什么公式 f 的输出将等于什么并保留它，而不是直接访问公式的结果。

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

异常很明显，您不能将方法 getCell() 的返回强制转换为 NumberFormulaCell，它返回 Cell 接口(interface)。它们是不同的界面。我在下面更改了源代码，我没有您的 excel 源文件，因此无法真正对其进行测试，但是该类转换异常现在应该不是问题。在没有原始 excel 源文件的情况下进行了以下这些更改，带有 Sheet.addCell 的那一行在我的工作站上给出了异常，但这与您报告的问题完全不同。希望它对您有用。

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();
    }
}