ColdFusion Query object with columns in correct order?

[tommuck1]

I know this is a long standing issue with ColdFusion, and there are many workarounds, but is there an easy way to get the query object to have the columns in the correct order without jumping through hoops? I haven't dealt with it before because I always had display outputs that were manually put together, but when I started using the spreadsheet functions -- spreadsheetAddrows() puts the data into a spreadsheet with the columns in the wrong order. Any easy fix? 

Tom Muck

[tommuck1]

My workaround will be to name my columns using aliases as a, b, c, d, e, f, etc. so they show up in the correct order, then add the column names directly to the spreadsheet. Not optimal, because I have to do this to over 40 different report queries, but it's workable.

Tom

[BKBK]

Hi tommuck1,

Your solution is the neatest and most efficient that I know of.

Nevertheless we should check the documentation to see whether there is a new feature to order query columns.

[tommuck1]

Thanks, it got me moving again, which was the important thing. I didn't want to do a manual insert, given the number of reports I have. I was able to get all the coding complete yesterday, and the aliases did not affect the efficiency at all.

Tom

[WolfShade]

Hi, tommuck1,

I haven't worked with cfspreadsheet in a long time, but I do know that I am one of the most pedantic people that I know.  If you are just throwing a query object at spreadsheetAddRows(), then I believe CF is going to sort the columns alphanumerically.

I don't currently have the code on this system, but I had to manually build the spreadsheet creating, and formatting (twice), and populating of data from a form submission (which I get it.. kinda different from a query object).  If I post it, here, you could use my code as an example of how a pedantic person, such as myself, would do it.  I'd loop through the query and manually enter the column name and index for each record in the recordset.  Doing it this way also allows you to freeze the column header rows in the Excel spreadsheet, so that as a user scrolls up or down he/she will always know what column contains what data.

Let me know if you'd like to see how I work with spreadsheets in CF.

V/r,

^ _ ^

[tommuck1]

Thanks for the reply. I would love to see how you did it, but I was hoping for a less manual approach, as I might have to create 10-20 reports at a time and some would likely be thousands of rows. I am formatting the cells in Excel twice as well -- when I try to format the data, the formatting of the font styles wipes out the data formatting, so I have to come back and format the data AFTER formatting the font.

Tom

[WolfShade]

Yup, that was my experience, too.  I would format the cells, then the data being added wiped out the formatting, so I placed the formatting after the data insert, to no avail.  I placed formatting before and after the data insert, and everything worked.  Don't know why, but there it is.

I completely forgot that I wrote an example of manual inserting a pseudo recordset into a spreadsheet.  But it's on my work system.  So, I will log off from my personal system, go to my work system, and post the code, for you.  Please ask if you have any questions.

V/r,

^ _ ^

[WolfShade]

Here is my example of manually inserting the data into a spreadsheet.  Pseudo code, pretend there's a query object called "variables.rosterQry" that contains contact information.

<cfsilent>
	<cfscript>
                variables.cont = false;
    		/*variables.rosterQry is the name of my query object*/
                switch(IsQuery(variables.rosterQry)){
                    case true:
                        variables.cont = true;
                        variables.rqCols = ArrayToList(variables.rosterQry.getColumnNames(),',');
                        variables.rqLen = ListLen(variables.rqCols,',');
                        variables.thisFileName = "Report-" & DateTimeFormat(now(),'yyyymmdd_HHnnss') & ".xlsx";
                        variables.ssObj = SpreadsheetNew(left(trim(form.reportName),30),'true');/* Setting last argument to 'true' makes this an xlsx, not xls. */
                        variables.format = StructNew();
                        variables.format.font = "Arial";
                        variables.format.textwrap = "true";
                        variables.format.verticalalignment = "VERTICAL_TOP";
			variables.format.dataformat = "text";
			SpreadsheetFormatColumns(variables.ssObj,variables.format,"1-#val(variables.rqLen)#");
			SpreadsheetFormatRows(variables.ssObj,variables.format,"1,2");
                        SpreadsheetSetCellValue(variables.ssObj,"Report - " & replaceNoCase(form.reportName,'/','_','all'), 1, 1); /* This is the name of the report, top row */
			SpreadsheetAddFreezePane(variables.ssObj,0,2); /* Freeze top two rows */

			for(x = 1; x lte val(variables.rqLen); x++){ /* This inserts the column names as row headers */
				variables.colName = ListGetAt(variables.rqCols,x);
				SpreadsheetSetCellValue(variables.ssObj,variables.colName,2,x);
				}

			for(y = 1; y lte val(variables.rosterQry.recordCount); y++){ /* This loops the query records */
				for(x = 1; x lte val(variables.rqLen); x++){ /* This loops each column per recordset */
					variables.colName = ListGetAt(variables.rqCols,x);
					variables.thisValue = REreplaceNoCase(variables.rosterQry[variables.colName][y],"&##59;",";","all"); /* These make sure that no HTML entities are in the data */
					variables.thisValue = REreplaceNoCase(variables.thisValue,"&apos(&##59)?;","'","all");
					variables.thisValue = REreplaceNoCase(variables.thisValue,"&quot(&##59)?;",'"',"all");
					variables.thisValue = REreplaceNoCase(variables.thisValue,"&lt(&##59)?;",'<',"all");
					variables.thisValue = REreplaceNoCase(variables.thisValue,"&gt(&##59)?;",'>',"all");
					variables.thisValue = REreplaceNoCase(variables.thisValue,"&##40(&##59|;)","(","all");
					variables.thisValue = REreplaceNoCase(variables.thisValue,"&##41(&##59|;)",")","all");
					SpreadsheetSetCellValue(variables.ssObj,variables.thisValue,val(y + 2),x);
					}
							}
			SpreadsheetFormatColumns(variables.ssObj,variables.format,"1-#val(variables.rqLen)#");
			SpreadsheetFormatRows(variables.ssObj,variables.format,"1,2");
                    break;
                    default: /* Do nothing if the query object doesn't exist */
                    break;
                    }
                
	</cfscript>
</cfsilent><cfswitch expression="#variables.cont#">
	<cfcase value="yes">
		<cfheader name='Content-Disposition' value='attachment; filename=#variables.thisFileName#' />
		<cfheader name="Content-Type" value="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" />
		<cfcontent type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" variable="#spreadsheetReadBinary(variables.ssObj)#" reset="true"><!--- MUST USE SPREADSHEETREADBINARY() --->
	</cfcase>
	<cfdefaultcase><script type="text/javascript">alert('Object Returned Is Not A Query');</script></cfdefaultcase>
</cfswitch>
    </cfcase>
    <cfdefaultcase><script>alert('No Data Submitted - No Report To Generate');</script><cfabort></cfdefaultcase>
</cfswitch>

V/r,

^ _ ^

[tommuck1]

Thanks for that. I wasn't aware of the spreadsheetfreezepane function -- I'll be making use of that! 

I'll show you what I ended up doing. Each report has it's own template (column headings, query) so in that file I set an array to denote which columns are date columns:

<cfscript>
myDateFormat=StructNew(); 
myDateFormat.dataFormat="m/d/yy";
variables.dateFormats = ["7","8","9"]; 
</cfscript>

Then when I generate the report, I format the text, then I check for the array and format those date cells:

	myFormat2=StructNew();
	myFormat2.color="black";
	myFormat2.bold="false";
	myFormat2.alignment="left";
	myFormat2.font="Arial";
	myFormat2.fontSize = 9;
	
	SpreadsheetFormatRows(sheet,myFormat2,"2-#rsExcel.recordCount#");
	if (isDefined("variables.dateFormats") AND IsArray(variables.dateFormats)) {

		for (column in variables.dateFormats) {
			spreadsheetFormatColumn(sheet, myDateFormat, column);
		}
	}

[WolfShade]

Pretty slick with the date formatting array.

Yeah, CF has a lot of really nice SpreadSheet functions.  If you dig down through the list, you might find others that are quite useful.  Being the pedant that I am, this makes me a bit of a control freak, and the granularity offered does not disappoint.

V/r,

^ _ ^

[WolfShade]

FOLLOW UP:  I don't know if this will help, or not, but I only just now ran across this little tidbit on burnignorance.com.

<cfoutput>
#ArrayToList(myQuery.getColumnList())#
</cfoutput>

According to the site, this should get you a list of the column names in the original order they were retrieved from the database.

I have not tested this, but maybe give it a shot.

V/r,

^ _ ^

[tommuck1]

Thanks, I did try to go down that route, but it would have meant adding rows to the spreadsheet manually.

Tom

[Charlie Arehart]

Guys, I'm finding that when a query is added to a spreadsheet using spreadsheetaddrows, the order of the columns from teh select IS preserved. It's not made alphanumeric, as is indeed the case with the columnlist key of the struct if you use a RESULT attribute on a query.

FWIW I'm testing on CF2018, update 10. What version are you testing on?

Here's some sample code you can try. You should see the spreadsheet cols are in the same order as the select: orderid,customerfirstname,customerlastname,address...

<cfscript>
ArtOrders=QueryExecute("SELECT orderid,customerfirstname,customerlastname,address,total,city FROM orders ORDER BY orderid",[],{datasource="cfartgallery"});
spObj=spreadsheetNew("Art",true);
spreadsheetAddrows(spObj,ArtOrders);
spreadsheetWrite(spObj,"#expandpath('addrows.xlsx')#",true);
</cfscript>

Granted, it's using one of CF's sample DBs (which run on Derby), but I used the same code against a SQL Server db and saw the same.

Or am I misreading all this? I've read each comment.

[tommuck1]

Hi Charlie, looks like that works as you say (I'm on 2018 update 10). I'll have to investigate further when I get back to work on Monday, but when I was running the queries locally against a SQL Server database, then using query of queries to get my summarized results. The columns were definitely in alphabetic order. Weird stuff.

Tom

[Charlie Arehart]

Well, I added a q of q to that code and the result was still that the order of the cols (in the select within the q of q) was reflected in the spreadsheet, when that q of q query was passed to the spreadsheetaddrows.

But let's see what you may find when you check into things next week. As always, just trying to help.

[BKBK]

It seems like ColdFusion implements an alphanumeric ordering of columns by name when outputting a query object.

Demo:

<cfscript>
	artOrders=queryExecute("SELECT orderid,customerfirstname,customerlastname,address,total,city FROM orders ORDER BY orderid",[],{datasource="cfartgallery"});
	writedump(artOrders);
</cfscript>

[tommuck1]

Thanks again Charlie. I had tried that yesterday as well with your code sample, and I also tried with a simple query against a SQL database, but I'll go back to my working code next week when I get back to work. It's a mystery. In any event, I did find a workaround, but I am very curious now.

Tom

[Charlie Arehart]

BKBK, yep. In that case, the behavior is just like the columnlist discussion earlier above. But since Tom's focus was about passing the query into the spreadsheetaddrows functions, that's why the latter messages have focused on that. (And it's why I shared that my observation was that CF did properly keep the column order when doing that, in my own testing.)

Looking forward to hearing what Tom may find when he gets a chance to look into things again this week.

[tommuck1]

Hi Charlie, I backed out my column aliases on one report and ran it again, and things seem to be working correctly. I can't duplicate the exact steps I went through at this point, though, because I have also added formatting and other things to the reports, so it's still a mystery why I was getting alphabetical column names in my initial reports. Alls well that ends well, though. 

Tom

[BKBK]

I said earlier, "...alphanumeric ordering of columns by name when outputting a query object". I should in fact have made this point more strongly. 

That is, I think that ColdFusion holds the query object in memory with the columns in the same order as in the SQL-Select. It is only during an output, for example, that ColdFusion orders the query-data by column name. 

In other words, the columns are in the "correct" order when ColdFusion passes the query to the spreadsheet functions. Which agrees with Charlie's demonstration.

@tommuck1, to actually see this in action, run the following test code:

<cfscript>
queryToJSON=serializeJSON(yourQuery)
// Note how the columns are in the 'correct' order
writeoutput(queryToJSON);
</cfscript>

[tommuck1]

Thanks BKBK, I knew that, which is why I said in the original post that I know it's a long-standing issue. I was surprised to see it in the spreadsheet functions, but now I can't reproduce it, so I'm putting it behind me. If I run into it again I'll be sure to update the post.

Tom

[BKBK]

Clear. Thanks, Tom.

[Larry C. Lyons]

Hi Tom,

Try this, it's an undocumented function but has been in CF since MX.

ArrayToList(queryName.getColumnNames();

This will give you an array of column names in the same order as in the query. 

hth,

larry

[Larry C. Lyons]

It would help if I typed the darned thing properly without typos. It should be:

ArrayToList( queryName.getColumnNames() );

[James Moberg]

Thanks for the tip.  I didn't realize this and have been using queryName.getMetaData().getColumnLabels() with Adobe ColdFusion and  queryName.ColumnArray() with Lucee.  getColumnNames() appears to work on both platforms.