Export to Excel is generating a blank document

[deepthit36620990]

Hi,

We are using CF 2016. Whenever user tries to export data to excel (Sample code as below), document is appearing as blank. Blank meaning worksheets does not appear in the exported document. But if we try to save the file locally (Saved document is not loading as expected as well) and send the document over email, recipients are  able to open the document with proper data. So issue is happening only when user tries to open / save  the file locally.

I even tried to comment out cfcontent , cfheader and directly displayed WriteOutput(ResultTable.Trim() ). Data is appearing as expected. Problem with the exported document.

Can anyone please suggest the possible root cause for this issue?

Sample code for generating XLS:

<cfset Id = application.user.MVP.Id[session.Auth.GetUser_Record_ID()]>
<cfset Name = application.user.MVP.Name[session.Auth.GetUser_Record_ID()]>

<cfif isDefined("URL.format") and URL.format eq 'Excel'>

<cfscript>
  URL.record_id = Id; // needed to submit everything as a single structure below
  request = Application.MVP.data.getData(argumentCollection = URL);
  TotalsByPeriod =   'Period,Record_ID,RunID,Fade,ExitStrategyID';
  TotalsAcrossPeriods =  'Record_ID,RunID,Fade,ExitStrategyID, Investment Value,MI Proceeds';
  AllDataByPeriod =     'Period,Record_ID,RunID,Fade,ExitStrategyID,LoanID,LoanNumber';

... Additional information
  colList = request.ColumnList;
</cfscript>

<cfif ListFindNocase('TotalsByPeriod,AlDataByPeriod,OneDataByPeriod',Evaluate(URL.outputFormat))>
  <cfset request.sort(request.findColumn("Period"),TRUE)>
</cfif>

<cfloop index="colIx" list="#Evaluate(URL.outputFormat)#">
  <cfset colList = ListDeleteAt(colList,ListFindNoCase(colList,colIx))>
</cfloop>

<cfset colList = Evaluate(URL.outputFormat)&','&colList>

<cfsavecontent variable="ResultTable">
  <table cellpadding="1" cellspacing="0" border="1" class="display" style="border-collapse:collapse;">
            <thead>
             <tr>
                 <cfloop index="colIx" list="#colList#">
                     <cfoutput><th nowrap="nowrap" style="padding-left:2px;padding-right:2px;">#Application.CL.Utilities.cleanColumn(colIx)#</th></cfoutput>
                 </cfloop>
             </tr>
   </thead>
   <tbody>
             <cfoutput query="request"> 
     <tr>
                  <cfloop index="colIx" list="#colList#">
       <cfif colIx eq 'Fade'>
         <td style="padding-left:2px;padding-right:2px;">#LSNumberFormat(request[colIx][currentRow],'_.____')#</td>
        <cfelseif ListFindNoCase(Evaluate(URL.outputFormat),colIx)>
         <td style="padding-left:2px;padding-right:2px;">#request[colIx][currentRow]#</td>
        <cfelse>
         <td style="padding-left:2px;padding-right:2px;">#LSCurrencyFormat(request[colIx][currentRow],'local')#</td>
       </cfif>   
                  </cfloop>
                 </tr>       
             </cfoutput>
            </tbody>
        </table>
</cfsavecontent>

<!---Output excel --->
<cfheader name="Content-Disposition" value="attachment; filename=Data_#Id#_Run_#URL.runId#_Valuation_Report.xls"/>
     <cfcontent type="application/msexcel" reset="true"/>
  <cfset WriteOutput(ResultTable.Trim() )/>
<cfexit>
 
<cfelse>

<cfinclude template="./grids/dataGrid.cfm">

</cfif>

[BKBK]

deepthit36620990 wrote:

<!---Output excel --->
<cfheader name="Content-Disposition" value="attachment; filename=Data_#Id#_Run_#URL.runId#_Valuation_Report.xls"/>
<cfcontent type="application/msexcel" reset="true"/>
<cfset WriteOutput(ResultTable.Trim() )/>
<cfexit>

Try this:

<cfheader name="Content-Disposition" value="attachment; filename=Data_#Id#_Run_#URL.runId#_Valuation_Report.xls""/>

<cfcontent type="application/vnd.ms-excel" reset="true"/>

<cfoutput>#ResultTable.Trim()#</cfoutput>

<cfexit>

[deepthit36620990]

Thanks BKBK. Tried your suggestion. But it did not work. I replaced the code to use POIUtility.

[BKBK]

Using POI is a much better alternative. Recent security fixes in Microsoft Office make it difficult to translate an HTML table into an Excel sheet.

What POI utility did you use? Did it solve your problem?

In any case, using Coldfusion's Excel functions, your above code translates into something like

<!--- Create sheet named 'valuationData'. 'No' is for type XLS --->

<<cfset sheetObj = spreadsheetNew("valuationData","no")>

<cfset leadingRow = "">

<cfloop index="colIx" list="#colList#">

<cfset leadingRow = listAppend(leadingRow, application.CL.Utilities.cleanColumn(colIx))>

</cfloop>

<cfset spreadsheetAddRow(sheetObj,leadingRow,1,1)>

<cfset row = "">

<cfoutput query="request">

<cfloop index="colIx" list="#colList#">

    <cfif colIx eq 'Fade'>

        <cfset row = listAppend(row, LSNumberFormat(request[colIx][currentRow],'_ .____'))>

    <cfelseif ListFindNoCase(Evaluate(URL.outputFormat),colIx)>

        <cfset row = listAppend(row, request[colIx][currentRow])>

    <cfelse>

        <cfset row = listAppend(row, LSCurrencyFormat(request[colIx][currentRow], 'local'))>

    </cfif>

    <!--- Remember: data from the first query row goes to the second Excel row, and so on--->

    <cfset spreadsheetAddRow(sheetObj,row,currentRow+1,1)>

</cfloop>

</cfoutput>

<cfheader name="Content-Disposition" value="attachment; filename=Data_#Id#_Run_#URL.runId#_Valuation_Report.xls"/>

<cfcontent type="application/vnd.ms-excel" reset="true" variable="#spreadSheetReadBinary(sheetObj)#"/>

[paule12345]

deepthit,

If your users install the latest windows updates to the Office suite, it should resolve your issue.

This thread discusses the issue in depth:

KB3170008 for Office 2016 Breaks Functionality - MS16-088

    - Office 365 subscription (Click-to-Run)—install the latest updates

     - Windows Installer version (MSI)—you could wait about a week for the next security update for MSI, and it will update automatically. Or, to get the fix today, use the Download Center to get the KB for your version of Office:

• Office 2016: https://support.microsoft.com/kb/3115438
• Office 2013: https://support.microsoft.com/kb/3115455
• Office 2010: https://support.microsoft.com/kb/3115476