Sunday, September 10, 2006

Musing on Dreamweaver and CF

DW is like the Visual Basic for CF. You could code a
whole "coldfusion" site without really needing to know any
CF. Does that make one a CFer? Sure, and that's part
of the aim of CF, actually, but the power that is mixed with
the ease makes for a pretty diverse population, skillz wise.

And the skill groups seem to "clump" sorta, .. eh. Hard to
verbalize that idea...

But anyway it has some kind of effect on the use of things
like CFEclipse or MyEclipse.

Like Dan P says, we don't see much traffic on list about
things like JWS or JSP and whatnot, yet that's a big
chunk of the power of CF, and you know there are plenty
of people doing cool stuff along those lines...
And what about UML tools or j2ee related things and
frameworks and whatnot... guess that's not really
considered "coldfusion"? More sorta "general" or "specific",
with "no real centralized info" or "specialized list-servs and
whatnot".

I think the glass ceiling is switching from "websites" to
"applications". While you're doing "websites", DW is
the bomb. All the nifty tools are really useful, the Visual
editing is essential... since most stuff isn't programmatic,
or stored in a DB, DW plain kicks ass. The best visual
HTML editing tool available, IMHO.

But sooner or later (if you aren't just doing it to sustain
your other habits, as a day job, or whatnot ;) you start
to realize you're spending all your time in the code view.

DW stops being so helpful. You don't have a million files
all over the place anymore... CF and the site users are
doing the content work, not you and DW. You have a
couple of CSS files, and your HTML is pretty plain looking.
When you change stuff, it's the CSS files/CF functions,
not a DW template or library item.
Suddenly you're developing "web applications" instead of
"web sites" (the term website is pretty general, I know).
Unit tests and frameworks start sounding pretty good.
Automated deployment and continuous integration? Sign
me up!

That's where tools like Eclipse come into play. If you're
not doing all that, Dreamweaver is King. It's still good
for times where you need it, but I've found that I dislike
needing it. There is no reason for the markup to be so
convoluted you need to switch to "design" view just to
visualize it.

To sum up, Dreamweaver is a great tool, top notch in
it's field, which is the HTML IDE field.

The bias, is that I think CF is far more than HTML, and
thus, diserves far more than an HTML IDE. I know DW
can do CF, and PHP, etc., I just think it's geared more
toward WYSIWYG/HTML, than programing.

Bah. Other's have blogged better on this topic. I just
wanted to put down some thoughts, as it's cool to see
how one thunk and wrote, looking back. And maybe
someone will have a comment that gives me pause.

Life is cool like that. Kool, even.

Monday, September 04, 2006

Paginated Scaffold List XSL

<?xml version="1.0" encoding="UTF-8"?>

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
   <xsl:output method="text" indent="no" />

   <xsl:template match="/">
&lt;cfset viewEvent = viewstate.getValue("myself") &amp; viewstate.getValue("xe.view") /&gt;
&lt;cfset editEvent = viewstate.getValue("myself") &amp; viewstate.getValue("xe.edit") /&gt;
&lt;cfset deleteEvent = viewstate.getValue("myself") &amp; viewstate.getValue("xe.delete") /&gt;
&lt;cfparam name="url.maxrows" default="15" /&gt;
&lt;cfparam name="url.startrow" default="1" /&gt;

&lt;cfset <xsl:value-of select="object/alias"/>Query = viewstate.getValue("<xsl:value-of select="object/alias"/>Query") /&gt;
&lt;cfset variables.prvstart = max(1, url.startrow - url.maxrows) /&gt;
&lt;cfset variables.nextstart = min(<xsl:value-of select="object/alias"/>Query.recordcount, url.startrow + url.maxrows) /&gt;
&lt;cfset variables.end = min( <xsl:value-of select="object/alias"/>Query.recordcount, (url.startrow + url.maxrows) - 1 ) /&gt;


&lt;cfoutput&gt;
&lt;div id="breadcrumb"&gt;<xsl:value-of select="object/label"/>s / &lt;a href="#editEvent#"&gt;Add New <xsl:value-of select="object/label"/>&lt;/a&gt;&lt;/div&gt;
&lt;/cfoutput&gt;
&lt;br /&gt;
&lt;table class="list"&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;cfset displayedColumns = 1 /&gt;
<xsl:for-each select="object/properties/property">
<xsl:if test="primarykey = 'false' and relationship='false' and length &lt; 65535">
       &lt;cfset displayedColumns = displayedColumns + 1 /&gt;
          &lt;th&gt;<xsl:value-of select="label"/>&lt;/th&gt;
</xsl:if>
<!--
      <xsl:if test="relationship = 'true' and pluralrelationship = 'false' and length &lt; 65535">
       &lt;cfset displayedColumns = displayedColumns + 1 /&gt;
         &lt;th&gt;<xsl:value-of select="label"/>&lt;/th&gt;
      </xsl:if>
-->
</xsl:for-each>
   &lt;th&gt;&amp;nbsp;&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;cfif not <xsl:value-of select="object/alias"/>Query.recordcount&gt;
   &lt;tr&gt;
      &lt;cfoutput&gt;&lt;td colspan="#displayedColumns#"&gt;&lt;em&gt;No Records&lt;/em&gt;&lt;/td&gt;&lt;/cfoutput&gt;
   &lt;/tr&gt;
&lt;cfelseif <xsl:value-of select="object/alias"/>Query.recordcount gt 1&gt;
   &lt;cfoutput&gt;
      &lt;cfif url.startrow neq 1&gt;
         &lt;a href="#viewstate.getValue("myself")&amp;viewstate.getValue("event")#&amp;startrow=#urlEncodedFormat(variables.prvstart, "UTF-8")#"&gt;previous #url.maxrows#&lt;/a&gt; /
      &lt;/cfif&gt;
          #url.startrow# - #variables.end# of #<xsl:value-of select="object/alias"/>Query.recordcount#
      &lt;cfif variables.nextstart lt <xsl:value-of select="object/alias"/>Query.recordcount&gt;
         / &lt;a href="#viewstate.getValue("myself")&amp;viewstate.getValue("event")#&amp;startrow=#urlEncodedFormat(variables.nextstart, "UTF-8")#"&gt;next #url.maxrows#&lt;/a&gt;
      &lt;/cfif&gt;
   &lt;/cfoutput&gt;      
&lt;/cfif>

&lt;cfoutput query="<xsl:value-of select="object/alias"/>Query" startrow="#url.startrow#" maxrows="#url.maxrows#"&gt;
   &lt;cfset keyString = "<xsl:for-each select="object/properties/property"><xsl:if test="primarykey = 'true'">&amp;<xsl:value-of select="alias"/>=#urlEncodedFormat(<xsl:value-of select="/object/alias"/>Query.<xsl:value-of select="alias"/>)#</xsl:if></xsl:for-each>" />
   &lt;tr &lt;cfif <xsl:value-of select="object/alias"/>Query.currentRow mod 2 eq 0&gt;class="even"&lt;/cfif&gt;&gt;
<xsl:for-each select="object/properties/property">
         <xsl:if test="primarykey = 'false' and relationship='false' and length &lt; 65535">
            <xsl:if test="cfdatatype = 'date'">
                &lt;td&gt;&lt;a href="#viewEvent##keystring#"&gt;#dateFormat(<xsl:value-of select="alias"/>, "m/d/yyyy")# #timeFormat(<xsl:value-of select="alias"/>, "h:mm TT")#&lt;/a&gt;&lt;/td&gt;
            </xsl:if>
            <xsl:if test="cfdatatype != 'date'">
                &lt;td&gt;&lt;a href="#viewEvent##keystring#"&gt;#htmlEditFormat(<xsl:value-of select="alias"/>)#&lt;/a&gt;&lt;/td&gt;
            </xsl:if>
                        
            
</xsl:if>
<!--
<xsl:if test="relationship = 'true' and pluralrelationship = 'false' and length &lt; 65535">
            <xsl:if test="cfdatatype = 'date'">
             &lt;td&gt;&lt;a href="#viewEvent##keystring#"&gt;#dateFormat(<xsl:value-of select="sourceobject"/>_<xsl:value-of select="sourcecolumn"/>, "m/d/yyyy")# #timeFormat(<xsl:value-of select="sourceobject"/>_<xsl:value-of select="sourcecolumn"/>, "h:mm TT")#&lt;/a&gt;&lt;/td&gt;
            </xsl:if>
            <xsl:if test="cfdatatype != 'date'">
             &lt;td&gt;&lt;a href="#viewEvent##keystring#"&gt;#htmlEditFormat(<xsl:value-of select="sourceobject"/>_<xsl:value-of select="sourcecolumn"/>)#&lt;/a&gt;&lt;/td&gt;
            </xsl:if>
            
         </xsl:if>                     
-->
</xsl:for-each>
      &lt;td&gt;
         &lt;a href="#editEvent##keystring#"&gt;Edit&lt;/a&gt;   
         &lt;a href="##" onclick="if (confirm('Are you sure you want to delete this <xsl:value-of select="/object/label"/>?')) { document.location.replace('#deleteEvent##keystring#') }; return false"&gt;Delete&lt;/a&gt;
      &lt;/td&gt;
   &lt;/tr&gt;
&lt;/cfoutput&gt;
&lt;/tbody&gt;
&lt;/table&gt;

   </xsl:template>
</xsl:stylesheet>

Sunday, August 27, 2006

Creating "real" Excel files using JExcelAPI and ColdFusion


You probably know that most MS products will import HTML files, so long as you change the extension, e.g.: you create a HTML table and save it as a .XLS to have it open right in Excel. Or save the HTML as a .PPT and it will apparently open in PowerPoint. Stuff like that.

Or maybe you've played with the XML importing/exporting functions that are built into the latest versions of the Office line... wow, nifty- but maybe impractical for various reasons. You may need to support older versions of Excel, say, or just don't like the sheer volume of XML data and wankery that's needed to do so.

There is also Apache POI HSSF, which generates true Excel files, and I believe comes bundled with CFMX. A great alternative to the other two methods I've spoken of so far, but a pretty cumbersom API (POI does lots of stuff besides Excel). And I've heard that JExcelApi performs better on large spreadsheets, but I have not done any tests to verify. I think I heard formula handling was easier with JExcel, too... been a while since I checked out HSSF, so who knows.

Well, if you still want to try JExcelApi after checking out the other methods, you'll need to get the jxl.jar file from the project page of jexcelapi.sourceforge.com. Then you'll want to either drop it in your CF class-path, or use a JavaLoader, so CF can use it.

Once you've got that far, (and I can post an example using the javaloader, eventually) you can start making workbooks!

I really should post just a sample of how to do it plain, but I was working, so that sample is long since gone. Instead I've got a cffunction that will take a struct of queries, and generate sheets for each one in an excel workbook, which is then directly piped to the browser (I hate saving the file just to send it out and then delete anyway).
While this is good for people who don't care about the internals, it's not a very helpful learning example, as it uses a query. KISS, is better for explaining. Oh well, maybe later.

This code could be coded bad too... it works, which is generally what I care about, but I don't know if I'm var-ing stuff right or if reusing the same objects is kosher or whatever. Just to put it out there... I'm not a code guru. If it hurts your dog it's not my fault, blah blah blah
Bah. Here it is:

<cffunction name="queriesToXLS" output="false" hint="I create a XLS workbook from a struct of queries">
<cfargument name="inQueries" type="struct" required="true">
<cfscript>
var outStream = createObject("java","java.io.ByteArrayOutputStream").init();
var workbook = createObject("java","jxl.Workbook");
var ws = createObject("java","jxl.WorkbookSettings").init();
var locale=createObject("java","java.util.Locale").init("en","EN");
var labelObj=createObject("java","jxl.write.Label");
var numberObj=createObject("java","jxl.write.Number");
ws.setLocale(locale);
workbook = workbook.createWorkbook(outStream,ws);
for (i = 1; i lte structCount(inQueries); i = i + 1) {
sheetName = structKeyList(inQueries[i]);
sheetQuery = inQueries[i][structKeyList(inQueries[i])];
sheet = workBook.createSheet(sheetname, toString(i-1));
// ouput column headers
for (qCol = 1; qCol lte listLen(sheetQuery.columnlist); qCol = qCol + 1) {
thisLabel = labelObj.init(toString(val(qCol)-1),0,listGetAt(sheetQuery.columnlist,qCol));
sheet.addCell(thisLabel);
}
for (qRow = 1; qRow lte sheetQuery.recordcount; qRow = qRow + 1) {
for (qCol = 1; qCol lte listLen(sheetQuery.columnlist); qCol = qCol + 1) {
thisLabel = labelObj.init(toString(val(qCol)-1),toString(qRow),toString(sheetQuery[listGetAt(sheetQuery.columnlist,qCol)][qRow]));
sheet.addCell(thisLabel);
}
}
}
workbook.write();
outStream.flush();
workbook.close();
return outStream.toByteArray();
</cfscript>
</cffunction>


Then you'd use it like so:

<cfsetting enablecfoutputonly="true">
<cfquery name="getInfo" datasource="#request.thedsn#">
SELECT * FROM folks
</cfquery>
<cfquery name="getInfo2" datasource="#request.thedsn#">
SELECT * FROM peeps
</cfquery>
<cfset workbookSheets = structNew()>
<cfset workbookSheets[1]["Folks"] = getInfo>
<cfset workbookSheets[2]["Peeps"] = getInfo2>
<cfset daXls = queriesToXLS(workbookSheets)>
<cfset request.cvalue = 'attachment;'>
<cfset request.cvalue = request.cvalue & "filename=test.xls">
<cfheader name="Content-Disposition" value="#request.cvalue#">
<cfoutput><cfcontent type="application/msexcel" variable="#daXls#"></cfoutput>


And that should work. Sorry for lack of comments, etc. I'm a baaaaad coder!