Current Version: 1.2 - 05/04/2006
Transform a Microsoft Excel file into a ColdFusion query without installing
Excel on your server! This tag is written in 100% java, so there is no need
for the dreaded COM.
With CFX_Excel2Query you can read all or part of a Microsoft Excel ® file
into a native ColdFusion query. You can extract data from any workbook in
all rows and cells, or just the rows and cells that you need.
As of version 1.2, if your Excel file contains formulas, CFX_Excel2Query can
evaluate the formulas at runtime to give you the most accurate representation
of your Excel files possible.
If you bundle CFX_Query2Excel with CFX_Excel2Query you will have a complete
solution to handle all of your Excel integration needs with the flexibility of two
separate tags. Imagine being able to take an unformatted data file in Excel from
the user and return it fully formatted with your company's logo and tagline.
The possiblities are endless.
New! Shared hosting support!
Now you can use CFX_Excel2Query in a shared hosting envirnoment without needing
to register the CFX with the server! The only requirement is that your host
allows you to run the createObject("java","...") function! To download the shared
hosting interface, click here. Note, you
will need to purchase a license below in order to use this interface.
|
Click the "Buy Now" button below to purchase a server license for CFX_Excel2Query.
If you purchase the Excel Suite you'll get both CFX_Excel2Query
as well as CFX_Query2Excel and SAVE 20%!
You must purchase one license for each ColdFusion instance with which this tag will be
registered. You can purchase multiple licenses by specifying the quantity during checkout.
If you purchase the Excel Suite, you must purchase one license for each ColdFusion instance
with which either CFX_Excel2Query or CFX_Query2Excel will be registered. That is, a single license
covers both tags on one server. If the tags are installed separate servers, a license is required
for each server.
Be sure to download the trial version to ensure
compatibility with your Excel files. We will not issue a refund due to file incompatibility.
NOTE: The trial version will only return the first 10 rows.
Additionally, I have had many inquires regarding the source. I have made the source available for purchase.
No support will be provided.
|
| Product |
Price |
License |
|
| CFX_Excel2Query |
$29.99 |
Per Server Instance |
|
| Excel Suite |
$49.99
(SAVE 20%) |
Per Server Instance |
|
| Excel Suite w/ Full Source |
$399.99
|
Per Server Instance |
|
| Excel2Query Trial |
Free |
- |
Download
|
| Shared Hosting |
Free |
- |
Download
|
<cfx_excel2query
name="[resultsQuery]"
file="[excelFile]"
sheetName="[workSheetName]"
sheetNumber="[workSheetNum]"
startRow="[startRow]"
endRow="[endRow]"
maxRows="[maxRows]"
headerRow="[headerRow]"
startColumn="[startColumn]"
endColumn="[endColumn]"
evalFormulas="[yes|no]"
dateFormat="[dateFormatString]"
columnList="[variableName]"
longNames="[yes|no]"
version="[variableName]">
|
| Attribute |
Req'd |
Description |
| name |
Yes |
The name of the variable into which would would like the result query stored |
| file |
Yes |
The Microsoft Excel ® to process |
| sheetName |
No |
The workbook sheet to read |
| sheetNumber |
No |
The wookbook sheet number to read. The first sheet is zero, the second is one, etc. This is useful if you don't know the name of the sheet. (default: 1) |
| startRow |
No |
The row on which CFX_Excel2Query should start reading (default: 1) |
| endRow |
No |
The row on which CFX_Excel2Query should stop reading (default: no end row ) |
| maxRows |
No |
The total number of rows to output (default: no max row ) |
| headerRow |
No |
The row that contains the fields that should be used as the column names in the resultant query. (default: no header row) |
| startColumn |
No |
The column on which CFX_Excel2Query should start reading (default: no start column) |
| endColumn |
No |
The column on which CFX_Excel2Query should stop reading (default: no end column) |
| evalFormulas |
No |
Indicates whether or not CFX_Excel2Query should evaluate formulas. (default: false; the actual formula is output) |
| dateFormat |
No |
The format string, similar to the one used by ColdFusion's DateFormat() function. The date masks are a little different, however; check the table below for details. |
| columnList |
No |
The list of columns as they appeared in the Excel file. This is used to work around the automatic alphabetization of column names by ColdFusion |
| longNames |
No |
Indicates whether CFX_Excel2Query should use long column names or not. Long column names are in the form of column001, column002, .., columnN.
By default, column names are in the following format: column1, column2, .., columnN. (note: this attribute is ignored if the headerRow attribute is used) |
| version |
No |
The variable name into which CFX_Excel2Query will write it's version information. You can then output this information to determine the version of CFX_Excel2Query you are running.
|
|
| Letter |
Date or Time Component |
Presentation |
Examples |
| G |
Era designator |
Text |
AD |
| y |
Year |
Year |
1996; 96 |
| M |
Month in year |
Month |
July; Jul; 07 |
| w |
Week in year |
Number |
27 |
| W |
Week in month |
Number |
2 |
| D |
Day in year |
Number |
189 |
| d |
Day in month |
Number |
10 |
| F |
Day of week in month |
Number |
2 |
| E |
Day in week |
Text |
Tuesday; Tue |
| a |
Am/pm marker |
Text |
PM |
| H |
Hour in day (0-23) |
Number |
0 |
| k |
Hour in day (1-24) |
Number |
24 |
| K |
Hour in am/pm (0-11) |
Number |
0 |
| h |
Hour in am/pm (1-12) |
Number |
12 |
| m |
Minute in hour |
Number |
30 |
| s |
Second in minute |
Number |
55 |
| S |
Millisecond |
Number |
978 |
| z |
Time zone |
General time zone |
Pacific Standard Time; PST; GMT-08:00 |
| Z |
Time zone |
RFC 822 time zone |
-0800 |
Examples
| Date and Time Pattern |
Result |
| "yyyy.MM.dd G 'at' HH:mm:ss z" |
2001.07.04 AD at 12:08:56 PDT |
| "EEE, MMM d, ''yy" |
Wed, Jul 4, '01 |
| "h:mm a" |
12:08 PM |
| "hh 'o''clock' a, zzzz" |
12 o'clock PM, Pacific Daylight Time |
| "K:mm a, z" |
0:08 PM, PDT |
| "yyyyy.MMMMM.dd GGG hh:mm aaa" |
02001.July.04 AD 12:08 PM |
| "EEE, d MMM yyyy HH:mm:ss Z" |
Wed, 4 Jul 2001 12:08:56 -0700 |
| "yyMMddHHmmssZ" |
010704120856-0700 |
|