I have done more or less the same thing with my customers. It's a lot less
taxing on the server to create the spreadsheet from HTML/CSV and have it run
on the client than it is to manipulate it via an MS Office object on the
server, but you end up doing both. I create the look I want for my Excel
spreadsheet in HTML and save it as an HTML file then open it up in an Excel
object on the server:
<!--- Try to connect to the Excel object --->
<CFINCLUDE TEMPLATE="/_PM/payroll/com_get_excel_object.cfm">
<!--- Try to open the HTML file --->
<CFINCLUDE TEMPLATE="/_PM/payroll/com_open_excel_file.cfm">
<CFTRY>
<CFSCRIPT>
/* Get the first sheet */
objWorkSheet = objWorkSheets.Item(Val(1));
objWorkSheet.Name = SheetName;
</CFSCRIPT>
<CFCATCH TYPE="ANY">
<CFINCLUDE
TEMPLATE="/_PM/payroll/com_close_and_quit_excel.cfm">
<CFABORT
SHOWERROR="Error occured while trying to get the
active worksheet! Error
Details: #CFCATCH.MESSAGE# #CFCATCH.DETAIL#"
>
</CFCATCH>
</CFTRY>
Then I just save it as an Excel spreadsheet:
<CFTRY>
<CFSCRIPT>
/* Save the file */
objWorkSheet.SaveAs(ExcelFilePath, Val(1));
</CFSCRIPT>
<CFCATCH TYPE="ANY">
<CFINCLUDE
TEMPLATE="/_PM/payroll/com_close_and_quit_excel.cfm">
<CFABORT
SHOWERROR="Error occured while trying to save the HTML
as an Excel file!
Error Details: #CFCATCH.MESSAGE# #CFCATCH.DETAIL#"
>
</CFCATCH>
</CFTRY>
When the user is done filling in the workbook, they upload it via a form and
then I query the sheet:
<CFFILE
ACTION="UPLOAD"
FILEFIELD="Excel_Spreadsheet_Path"
DESTINATION="#ExcelFilePath#"
NAMECONFLICT="OVERWRITE"
ACCEPT="application/vnd.ms-excel"
>
<CFSCRIPT>
ExcelODBCConnectionString = "Driver={Microsoft Excel Driver
(*.xls)};DriverId=790;Dbq=" & ExcelFilePath & ";DefaultDir=" &
GetDirectoryFromPath(ExcelFilePath) & ";";
</CFSCRIPT>
<CFQUERY
NAME="GetDataSheet"
DBTYPE="dynamic"
BLOCKFACTOR="100"
CONNECTSTRING="#ExcelODBCConnectionString#"
>
SELECT *
FROM "#SheetName#$"
WHERE (Users_ID IS NOT NULL)
</CFQUERY>
It took a long time to figure out how to read the complete set of an
arbitrary number of columns and associate it with the correct row. Also,
validation is a real pain. I'm still working on vbscript validation that I
can convert from HTML, but haven't figured out how to do it yet.
-----Original Message-----
From: CF-Talk [mailto:[EMAIL PROTECTED]]
Sent: Saturday, December 28, 2002 10:34 PM
To: [EMAIL PROTECTED]
Date: Sat, 28 Dec 2002 22:00:48 +0100
From: [EMAIL PROTECTED]
Subject: excel as a vehicle to collect and write back data to a CF-App
Message-ID: <[EMAIL PROTECTED]>
I have a customer,
who runs an application,
where he can show, who
is a a promotor or a consultant
for certain products he is selling.
The application runs on: CF 4.5, Win NT 4 Server
with IIS 4 and MS-Access as a backend.
Updating and deleting data works on
a user-data-record base via web-frontend. Now the
customer wants to deliver certain data, that
has to be edited by certain responsible users.
He wants to deliver the lists, the people should
use in excel. What he wants is, that he collects
all the excel-lists and rewrite the changed records he got
back from the excel-lists and transferring them (via CF) to MS-Access.
Some aspects how to do this are clear, but can somebody
give me some advice on what the pitfalls are on scripting those templates,
maybe
somebody who has done more or less the same ?
Would be great.
Thanks.
Uwe
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Your ad could be here. Monies from ads go to support these lists and provide more
resources for the community. http://www.fusionauthority.com/ads.cfm
