Print
Add To Favorites
Email To Friend
Rate This Article
|
Import and Export Data From Excel Using ColdFusion
|
Published:
14 Apr 2009
|
Abstract
In this article Debjani discusses the various ways of importing and exporting data between excel sheets and database tables. She uses code snippets along with details explaining the code. |
|
by Debjani Mallick
Feedback
|
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days):
32161/
84
|
|
|
Introduction |
In many web-applications development, we come across the
requirement of importing data from tables present in database to excel sheets
and vice-versa. This is basically needed when we are migrating an already
existing application, where we would need the already existing data of the
application to be stored somewhere and then to be imported into database tables
of the new version of the application. Another scenario is when we provide the
end user of an web application to download some details or history, like
transaction history. In such cases, we can have the data saved to an excel
sheet. Though ColdFusion is a tag based language, and we have tags for
performing many complex functionalities without knowing any lower level details
for those, but there is no direct tag to perform the above operations. There
are many solutions to tackle the above issue. One of the easiest solution has
been provided by Ben Nadel, the POI Utility. The POI Utility is a ColdFusion
component that contains functions to read excel sheets into queries thus
making the data compatible with the ColdFusion, and to write database tables
data in the form of queries to excel sheets. This utility provides with the
flexibility to populate multiple worksheets of an excel sheet, thus allowing
the storage of all the required data in one excel sheet instead of multiple
excel sheets. Using this utility, its also possible to provide styling
specifications for the excel sheet data to be written from database table data.
To take all those advantages, we need to download the POI Utility into our
application. But what when we want to perform the above operations without
downloading the POI Utility? The answer to this question is that we have many
other alternatives to perform the above operations. Here I would be describing
some of the alternatives in detail.
|
|
|
User Comments
Title:
Mr.
Name:
John Glynn
Date:
2011-09-14 10:45:00 AM
Comment:
Thank you Debjani Mallick! This is a terrific tutorial that worked right out of the box. I was wondering though, is there a way to return the table columns in a different order than alphabetical, order by current column order or something like that?
Thanks again! John
|
Title:
readExcelSheet
Name:
HP
Date:
2011-06-13 11:43:57 AM
Comment:
I got this error when trying to use the readExcelSheet function. NOt sure why I got this error. Any idea? [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
|
Title:
missing columns after read op'n
Name:
sooraj
Date:
2010-08-24 10:10:15 AM
Comment:
hi Debjani, great stufff for cf'ers... I have an interesting issue when I used ur functioanlity to read excel file, the problem is my file contyains 4 columns/fields and after upload and read operation, dumping shows only 3 columns...why this happens?
Any ideas..Is I am missing anything...
thanks
|
Title:
Hi
Name:
Smith
Date:
2010-03-31 11:54:50 AM
Comment:
Yes waiting for it
|
Title:
Where R ur Articles???
Name:
Abhinash
Date:
2010-03-31 10:57:19 AM
Comment:
Waiting for ue next big article
|
Title:
Data not being written to new columns
Name:
Chuck
Date:
2010-01-14 3:49:00 PM
Comment:
We are trying method 2 and we are not writing data to new columns, instead its writing to a new line for every column. Also it's sorting the data in alphabetical order.
|
Title:
instance, a FileOutputStream or ServletOutputStream
Name:
Lance
Date:
2009-06-17 3:00:53 PM
Comment:
Do have an example of how to use ServletOutputStream
|
Title:
Reply: How do you specify sheetname
Name:
Debjani Mallick
Date:
2009-06-11 12:16:21 PM
Comment:
For specifying the arguments using the second write method, the syntax is: writeExcelSheet('table name','datasource name','path to the excel sheet with sheet name'); This will create a sheet with the default name. Using the second write method, its first of all not possible to specify worksheet names and secondly, its also not possible to write to multiple tabs of an excel sheet through this method. But the first method specified, accomplishes both the tasks.
|
Title:
How do you specify sheetname
Name:
jtansey
Date:
2009-06-11 9:19:14 AM
Comment:
So, in example two of writing to an excel worksheet, it says to specify the fileName as the full path along with the excelsheetname. I need to write to multiple sheets. How exactly do I specify the sheetname?
|
Title:
Good one
Name:
Abx-68
Date:
2009-05-07 4:26:03 AM
Comment:
Good one!! Keep Going
|
Title:
Helpful article
Name:
John
Date:
2009-04-22 11:15:24 AM
Comment:
Thank you for the article. It is really helping.
|
|
Product Spotlight
|
|