removed records merge cells from /xl/worksheets/sheet1.xml part coldfusion poi

Question

I am creating an excel file with java poi library, it gets created but when I am opening the file excel pops a message saying "We found some problem with the content of file.Do you want us to try to recover as much as we can.If you trust the source of workbook."

On clicking Yes, it pops "Repaired Part: /xl/worksheets/sheet1.xml part with XML error. Load error. Line 121, column 0. Removed Records: Merge cells from /xl/worksheets/sheet1.xml part".

And there is no data loss in file, I am unable to get the reason behind excel's error.

I cannot write the whole code here, But here's the main content related to xlsx file :

<cfdirectory action="list" directory="#expandPath('/poi_39')#" name="poidir"/>
            <cfset paths = arrayNew(1)>
            <cfloop query="poidir">
                <cfset arrayAppend(paths, directory & "\" & name) />
            </cfloop>
            <cfset server.loader = createObject("component", "javaloader.JavaLoader").init(loadPaths=paths, loadColdFusionClassPath=true) />
            <cfscript>
                _Thread = createObject("java", "java.lang.Thread");
                currentClassloader = _Thread.currentThread().getContextClassLoader();
                try {
                    // Set the current thread's context class loader as Javaloader's classloader, so dom4j doesn't die
                    _Thread.currentThread().setContextClassLoader(server.loader.getURLClassLoader());
                    //code found online
                    //inp = createObject("java", "java.io.FileOutputStream").init("#fullpath#");
                    //reseting the value on code
                    inp = 100;
                    //workBook = server.loader.create("org.apache.poi.xssf.usermodel.XSSFWorkbook").Init(inp);
                    workBook = server.loader.create("org.apache.poi.xssf.streaming.SXSSFWorkbook").Init(inp);
                }
                catch(Any exc) {
                    rethrow;
                }
                finally { // We have to reset the classloader, due to thread pooling.
                    _Thread.currentThread().setContextClassLoader(currentClassloader);
                }
            </cfscript>

            <!--- New code ends--->
            <cfset cellstyle = workbook.createCellStyle()>
            <cfset fontface = workbook.createFont()>
            <cfset fontface.setBoldweight(fontface.BOLDWEIGHT_BOLD)>
            <cfset cellstyle.setFont(fontface)>
            <cfset cellstyleData = workbook.createCellStyle()>
            <cfset format = workbook.createDataFormat() >
            <cfset cellintstyle = workbook.createCellStyle()>
            <cfset cellintstyle.setDataFormat(format.getFormat("0"))>
            <cfset cellfloatstyle = workbook.createCellStyle()>
            <cfset cellfloatstyle.setDataFormat(format.getFormat("0.0##"))>
            <cfset cellstyleData.setWrapText(true)/>

            <cfset cellstyleforallrows = workbook.createCellStyle()>
            <cfset fontfaceforallrows = workbook.createFont()>
            <cfset fontfaceforallrows.setColor(CreateObject( "java","org.apache.poi.hssf.util.HSSFColor$BLUE").getIndex())>
            <cfset cellstyleforallrows.setFont(fontfaceforallrows)>
            <cfset cellstyleforallrows.setWrapText(true)/>

                    <!--- creating new row object for the LEGEND title--->      
                    <cfset rowLegend = newSheetLegend.createRow(0)/>
                    <!--- Assigning the sheet name Legend--->
                    <cfset workBook.setSheetName(0, "LEGEND")/>
                    <!--- First Row First column text should be LEGEND. Giving that in bold --->
                    <cfset cellLegend = rowLegend.createCell(0)/> 
                    <cfset cellStyleStatic = createObject("java","org.apache.poi.xssf.usermodel.XSSFCellStyle")/>
                    <cfset cellLegend.setCellStyle(cellstyle)/>
                    <cfset cellLegend.setCellValue("LEGEND")/>
                    <cfset region = server.loader.create("org.apache.poi.ss.util.CellRangeAddress").init(0,0,0,48)/>
                    <cfset newSheetLegend.addMergedRegion(region)/>

Didn't get anything relevant on web also, Has anyone faced any such issue ? If yes help me resolving this. Thanks

Here's the xml content of sheet1's mergecell xml :

<x:mergeCells xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
  <x:mergeCell ref="A1:AW1" />
  <x:mergeCell ref="B2:AW1" />
  <x:mergeCell ref="C3:AW1" />
  <x:mergeCell ref="D4:AW1" />
  <x:mergeCell ref="E5:AW1" />
  <x:mergeCell ref="F6:AW4" />
  <x:mergeCell ref="G7:C1" />
  <x:mergeCell ref="G7:AW4" />
  <x:mergeCell ref="H8:AW4" />
  <x:mergeCell ref="I9:AW4" />
  <x:mergeCell ref="J10:AW4" />
  <x:mergeCell ref="K11:AW4" />
  <x:mergeCell ref="L12:AW4" />
  <x:mergeCell ref="M13:AW4" />
  <x:mergeCell ref="N14:AW4" />
  <x:mergeCell ref="O15:AW4" />
  <x:mergeCell ref="P16:AW4" />
  <x:mergeCell ref="Q17:AW4" />
  <x:mergeCell ref="R18:AW4" />
  <x:mergeCell ref="S19:AW4" />
  <x:mergeCell ref="T20:AW4" />
  <x:mergeCell ref="U21:AW4" />
  <x:mergeCell ref="V22:AW4" />
  <x:mergeCell ref="W23:AW4" />
  <x:mergeCell ref="X24:AW4" />
  <x:mergeCell ref="Y25:AW4" />
  <x:mergeCell ref="Z26:AW4" />
  <x:mergeCell ref="AA27:AW4" />
  <x:mergeCell ref="AB28:AW4" />
  <x:mergeCell ref="AC29:AW4" />
  <x:mergeCell ref="AD30:AW4" />
  <x:mergeCell ref="AE31:AW4" />
  <x:mergeCell ref="AF32:AW4" />
  <x:mergeCell ref="AG33:AW4" />
  <x:mergeCell ref="AH34:AW4" />
  <x:mergeCell ref="AI35:AW4" />
  <x:mergeCell ref="AJ36:AW4" />
  <x:mergeCell ref="AK37:AW4" />
  <x:mergeCell ref="AL38:AW4" />
  <x:mergeCell ref="AM39:AW4" />
  <x:mergeCell ref="AN40:AW4" />
  <x:mergeCell ref="AO41:AW4" />
  <x:mergeCell ref="AP42:AW4" />
  <x:mergeCell ref="AQ43:AW4" />
  <x:mergeCell ref="AR44:AW4" />
  <x:mergeCell ref="AS45:AW4" />
  <x:mergeCell ref="AT46:AW4" />
  <x:mergeCell ref="AU47:AW4" />
  <x:mergeCell ref="AV48:AW4" />
  <x:mergeCell ref="AW49:AW4" />
  <x:mergeCell ref="AX50:AW4" />
  <x:mergeCell ref="AY51:AW4" />
  <x:mergeCell ref="AZ52:AW4" />
</x:mergeCells>

Answer 1

This thread suggests the newer format may be less tolerant about overlaps or duplicates. Check your CF code to see if it inadvertently creates any duplicate or conflicting regions.

I was able to reproduce the error, under both POI 3.9 and 3.13, by adding the same region to a spreadsheet twice OR by updating an existing file and adding a region that already exists (or conflicts with) an existing region in the spreadsheet. Opening the file with a zip tool, and examining the /xl/worksheets/sheet1.xml file with notepad, showed the merged cell node was added twice:

<mergeCells>
   <mergeCell ref="A1:AW1"/>
   <mergeCell ref="A1:AW1"/>
</mergeCells>

After "repairing" the file, the duplicate node was removed (and of course the error went away):

<mergeCells>
   <mergeCell ref="A1:AW1"/>
</mergeCells>

So again, check any of the CF code that adds a region. If you are still not sure which part of the code is causing the problem, try reviewing /xl/worksheets/sheet1.xml and do a search for "mergeCells". That should point you in the right direction.

Update:

Looking at the mergedcell nodes from sheet1.xml, it looks like a number of the regions overlap, or are possibly invalid. For example, the first two regions both end at column AW, row 1. (Since the second region starts on row 2, I am not sure the end point is even valid?)

<x:mergeCell ref="A1:AW1" />
<x:mergeCell ref="B2:AW1" />

Anyway, adjusting the ranges so they do not overlap (for example, if the second range ended in column AW, row 2) should resolve the error.

<x:mergeCell ref="A1:AW1" />
<x:mergeCell ref="B2:AW2" />