Simple code to create a spreadshet

I call this function from any one of my report handler functions using something like:

setNextEvent(event="report.makeXLS"persist="result"); 

rc.result is the results of what ever report query I am running. This allows me to simply create a XLS styled button on any of my reports and it dumps the results of the report to an excel spreadsheet.

Handler:

function makeXLS(event,rc,prc)
{
        rc.theSheet = SpreadsheetNew("Sheet1");
        LOCAL.metaData=getMetaData(rc.result);
        LOCAL.header="";
        for (LOCAL.i=1;LOCAL.i<arrayLen(LOCAL.metaData);LOCAL.i=LOCAL.i+1)
        {
        if(LOCAL.i eq 1) LOCAL.header=LOCAL.metaData[i].name;
        else LOCAL.header=LOCAL.header & ',' & LOCAL.metaData[i].name;
        }
   SpreadsheetAddRow(rc.theSheet,LOCAL.header,1,1,1);   
  SpreadsheetAddRows(rc.theSheet,rc.result,2,1,1);   
  event.setView("report/makeXLS");
}
 
A little note here. You may wonder why I build the header using the meta data. Well it's simple it's because if you use the coldfusion function is tries to do you a favor and alphabetically sorts the headers. This isn't what you want because then your headers and results won't match.
 
View:
<cfheader name="content-disposition" value="inline; filename=export.xls" />  
<cfheader name="content-transfer-encoding" value="binary" />  
<cfcontent type="application/msexcel" variable="#spreadsheetReadBinary(rc.theSheet)#" reset="true" /> 

 


ContentBox Connections

ContentBox

From the ContentBox team:

"The ContentBox team is proud to announce the ContentBox Connection.  This is a new series of bi-weeily online meetings to showcase new ContentBox functionality and give the community a chance to ask questions and collaborate.  The first ContentBox Connection starts today (Oct 4th) at Noon Central.  We'll be showcasing some of the new multi-editor support and giving you a chance to ask questions and chat with the team."

Here is the meeting URL.

http://experts.adobeconnect.com/contentbox-connection/


Site description issue

ContentBox

I've also I found if  when using html in the contentBox site description. If using HTML you're going to want to use single quotes instead of double quotes. If you use double quotes it causes an issue with the description meta tag.

 


New Layout

ContentBox, News

We've created out own EvenGame ContentBox layout. Groovy. I will continue to tweak. While working on it I found a ContentBox issue. There is a layout name pagenosidebar which basically calls the primary page layout and passes and argument to not display the sidebar. Like this:

#cb.quickLayout(layout="pages",args={sidebar=false})#

whcih calls the CBHelper plugin function quickview

function quickView(required view,cache=false,cacheTimeout,cacheLastAccessTimeout,cacheSuffix,module,args,collection,collectionAs,prepostExempt){

arguments.view = "#layoutName()#/views/#arguments.view#";

return renderView(argumentCollection=arguments);

}

which calls

function layoutName(){

var prc = getRequestCollection(private=true);

return prc.cbLayout;

}

Here is stack trace where it seems there is a an extra slash but I can't determine where.

Fault Action: index
Error Information:
Could not find the included template //layouts/default/layouts/pages.cfm. Note: If you wish to use an absolute template path (for example, template="/mypath/index.cfm") with CFINCLUDE, you must create a mapping for the path using the ColdFusion Administrator. Or, you can use per-application settings to specify mappings specific to this application by specifying a mappings struct to THIS.mappings in Application.cfc.
Using relative paths (for example, template="index.cfm" or template="../index.cfm") does not require the creation of any special mappings. It is therefore recommended that you use relative paths with CFINCLUDE whenever possible.
More Information:
coldfusion.tagext.lang.IncludeTag$NoSuchIncludeTemplateException: Could not find the included template //layouts/default/layouts/pages.cfm. at coldfusion.tagext.lang.IncludeTag.setTemplate(IncludeTag.java:349) at cfRenderer2ecfc730941706$funcRENDERVIEWCOMPOSITE.runFunction(C:\inetpub\wwwroot\evenGame\jonathan\coldbox\system\plugins\Renderer.cfc:335) at coldfusion.runtime.UDFMethod.invoke(UDFMethod.java:472) at coldfusion.filter.SilentFilter.invoke(SilentFilter.java:47) at coldfusion.runtime.UDFMethod$ReturnTypeFilter.invoke(UDFMethod.java:405) at coldfusion.runtime.UDFMethod$ArgumentCollectionFilter.invoke(UDFMethod.java:368) at coldfusion.filter.FunctionAccessFilter.invoke(FunctionAccessFilter.java:55) at coldfusion.runtime.UDFMethod.runFilterChain(UDFMethod.java:321) at coldfusion.runtime.UDFMethod.invoke(UDFMethod.java:220) at coldfusion.runtime.CfJspPage._invokeUDF(CfJspPage.java:2582) at cfRenderer2ecfc730941706$funcRENDERLAYOUT.runFunction(C:\inetpub\wwwroot\evenGame\jonathan\coldbox\system\plugins\Renderer.cfc:460) at coldfusion.runtime.UDFMethod.invoke(UDFMethod.java:472) at coldfusion.filter.SilentFilter.invoke(SilentFilter.java:47) at coldfusion.runtime.UDFMethod$ReturnTypeFilter.invoke(UDFMethod.java:405) at coldfusion.runtime.UDFMethod$ArgumentCollectionFilter.invoke(UDFMethod.java:368) at coldfusion.filter.FunctionAccessFilter.invoke(FunctionAccessFilter.java:55) at coldfusion.runtime.UDFMethod.runFilterChain(UDFMethod.java:321) at coldfusion.runtime.UDFMethod.invoke(UDFMethod.java:517) at coldfusion.runtime.TemplateProxy.invoke(TemplateProxy.java:496) at coldfusion.runtime.TemplateProxy.invoke(TemplateProxy.java:355) at coldfusion.runtime.CfJspPage._invoke(CfJspPage.java:2301) at cfFrameworkSupertype2ecfc449160194$funcRENDERLAYOUT.runFunction(C:\inetpub\wwwroot\evenGame\jonathan\coldbox\system\FrameworkSupertype.cfc:186) at coldfusion.runtime.UDFMethod.invoke(UDFMethod.java:472) at coldfusion.filter.SilentFilter.invoke(SilentFilter.java:47) at coldfusion.runtime.UDFMethod$ReturnTypeFilter.invoke(UDFMethod.java:405) at coldfusion.runtime.UDFMethod$ArgumentCollectionFilter.invoke(UDFMethod.java:368) at coldfusion.filter.FunctionAccessFilter.invoke(FunctionAccessFilter.java:55) at coldfusion.runtime.UDFMethod.runFilterChain(UDFMethod.java:321) at coldfusion.runtime.UDFMethod.invoke(UDFMethod.java:517) at coldfusion.runtime.CfJspPage._invokeUDF(CfJspPage.java:2547) at cfCBHelper2ecfc1921862092$funcQUICKLAYOUT.runFunction(C:\inetpub\wwwroot\evenGame\jonathan\modules\contentbox\plugins\CBHelper.cfc:835) at coldfusion.runtime.UDFMethod.invoke(UDFMethod.java:472) at coldfusion.runtime.UDFMethod$ArgumentCollectionFilter.invoke(UDFMethod.java:368) at coldfusion.filter.FunctionAccessFilter.invoke(FunctionAccessFilter.java:55) at coldfusion.runtime.UDFMethod.runFilterChain(UDFMethod.java:321) at coldfusion.runtime.UDFMethod.invoke(UDFMethod.java:517) at coldfusion.runtime.TemplateProxy.invoke(TemplateProxy.java:496) at coldfusion.runtime.TemplateProxy.invoke(TemplateProxy.java:355) at coldfusion.runtime.CfJspPage._invoke(CfJspPage.java:2301) at cfpageNoSidebar2ecfm1912654097.runPage(C:\inetpub\wwwroot\evenGame\jonathan\modules\contentbox\layouts\default\layouts\pageNoSidebar.cfm:27) at coldfusion.runtime.CfJspPage.invoke(CfJspPage.java:231) at coldfusion.tagext.lang.IncludeTag.doStartTag(IncludeTag.java:416) at coldfusion.runtime.CfJspPage._emptyTcfTag(CfJspPage.java:2722) at cfRenderer2ecfc730941706$funcRENDERVIEWCOMPOSITE.runFunction(C:\inetpub\wwwroot\evenGame\jonathan\coldbox\system\plugins\Renderer.cfc:335) at coldfusion.runtime.UDFMethod.invoke(UDFMethod.java:472) at coldfusion.filter.SilentFilter.invoke(SilentFilter.java:47) at coldfusion.runtime.UDFMethod$ReturnTypeFilter.invoke(UDFMethod.java:405) at coldfusion.runtime.UDFMethod$ArgumentCollectionFilter.invoke(UDFMethod.java:368) at coldfusion.filter.FunctionAccessFilter.invoke(FunctionAccessFilter.java:55) at coldfusion.runtime.UDFMethod.runFilterChain(UDFMethod.java:321) at coldfusion.runtime.UDFMethod.invoke(UDFMethod.java:220) at coldfusion.runtime.CfJspPage._invokeUDF(CfJspPage.java:2582) at cfRenderer2ecfc730941706$funcRENDERLAYOUT.runFunction(C:\inetpub\wwwroot\evenGame\jonathan\coldbox\system\plugins\Renderer.cfc:460) at coldfusion.runtime.UDFMethod.invoke(UDFMethod.java:472) at coldfusion.filter.SilentFilter.invoke(SilentFilter.java:47) at coldfusion.runtime.UDFMethod$ReturnTypeFilter.invoke(UDFMethod.java:405) at coldfusion.runtime.UDFMethod$ArgumentCollectionFilter.invoke(UDFMethod.java:368) at coldfusion.filter.FunctionAccessFilter.invoke(FunctionAccessFilter.java:55) at coldfusion.runtime.UDFMethod.runFilterChain(UDFMethod.java:321) at coldfusion.runtime.UDFMethod.invoke(UDFMethod.java:517) at coldfusion.runtime.TemplateProxy.invoke(TemplateProxy.java:496) at coldfusion.runtime.TemplateProxy.invoke(TemplateProxy.java:355) at coldfusion.runtime.CfJspPage._invoke(CfJspPage.java:2301) at cfpage2ecfc1203250773$funcAROUNDINDEX.runFunction(C:\inetpub\wwwroot\evenGame\jonathan\modules\contentbox-ui\handlers\page.cfc:74) at coldfusion.runtime.UDFMethod.invoke(UDFMethod.java:472) at coldfusion.runtime.UDFMethod$ArgumentCollectionFilter.invoke(UDFMethod.java:368) at coldfusion.filter.FunctionAccessFilter.invoke(FunctionAccessFilter.java:55) at coldfusion.runtime.UDFMethod.runFilterChain(UDFMethod.java:321) at coldfusion.runtime.UDFMethod.invoke(UDFMethod.java:517) at coldfusion.runtime.TemplateProxy.invoke(TemplateProxy.java:496) at coldfusion.runtime.TemplateProxy.invoke(TemplateProxy.java:355) at coldfusion.runtime.CfJspPage._invoke(CfJspPage.java:2301) at coldfusion.tagext.lang.InvokeTag.doEndTag(InvokeTag.java:389) at coldfusion.runtime.CfJspPage._emptyTcfTag(CfJspPage.java:2723) at cfController2ecfc642340398$funcINVOKER.runFunction(C:\inetpub\wwwroot\evenGame\jonathan\coldbox\system\web\Controller.cfc:739) at coldfusion.runtime.UDFMethod.invoke(UDFMethod.java:472) at coldfusion.filter.SilentFilter.invoke(SilentFilter.java:47) at coldfusion.runtime.UDFMethod$ReturnTypeFilter.invoke(UDFMethod.java:405) at coldfusion.runtime.UDFMethod$ArgumentCollectionFilter.invoke(UDFMethod.java:368) at coldfusion.filter.FunctionAccessFilter.invoke(FunctionAccessFilter.java:55) at coldfusion.runtime.UDFMethod.runFilterChain(UDFMethod.java:321) at coldfusion.runtime.UDFMethod.invoke(UDFMethod.java:220) at coldfusion.runtime.CfJspPage._invokeUDF(CfJspPage.java:2582) at cfController2ecfc642340398$funcRUNEVENT.runFunction(C:\inetpub\wwwroot\evenGame\jonathan\coldbox\system\web\Controller.cfc:606) at coldfusion.runtime.UDFMethod.invoke(UDFMethod.java:472) at coldfusion.filter.SilentFilter.invoke(SilentFilter.java:47) at coldfusion.runtime.UDFMethod$ReturnTypeFilter.invoke(UDFMethod.java:405) at coldfusion.runtime.UDFMethod$ArgumentCollectionFilter.invoke(UDFMethod.java:368) at coldfusion.filter.FunctionAccessFilter.invoke(FunctionAccessFilter.java:55) at coldfusion.runtime.UDFMethod.runFilterChain(UDFMethod.java:321) at coldfusion.runtime.UDFMethod.invoke(UDFMethod.java:517) at coldfusion.runtime.TemplateProxy.invoke(TemplateProxy.java:496) at coldfusion.runtime.TemplateProxy.invoke(TemplateProxy.java:355) at coldfusion.runtime.CfJspPage._invoke(CfJspPage.java:2301) at cfColdbox2ecfc1447502974$funcPROCESSCOLDBOXREQUEST.runFunction(C:\inetpub\wwwroot\evenGame\jonathan\coldbox\system\Coldbox.cfc:236) at coldfusion.runtime.UDFMethod.invoke(UDFMethod.java:472) at coldfusion.runtime.UDFMethod$ReturnTypeFilter.invoke(UDFMethod.java:405) at coldfusion.runtime.UDFMethod$ArgumentCollectionFilter.invoke(UDFMethod.java:368) at coldfusion.filter.FunctionAccessFilter.invoke(FunctionAccessFilter.java:55) at coldfusion.runtime.UDFMethod.runFilterChain(UDFMethod.java:321) at coldfusion.runtime.UDFMethod.invoke(UDFMethod.java:220) at coldfusion.runtime.TemplateProxy.invoke(TemplateProxy.java:491) at coldfusion.runtime.TemplateProxy.invoke(TemplateProxy.java:337) at coldfusion.runtime.CfJspPage._invoke(CfJspPage.java:2360) at cfApplication2ecfc805610181$funcONREQUESTSTART.runFunction(C:\inetpub\wwwroot\evenGame\jonathan\Application.cfc:100) at coldfusion.runtime.UDFMethod.invoke(UDFMethod.java:472) at coldfusion.runtime.UDFMethod$ReturnTypeFilter.invoke(UDFMethod.java:405) at coldfusion.runtime.UDFMethod$ArgumentCollectionFilter.invoke(UDFMethod.java:368) at coldfusion.filter.FunctionAccessFilter.invoke(FunctionAccessFilter.java:55) at coldfusion.runtime.UDFMethod.runFilterChain(UDFMethod.java:321) at coldfusion.runtime.UDFMethod.invoke(UDFMethod.java:220) at coldfusion.runtime.TemplateProxy.invoke(TemplateProxy.java:491) at coldfusion.runtime.TemplateProxy.invoke(TemplateProxy.java:337) at coldfusion.runtime.AppEventInvoker.invoke(AppEventInvoker.java:88) at coldfusion.runtime.AppEventInvoker.onRequestStart(AppEventInvoker.java:258) at coldfusion.filter.ApplicationFilter.invoke(ApplicationFilter.java:349) at coldfusion.filter.RequestMonitorFilter.invoke(RequestMonitorFilter.java:48) at coldfusion.filter.MonitoringFilter.invoke(MonitoringFilter.java:40) at coldfusion.filter.PathFilter.invoke(PathFilter.java:94) at coldfusion.filter.ExceptionFilter.invoke(ExceptionFilter.java:70) at coldfusion.filter.ClientScopePersistenceFilter.invoke(ClientScopePersistenceFilter.java:28) at coldfusion.filter.BrowserFilter.invoke(BrowserFilter.java:38) at coldfusion.filter.NoCacheFilter.invoke(NoCacheFilter.java:46) at coldfusion.filter.GlobalsFilter.invoke(GlobalsFilter.java:38) at coldfusion.filter.DatasourceFilter.invoke(DatasourceFilter.java:22) at coldfusion.filter.CachingFilter.invoke(CachingFilter.java:62) at coldfusion.CfmServlet.service(CfmServlet.java:200) at coldfusion.bootstrap.BootstrapServlet.service(BootstrapServlet.java:89) at jrun.servlet.FilterChain.doFilter(FilterChain.java:86) at com.intergral.fusionreactor.filter.FusionReactorFilter.c(FusionReactorFilter.java:428) at com.intergral.fusionreactor.filter.FusionReactorFilter.d(FusionReactorFilter.java:262) at com.intergral.fusionreactor.filter.FusionReactorFilter.doFilter(FusionReactorFilter.java:171) at jrun.servlet.FilterChain.doFilter(FilterChain.java:94) at coldfusion.monitor.event.MonitoringServletFilter.doFilter(MonitoringServletFilter.java:42) at coldfusion.bootstrap.BootstrapFilter.doFilter(BootstrapFilter.java:46) at jrun.servlet.FilterChain.doFilter(FilterChain.java:94) at jrun.servlet.FilterChain.service(FilterChain.java:101) at jrun.servlet.ServletInvoker.invoke(ServletInvoker.java:106) at jrun.servlet.JRunInvokerChain.invokeNext(JRunInvokerChain.java:42) at jrun.servlet.JRunRequestDispatcher.invoke(JRunRequestDispatcher.java:286) at jrun.servlet.ServletEngineService.dispatch(ServletEngineService.java:543) at jrun.servlet.jrpp.JRunProxyService.invokeRunnable(JRunProxyService.java:203) at jrunx.scheduler.ThreadPool$ThreadThrottle.invokeRunnable(ThreadPool.java:428) at jrunx.scheduler.WorkerThread.run(WorkerThread.java:66)

 


writing a model in cfscript

ColdBox, ColdFusion

So I've been wanting to rewrite stored procedures from markup to cfscript. I've done it so I'm posting an example for other newbies out there in the world to see.

public query function testFunction(var1,var2)

{

sp = new storedproc();

sp.setDatasource(THIS.DSN);

sp.setProcedure("MyGroovySQLSproc");

sp.addParam(cfsqltype="cf_sql_integer", type="in",value=arguments.var1);

sp.addParam(cfsqltype="cf_sql_integer",type="in",value=arguments.var2);

sp.addProcResult(name="rs1",resultset=1);

result = sp.execute();

return result.GetProcResultSets(1).rs1;

}

That's it. I'm not sure what is the benefit. It seems that they could have made a shorter way of doing this. Maybe that's another post.