Export excel - query names columns headers

[willardnesss]

Howdy - I would like CF to basically import a SQL table into EXCEL without creating an HTMl page. I want CF to create the excel file from scratch on the fly - no using templates.

I want CF to name the excel column headers, then export all of the records into the excel file.

I have about 150 column names that could change over time, so I do not want to create an html page - I want CF to create the columns based on the SQL table column names.

I could then either save, or open the file

Anyone got any ideas - thanks!

[-__cfSearching__-]

So you wish to export multiple tables? Use your database's metadata to obtain the column names for each table. That syntax is determined by your database. An MS SQL example is

<cfquery name="getColumns" datasource="...">
SELECT Column_Name
FROM INFORMATION_SCHEMA.Columns
WHERE Table_Name = 'YourTableName'
AND Table_Catalog = 'YourDatabaseName'
</cfquery>

In a second query use the column names to retrieve the data.

<cfquery name="getData" datasource="...">
SELECT #ValueList(getColumns.Column_Name)#
FROM YourTable
</cfquery>

Then dynamically display the values using cfquery's columnList variable and array syntax. Use cfheader and cfcontent to simulate an excel file.

..
<cfoutput query="getData">
<tr><cfloop list="#getData.columnList#" index="columnName">
<td>#getData[columnName][currentRow]#</td>
</cfloop>
</tr>
</cfoutput>
...

This is very a common task. Search the forums and google. You will find a range of examples and options, such as
http://www.cflib.org/udf.cfm?ID=1197
http://www.bennadel.com/blog/474-ColdFusion-Component-Wrapper-For-POI-To-Read-And-Write-Excel-Files....
...

[willardnesss]

Thanks - very helpful.

However - having one issue. The returned data is being ordered in a different sequence than the column names

Is there an order by command in SQL to order the data in the ORIGINAL sequence listed in the SQL table. It seems that SQL is creating its own order sequence for the data, so the data ends up not matching the column name created in the column name query....

Thanks!

[-__cfSearching__-]

Unless you are using two different column lists, the data should always match the column headers.

The column order is a different story. The cfquery "columnList" variable always returns columns in alphabetical order. You must create your own column list to preserve the desired order.

For MS SQL, order the columns by ORDINAL_POSITION. Then use ValueList to create the list of columns

<cfset columnNames = ValueList(getColumns.Column_Name)>

When you output the headers and data use the #columnNames# variable, not #getData.columnList#.

[willardnesss]

Thank you again - incredibly helpful - and sorry, this seems easy enough, but having a minor snafu

Using this query:

<cfquery name="getColumns" datasource="...">
SELECT Column_Name
FROM INFORMATION_SCHEMA.Columns
WHERE Table_Name = 'Fields'
AND Table_Catalog = 'mydatabase'
</cfquery>

when i create the list to order by, this is what happens:

<CFSET variables.Column_Order = ValueList(getColumns.column_Name)>

Then I test the output to see what it looks like:

<CFOUTPUT>#variables.column_Order#</CFOUTPUT>

And the result is weird - it gives me a perfect list with commas, then a second list of the same data with no delimiters...

Example of output - this is exactly how it displays on the web page - so I can not use this output in my order by statement, cause throws a query error due to the second line of duplicate non-delimited results.. any idea why this is happening - theoretically this process makes sense, but maybe I am missing a bit of syntax? THANKS!

AGENTCODE,POLICYNUMBER,POLICYSTATUS,VEHICLEID,DATEPOLICY
AGENTCODEPOLICYNUMBERPOLICYSTATUSVEHICLEID,DATEPOLICY

[-__cfSearching__-]

That should not happen. My guess would be a problem in the code or nesting of cfoutput tags.

To isolate the issue, create a page containing only

<cfquery name="getColumns" datasource="...">
SELECT Column_Name
FROM INFORMATION_SCHEMA.Columns
WHERE Table_Name = 'Fields'
AND Table_Catalog = 'mydatabase'
</cfquery>
<CFSET variables.Column_Order = ValueList(getColumns.column_Name)>

<cfoutput>#variables.Column_Order#</cfoutput>

If it displays the list correctly, the problem is with your code. If not, dump the getColumns query and post the results.

[willardnesss · Accepted solution]

This is the exact code that now works perfectly!!!! - Thank you so much - I was really stuck on trying to do the order in the order by clause of the query, which was totaly wrong - cool to have cfloop list do all of the ordering.



<body>


<cfquery name="getColumns" datasource=".........">
SELECT Column_Name
FROM INFORMATION_SCHEMA.Columns
WHERE Table_Name = 'Fields'
AND Table_Catalog = '......'
</cfquery>


<cfquery name="getData" datasource="........">
SELECT #ValueList(getColumns.Column_Name)#
FROM Fields
</cfquery>

<cfset variables.columnNames = ValueList(getColumns.Column_Name)>

<table border="0" cellspacing="0" cellpadding="0">

<TR><CFOUTPUT QUERY="GetColumns">
<TD>#column_Name#</TD>
</CFOUTPUT></TR>
<cfoutput query="getData">
<tr><cfloop list="#variables.ColumnNames#" index="columnName">
<td>#getData[columnName][currentRow]#</td>
</cfloop>
</tr>
</cfoutput>

</table>

</body>
</html>

<cfheader
name="Content-Type"
value="application/ms-excel">

<cfheader
name="Content-Disposition"
value="attachment; filename=Report.xls">

[-__cfSearching__-]

Actually it is the initial query that determines the order of the column names. I think MS SQL retrieves the names in ordinal position by default. By using #ValueList(getColumns.Column_Name)# you simply preserve that order. Though to guarantee the order, it is best to use an explicit order by clause.

Glad everything is working for you now.

[willardnesss]

Howdy - 1 more quick question - is there an easy way to format the data. The excel sheet is not displaying dates as a date data type.

Is there a quick easy trick for doing this besides manually changing the excel table

I am going ot have users (with no computer experience) save the excel files, so I would prefer not to have them then have to format a few of the excel colums to dates

Thanks - you have been incredibly helpful - this is new territory for me.

[-__cfSearching__-]

Use the column data types to identify the date columns. When a date column is found, use CF's dateFormat function to format the value as desired. You can obtain the column data types using either CF's getMetadata function or from MS SQL.

Here is an example using MS SQL's data types. It checks for columns with a "datetime" type. Your table may contain other date types like small datetime. Modify as needed.

<cfquery name="getColumns" datasource="....">
SELECT Column_Name, Data_Type
FROM ....
</cfquery>

<!--- use arrays instead of lists for easier access --->
<cfset columnNames = listToArray(valueList(getColumns.Column_Name))>
<cfset columnTypes = listToArray(valueList(getColumns.Data_Type))>

....
<cfoutput query="getData">
<tr><cfloop from="1" to="#arrayLen(columnNames)#" index="x">
<td><!--- if this is a datetime column and the value is not null, format it --->
<cfif columnTypes is "datetime" and IsDate(getData[columnNames][currentRow])>
#DateFormat(getData[columnNames][currentRow], "mm/dd/yyyy")#
<cfelse>
#getData[columnNames][currentRow]#
</cfif>
</td>
</cfloop>
</tr>
</cfoutput>
</table>

[willardnesss]

You completely and thoroughly rock - that is very cool.

If you live in bay area and ever are looking for projects, let me know. Also, I will buy you a beer. This just saved ours of work for users and myself. Bless CF and belss you for taking the time to help.

[-__cfSearching__-]

You are welcome. I am not in that area at present, but one never knows. I may take you up on it someday.

Cheers