Search code examples
coldfusionspreadsheetcoldfusion-10cfspreadsheet

SpreadsheetFormatRow abruptly stops working

I've seen this post, but there does look to be a resolution. Anyway, I'm using ColdFusion 10 to generate an Excel spreadsheet. However, when I use SpreadsheetFormatRow() and pass in the rows to be formatted, it only does about 3 and then abruptly stops. Here is an example...

ColdFusion Code

<cfscript>

    rowCount = 1;
    headingRows = 4;

    // Create instance of new Spreadsheet
    excelSheet = SpreadsheetNew("ReportName",false); 

    // HEADING (IMAGE) ROW FORMAT
    formatHeadingRow = StructNew();
    formatHeadingRow.fgcolor="blue";        

    // Add rows to fill the header area (must add as many as we are spanning with the above image)
    for (x=0;x<headingRows;x++) {
        SpreadsheetAddRow(excelSheet,"TEST,TEST,TEST,TEST,TEST,TEST,TEST,TEST,TEST,TEST,TEST,TEST");
        SpreadsheetFormatRow(excelSheet,formatHeadingRow,rowCount);
        rowCount++;
    }

</cfscript>

<!--- stream it to the browser --->
<cfheader name="Content-Disposition" value="inline; filename=reportName.xls">
<cfcontent type="application/vnd.ms-excel" variable="#SpreadSheetReadBinary(excelSheet)#">

and here is a screenshot of the resulting Excel sheet

enter image description here

Why is the formatting stopping after X number of rows and cells? If I switch to using XML format with 

excelSheet = SpreadsheetNew("ReportName",true);

it works properly. However I'm using a custom palette for my colors so I don't think switching to XLSX format is going to work for me. When I try and then call 

palette = excelSheet.getWorkbook().getCustomPalette();

I get an error stating that getCustomPalette() method is undefined.

coldfusion.runtime.java.MethodSelectionException: The getcustompalette method was not found

Can anyone help me figure this out? Thank you!!!

Or even better since it works with the XML format, can anyone show example of how to use a custom palette with the XLSX (xml format)

Solution

  • This is an issue I have seen often when dealing with xls files from CF; they seem to stop applying styles after a certain number of cells. I've been able to work around it by outputting to xlsx instead. (I was able to replicate and "fix" your issue by doing so.)

    excelSheet = SpreadsheetNew("ReportName",true);

    ...

    <cfheader name="Content-Disposition" value="inline; filename=reportName.xlsx">
<cfcontent type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
        variable="#SpreadSheetReadBinary(excelSheet)#">