CODEWITHSUNDEEP
coldfusioncfspreadsheet
bman2013
bman2013

Reputation: 407

CF10 CFSPREADSHEET Column Names - escaping special characters

So apparently I can't use any special characters, not even abbreviate with a dot for setting column names in cfspreadsheet. Is there a way to get around this? I'm basically dumping my query results into an excel file using cfspreadsheet and need to retain certain characters such as the dot sign to abbreviate certain terms. It'd be nice to actually take it to the next level and use special characters in these column names too. Is there a work around to this?

Code Sample. 

<cfset qryColumnList = "CaseNo,Model,Name" />
<cfset qryResultSet = QueryNew(qryColumnList) />

<cfoutput query="getUsedCars">
                <cfset QueryAddRow(qryResultSet, 1) />
                <cfset querySetCell(qryResultSet, "CaseNo", caseno EQ "&nbsp;" ? "" : caseno) />
                <cfset querySetCell(qryResultSet, "Model", model EQ "&nbsp;" ? "" : model) />
                <cfset querySetCell(qryResultSet, "Name", name EQ "&nbsp;" ? "" : name) />                      
</cfoutput>

<cfscript>
                xlsxopencases = SpreadsheetNew("opencases",true);
                SpreadsheetAddRow(xlsxopencases,qryColumnList);
                SpreadsheetAddRows(xlsxopencases,qryResultSet);
                strFileName=GetDirectoryFromPath(GetCurrentTemplatePath()) & "OpenCasesReport.xlsx";
                SpreadsheetFormatRow(xlsxopencases, {bold="true"}, 1);
                SpreadsheetFormatRows(xlsxopencases, {dataformat="text"}, "1-#qryResultSet.recordcount + 1#");
                SpreadsheetFormatColumn(xlsxopencases, {dataformat="dd/MM/yy HH:mm:SS"},18);
</cfscript>

<cfspreadsheet action="write"
                                                   filename="#strFileName#"
                                                   name="xlsxopencases"
                                                   sheet="1"
                                                   sheetname="OpenCasesReport"
                                                   overwrite="true" />

<cfheader name="Content-Disposition" value="attachment; filename=OpenCasesReport.xlsx">
<cfcontent file="#strFileName#" type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" />

<cffile action="delete" file="#strFileName#" />
</cfif>

Upvotes: 1

Views: 1299

Answers (1)

John Whish
John Whish

Reputation: 3036

So I think what you are asking is how you can use a column name of something like Case No…. In which case you'll be getting an error like: The column name Case No… is invalid when you're passing that into QueryNew. I'm also assuming that the column names you are trying to use are dynamic.

What you can do is strip out any of the invalid characters when creating the query and then populate the query with data using the valid column names. Then when you create the spreadsheet just use the original column names.

I've simplified your example and changed it so that it will produce the result I think you want :)

<!--- the wanted spreadsheet column headers with special chars in --->
<cfset headersList = "Name,Case No…">
<!--- strip out invalid characters so can use as query column names --->
<cfset qryColumnList = reReplaceNoCase(headersList, "[^a-z0-9,]", "", "all")>
<cfset qryResultSet = QueryNew(qryColumnList)>

<!--- add some data to the query object... --->
<cfset queryAddRow(qryResultSet)>
<cfset querySetCell(qryResultSet, "Name", "Hellip")>
<!--- note that instead of referring to the field as 'Case No…' using 'CaseNo' --->
<cfset querySetCell(qryResultSet, "CaseNo", "this text is truncated…")>

<cfscript>
xlsxopencases = SpreadsheetNew("opencases",true);
// note: using the headersList variable which has special chars in it
SpreadsheetAddRow(xlsxopencases,headersList);
SpreadsheetAddRows(xlsxopencases,qryResultSet);
strFileName=GetDirectoryFromPath(GetCurrentTemplatePath()) & "OpenCasesReport.xlsx";
SpreadsheetFormatRow(xlsxopencases, {bold="true"}, 1);
SpreadsheetFormatRows(xlsxopencases, {dataformat="text"}, "1-#qryResultSet.recordcount + 1#");
</cfscript>

<cfspreadsheet action="write"
    filename="#strFileName#"
    name="xlsxopencases"
    sheet="1"
    sheetname="OpenCasesReport"
    overwrite="true">

<cfheader name="Content-Disposition" value="attachment; filename=OpenCasesReport.xlsx">
<cfcontent file="#strFileName#" type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet">

This will produce a spreadsheet that looks like:

------------------------------------
| Name   | Case No…                |
------------------------------------
| Hellip | this text is truncated… |
------------------------------------

I hope I've understood your question correctly!

Upvotes: 3

Related Questions