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: [email protected]
ReportedBy: [EMAIL PROTECTED]
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: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
