更新的Excel文件仍然返回旧值 [英] Updated excel file still returns old values

查看:81
本文介绍了更新的Excel文件仍然返回旧值的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我的Java代码正在使用jxl库读取Excel文件.它首先复制原始的excel文件以创建temp.xls.创建该文件时,它会更改一些值,并成功使用公式创建temp.xls.

My java code is reading an excel file by using jxl library. It first copies the original excel file to create temp.xls. While creating that file it changes some values, and it successfully creates the temp.xls with the formula.

原始文件:

A   1       Y   3 (has formula: A+B)
B   2       

复制的文件:

A   5       Y   13 (has formula: A+B)
B   8       

但是,即使复制的文件发生了更改,该代码仍会给出"3"作为输出,而不是"13".如何使它给出正确的输出?

However, even the copied file changes, the code still gives "3" as an output instead of "13". How can I make it give the correct output?

import java.io.File;
import java.io.IOException;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import jxl.write.*;
import jxl.write.Number;
import jxl.write.biff.RowsExceededException;

public class Reader {

    public static void excelWriting() {
         try {
         Workbook workbook = Workbook.getWorkbook(new File(
         "D:\\parser\\calc.xls"));
         WritableWorkbook copy = Workbook.createWorkbook(new File(
         "D:\\parser\\temp.xls"), workbook);

         WritableSheet tempSheet = copy.getSheet(0);

         Number num1 = new Number(1, 2, 5);
         Number num2 = new Number(1, 3, 8);

         tempSheet.addCell(num1);
         tempSheet.addCell(num2);

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

         } catch (BiffException e) {
         // TODO Auto-generated catch block
         e.printStackTrace();
         } catch (IOException e) {
         // TODO Auto-generated catch block
         e.printStackTrace();
         } catch (RowsExceededException e) {
         // TODO Auto-generated catch block
         e.printStackTrace();
         } catch (WriteException e) {
         // TODO Auto-generated catch block
         e.printStackTrace();
         }

    }

    public static void main(String[] args) {

        excelWriting();
        int value = 0;

        try {   
                Workbook wrk1 = Workbook.getWorkbook(new File(
                    "D:\\parser\\temp.xls"));

            // Obtain the reference to the first sheet in the workbook
            Sheet sheet1 = wrk1.getSheet(0);
            Cell cell = sheet1.getCell(4, 2);
            value = Integer.parseInt(cell.getContents());

        } catch (BiffException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        System.out.println(value);

    }
}

  • 在excelWriting()方法中,num1表示"A"等于1
  • 在excelWriting()方法中,num2表示"B"等于2
  • main()方法中的sheet1.getCell(4,2)是"Y",等于3
  • 推荐答案

    我不太了解jxl库,而且我也无法运行您的示例( Number应该使用什么导入?),但:

    I don't know the jxl library very well, and I couldn't get your example running (what import should I use for Number?), but:

    Excel文件格式不仅存储公式,而且还存储文件中的计算结果.因此,当您更改值A和B时,您需要以某种方式重新计算公式,以便存储新结果.很有可能该公式没有重新求值,但仍返回旧值.

    The Excel file formats not only stores formulas, but also the calculated results in the file. So when you change the values A and B, you somehow need to re-evaluate the formulas in order for the new result to be stored. Most likely, the formula is not re-evaluated and still returns the old value.

    看看jxl API,有一个命令 setRecalculateFormulasBeforeSave()在正确的位置使用(可能在 copy.write()之前)可能会有所帮助:

    Looking at the jxl API, there is a command setRecalculateFormulasBeforeSave() that might be of help when used at the right place (likely before your copy.write():

    tempSheet.getSettings().setRecalculateFormulasBeforeSave(true);
    

    可悲的是,由于无法运行您的示例,我无法亲自尝试,但是值得一试.

    Sadly, I couldn't try it out myself as I wasn't able to get your example running, but it might be worth a try.

    更新:好的,终于使您的示例成功了.我的上述方法无济于事.我的解释:如上所述,Excel文件格式确实将公式结果存储在文件中.更改单元格值需要重新评估公式.这种重新评估/重新计算只能由Excel本身完成,而不能由JXL进行.对此进行更多思考,这是有道理的:如果JXL需要重新计算值,则它必须支持Excel本身支持的所有公式,包括所有复杂的统计和财务公式,这可能太多了.因此,JXL所能做的就是更改单元格的值,但实际的计算却无法完成.因此,除了在Excel中打开创建的文件并重新保存外,似乎没有其他方法可以更新公式结果.

    UPDATE: okay, finally got your example working. My approach above does not help. My interpretation: As mentioned, the Excel file format does store formula results in the file. Changing cell values need re-evaluation of formulas. This re-evaluation/recalculation is only done by Excel itself, but not by JXL. Thinking a bit more about this, this makes sense: If JXL would need to recalculate the values, it would have to support all the formulas supported by Excel itself, including all the complex statistical and financial formulas, which is likely just too much. So all JXL can do is change the values of cells, but the actual calculation it cannot do. So, except opening the created file in Excel and re-saving it, there seems to be no other way to update the formula result.

    这篇关于更新的Excel文件仍然返回旧值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆