Inspiring

Question

ColdFusion Query object with columns in correct order?

Forum|Forum|5 years ago
August 6, 2020
7 replies
3827 views

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

This topic has been closed for replies.

L

Larry C Lyons

Inspiring

Glad to help. I had stumbled over it a few years ago and just kept it in my snippets collections. That said, remember this is an undocumented feature (although it is listed in the Dreamweaver help), so no idea if it is going to go away with an update in the future.

L

Larry C Lyons

Inspiring

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

L

Larry C Lyons

Inspiring

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

ArrayToList( queryName.getColumnNames() );

Charlie Arehart

Adobe Expert

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.

/Charlie (troubleshooter, carehart. org)

tommuck1Author

Inspiring

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

Adobe Expert

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.

/Charlie (troubleshooter, carehart. org)

WolfShade

Brainiac

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,

^ _ ^

tommuck1Author

Inspiring

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

Tom

WolfShade

Brainiac

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,

^ _ ^

tommuck1Author

Inspiring

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

Brainiac

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

Brainiac

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,

^ _ ^

tommuck1Author

Inspiring

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

Brainiac

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,

^ _ ^

tommuck1Author

Inspiring

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

Adobe Expert

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.

tommuck1Author

Inspiring

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

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded