Skip to main content
M
mwoods1971
Inspiring
June 13, 2023
Answered

POI errors when moving from 3.12 to 3.17

  • June 13, 2023
  • 2 replies
  • 1660 views

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?

 

    This topic has been closed for replies.
    Correct answer mwoods1971

    Ok sorry again for coming back so late (1 year, yeah I know) 

     

    To catch up: I was able to get the till of the cell done:

     

    <cfset cellStyleBGYellow = wb.createCellStyle()/>

    <cfset cellStyleBGYellow.setFillForegroundColor(createObject("java","org.apache.poi.hssf.util.HSSFColor$LIGHT_YELLOW").getIndex())/>
    <cfset FillPatternType = createObject("java","org.apache.poi.ss.usermodel.FillPatternType")/>
    <cfset cellStyleBGYellow.setFillPattern(FillPatternType.SOLID_FOREGROUND)/>
    <!--- <cfset cellStyleBGYellow.setFillPattern(cellStyleBGYellow.SOLID_FOREGROUND)/> ** --->

     

    The commented line is the way it was, and the lin above is the working change. This fills the cell with yellow. There are a lot of these in the output from yello to greeen etc. I have all of these working. 

     

    What is not working is appling an undeline to a cell. Here is the original line of code.

     

    <cfset cellStyleCurrencyBottomBorderDoubleGreen.setBorderBottom(cellStyleCurrencyBottomBorderDoubleGreen.BORDER_DOUBLE)/>

    This would apply a border at the bottom of the cell. There is an error when doing this as things have changed in this version of apache POI. I did raise a question on stack overflow on how to do this which was answered however I'm not sure how to implement it as what I tried didn't work.

     

    The answer: The border type is now in an own Enum instead of being a member of CellStyle. See https://poi.apache.org/apidocs/dev/org/apache/poi/ss/usermodel/CellStyle.html#setBorderBottom-org.apache.poi.ss.usermodel.BorderStyle-

    https://poi.apache.org/apidocs/dev/org/apache/poi/ss/usermodel/BorderStyle.html

    Here is what I tried: <cfset cellStyleCurrencyBottomBorder.setBorderBottom(BorderStyle.THIN) /> which didn't work

    This is the last step to "fix"this since moving to 2021 and I know I have to be close. If anyone has an suggestions, its def apprciated. 

     

    Thanks

     

     


    Update:

     

    I was able to figure it out and hopefully this will help someone.

    For Apache POI 4.01 ColdFusion 2021 this is how you set underlines for a cell.

     

    The commented and italicized code is the old way of doing this and the uncommented code above is the new way we need to call it with CF. 

     

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

    <cfset cellStyleCurrencyBottomTopBorderTan.setBorderBottom(createObject("java", "org.apache.poi.ss.usermodel.BorderStyle").THICK)>
    <!--- <cfset cellStyleCurrencyBottomTopBorderTan.setBorderBottom(cellStyleCurrencyBottomTopBorderTan.BORDER_DOUBLE)/> ** --->

    <cfset cellStyleCurrencyBottomTopBorderTan.setBorderTop(createObject("java", "org.apache.poi.ss.usermodel.BorderStyle").THIN)>
    <!--- <cfset cellStyleCurrencyBottomTopBorderTan.setBorderTop(cellStyleCurrencyBottomTopBorderTan.BORDER_THIN)/> ** --->

    2 replies

    James Moberg
    James Moberg
    Inspiring
    June 25, 2023

    I wrote a POI-based CFTag in 2015 for CF10 that we use internally.  I'm in the process of updating it so that I can use it with CF2016, CF2018, CF2021, CF2023 and Lucee. To achieve this, I'm updating my CFTag to leverage the spreadsheet-cfml CFClibrary (which uses POI) rather than read the POI documentation to update it myself. If you haven't used this library yet, I highly recommend it.  It's well documented (way more detailed than ACF documentation) and has a lot of additional features (like the memory-efficient SXSSF streaming format for large data sets.)

    M
    mwoods1971Author
    Inspiring
    August 21, 2023

    Thanks I am just checking this after a few weeks and I'll defiantly look at this. 

    D
    Community Expert
    Dave WattsCommunity Expert
    Community Expert
    June 13, 2023

    I have no idea about POI or the specific cause of your problem. But I'm curious, what's in cellStyleBGYellow or rather wb.createCellStyle()? Can you look at it with CFDUMP or writeDump?

     

    Dave Watts, Eidolon LLC

    Dave Watts, Eidolon LLC
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices