4 Replies Latest reply on Sep 24, 2016 7:50 AM by paule12345

    Export to Excel is generating a blank document

    deepthit36620990 Level 1

      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>

        • 1. Re: Export to Excel is generating a blank document
          BKBK Adobe Community Professional & MVP

          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>

          • 2. Re: Export to Excel is generating a blank document
            deepthit36620990 Level 1

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

            • 3. Re: Export to Excel is generating a blank document
              BKBK Adobe Community Professional & MVP

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

              • 4. Re: Export to Excel is generating a blank document
                paule12345 Level 1

                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: