Tim Dempsey asked:
> How do I take that data and get it into Excel on the client's machine?
We had to do something like this last month (with Doughnut charts instead of
Pie charts, but it's close enough). I never could get it to work 100% in
CF, as Excel wouldn't honor my Quit() command and would just stay open,
leaving lots of little zombies all over the place. However, on a whim I
translated it into Perl and got it to work that way. (And, oddly enough,
the Perl version is *much* easier on the eyes, for once.)
Anyway, I apologize for the minimal documentation, but this was really just
a quick hack for me. YMMV and all that. If you ever figure out the Quit()
problem, let me know. I'd love to know what I'm doing wrong. Also, if you
want the (working) Perl version, let me know and I'll send it via separate
channels (as I figured I'd get flamed 'til the end of time if I posted Perl
to this list). It's much smarter and does things like palette conversion
(so that you aren't stuck with Excel's default colors), and other neat-o
tricks.
-R
<CFSETTING ENABLECFOUTPUTONLY="YES" SHOWDEBUGOUTPUT="No">
<!---
graph/doughnut.cfm
--->
<!---
Constants
--->
<CFSET xlWorksheet=-4167>
<CFINCLUDE TEMPLATE="../../include/_ext2attr.cfm">
<CFPARAM NAME="Attributes.Height" DEFAULT="200">
<CFPARAM NAME="Attributes.Width" DEFAULT="200">
<CFPARAM NAME="Attributes.Rows" DEFAULT="0">
<CFPARAM NAME="Attributes.Cols" DEFAULT="0">
<CFPARAM NAME="Attributes.Title" DEFAULT="">
<CFPARAM NAME="Attributes.Legend" DEFAULT="0">
<CFPARAM NAME="Attributes.Type" DEFAULT="gif">
<CFSET Attributes.Height=Attributes.Height*0.749999999>
<CFSET Attributes.Width=Attributes.Width*0.749999999>
<CFSET FileName="">
<CFTRY>
<CFTRY>
<CFOBJECT ACTION="CONNECT" TYPE="COM" CLASS="Excel.Application"
NAME="App">
<CFCATCH>
<CFOBJECT ACTION="CREATE" TYPE="COM" CLASS="Excel.Application"
NAME="App">
</CFCATCH>
</CFTRY>
<CFSET Workbooks=App.Workbooks>
<CFSET Workbook=Workbooks.Add(xlWorksheet)>
<CFLOOP COLLECTION="#Workbook.WorkSheets#" ITEM="Sheet">
<CFLOOP FROM="1" TO="#Attributes.Rows#" INDEX="Row">
<CFLOOP FROM="1" TO="#Attributes.Cols#" INDEX="Col">
<CFSET ColLetter=Chr(Col + Asc('A') - 1)>
<CFSET CellName="vr#Row#c#Col#">
<CFIF StructKeyExists(Attributes,CellName)>
<CFSET Range=Sheet.Range("#ColLetter##Row#")>
<CFSET Range.Value=Attributes[CellName]>
</CFIF>
</CFLOOP>
</CFLOOP>
<CFSET ColLetter=Chr(Attributes.Cols + Asc('A') - 1)>
<CFSET SourceRange=Sheet.Range("A1:#ColLetter##Attributes.Rows#")>
<CFSET ChartObjects=Sheet.ChartObjects()>
<CFSET Chart=ChartObjects.Add(0,0,Attributes.Width,Attributes.Height)>
<CFSET Donut=Chart.Chart>
<CFSET Donut.ChartWizard(SourceRange, -4120, 1, 2, 1, 0)>
<CFSET Series=0>
<CFLOOP FROM="1" TO="#DecrementValue(Attributes.Cols)#" INDEX="Series">
<CFSET Ser=Donut.SeriesCollection(Series)>
<CFSET Ser.HasDataLabels=0>
<CFLOOP FROM="1" TO="#Attributes.Rows#" INDEX="Row">
<CFSET Cellname="c_#Series#_#Row#">
<CFIF StructKeyExists(Attributes,CellName)>
<CFSET CellColor=Attributes[CellName]>
<CFOUTPUT><P>Resetting color #CellColor#</P></CFOUTPUT>
<CFIF Left(CellColor,1) IS "##">
<CFSET CellColor=InputBaseN(Mid(CellColor,6,2) &
Mid(CellColor,4,2) & Mid(CellColor,2,2),16)>
</CFIF>
<CFSET Point=Ser.Points(Row)>
<CFSET Interior=Point.Interior>
<CFSET Interior.Color=CellColor>
</CFIF>
</CFLOOP>
</CFLOOP>
<CFIF Attributes.Title IS "">
<CFSET Donut.HasTitle=0>
<CFSET PlotArea=Donut.PlotArea>
<CFSET PlotArea.Top=0>
<CFSET PlotArea.Left=0>
<CFSET PlotArea.Height=Attributes.Height-8>
<CFSET PlotArea.Width=Attributes.Width-8>
<CFELSE>
<CFSET Donut.HasTitle=1>
<CFSET ChartTitle=Donut.ChartTitle>
<CFSET ChartTitle.Caption=Attributes.Title>
</CFIF>
<CFIF Attributes.Legend>
<CFSET Donut.HasLegend=1>
<CFELSE>
<CFSET Donut.HasLegend=0>
</CFIF>
<CFSET Border=Chart.Border>
<CFSET Border.LineStyle=-4142>
<CFSET FileName="c:\temp\Doughnut#CreateUUID()#.#Attributes.Type#">
<CFSET Donut.Export(FileName,Attributes.Type,0)>
<CFSET Donut.Delete()>
</CFLOOP>
<CFCATCH TYPE="Any">
<CFOUTPUT>
<H1>Error</H1>
<P>#CFCATCH.Message#</P>
<BLOCKQUOTE>#CFCATCH.Detail#</BLOCKQUOTE>
</CFOUTPUT>
</CFCATCH>
</CFTRY>
<CFSET WorkBook.Saved=-1>
<CFSET App.Quit()>
<CFSET App="">
<CFIF FileName IS NOT "">
<CFCONTENT TYPE="image/#Attributes.Type#" FILE="#FileName#"
DELETEFILE="Yes">
</CFIF>
<CFSETTING ENABLECFOUTPUTONLY="NO">
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
