Creating an Excel spreadsheet from FDMEE using Jython

A client recently asked me to write a report in FDMEE to show the HFM Data Load error file. They didn’t want to use the Intersection Validation Report because it stopped the process. They wanted to get into HFM whatever data they could and then review the error file to identify problems. I wrote the script and the report but, while it worked perfectly on my environment, it didn’t work on the client’s. My heart sank because trying to work out why a report is not displaying the data can be very time-consuming so I thought I would investigate a different way of doing it.

The most direct way of allowing the user to review the load errors is to show the load error file to the user. The actual load error file format is not great so I thought the best way was to read the HFM data load error file into an Excel spreadsheet and then use the FDMEE function showCustomFile. I wrote a test script for the AftLoad event to show the actual error file using the fdmAPI function showCustomFile. That worked so the only problem now was how to write the contents of the error file into Excel.

There was nothing in the documentation or API on how to write to Excel although FDMEE can clearly do it because of the export to Excel in maps. An internet search for “Jython create excel” pointed me to this page:

https://community.oracle.com/thread/3770307?start=0&tstart=0

And from that page I deduced that the Java module that FDMEE uses to create Excel files is called POI. A second internet search for “python excel poi” pointed me to this page:

https://wiki.python.org/jython/PoiExample

And this gave me the code for creating a spreadsheet using POI. I put the two pieces of code together and with a little bit of tinkering, it works. It creates an Excel spreadsheet which I can then show to the user using showCustomFile. It is then relatively simple to read the error file and put it into table format in Excel. It is a great alternative if the Intersection Validation Report is not appropriate.

I have attached my test script for creating an Excel spreadsheet here: ExportToExcel. You will need to review the import section to make sure that it is pointing to the correct folder in your environment (lines 2 and 3). And you will also need to check the folder for the output (line 12). The script is also below but if you copy it the indentation is lost. If you can’t download the script let me know and I will send it to you:


from sys import path
path.append("C:\\Oracle\\Middleware\\EPMSystem11R1\\products\\FinancialDataQuality\\lib\\poi-3.8-20120326.jar")
path.append("C:\\Oracle\\Middleware\\EPMSystem11R1\\products\\FinancialDataQuality\\lib\\poi-ooxml-3.8-20120326.jar")

from java.io import FileOutputStream
from java.util import Date
from java.lang import System, Math
from org.apache.poi.hssf.usermodel import *
from org.apache.poi.hssf.util import HSSFColor

wb = HSSFWorkbook()
fileOut = FileOutputStream(“C:\\TEMP\\POIOut2.xls”)

# Create 3 sheets
sheet1 = wb.createSheet(“Sheet1”)
sheet2 = wb.createSheet(“Sheet2”)
sheet3 = wb.createSheet(“Sheet3”)
sheet3 = wb.createSheet(“Sheet4”)

# Create a header style
styleHeader = wb.createCellStyle()
fontHeader = wb.createFont()
fontHeader.setBoldweight(2)
fontHeader.setFontHeightInPoints(14)
fontHeader.setFontName(“Arial”)
styleHeader.setFont(fontHeader)

# Create a style used for the first column
style0 = wb.createCellStyle()
font0 = wb.createFont()
font0.setColor(HSSFColor.RED.index)
style0.setFont(font0)

# Create the style used for dates.
styleDates = wb.createCellStyle()
styleDates.setDataFormat(HSSFDataFormat.getBuiltinFormat(“m/d/yy h:mm”))

# create the headers
rowHeader = sheet1.createRow(1)
# String value
cell0 = rowHeader.createCell(0)
cell0.setCellStyle(styleHeader)
cell0.setCellValue(“Name”)

# numbers
for i in range(0, 8, 1):
cell = rowHeader.createCell((i + 1))
cell.setCellStyle(styleHeader)
cell.setCellValue(“Data ” + str( (i + 1)) )

# Date
cell10 = rowHeader.createCell(9)
cell10.setCellValue(“Date”)
cell10.setCellStyle(styleHeader)

for i in range(0, 100, 1):
# create a new row
row = sheet1.createRow(i + 2)
for j in range(0, 10, 1):
# create each cell
cell = row.createCell(j)
# Fill the first column with strings
if j == 0:
cell.setCellValue(“Product ” + str(i))
cell.setCellStyle(style0)

# Fill the next 8 columns with numbers.
elif j < 9:
cell.setCellValue( (Math.random() * 100))

# Fill the last column with dates.
else:
cell.setCellValue(Date())
cell.setCellStyle(styleDates)

# Summary row
rowSummary = sheet1.createRow(102)
sumStyle = wb.createCellStyle()
sumFont = wb.createFont()
sumFont.setBoldweight( 5)
sumFont.setFontHeightInPoints(12)
sumStyle.setFont(sumFont)
sumStyle.setFillPattern(HSSFCellStyle.FINE_DOTS)
sumStyle.setFillForegroundColor(HSSFColor.GREEN.index)

cellSum0 = rowSummary.createCell( 0)
cellSum0.setCellValue(“TOTALS:”)
cellSum0.setCellStyle(sumStyle)

# numbers
# B
cellB = rowSummary.createCell( 1)
cellB.setCellStyle(sumStyle)
cellB.setCellFormula(“SUM(B3:B102)”)

wb.write(fileOut)
fileOut.close()