在Apache POI公式中引用工作表

Question

我使用Apache POI 3.6来生成Excel（2003）工作表。我想将一个公式插入到计算几张纸上特定单元格的总和的单元格中。

我有命名为A，B和C表和要计算的总和的单元格A1

我尝试：

cell.setCellFormula("a!A1+b!A1+c!A1"); 

POI不产生任何错误，但当我打开片我在OpenOffice中出现错误：

Err: 522 - =$#REF!.A1+$#REF!.A1+$#REF!.A1 

我做了一些研究，显然在引用多个工作表时存在错误。 （例如https://issues.apache.org/bugzilla/show_bug.cgi?id=46670）有没有人有一个想法如何使用POI中的多个工作表使用公式？

--------------源代码-------------------

public static void main(String args[]){ 
    Workbook wb = new HSSFWorkbook(); 
    CreationHelper createHelper = wb.getCreationHelper(); 
    Sheet sheet = wb.createSheet("Total"); 

    Row row = sheet.createRow((short)0); 
    Cell cell = row.createCell(0); 

    cell.setCellFormula("a!A1+b!A1+c!A1"); 

    Sheet sheet1 = wb.createSheet("a"); 
    Sheet sheet2 = wb.createSheet("b"); 
    Sheet sheet3 = wb.createSheet("c"); 
    Sheet sheet4 = wb.createSheet("d"); 

    createVal(sheet1, createHelper, 5); 
    createVal(sheet2, createHelper, 10); 
    createVal(sheet3, createHelper, 15); 
    createVal(sheet4, createHelper, 20); 

    try { 
     FileOutputStream fileOut = new FileOutputStream("workbook.xls"); 
     wb.write(fileOut); 
     fileOut.close(); 
     System.out.println("done"); 
    } catch (IOException e) { 
     e.printStackTrace(); 
    } } 

public static void createVal(Sheet sheet, CreationHelper createHelper, int i){ 
    Row row = sheet.createRow((short)0); 
     // Create a cell and put a value in it. 
     Cell cell = row.createCell(0); 
     // Or do it on one line. 
     row.createCell(0).setCellValue(i); 

} 

Answer 1

你的代码将工作如果您将填入单张纸后，将“Total”工作表的创建者移动到，则可以。

象下面这样：

...  
    createVal(sheet1, createHelper, 5); 
    createVal(sheet2, createHelper, 10); 
    createVal(sheet3, createHelper, 15); 
    createVal(sheet4, createHelper, 20); 

    Sheet sheet = wb.createSheet("Total"); 
    Row row = sheet.createRow((short)0); 
    Cell cell = row.createCell(0); 

    cell.setCellFormula("a!A1+b!A1+c!A1"); 

细胞Total!A1显示30