Referencing multiple sheets in Apache POI Formulas

[Marc Giombetti]

Hello,

I am using apache POI 3.6 to generate excel (2003) sheets. I want to 
insert a formula to a cell which calculates a sum of a certain cells on 
several sheets.

I have sheets named a, b and c and want to calculate the sum the cells A1

I tried: cell.setCellFormula("a!A1+b!A1+c!A1"); POI does not produce any 
errors, but when I open the sheet i get an error in OpenOffice

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

I did a bit of research and appearingly there are bugs when referencing 
multiple sheets. 
(https://issues.apache.org/bugzilla/show_bug.cgi?id=46670) Does anyone 
have an idea how to use formulas using multiple sheets in POI.

Thanks a lot Marc



------------------------------------------------------------------------------------------------------------------------------------------

OSB AG

Vorstand: Denis Sisic (Vors.), Frank Oestmann, Michael Witte
Vors. des Aufsichtsrates: Robert Strassmeir

Sitz der Gesellschaft: München
Amtsgericht München HRB 147 160

Diese Mitteilung ist ausschließlich für den beabsichtigten Empfänger bestimmt. 
Sie kann Betriebs- oder Geschäftsgeheimnisse oder sonstige vertrauliche 
Informationen enthalten. Jede(r) unberechtigte Gebrauch, Kopie, Weitergabe oder 
Veröffentlichung ist untersagt. Sollten Sie diese E-Mail irrtümlich erhalten 
haben, benachrichtigen Sie uns bitte sofort durch Antwortmail und löschen Sie 
diese E-Mail nebst etwaigen Anlagen und einschließlich aller angefertigten 
Kopien von Ihrem System.

This message is for the sole use of the intended recipient(s) and may contain 
trade secrets or other confidential and privileged information. Any 
unauthorized review, use, copy, disclosure or distribution is prohibited. If 
you are not the intended recipient, please inform us immediately by reply 
e-mail and delete this message including any attachment or copies thereof from 
your system.-------------- source code -------------------

import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

public class ExcelTest {

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

    }
}