Skip to main content
deepthit36620990
deepthit36620990
Participating Frequently
September 22, 2016
Question

Export to Excel is generating a blank document

  • September 22, 2016
  • 2 replies
  • 1105 views

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>

    This topic has been closed for replies.

    2 replies

    P
    paule12345
    Inspiring
    September 24, 2016

    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:

    BKBK
    Community Expert
    BKBKCommunity Expert
    Community Expert
    September 22, 2016

    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
    deepthit36620990Author
    Participating Frequently
    September 23, 2016

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

    BKBK
    Community Expert
    BKBKCommunity Expert
    Community Expert
    September 24, 2016

    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)#"/>

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