4

Take the following code...

<cfspreadsheet action="read" src="#z.xlsfile#" query="data" headerrow="1" excludeHeaderRow="true" />
<cfoutput>
    <!--- Lets replace spaces with $ so they are easy to see --->
    <cfloop query="data">
        #data["Doc Type"][data.currentrow]# - #replace(data["Doc Type"][data.currentrow],' ','$','all')#<br>
        #data["Doc No"][data.currentrow]# - #replace(data["Doc No"][data.currentrow],' ','$','all')#<br>
        #data["Doc Rev"][data.currentrow]# - #replace(data["Doc Rev"][data.currentrow],' ','$','all')#<br>
        #data["File Name"][data.currentrow]# - #replace(data["File Name"][data.currentrow],' ','$','all')#<br>
        <hr>
    </cfloop>
</cfoutput>
<cfabort>

The XLS file that is being read has cell data that has leading and trailing spaces. On one ColdFusion server the output shows no spaces as if <cfspreadsheet> automatically trimmed all the data. On another server it does not. I'm thinking maybe the poi.jar was changed? Has anyone else experienced this? I haven't been able to find anyone complaining about this.

This ColdFusion server appears to automatically trim the data...

Version: 2016,0,02,299200

Update Level: chf20160002.jar

This one does not...

Version: 10,0,19,298511

Update Level: chf10000019.jar

gfrobenius
  • 3,987
  • 8
  • 34
  • 66
  • Silly question, did you compare the `len()`, and also output the raw values with something like `
    |#value#|
    ` to make it easier to view any differences?     – Leigh Jun 16 '16 at 19:13
  • I just did both `
    ` and `len()`, results validated.  One version definitely has leading and training spaces in the data right after the read and the other version does not.
     – gfrobenius Jun 16 '16 at 19:29
  • 2
    Ugh.... It is not due to POI. The change must be on the CF side. If you run this [trycfm.com example](http://trycf.com/gist/cb811394b56fec0e5c3321f52c896852/acf2016?theme=monokai), the spaces are being removed by the CF functions. Did you check the 2016 documentation to see if it mentions any changes to the spreadsheet functions (I have not yet)? – Leigh Jun 16 '16 at 21:12
  • That site is killer! I see on that site where you can switch the ColdFusion versions. CF11 auto trims, CF10 does not. Just what I'm experiencing. Surprised I haven't been able to find anyone else on the net talking about this issue. I will manually trim to be safe of course. Just glad I know it's not me now :) Thanks! – gfrobenius Jun 16 '16 at 21:21
  • 2
    (Edit) What a great idea, right? That site and http://cflive.net are great for sharing. Yes, it is definitely not you. My searches on that issue turned up zilch too, so if it is not in the release notes or docs I would also file a [bug report](http://bugbase.adobe.com). Hopefully they will fix it or at least document the behavior, since it is a pretty significant change to add without mention. (Personally I do not think it should automatically trim unless asked to ..). – Leigh Jun 16 '16 at 21:28
  • Done. That was the first time I ever submitted a bug. Hope I did it correctly. https://bugbase.adobe.com/index.cfm?event=bug&id=4165319. Thanks again. – gfrobenius Jun 16 '16 at 21:47
  • You are welcome. Looks good, though I would probably include the code, just in case the external site is unavailable for some reason. Since the confirmation and bug report is probably the "answer" here, you may as well write it up. It will save the next guy from scratching his head :) – Leigh Jun 16 '16 at 22:01

0 Answers0