Generating Excel Reports - Server Side

[ApexSys]

I'm needing to generate a weekly report in Excel. The data will be coming from a CF/Access application. Each report will be generated from the same update/insert query but will be scheduled to get a new name at the end of each week.
The names will represent the prefix, date and instance. eg: KPI_02122001_1.xls , translates to "K"ey "P"erformance "I"ndicator #1 generated on Feb. 12, 2001.

The end user is not going to access the files via the web so this means I won't be using CFContent to generate the spreadsheet files. Also, there should not be any user action needed to generate the reports.

My thought is to create datasource assiged to one Excel spreadsheet and then use CFFile and the Scheduler to rename this spreadsheet once a week with the new inserted/updated data.

Does anyone see any locking or other issues with using such an approach? Anyone have a better method?

Is cfschedule a 4.5 or 4.0 tag?

Thanks,
Bill

 

[philhege]

Hi ApexSys,
Use CFFILE to create a .csv that Excel users can open. The Excel driver doesn't like INSERTs or UPDATEs.

Although it's a .csv, you should delimit the columns with tabs (#chr(9)#) and end with carriage returns (#chr(13)#).

I do this all the time; works well.

Let us know if you hit any snags. I'm not going to touch the CFSCHEDULE portion; I suggest using AT if you're in the NT world.

philhege

 

[GunJack]

If you're going to schedule it and no one else will be running it manually, you shouldn't have a problem with locking conflicts. I would wrap a <cflock> around the entire script just in case though and do proper error checking with fileexists() before renaming/creating files, etc...

Cfschedule is available in 4.0 although I always schedule tasks through the admin. I can't comment on any problems with it but I've rarely had a problem with the scheduling engine through the admin page.

GJ