Generating and downloading an .xlsx file and opening it successfully

Report · Jul 12, 2022

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.

Report · Jul 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>

Report · Jul 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.

Report · Jul 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.

Report · Jul 23, 2022

Hi @mattt16931977 ,

I am sorry I can only respond now. I have been away from the forum for some days. It was a busy week, working on a hectic project and attending the Adobe ColdFusion Developer Week 2022.

On to your question. First of all, I would suggest the following:

(1)

Ensure that attributes.FileName has extension XLS, not XLSX.

(2)

Replace application/msexcel with application/vnd.ms-excel. (application/msexcel is incorrect).

(3)

Let the cfcontent tag determine the content-type and delete the HTML attributes. In other words, replace

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

with

<cfheader name="content-disposition" value="attachment;filename=#attributes.FileName#" />
<cfcontent type="application/vnd.ms-excel">
<html>

@mattt16931977 wrote:

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

As far as I know, yes. In any case, I think those are the only reliable options.

@mattt16931977 wrote:

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

Which is why I would suggest that you use a reliable method to generate the XLSX file. You can expect examples on how to achieve this in my next post.

Report · Jul 25, 2022

There are 2 simple, convenient and reliable ways to generate the XLSX file, namely:

Bundle the data into a query, according to the columns that have to appear in the sheet.
Use the spreadsheetNew() function, with second attribute set to true.

That's it.

Example (Try it):

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

<!--- The sheet will be saved within the current directory --->
<cfset filePath=GetDirectoryFromPath(GetCurrentTemplatePath()) & "Employees.xlsx">

<cfspreadsheet action="write" filename="#filePath#" query="getEmployees" sheetname="Employees" overwrite=true>
Done

Should you want to format a cell, row or column of the sheet, you could use any of the following functions:
SpreadsheetFormatCell
SpreadsheetFormatRow
SpreadsheetFormatRows
SpreadsheetFormatColumn
SpreadsheetFormatColumns

An example which formats the data in columns 2 to 3 in the XLSX sheet (Try it):

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

<cfscript>
//Absolute path: the current directory path.
filePath=GetDirectoryFromPath(GetCurrentTemplatePath()) & "Employees.xlsx";

//Create a new Excel spreadsheet object. True=XLSX.
theSheet = spreadsheetNew("EmployeeData", true);

// Add the query data as rows to the new sheet
spreadsheetAddRows(theSheet, getEmployees);

// Define a format.
format1=structNew();
format1.font="Courier";
format1.fontsize="10";
format1.color="dark_blue;";
format1.italic="true";
format1.bold="true";
format1.alignment="left";
format1.textwrap="true";
format1.fgcolor="pale_blue";
format1.bottomborder="dotted";
format1.bottombordercolor="blue_grey";
format1.leftborder="thick";
format1.leftbordercolor="blue_grey";
format1.rightborder="thick";
format1.rightbordercolor="blue_grey";

// Apply the format to columns 2 to 3
spreadsheetFormatColumns(theSheet,format1,"2-3");
</cfscript>

<!--- Write the spreadsheet to a file in the current directory. --->
<cfspreadsheet action="write" filename="#filePath#" name="theSheet" sheetname="Employees" overwrite=true>

Done

Report · Jul 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.)

Adobe Community

Generating and downloading an .xlsx file and opening it successfully