New to this and a little ignorant, but need help

[dgotti007]

I am interested in finding out how I can build a website using Coldfusion and an excel spreadsheet.

I used to work for a company that did this and i am now looking to see how to do it.

No geekspeak here:
I know that the Coldfusion would take the header and footer and combine it with the information from the spreadsheet. I was also told that each page was "made on the fly". The site this was done on is no longer around.

Please forgive my ignorance when it comes to this, but any help is greatly appreciated.

[arrgh]

I am interested in finding out how I can build a website using Coldfusion and an excel spreadsheet.

I used to work for a company that did this and i am now looking to see how to do it.

No geekspeak here:
I know that the Coldfusion would take the header and footer and combine it with the information from the spreadsheet. I was also told that each page was "made on the fly". The site this was done on is no longer around.

Please forgive my ignorance when it comes to this, but any help is greatly appreciated.

There are some variables here like do you mean an real .xls file or something compatible like a .csv (comma separated) or .txt (tab delimited) file?

But generally speaking:
Within ColdFusion you can read information from an Excel spreadsheet (or delimited file). Then use that use (or display) that information in a ColdFusion page. Can you give a concrete example of what you need to do. It does not need to be technical, just an example like:

I have a .xls spreadsheet containing two columns. I would like to display that information on a ColdFusion page, along with a company header and footer:

Example of the Excel file contents:
===================
Customer Name, Address
B. Smith, 84 Sycamore Street
....

That said, using Excel as a "data source" is not always the best option. It is not a real database .. so there are quirks. Usually is usually better to store information in a real database like MySQL, MS SQL, etcetera.

[dgotti007]

There are some variables here like do you mean an real .xls file or something compatible like a .csv (comma separated) or .txt (tab delimited) file?

But generally speaking:
Within ColdFusion you can read information from an Excel spreadsheet (or delimited file). Then use that use (or display) that information in a ColdFusion page. Can you give a concrete example of what you need to do. It does not need to be technical, just an example like:

I have a .xls spreadsheet containing two columns. I would like to display that information on a ColdFusion page, along with a company header and footer:

Example of the Excel file contents:
===================
Customer Name, Address
B. Smith, 84 Sycamore Street
....

That said, using Excel as a "data source" is not always the best option. It is not a real database .. so there are quirks. Usually is usually better to store information in a real database like MySQL, MS SQL, etcetera.

I am referring to real .xls file.

Your example of “I have a .xls spreadsheet containing two columns. I would like to display that information on a ColdFusion page, along with a company header and footer:” is perfect but the amount of columns will be closer to ten.

Basically the spread sheet would have product information broken apart categories: price, name, description, weight, dimensions, etc.

I know that using excel is not the best but right now I do not the option of using a server or MySQL to house this information.

This is a very basic start up project.

[arrgh]

The basics of what you need to do are not difficult The only tricky part is extracting the data from excel. But let's take a single file as an example. (I will assume you are using at least MX7)

In general terms there would be a few pieces:

1) Obviously the excel files would need to be copied to your server. Either manually or you could provide an "upload" form to let users upload excel files through a web page. But that all depends on your needs...

2) You would need to create two simple files for your header and footer. Say:

- companyHeader.cfm
- companyFooter.cfm

You could then include (ie display) those files on every page by using an Application.cfc file. It is a special file that runs every time a .cfm page is requested. It contains functions you can use for exactly this purpose.

3) You would need to create a page that would read in the excel file data and store the information in a "query" object. Then you could display it quite simply by using cfoutput.

<cfoutput query="yourQuery">
<!--- columns in the excel file --->
#product# #category#, ... etc...
</cfoutput>

There are a few options for reading information from excel files. From best to worst:

1) You can use a java tool like jExcel or POI to read the excel file
PROS: It is less buggy than other methods.
CONS: It is more complicated. However, there are a number of opensource utilities that greatly simplify things (ie hide the complexity)

For example this utility can convert the data into a query object transparently. So it is very simple to use:
http://www.bennadel.com/projects/poi-utility.htm

2) You could create an ODBC datasource in the CF Administrator to read information from an Excel
PROS: Simpler than the java options
CONS: It is more buggy when retrieving certain value types. It is also less flexible

3) You could also use COM to read the excel file
CONS: It is an old technology and is not recommended for use on a server.

There are also other options if you were using an MS database. Bear in mind, there is still not a lot of support for the newer excel format 2007. So if that is what you are using, you may have problems.

HTH

[arrgh]

So did you figure it out?

[dtlease]

I know i wasn't involved in the beginning of this thread but i am encountering a similar issue in attempting to get data from an excel file. I'm trying to choose the best solution but here are the issue's i'm having with your three recommendations:

1. POI utility- i've attempted to use several but either i'm retarded (most likely) or they are all confusing as hell. Additionally, i'm using a server that does not allow me to use the coldfusion function createObject and most of the existing POIs use that.
2. ODBC - that works if it's always the same excel file and you can make that declaration in Coldfusion administrator but what if your uploading from a different file every time?
3. I have no idea what COM is... please explain.

Any help you can provide would be much appreciated.

Thanks,

-David

[arrgh]

Well .. jExcel, POI, etc... are the better options (IMO). BUT they are definitely more complicated. Of course if you are not allowed to use createObject, you'll have to forget about those options, and COM as well. All of them require createObject. There is no way around that.

(By the way, COM is an old technology and is really not well suited for server use. But there are some good, albeit old, examples on this site)
http://cfregex.com/cfcomet/Excel/index.cfm?ArticleID=0239B2E5-5FED-11D3-B3E9004033E03EF9

Anyway, that basically leaves you with ODBC. It has been a while since I've used an Excel ODBC datasource. But I _think_ you can use a single datasource to read multiple files. Either with an Excel or Access ODBC Datasource.

IIRC, one or both of those datasource types will accept a file path in the query SQL. So you should be able read any excel file, as long as the CF account has permission to access it.

I can't recall the exact syntax, but something along these lines.

<cfquery ... >
SELECT *
FROM [Sheet1$]
IN 'c:\someFolder\yourFile.xls' 'EXCEL 8.0;'
</cfquery>

[jedimatt]

What about converting the Excel file to xml and then reading that in instead of the native xls format?

Just a thought.

JM

[arrgh]

Yes, that would be fine for a single file. But probably not an option if they need to work with many/dynamic files.