Creating Spreadsheets with ColdFusion without headers

This question came in yesterday and I thought I’d share it. Paul asked a simple question - he had a query and wanted to write it to a spreadsheet without having the query columns being used as headers. There is an excludeHeaderRow attribute in cfspreadsheet, but it applies to reading only.

To properly handle this, you have to skip using cfspreadsheet completely. Instead, simply use the various spreadsheet functions to write out the query row by row. Here is an example minus the fake query created earlier.

ss = spreadsheetNew("Main");
colList = parametersQuery.columnList;
for(i=1; i<=parametersQuery.recordCount;i++) {
	rowStr = "";
	row = [];
	for(x=1; x<listLen(colList); x++) {
		col = listGetAt(colList, x);
		arrayAppend(row, parametersQuery[col][i]);
	}
	//writedump(row);
	rowStr = arrayToList(row);
	writeoutput("writing #rowStr#<br>");
	spreadsheetAddRow(ss, rowStr);
}
spreadsheetWrite(ss, expandPath('./' & spreadsheetname),true);

In case you’re wondering why I create an array and then turn it back into a list, that was done to ensure the empty cells are preserved in the spreadsheet row.

So as I wrote this, I decided to look at the docs a bit more. It bugged me that all the functions seemed to require either a query or a list. Specifically, it bugged me that I couldn’t pass an array. Using a list in this example is inherently dangerous because a query cell value could include a comma. I then noticed that spreadsheetAddRows does support using an array. Unfortunately the function is broken. Like, seriously - try the sample code at the link I just shared. First fix the typo of course (if I have time I’ll edit the wiki page). You get this error trying to add rows: Invalid row number (-1) outside allowable range (0..65535). I’ll file a bug report and add the link as a comment.

Raymond Camden's Picture

About Raymond Camden

Raymond is a developer advocate looking for his next gig. He focuses on JavaScript, serverless and enterprise cat demos. If you like this article, please consider visiting my Amazon Wishlist or donating via PayPal to show your support.

Lafayette, LA https://www.raymondcamden.com

Comments