poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Marc Giombetti" <m.giombe...@osb-ag.de>
Subject Referencing multiple sheets in Apache POI Formulas
Date Mon, 21 Jun 2010 10:02:00 GMT
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);

     }
}


Mime
  • Unnamed multipart/alternative (inline, 7-Bit, 0 bytes)
View raw message