Skip to main content
mattt16931977
mattt16931977
Participant
July 12, 2022
Question

Generating and downloading an .xlsx file and opening it successfully

  • July 12, 2022
  • 2 replies
  • 4802 views

Hello,
I am new to CF and I am struggling to convert an existing form that creates and downloads an .xls file
into a form that creates and downloads an .xlsx file.

We are currently running ColdFusion Server Enterprise 2021,0,03,329779.

The current main .cfm page queries the data, formats the output,

and calls another .cfm to generate the .xls file.

The struggle is after code changes are made and after the .xlsx file is created and downloaded,
I receive the error message:
"Excel cannot be open the file 'nnnnn.xlsx' because the file format or file extension is not valid"

 

The main.cfm page uses the following file name, header data, and formatting masks structure:

<cfsilent>
<cfscript>	
	variables.fileName = "#replace(reportData.headerData.reportTitle, ' ', '', 'all')#_#dateFormat(reportData.headerData.reportUpdatedDate, 'yyyymmdd')#.xls";
	variables.headerContent = "#reportData.headerData.reportTitle#";
	// styles
	styles = StructNew();
	styles.th      = "border: 1px solid ##FFFFFF; background-color:##15317E; color:##FFFFFF; text-align:center; font-weight:bold;";
	styles.td      = "border: 1px solid black; background-color:##FFFFFF; color:##000000;";
	styles.note    = "background-color:##FFFFFF; color:##000000;";
	styles.salesPA = "mso-number-format:'00000';";
	styles.starsID = "mso-number-format:'000000000';";
	styles.pct	   = "mso-number-format:'0\.0000%';";
	styles.date    = "mso-number-format:'mmmm yyyy';";
	styles.head1   = "font-size:18px; font-weight:bold;";
	styles.head2   = "font-size:16px; font-weight:bold;";
	styles.head3   = "font-size:16px; font-weight:normal; color:red;";
	styles.hr      = "border-bottom: 1px solid black; background-color:##FFFFFF; color:##000000;";
	variables.colSpan = reportData.headerData.numColumns;
</cfscript>
</cfsilent>

The remaining code formats the output from the database query. ( Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production)

<cfsavecontent variable="variables.outputContent">
<table border="0">
	<cfoutput>
	<!--- report header --->
	<tr>
		<td colspan="#variables.colSpan#" align="center" style="#styles.head1#">
			#UCase(reportData.headerData.reportTitle)#
		</td>
	</tr>
	<!--- Overall info --->
	<tr>
		<td colspan="#variables.colSpan#">&nbsp;</td>
	</tr>
	<cfif reportData.headerData.reportType eq "NATHIST">
	<tr>
		<td colspan="#variables.colSpan#" align="left" style="#styles.head2#">
			National Report
		</td>
	</tr>
	<cfelse>
	<tr>
		<td colspan="#variables.colSpan#" align="left" style="#styles.head2#">
			Market Area: #reportData.headerData.hubName#
		</td>
	</tr>
	<tr>
		<td colspan="#variables.colSpan#" align="left" style="#styles.head2#">
			Region: #reportData.headerData.regionName#
		</td>
	</tr>
	</cfif>
	<tr>
		<td colspan="#variables.colSpan#">&nbsp;</td>
	</tr>
	<tr>
		<td colspan="#variables.colSpan#" align="left" style="#styles.head3#">
			<strong>Note:</strong> In order to display on this report, dealerships must achieve #application.program.getDisplayName()# at least once.
		</td>
	</tr>
	<tr>
		<td colspan="#variables.colSpan#">&nbsp;</td>
	</tr>
	</cfoutput>

	<cfif reportData.qBodyData.recordCount eq 0>
	<tr>
		<cfoutput>
		<td colspan="#variables.colSpan#">No records were found matching your search criteria.<br>Please try another search.</td>
		</cfoutput>
	</tr>
	<cfelseif reportData.qBodyData.recordcount gt 65500>
	<tr>
		<cfoutput>
		<td colspan="#variables.colSpan#">
			The report has exceeded the Microsoft Excel record limitation of 65,500 records.
			<br/>Please revise your reporting criteria and regenerate your report.
		</td>
		</cfoutput>
	</tr>
	<cfelse>
		<cfoutput>
		<!--- header row --->
		<tr valign="bottom">
			<cfif reportData.headerData.hubName eq "All">
			<td style="#styles.th#" width="80">MARKET<br/>AREA</td>
			</cfif>
			<cfif reportData.headerData.regionName eq "All">
			<td style="#styles.th#" width="95">#UCASE(form.rptMode)#<br/>REGION</td>
			</cfif>
			<td style="#styles.th#" width="55">SALES<br/>CODE</td>
			<td style="#styles.th#" width="55">PARTS<br/>CODE</td>
			<td style="#styles.th#" width="300">DEALER NAME</td>
			<td style="#styles.th#" width="100">DEALER STATUS</td>
			<cfloop query="reportData.headerData.qProgramYears">
			<td style="#styles.th#" width="35">#reportData.headerData.qProgramYears.program_year#</td>
			</cfloop>
			<td style="#styles.th#" width="50">TOTAL<br/>COUNT</td>
		</tr>
		</cfoutput>
		<cfoutput query="reportData.qBodyData" group="hub_name">
			<cfoutput group="region_name">
				<cfoutput group="sales_code">
					<cfoutput group="parts_code">
						<tr>
							<cfif reportData.headerData.hubName eq "All">
							<td style="#styles.td#">#reportData.qBodyData.hub_name#</td>
							</cfif>
							<cfif reportData.headerData.regionName eq "All">
							<td style="#styles.td#" align="center">#reportData.qBodyData.region_name#</td>
							</cfif>
							<td style="#styles.td##styles.salesPA#">#reportData.qBodyData.sales_code#</td>
							<td style="#styles.td##styles.salesPA#">#reportData.qBodyData.parts_code#</td>
							<td style="#styles.td#">#reportData.qBodyData.dealer_name#</td>
							<td style="#styles.td#">#reportData.qBodyData.dealer_status#</td>
							<cfoutput>
							<td style="#styles.td#" align="center">#reportData.qBodyData.winner_flag#</td>
							</cfoutput>
							<td style="#styles.td#" align="right">#reportData.qBodyData.total_count#</td>
						</tr>
					</cfoutput>
				</cfoutput>
			</cfoutput>
		</cfoutput>
	</cfif>
</table>
</cfsavecontent>

The cfsavecontent is then passed to the following to generate and download the .xls file.

<common:generateExcelFile
	FileName="#variables.fileName#"
	FileContent="#variables.outputContent#"
	PrintScale="#variables.printScale#"
	HeaderContent="#variables.headerContent#"

 The following generates and downloads the .xls file.

<cfif thisTag.executionMode eq "start">
	<cfheader name="Content-Type" value="unknown">
	<cfheader name="Content-Disposition" value="attachment;filename=#attributes.FileName#">	
	<cfcontent type="application/msexcel">
	<html xmlns:o="urn:schemas-microsoft-com:office:office"
	  xmlns:x="urn:schemas-microsoft-com:office:excel"
	  xmlns="https://www.w3.org/TR/html40/">
	<body>
	<cfoutput>#attributes.FileContent#</cfoutput>
	</body>
	</html>
</cfif>

I have changed the file name to have an extension of ".xlsx" in the main.cfm and in the .cfm that generates the excel file, I modified <cfcontent type="application/msexcel"> to <cfcontent type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet">

The goal, if at all possible, is to keep the same formatting that is used now. I am aware of the various "Spreadsheet" functions but I am trying to avoid rewriting the code and having to reformat the excel file. 

I have also attached a small test page to try and download an .xlsx file and it also gives the same error:

"Excel cannot be open the file 'nnnnn.xlsx' because the file format or file extension is not valid"

Please let me know what I may be missing for the xls to xlsx conversion.

    This topic has been closed for replies.

    2 replies

    James Moberg
    James Moberg
    Inspiring
    July 25, 2022

    Your mileage may vary using the built-in Adobe spreadsheet functions.  We kept encountering unavoidable Java heap errors when our users attempted to export large datasets.  We stopped using the built-in  ACF CFSpreadsheet in favor of the spreadsheet-cfml library.  It uses newer Java POI libraries and has more features, allows for chainable commands and has unique helper functions. It also has the ability to generate a "streaming" XLSX file. (This is critical as Adobe attempts to generate the Exce file in-memory which can result in throwing a Java HEAP error if there's too much data.)

    downloadFileFromQuery is a convenient helper functions.  Here's what that full syntax would look like to download an XLSX file from a query... only 3 lines:

    // to download XLSX file from a query object
spreadsheet = New spreadsheet(dateFormats={DATE: "mm/dd/yyyy"});
myFilename = "myReportFileName";
spreadsheet.downloadFileFromQuery(data=myQuery, filename=myFilename, addHeaderRow=true, boldHeaderRow=true, xmlFormat=true, streamingXml=true);

// To generate CSV from a query (the queryToCsv method)
// myCsvFileContent = spreadsheet.queryToCsv(query=myQuery, includeHeaderRow=true, delimiter=chr(9));


    This free third-party ColdFusion/java project is cross-compatible with CF2016-CF2021 and Lucee.  (I previously encountered some minor issues on Adobe ColdFusion 2016, but the author has been very responsive and quickly issued updates to address every issue.)


     

      BKBK
      Community Expert
      BKBKCommunity Expert
      Community Expert
      July 13, 2022

      That is a lot of complex lines of code to look at. So I shall simplify matters in my feedback.

       

      I think the error message contains the biggest clue: "Excel cannot open the file 'nnnnn.xlsx' because the file format or file extension is not valid". That is perhaps because the conversion from HTML-table to Excel is an old trick. As such, it might fail for conversion to XLSX, even though it works for XLS.

       

      So I would suggest:

      1. First convert from HTML-table to XLS. See, for example, the sample code below. It is based on the built-in cfdocexamples database. You can therefore run it directly, as-is. The result is an XLS file.

      2. Open the XLS file in Excel, then save it as an XLSX file. You may also find a library to help you automate this. Search the web.

       

      <cfquery name="getEmployees" datasource="cfdocexamples"> 
SELECT * FROM Employee 
</cfquery> 

<cfheader name="content-disposition" value="inline;filename=testFile.xls" />
<cfcontent type="application/vnd.ms-excel">

<HTML>
<HEAD>
    <TITLE>Outputting Query Results</TITLE>
</HEAD>

<BODY>

<H2>Employee Records</H2>
<!--- Create an HTML table for outputting the query results.  This section 
      creates the first row of the table - used to hold the column 
      headers --->
<TABLE>
<TR >
    <TH>ID</TH>
    <TH>Firstname</TH>
    <TH>lastname</TH>
    <TH>Dept</TH>
    <TH>Project_docs</TH>
    <TH>Contract_file</TH>
    <TH>Salary</TH>
    <TH>Startdate</TH>
</TR>

<!--- the CFOUTPUT tag is used in conjunction with the QUERY attribute to loop 
      over each row of data in the result set.  During each iteration of the 
      loop, a table row is dynamically created and populated with the query
      data from the current row. --->     
<CFOUTPUT QUERY="getEmployees">
<TR>
<TD>#emp_id#</TD>
    <TD>#firstname#</TD>
    <TD>#lastname#</TD>
    <TD>#dept_id#</TD>
    <TD>#project_docs#</TD>
    <TD>#contract_file#</TD>
    <TD>#salary#</TD>
    <TD>#startdate#</TD>
</TR>    
</CFOUTPUT>
</TABLE>

</BODY>
</HTML>

       

      mattt16931977
      mattt16931977Author
      Participant
      July 13, 2022

      Hi BKBK,

      Thank you for looking at my questions. I wanted to provide as much detail as possible. Sorry, I know it was alot to go through. But the complex code I posted at the beginning, isn't that essentially formatting for an .xls download? It is storing the data and formatting it in cfsavecontent and passing it to the generateExcelFile code. There it is using the following:

      <cfif thisTag.executionMode eq "start">
	<cfheader name="Content-Type" value="unknown">
	<cfheader name="Content-Disposition" value="attachment;filename=#attributes.FileName#">	
	<cfcontent type="application/msexcel">
	<html xmlns:o="urn:schemas-microsoft-com:office:office"
	  xmlns:x="urn:schemas-microsoft-com:office:excel"
	  xmlns="https://www.w3.org/TR/html40/">
	<body>
	<cfoutput>#attributes.FileContent#</cfoutput>
	</body>
	</html>
</cfif>

       Can my existing code be altered at all to use

      <cfcontent type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet">      (.xlsx)

      in place of

      <cfcontent type="application/msexcel"> (xls) ?

      We need the user to be able to download a readable .xlsx file from the application.

      Are xls to xlsx conversion libraries and rewriting the code using "spreadsheet" functions the only options?

      Being a newbie, I just need to be able to understand what options are possible.

      Thank you.

      mattt16931977
      mattt16931977Author
      Participant
      July 22, 2022

      Hello BKBK, Adobe Support Community,

      If possible, Could someone please review my follow-up question to BKBK's recommendation that I sent back on 7/13/22. 

      I will post it here again.

       

      Hi BKBK,

      Thank you for looking at my questions. I wanted to provide as much detail as possible. Sorry, I know it was alot to go through. But the complex code I posted at the beginning, isn't that essentially formatting for an .xls download? It is storing the data and formatting it in cfsavecontent and passing it to the generateExcelFile code. There it is using the following:

      <cfif thisTag.executionMode eq "start">
	<cfheader name="Content-Type" value="unknown">
	<cfheader name="Content-Disposition" value="attachment;filename=#attributes.FileName#">	
	<cfcontent type="application/msexcel">
	<html xmlns:o="urn:schemas-microsoft-com:office:office"
	  xmlns:x="urn:schemas-microsoft-com:office:excel"
	  xmlns="https://www.w3.org/TR/html40/">
	<body>
	<cfoutput>#attributes.FileContent#</cfoutput>
	</body>
	</html>
</cfif>

       Can my existing code be altered at all to use

      <cfcontent type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet">      (.xlsx)

      in place of

      <cfcontent type="application/msexcel"> (xls) ?

      We need the user to be able to download a readable .xlsx file from the application.

      Are xls to xlsx conversion libraries and rewriting the code using "spreadsheet" functions the only options?

      Being a newbie, I just need to be able to understand what options are possible.

      Thank you.

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