tracker issue : CF-3956121

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

ER: spreadsheetAddRows should accept 2D arrays for populating spreadsheets.

| View in Tracker

Status/Resolution/Reason: Closed/Fixed/

Reporter/Name(from Bugbase): / ext-user (Piyush Kumar Nayak)

Created: 03/19/2015

Components: Document Management, Office Integration

Versions: 11.0

Failure Type: Unspecified

Found In Build/Fixed In Build: 11,0,04,293328 /

Priority/Frequency: Normal / Some users will encounter

Locale/System: English / Mac 10 All,Win XP All

Vote Count: 4

Problem:
spreadsheetAddRows should accept 2D arrays for populating spreadsheets.

Method:
	out_fl = "#expandpath("./")#outdata.xlsx";
	xlobj = SpreadsheetNew("2d_arr_data", true); 

	qry_data = queryNew("cellA_data, cellB_data, cellC_data");
	queryAddRow(qry_data, {cellA_data:"a1", cellB_data="b1", cellC_data="c1"});
	queryAddRow(qry_data, {cellA_data:"a2", cellB_data="b2", cellC_data="c2"});
	writeDump(qry_data);
	
	arr2d = [["a1","b1","c1"],["a2","b2","c2"]]; 
	writeDump(arr2d);

	spreadsheetAddRows(xlobj, arr2d, 2, 1);
	cfspreadsheet(action="write", filename="#out_fl#", name="xlobj", overwrite=true);
	cfspreadsheet(action="read", src="#out_fl#", query="qryxl");
	writeDump(qryxl);
	
Result:
The system has attempted to use an undefined value, which usually indicates a programming error, either in your code or some system code.
Null Pointers are another name for undefined values.
The error occurred in C:/inetpub/cf11/ss/delim.cfm: line 20
20 : 	spreadsheetAddRows(xlobj, arr2d, 2, 1);
java.lang.NullPointerException
	at coldfusion.runtime.CFPage.SpreadSheetAddRows(CFPage.java:8696)
	at coldfusion.runtime.CFPage.SpreadSheetAddRows(CFPage.java:8649)
	at coldfusion.runtime.CFPage.SpreadSheetAddRows(CFPage.java:8735)
	at cfdelim2ecfm1951762110.runPage(C:\inetpub\cf11\ss\delim.cfm:20)
	at coldfusion.runtime.CfJspPage.invoke(CfJspPage.java:246)
	
Expected:
The same output as is achieved by replacing 
spreadsheetAddRows(xlobj, arr2d, 2, 1);
with
spreadsheetAddRows(xlobj, qry_data, 2, 1); 

Workaround:
n/a

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

Watson Bug ID:	3956121

External Customer Info:
External Company:  
External Customer Name:  
External Customer Email:

Attachments:

Comments:

+1 ......................
8001 | December 23, 2015 12:57:51 PM GMT
This is not fixed (at least in ACF11). The excel file generated (whether xls or xlsx) will have several empty rows. Converting it back to a query will work around this issue, but it is not the case that spreadsheetAddRows will accept a two dimensional array and produce useful output. Add a couple more elements to the 2d array, e.g. arr2d = [["a1","b1","c1"],["a2","b2","c2"],["a3","b3","c3"],["a4","b4","c4"]]; Then look at the outdata.xlsx file rather than the query. I get 11 rows rather than 4: a1 b1 c1 a2 b2 c2 a3 b3 c3 a4 b4 c4
Comment by Samuel Knowlton
7998 | April 14, 2017 07:22:16 PM GMT
Samuel, We'll take up this fix for CF11 in Update 13.
7999 | April 19, 2017 05:20:29 AM GMT
We will evaluate if this fix can be made available in the next bug-fix update release for 11.0
8000 | September 14, 2017 04:11:51 PM GMT
Given that this ticket is marked "closed" and was never updated, I assume that this fix never made it into ACF11. Our (largest) client who asked us to inquire about it last year just asked again, and they didn't understand when we explained what "supported" meant for ACF11. They've asked us to stop relying on products with that definition of support.
Comment by Samuel Knowlton
29674 | September 07, 2018 03:53:50 PM GMT
expect the fix for this to be made available for CF11 with update 16
30045 | December 12, 2018 01:10:18 PM GMT