I am trying to move a site that was on CF11 to CF2021 and there is an issue with moving the POI portion which creates excel files to CF2021. The first thing I did look for is to find the version of POI on CF11 (which is 3.12) and the version of POI on CF2021 (Which is 3.17). The code that is giving me trouble revolves around coloring the foreground of cells. First the workbook is created:

<cfset wb = createObject("java","org.apache.poi.hssf.usermodel.HSSFWorkbook").init()/>

<cfset fontRedUnderlineBold = wb.createFont()>

<cfset fontRedUnderlineBold.setColor(createObject("java","org.apache.poi.hssf.util.HSSFColor$RED").getIndex())>

<cfset fontRedUnderlineBold.setUnderline(1)>

<cfset fontRedUnderlineBold.setBold(true)>

<cfset cellStyleCurrency = wb.createCellStyle()/>

<cfset cellStyleCurrency.setDataFormat(createObject("java","org.apache.poi.hssf.usermodel.HSSFDataFormat").getBuiltinFormat("($##,####0.00_);[Red]($##,####0.00)"))/>

<cfset cellStyleNumeric = wb.createCellStyle()/>

<cfset cellStyleNumeric.setDataFormat(createObject("java","org.apache.poi.hssf.usermodel.HSSFDataFormat").getBuiltinFormat("##,####0.00"))/>

<cfset cellStyleBGYellow = wb.createCellStyle()/>

<cfset cellStyleBGYellow.setFillForegroundColor(createObject("java","org.apache.poi.hssf.util.HSSFColor$LIGHT_YELLOW").getIndex())/>

<cfset cellStyleBGYellow.setFillPattern(cellStyleBGYellow.SOLID_FOREGROUND)/>

The code above works just fine until we get to the last line which used to set the foreground. When this is run this error appears: Element SOLID_FOREGROUND is undefined in CELLSTYLEBGYELLOW. So, this used to work in 3.12 but now doesn't in 3.17. I have been trying to search on what has changed but so far have come up empy. Has anyone else experienced this?

According to my CommandBox ACF 2021 instance it's using POI 4.1.2, but in any case in 3.17 they did remove some previously deprecated methods which used cell style field values. Try replacing your last line with:

<cfset cellStyleBGYellow.setFillPattern(cellStyleBGYellow.getFillPattern()[ "SOLID_FOREGROUND" ])/>

@malllory.woods @ian.hickey Threads, please 🙂