Excel公式未使用Apache POI从Java应用程序中删除行删除

I'm using Apache POI in my application to write data to an excel file. I've an excel file template and a few formulas are also there in it. In my application, i use the excel template, write into it ,then delete unused rows and calculate formulas in the end. I'm using SUM formula in the file. The problem is when rows are deleted, the SUM formula is not updating,due to which error values are coming up in excel.

我在我的应用程序中使用Apache POI将数据写入excel文件。我有一个excel文件模板,其中还有一些公式。在我的应用程序中,我使用excel模板,写入,然后删除未使用的行并最终计算公式。我在文件中使用SUM公式。问题是当删除行时,SUM公式不会更新,因为excel中会出现错误值。

Example : the formula being used is : for cell B215 : SUM(B15:B214). in the application,after writing to the file i delete unused rows. now I've data till 70th row in the file.All other rows have been deleted. So my formula should get updated to : SUM(B15:B69) for cell B70. But in the file it's still showing the formula as SUM(B15:B214). Hence the value of that cell is "VALUE#

示例:正在使用的公式为:对于单元格B215:SUM(B15:B214)。在应用程序中,写入文件后我删除了未使用的行。现在我的数据直到文件中的第70行。所有其他行都被删除了。所以我的公式应该更新为:单元格B70的SUM(B15:B69)。但是在文件中,它仍然将公式显示为SUM(B15:B214)。因此,该单元格的值为“VALUE#”

Code snippet :

代码段:

File file = new File(path)
InputStream is = new FileInputStream(file)
POIFSFileSystem fs = new POIFSFileSystem(is)
HSSFWorkbook wb = new HSSFWorkbook(fs)
HSSFSheet excelSheet
int[] indexArray = populateSheet(excelSheet)

//indexArray is array with 3 values as startrow, lastrow, and first empty row.

// indexArray是具有3个值的数组,如startrow,lastrow和第一个空行。

removeBlankRows(excelSheet,indexArray)



//evaluate formula

FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator()
for(HSSFRow r : excelSheet) {
 for(HSSFCell c : r) {
  if(c.getCellType() == Cell.CELL_TYPE_FORMULA) {
    String formula = c.getCellFormula();
    evaluator.evaluateFormulaCell(c)
    }
 }
}

private void removeBlankRows(HSSFSheet sheet, int[] shiftInfo){
        for(int i = shiftInfo[2]; i <= shiftInfo[1]; ++i) {
            sheet.removeRow(sheet.getRow(i))
        }
        //Shift up the rows
        def startRow = shiftInfo[1]+1
        def endRow = sheet.getLastRowNum()
        def rowCount = -1* (shiftInfo[1] - shiftInfo[2] + 1)
        sheet.shiftRows(startRow, endRow, rowCount)
    }

1 个解决方案

#1

This is an Excel bug. I've dealt with this in the past by doing the following:

这是一个Excel错误。我过去通过以下方式解决了这个问题:

Label the sum cell BSUM

标记和细胞BSUM

We need a stable range that won't be affected by inserts/deletes. Add a formula to a safe (one that won't get deleted) cell, for this example D15: ="B15:B"&ROW(BSUM)-1 This will produce a stable range.

我们需要一个不受插入/删除影响的稳定范围。将公式添加到安全(一个不会被删除的)单元格,对于此示例D15:=“B15:B”&ROW(BSUM)-1这将产生一个稳定的范围。

Use INDIRECT in the BSUM cell like so:
=SUM(INDIRECT(D15))

在BSUM单元格中使用INDIRECT,如下所示:= SUM(INDIRECT(D15))

#1