tracker issue : CF-4051598

select a category, or use search below
(searches all categories and all time range)
Title:

spreadsheet.addRows() doesn't work with commas in data

| View in Tracker

Status/Resolution/Reason: Closed/Won't Fix/

Reporter/Name(from Bugbase): Adam Cameron / Adam Cameron (Adam Cameron)

Created: 09/08/2015

Components: Document Management, Office Integration

Versions: 11.0

Failure Type:

Found In Build/Fixed In Build: CF11_Final /

Priority/Frequency: Major / Some users will encounter

Locale/System: ALL / Platforms All

Vote Count: 1

Problem Description:
The addRows() method treat commas as column delimiters, not as data.

See repro case below

One can pass an array of data in to addRows(), but instead of this being an array of arrays, its an array of comma-delimited lists. So it's not possible to insert data values with commas in them.

Any functionality in CFML which accepts a list, must also accept a delimiter. It is a bug to not do so, as it doesn't follow the Principle of Least Astonishment (https://en.wikipedia.org/wiki/Principle_of_least_astonishment)

And *this* function should accept an array of arrays (although that's a feature req, I get that).


Steps to Reproduce:
<cfscript>
spreadsheet = spreadsheetNew();
data = [
	"tahi",
	"rua",
	"toru,wha",	// this is one data value
	"rima,ono" // so is this
];
filePath= expandPath("./withArray.xls");

spreadsheet.addRows(data,1,1);
spreadsheet.write(filePath, true);
</cfscript>

Actual Result:
"wha" ends up in B3
"ono" ends up in B4

Expected Result:
A3's value should be "toru,wha"
A4's value should be "rima,ono"


And, yes I know I can use a query. That's beside the point: I have an array, and this thing is supposed to work with arrays.

----------------------------- Additional Watson Details -----------------------------

Watson Bug ID:	4051598

External Customer Info:
External Company:  
External Customer Name: Adam Cameron
External Customer Email:  
External Test Config: My Hardware and Environment details:

Attachments:

Comments:

+1 - Adobe, please never forget the delimiter parameter when adding a new function that accepts a list. It just adds more work later.
Vote by External U.
5975 | September 08, 2015 05:50:40 PM GMT
Supporting array of array makes sense. We would add that and deprecate array of list. IMO, Supporting list in SpreasheetAddRow and many other functions was a bad idea to begin with. To keep the functions clean, instead of adding 'delimiter' and 'ignoreEmptyFields' in every function, a better approach is to use ListToArray function and then pass that array here.
Comment by Rupesh K.
5961 | September 13, 2015 11:50:26 PM GMT
Agreed: lists in general aren't a cool idea, really: I don't think their usage should be perpetuated for new functionality. The suggested approach to dealing with this is a good one.
Comment by External U.
5962 | September 14, 2015 12:14:43 AM GMT
We can pass comma separated data in array. If there are single quotes, take them as escape characters for the string. Example: ' "ddd,ddd" ' will be one cell value. Please see the below snippet <cfscript> spreadsheet = spreadsheetNew(); data = [ "tahi", "rua", "'toru,wha'", // this is one data value "rima,ono" ]; filePath= expandPath("./withArray.xls"); spreadsheet.addRows(data,1,1); spreadsheet.write(filePath, true); </cfscript>
Comment by Mayur J.
5963 | September 14, 2015 05:03:26 AM GMT
Adobe, Why is this marked NeverFix? Thanks!, -Aaron
Comment by External U.
5964 | September 30, 2015 03:51:16 AM GMT
Adobe, Regarding "Supporting array of array makes sense. We would add that[....]", why is this ticket marked NeverFix? Can you please clarify? Thanks!, -Aaron
Comment by External U.
5965 | October 06, 2015 12:31:58 AM GMT
Please clarify.
Comment by External U.
5966 | October 06, 2015 05:01:29 AM GMT
Hi Aaron, Raymond, We have added support for array of array. bug no# CF-3956121 is raised for the same. Regarding commas as column delimiter can be escaped if data is passed like below example. data = [ "tahi", "rua", "'toru,wha'", // this is one data value "rima,ono" ]; Notice there is single quote before double quotes.
Comment by Mayur J.
5967 | October 06, 2015 06:16:39 AM GMT
I'm happy you support array of arrays, but that does not address the original issue, which is that a function in CFML that accepts a list must also accept a delimiter. Add a fourth argument for delimiter, or remove the functionality since it doesn't match other list functions. It will confusion as it is inconsistent. (To be clear, when I say remove the feature, I mean the ability to use lists, not the new array of array support which is what I'd recommend to folks anyway.)
Comment by External U.
5968 | October 06, 2015 06:22:02 AM GMT
As we had mentioned earlier, we are deprecating the usage of list in this method and hence we would not add the support for delimiter. Array of array would be the recommended way to use.
Comment by Rupesh K.
5969 | October 06, 2015 06:43:51 AM GMT
I didn't see the deprecation part - good!
Comment by External U.
5970 | October 06, 2015 07:46:41 AM GMT
Hi Mayur and Rupesh CF-3956121 isn't public. Can you please make CF-3956121 public so we can comment/vote on it? Thanks!, -Aaron P.S. Thanks for clarifying!
Comment by External U.
5971 | October 06, 2015 01:33:33 PM GMT
Hi Mayur, You mentioned CF-3956121 but did not make it public. Can you please make it public for voting/commenting? Thanks!, -Aaron
Comment by External U.
5972 | December 04, 2015 05:56:45 AM GMT
Aaron, We have made #CF-3956121 public. You should be able to view the report.
Comment by Piyush K.
5973 | December 23, 2015 09:13:23 AM GMT
Hi Piyush, Yes, I'm able to view it now. Thanks very much! -Aaron
Comment by External U.
5974 | December 23, 2015 12:57:32 PM GMT