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);
	}
}