poi-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From bugzi...@apache.org
Subject DO NOT REPLY [Bug 45060] New: Formula written incorrectly produces #VALUE error
Date Thu, 22 May 2008 11:17:03 GMT
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


Mime
View raw message