• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

Export to Excel is generating a blank document

New Here ,
Sep 21, 2016 Sep 21, 2016

Copy link to clipboard

Copied

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>

Views

901

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Sep 22, 2016 Sep 22, 2016

Copy link to clipboard

Copied

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>

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Sep 23, 2016 Sep 23, 2016

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Sep 24, 2016 Sep 24, 2016

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Sep 24, 2016 Sep 24, 2016

Copy link to clipboard

Copied

LATEST

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:

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation