CFLib.org – Common Function Library Project

Query2Excel(query[, headers][, cols][, alternateColor])

Last updated February 23, 2005

author

Jesse Monson

Version: 2 | Requires: CF5 | Library: DataManipulationLib

Description:
By passing in a query, list of query columns, and list of display headers and an alternating row color you can create content that can be saved directly with a .xls extension. Must be able to use the CFCONTENT tag.

Return Values:
Returns a string.

Example:

<cfscript>
	cols = "sName,sAge";
	heads = "First Name,Age";
	data = queryNew(cols);
	queryAddRow(data,2);
	querySetCell(data,"sName","Joe",1);
	querySetCell(data,"sAge","25",1);
	querySetCell(data,"sName","John",2);
	querySetCell(data,"sAge","30",2);
</cfscript>
<!--- Uncomment when testing.
<CFHEADER NAME="Content-Disposition" VALUE="inline; filename=foo.xls">
<cfcontent type="application/msexcel">---><cfoutput>#Query2Excel(data,heads,cols,"DDDDDD")#</cfoutput><!---<cfabort>--->

Parameters:

Name Description Required
query The query to use. Yes
headers A list of headers. Defaults to col. No
cols The columns of the query. Defaults to all columns. No
alternateColor The color to use for every other row. Defaults to white. No

Full UDF Source:

/**
 * Converts a query to excel-ready format.
 * Version 2 by Andrew Tyrone. It now returns a string instead of directly outputting.
 * 
 * @param query 	 The query to use. (Required)
 * @param headers 	 A list of headers. Defaults to col. (Optional)
 * @param cols 	 The columns of the query. Defaults to all columns. (Optional)
 * @param alternateColor 	 The color to use for every other row. Defaults to white. (Optional)
 * @return Returns a string. 
 * @author Jesse Monson (jesse@ixstudios.com) 
 * @version 2, February 23, 2005 
 */
function Query2Excel(query) {
	var InputColumnList = query.columnList;
	var Headers = query.columnList;

	var AlternateColor = "FFFFFF";
	var header = "";
	var headerLen = 0;
	var col = "";
	var colValue = "";
	var colLen = 0;
	var i = 1;
	var j = 1;
	var k = 1;
	
	var HTMLData = "";
	
	if (arrayLen(arguments) gte 2) {
		Headers = arguments[2];
	}
	if (arrayLen(arguments) gte 3) {
		InputColumnList = arguments[3];
	}

	if (arrayLen(arguments) gte 4) {
		AlternateColor = arguments[4];
	}
	if (listLen(InputColumnList) neq listLen(Headers)) {
		return "Input Column list and Header list are not of equal length";
	}
	
	HTMLData = HTMLData & "<table border=1><tr bgcolor=""C0C0C0"">";
	for (i=1;i lte ListLen(Headers);i=i+1){
		header=listGetAt(Headers,i);
		headerLen=Len(header)*10;
		HTMLData = HTMLData & "<th width=""#headerLen#""><b>#header#</b></th>";
	}
	HTMLData = HTMLData & "</tr>";
	for (j=1;j lte query.recordcount;j=j+1){
		if (j mod 2) {
			HTMLData = HTMLData & "<tr bgcolor=""FFFFFF"">";
		} else {
			HTMLData = HTMLData & "<tr bgcolor=""#alternatecolor#"">";
		}
		for (k=1;k lte ListLen(InputColumnList);k=k+1) {
			col=ListGetAt(InputColumnList,k);
			colValue=query[trim(col)][j];
			colLength=Len(colValue)*10;
			if (NOT Len(colValue)) {
				colValue="&nbsp;";
			} 
			if (isNumeric(colValue) and Len(colValue) gt 10) {
				colValue="'#colValue#";
			} 
			HTMLData = HTMLData & "<td width=""#colLength#"">#colValue#</td>";
		}
	HTMLData = HTMLData & "</tr>";
	}
	HTMLData = HTMLData & "</table>";
	
	return HTMLData;
}
blog comments powered by Disqus

Search CFLib.org


Latest Additions

Kevin Cotton added
date2ExcelDate
May 5, 2016

Raymond Camden added
CapFirst
April 25, 2016

Chris Wigginton added
loremIpsum
January 18, 2016

Gary Stanton added
calculateArrival...
November 19, 2015

Sebastiaan Naafs - van Dijk added
getDaysInQuarter
November 13, 2015

Created by Raymond Camden / Design by Justin Johnson