Creating excel with add-on Bloomberg function using POI

[Rurien Wintercraft]

Hi,
I'm currently developing a java code that creates an excel using Apache POI. I know this falls more on POI rather than java but I can't seem to access their forum and I was hoping someone here can help me out.

My problem is that when I'm creating a formula cell, as long as it's a built-in function from Excel it works. However, I need to place an add-on Bloomberg function to my cell and when I do that, with the original POI jar file (version poi-3.7.jar), it doesn't even accept the function, stating that the bloomberg function (=BDP...) does not exist. What I did was add the BDP function to the functionMetaData.txt of POI's source, built it, and used that as my new jar. With my edited POI jar, it returns "#VALUE!" using MS Excel below 2007 and "=#N/A" when using MS Excel 2007. Am I missing a step in my code? Please help.

Here's my code:

 

			    FileOutputStream out = new FileOutputStream(file);
				
			    HSSFWorkbook workbook = new HSSFWorkbook();
			    HSSFSheet sheet = workbook.createSheet(Constant.SHEET1);
			    HSSFCellStyle style = workbook.createCellStyle();
			    style.setDataFormat(HSSFDataFormat.getBuiltinFormat(Constant.DATE_TIMESTAMP));
			    Row headerRow = null;
			    Cell cell = null;

				ResultSetMetaData rsmd = rs.getMetaData();
				int nRow = 0;
				headerRow = sheet.createRow(nRow);
				for(int index = 1; index <= rsmd.getColumnCount(); index++) {
				    cell = headerRow.createCell(index-1, HSSFCell.CELL_TYPE_STRING);
					cell.setCellValue(rsmd.getColumnName(index));
				}				
				nRow++;
				while(rs.next()) {
					headerRow = sheet.createRow(nRow);
					String valueDate = VerifyNull.ifNull(rs.getString(1), Constant.BLANK);
					cell = headerRow.createCell(0, HSSFCell.CELL_TYPE_FORMULA);
					cell.setCellFormula(valueDate);
					String rateDate = VerifyNull.ifNull(rs.getString(2), Constant.BLANK);
					cell = headerRow.createCell(1, HSSFCell.CELL_TYPE_FORMULA);
					cell.setCellFormula(rateDate);
					String market = VerifyNull.ifNull(rs.getString(3), Constant.BLANK);
					cell = headerRow.createCell(2, HSSFCell.CELL_TYPE_STRING);
					cell.setCellValue(market);
					String baseCCY = VerifyNull.ifNull(rs.getString(4), Constant.BLANK);
					cell = headerRow.createCell(3, HSSFCell.CELL_TYPE_STRING);
					cell.setCellValue(baseCCY);
					String quoteCCY = VerifyNull.ifNull(rs.getString(5), Constant.BLANK);
					cell = headerRow.createCell(4, HSSFCell.CELL_TYPE_STRING);
					cell.setCellValue(quoteCCY);
					String label = VerifyNull.ifNull(rs.getString(6), Constant.BLANK);
					cell = headerRow.createCell(5, HSSFCell.CELL_TYPE_STRING);
					cell.setCellValue(label);
					String bid = VerifyNull.ifNull(rs.getString(7), Constant.BLANK);
					// bid is BDP("EURON Curncy","PX_BID","DATA")*1;
					cell = headerRow.createCell(6, HSSFCell.CELL_TYPE_FORMULA);
					cell.setCellFormula(bid);
					String offer = VerifyNull.ifNull(rs.getString(8), Constant.BLANK);
					// offer is BDP("EURON Curncy","PX_ASK","DATA")*1; 
					cell = headerRow.createCell(7, HSSFCell.CELL_TYPE_FORMULA);
					cell.setCellFormula(offer);
					String timeStamp = VerifyNull.ifNull(rs.getString(9), Constant.BLANK);
					cell = headerRow.createCell(8, HSSFCell.CELL_TYPE_FORMULA);
					cell.setCellFormula(timeStamp);
					cell.setCellStyle(style);
					nRow++;
				}
				workbook.write(out);
				workbook.close();