Mailing-List: contact dev-help@poi.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "POI Developers List" <dev@poi.apache.org>
From: bugzilla@apache.org
To: dev@poi.apache.org
Subject: DO NOT REPLY [Bug 45060] New: Formula written incorrectly produces
 #VALUE error
Message-ID: <bug-45060-47293@https.issues.apache.org/bugzilla/>
Content-Type: text/plain; charset="UTF-8"
MIME-Version: 1.0
Date: Thu, 22 May 2008 04:17:03 -0700 (PDT)

https://issues.apache.org/bugzilla/show_bug.cgi?id=45060

           Summary: Formula written incorrectly produces #VALUE error
           Product: POI
           Version: 3.0-dev
          Platform: PC
        OS/Version: Windows XP
            Status: NEW
          Severity: normal
          Priority: P3
         Component: HSSF
        AssignedTo: dev@poi.apache.org
        ReportedBy: cpuidle@gmx.de
            Blocks: 45041


This is a follow-on issue to bug 45041. 

The following example creates a sheet with
two rows as input data for the final formula in row 3. The formula produces a
#VALUE error
in excel (tested on poi 3.1beta).
Visually the formula in Excel appears ok. Just pressing
"Enter" in the formula field again fixes the problem and field displays a
value. It seems
Excel is correcting the formula in the background- visually it does not change
by this
process.

import java.io.*;
import java.util.*;

import org.apache.poi.hssf.usermodel.*;

public
class Test
{
        static int row = 0;

        static HSSFSheet sheet;

        static HSSFCellStyle
xlsDateStyle, xlsNumericStyle, xlsPercentStyle;

        /**
         * @param args
         * @throws
Exception 
         */
        public static void main(String[] args) throws Exception
        {
                FileOutputStream
out = new FileOutputStream("d:\\test.xls");
                HSSFWorkbook wb = new HSSFWorkbook();

                sheet
= wb.createSheet("test");

                // Excel-formatted date object
                xlsDateStyle = wb.createCellStyle();
               
xlsDateStyle.setDataFormat(wb.createDataFormat().getFormat("dd.mm.yyyy"));

                //
Excel-formatted number
                xlsNumericStyle = wb.createCellStyle();
               
xlsNumericStyle.setDataFormat(wb.createDataFormat().getFormat("0.00"));

                //
Excel-formatted percent object
                xlsPercentStyle = wb.createCellStyle();
               
xlsPercentStyle.setDataFormat(wb.createDataFormat().getFormat("0.00%"));

                addRow(new
Date(0, 0, 1), 100.0);
                addRow(new Date(1, 0, 1), -110.0);

                HSSFRow r = sheet.createRow(row++);

                //
create the IRR formula
                short col = 2;
                HSSFCell c = r.createCell(col++);
                c.setCellStyle(xlsPercentStyle);
                c.setCellType(HSSFCell.CELL_TYPE_FORMULA);
               
c.setCellFormula("(1+IRR(SUMIF(A:A,ROW(INDIRECT(MIN(A:A)&\":\"&MAX(A:A))),B:B),0))^365-1");

                wb.write(out);
                out.close();
        }

        private
static void addRow(Date date, double d)
        {
                HSSFRow r = sheet.createRow(row++);

                short
col = 0;
                HSSFCell c = r.createCell(col++);
                c.setCellValue(date);
                c.setCellStyle(xlsDateStyle);

                c
= r.createCell(col++);
                c.setCellValue(d);
                c.setCellStyle(xlsNumericStyle);
        }
}


-- 
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org