Does anyone know how to get ColdFusion to render the symbols ≥ and ≤ in Excel exports? I am talking about generated output, not how to do it within the Excel app. I have searched online for how to do it and so far have come up with nothing, which surprises me. Maybe it's obvious and I'm overlooking it but I get junk output in spreadsheets. HTML renders fine using ≥ and ≤ but that's the browser being knowledgeable.

Since there are many ways to create Excel export how are you doing it, Are you creating a Zip File with the many XML files embedded? Are you creating a single XML file to represent the worksheet? Are you creating an HTML table and telling excel to pretend that it is an Excel File? Are you creating a CSV and having Excel open it? Are you using cfspreadsheet or some other POI tool to create an Excel File?

Yeah, short version... show us the code you have which ain't working. Pretty hard to guess what yer doing wrong when we have to... like... guess. As far as I can tell, it just works: https://trycf.com/gist/a826e54e3f80acd220899970c1af4c65/acf2018?theme=monokai

The Excel file is created using <cfset wsFile = fileOpen()> and specifying the file path and "write" params followed by the fileWrite() function that passes wsfile and fileContent params. The fileContent is created from a cfsaveconent var and wraps content that contains the following key data tags:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<worksheet xmlns="(path to xml schema)" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:html="<http://www.w3.org/TR/REC-html40>">
<cols><col min="1" max="1" width="24"/><col min="2" max="2" width="75"/></cols><sheetData>
<cfloop array="#summaryInfo.criteria#" index="curStruct">
<row><c s="2" t="inlineStr"><is><t>#xmlFormat(curStruct.name)#</t></is></c>
<c s="1" t="inlineStr"><is><t>#xmlFormat(replaceNoCase(curStruct.value, "&ndash;", "-","ALL"))#</t></is></c></row>
</sheetData></worksheet>

That is not all the content but the salient part that concerns this issue. I also removed <cfoutput> tag wrappers where needed. The closing tags then follows <cfset fileClose(wsFile)>

Lovely, but can you chuck something together in trycf.com that shows only code sufficient to demonstrate the problem. Similar to the one I posted, but using whatever strategy that is above. Also pls avoid saying "that didn't work". In what way did it not work?

Did you try the Unicode escapes... https://stackoverflow.com/questions/40125180/what-is-the-equivalent-of-greater-than-equal-to-or-less-than-equal-to-in-xml

By "not working" I mean I see garbled symbols. I just now tried the unicode escape approach and you will see both here:

By Unicode espcaes I was meaning these ones...

≤

and

≥

I get an error for the # until I escape it with another # (&##x2264) but then I see this:

Don't hand-crank it. Use

encodeForXml

. (eg: https://trycf.com/gist/4deafeea80b71b514c632031f6bfc53c/lucee5?theme=monokai)

I tried it without and with the hemlEditFormat

if all fails do it yourself in a worksheet save the XLSX, open in a zip viewer and view the xml file and see what microsoft does.

Here's my assembled code but it appears trycf.com doesn't like some of the functions

<cfscript>
    curStruct.value = "≥ 25";
</cfscript>

<cfsavecontent variable="fileContent">
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
	<worksheet xmlns="<http://schemas.openxmlformats.org/spreadsheetml/2006/main>" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:html="<http://www.w3.org/TR/REC-html40>">
		<cols>
	        <col min="1" max="1" width="24"/>
            <col min="2" max="2" width="75"/>
        </cols>
        <sheetData>
            <row>
				<c s="2" t="inlineStr"><is><t><cfoutput>#xmlFormat('Surface')#</cfoutput></t></is></c>
				<c s="1" t="inlineStr"><is><t><cfoutput>#xmlFormat(replaceNoCase(curStruct.value, "&ndash;", "-", "ALL"))#</cfoutput></t></is></c>
			</row>
	    </sheetData>
    </worksheet>
</cfsavecontent>

<cfscript>
    filePath = getTempDirectory() & "mine.xml";
    wsFile = fileOpen(filePath, "write");
    fileWrite(wsFile, fileContent);
	fileClose(wsFile);
</cfscript>

Cool, yeah it doesn't like the file functions. But this is OK... I can run this locally now and see what yer up to.

ACF2018

Right. That all made things a lot easier.

How did you get it to display correctly?

(I only have CF2021, but... shouldn't be an issue. Always tell ppl when you raise an issue) In the case of your example, you might need to have

<cfprocessingdirective pageencoding="UTF8">

at the top of your file as you have non-ascii stuff in it. I thought CF had fixed this, but I had to do it to even get the char into the XML. Second... you were making a rod for your own back even involving Excel in the mix here. That code you gave me doesn't even generate the XML properly, so Excel deg ain't gonna be able to fix that.

Not following you on point 2 about making a rod...

or you could

encodeForXml

non-ascii stuff when building the XML. If I do that, I don't need to tell the file-ops to use UTF-8, as it won't matter.

I may have mangled the xml code in trying to copy/paste from multiple parts of the code.

nono, the schema is fine. The values in it aren't.

There's only one variable value, the curstruct.value

but changing it to encoded ucode values didn't help

Make the changes I pointed out.

@Adam Cameron cfprocessingdirective was already in the file but did not have UTF-8 specified but when added there is a CF error stating the function containing that code is declared twice. I remove that attribute and error goes away. No idea what that is about. Making use of encodeForXml function to encode those two symbols and they do not render, they stay as values shown in the screenshots provided previously.

I cannot guess what you have done wrong with the code unless you show me

I've tried to simplify it. But to show all involved code comes from multiple files and my employer wouldn't allow it to be exposed. I did try it in trycf.com but it blacklists some of the functions, filewrite probably.

Yeah don't run the code in trycf. Run it locally. You were only giving it to me there so I had something to work with.

I'm trying to run it in my local sandbox but the xml value for those two symbols are gobbledygook

Right so a couple things. This does not have the

<cfprocesingdiective>

on it. You're still using

xmlFormat

. Also you've put the

encodeForXml

format into the XML string. It's a CFML function, like I said: use it instead of

xmlFormat

.

@Adam Cameron I got my local simple version to work. Using the cfprocessingdirective with pageEncoding combined with using ecodeForXml() instead of XMLFormat() made the difference. It's odd that the Adobe docs say you should no longer need to use pageEncoding version of cfprocessingdirective as the server should be able to automatically detect the char set. Using the UTF-8 attribute on the fileOpen and fileWrite functions didn't make any difference. Adobe docs also say don't use both suppressWhitespace and pageEncoding attributes in the same tag, if needed create two tags.

should be able to automatically detect the char set.

I think it only looks so far through the file, but don't quote me on that.

as I add a second cfprocessingdirective tag pair or add pageEncoding attribute on the first cfprocessingDirective tag pair

I don't believe this, sorry.

cfprocessingdirective

is a compilation directive & that error you're talking about is a runtime one. Using

suppressWhitespace

is a bit archaic though. Why have you got that? If yer doing something in CFML where the whitespace matters, yer probably not doing it right. Is this a tactic to get rid of the bad whitespace at the top of your XML that you're creating with

<cfsavecontent>

? If so... just... don't put it in there in the first place. And use

<cfxml>

not

<cfsavecontent>

. That's what it's for.

@Adam Cameron I'm working with old code. It constructs the page content in chunks, using multiple cfsavecontent variables to piece it together into multple files and then those files later contructed into the excel file. It's complicated and probably needs to be re-written or at least looked at with new eyes. I was hoping for a quick fix but it has turned out to be far more time consuming than makes sense to spend on this. I did try replacing the 8-10 cfsavecontents with cfxml tags but it complains that the xml tag structure within it are incomplete with missing closing tags, which occur in later chunks. Yes the suppressWhitespace was to prevent ballooning file size in constructing this export. End result so far is that when i either add a cfprocessordirective for pageEncoding (in addition to the one for suppressWhitespace) OR if I simply add pageEncoding to the existing one for suppressWhitespace, it throws a CF compiler error that I can't declare the routine more than one and claims the wrapper function has been declared twice. It hasn't. I remove the above and error goes away and all exports as it has for years, but those two symbols I added do not get rendered properly in the xml file generated. (and you are right, this is not an excel file issue but gets mangled in the xml file creation.) If you have any other ideas to try I'm open to your suggestions but you have already spent a lot of time helping me and it appears short of re-writing this (if it's even possible given the design approach)just doesn't make sense to spend so much effort on this. I wanted to find out the root cause and I think that has been answered, but fixing this code doesn't appear to be possible unless the cause of the cfml compiler error can be determined. I'm not expecting anyone to know why without looking at the code but I can't share that.

The only think I can think with the

<cfprocessingdirective>

thing is to clear out yer

cfclasses

dir and flush yer component cache in case the change of compilation rules somehow screw around with the compiled classes badly. It is absolutely not a problem to have both, and would not change any logic around how functions are loaded.

Neither action helped. Thanks again for your help!

So weird.