Hello Marc,
I have just had a look at the bug you linked to and wanted to say that is a
slightly different issue. There, the problem occurs trying to reference a
cell on a sheet within a different workbook, not on a sheet within the same
workbook.
Have you tried using either Excel of OpenOffice to create and test that
formula directly? What seems to be happening is that OpenOffice is having
problems working with single character sheet names. If it were me, I would
prove to myself that it is possible to create such a formula using either
Excel or OpenOffice directly, make a note of the formula's syntax and then
use POI to recreate it. Next, I would try giving the sheets longer names or
even accepting the defaults and trying the formula with those to see if that
resolves the problem.
Yours
Mark B
Marc Giombetti-2 wrote:
>
> 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);
>
> }
> }
>
>
>
--
View this message in context:
http://old.nabble.com/Referencing-multiple-sheets-in-Apache-POI-Formulas-tp28946849p28950702.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscr...@poi.apache.org
For additional commands, e-mail: user-h...@poi.apache.org
