Creating Spreadsheets with ColdFusion without headers

This post is more than 2 years old.

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]);
	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 senior developer evangelist for Adobe. He focuses on document services, JavaScript, and enterprise cat demos. If you like this article, please consider visiting my Amazon Wishlist or donating via PayPal to show your support. You can even buy me a coffee!

Lafayette, LA

Archived Comments

Comment 1 by Raymond Camden posted on 12/24/2014 at 12:31 PM
Comment 2 (In reply to #0) by Raymond Camden posted on 12/24/2014 at 8:53 PM

Well actually, it isn't way more code. It would be one more loop. It is more "operations" for sure, and would be slower, but probably not significantly so. (I'd have to test.) *And* it solves the issue I have with using lists and worrying about commas.

Hell - I'd say what you suggested would be *preferred* over what I showed - so thanks. :)

Comment 3 (In reply to #0) by Raymond Camden posted on 12/25/2014 at 2:13 AM

There are only 3 comments here - so feel free to share. Although I'd share a Gist link.