/**
 * Converts a query to excel-ready format.
 * 
 * @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 1, June 26, 2002 
 */
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;
	
	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";
	}
	
	writeOutput("<table border=1><tr bgcolor=""C0C0C0"">");
	for (i=1;i lte ListLen(Headers);i=i+1){
		header=listGetAt(Headers,i);
		headerLen=Len(header)*10;
		writeOutput("<th width=""#headerLen#""><b>#header#</b></th>");
	}
	writeOutput("</tr>");
	for (j=1;j lte query.recordcount;j=j+1){
		if (j mod 2) {
			writeOutput("<tr bgcolor=""FFFFFF"">");
		} else {
			writeOutput("<tr bgcolor=""#alternatecolor#"">");
		}
		for (k=1;k lte ListLen(InputColumnList);k=k+1) {
			col=ListGetAt(InputColumnList,k);
			colValue=query[trim(col)][j];
			
			if (trim(colValue) eq "")  {
				colValue = " ";
				colLength = 10;
			}  else if (isDate(colValue)) {
				// format the dates so that we have good looking dates
				colValue = dateFormat(colValue,"mm/dd/yyyy");
				colLength=Len(colValue)*10;
			} else if (isnumeric(trim(colvalue)))  {
				// trim numbers and set them with no quotes so that they are interpreted as numbers
				colValue = trim(colvalue);
				colLength=0;
			} else {
				// if they get here, the field is probably text.
				colValue = replaceNoCase(colValue,"""","&quot;","ALL");
				colValue = replaceNoCase(colValue,"'","","ALL");			
				// if there are any special characters at the begining of the string, excel will interpret the string as a function
				// so we'll add quotes so that it doesn't.
				if (listfind("-,+,*,&,%,(,),/",left(trim(colvalue),1)))  { 
					colValue = '"' & colvalue & '"';	
				}
				colLength=Len(colValue)*10;
			}  	
			writeOutput("<td width=""#colLength#"">#colValue#</td>");
		}
	writeOutput("</tr>");
	}
	writeOutput("</table>");
	return "";
}