Import and Export Data From Excel Using ColdFusion
page 3 of 5
by Debjani Mallick
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 37496/ 45

Writing to an excel sheet (method 1)
<cffunction name="writeExcelSheet" access="public" output="false" returntype="void">
      <cfargument name="tableName" required="true" type="string" />
      <cfargument name="dsnName" required="true" type="string" />
      <cfargument name="fileName" required="true" type="string" />
      <cfargument name="sheetName" required="true" type="string" />
      <cfscript>
            var getRecords = '';
            var workBook = '';
            var newSheet = '';
            var listOfColumns = '';
            var count = 0;
            var index = '';
            var row = 0;
            var cell = 0;
            var fileOutStream = '';
      </cfscript>
      <cfquery name="getRecords" datasource="#arguments.dsnName#">
            select * from #arguments.tableName#
      </cfquery>
      <cfif getRecords.RecordCount>
            <cfscript
                  workBook = createObject>("java", 
                    "org.apache.poi.hssf.usermodel.HSSFWorkbook").init();
                  newSheet = workBook.createSheet();
                  workBook.setSheetName(0, "#arguments.sheetName#");
                  listOfColumns = "#getRecords.columnList#";
                  count = 0;
            </cfscript>
            <cfloop list="#listOfColumns#" index="index">
                  <cfscript  
                        row = newSheet>.createRow(0);
                        cell = row.createCell(count);
                        cell.setCellValue(index);
                        count = count + 1;
                  </cfscript>
            </cfloop>
            <cfloop query="getRecords">
                  <cfscript
                        row = newSheet>.createRow(#getRecords.currentRow#);
                        count = 0;
                  </cfscript>
                  <cfloop list="#listOfColumns#" index="index">
                        <cfscript
                              cell = row>.createCell(count);
                              cell.setCellValue(getRecords[index][currentRow]);
                              count = count + 1;
                        </cfscript>
                  </cfloop>
            </cfloop>
            <cfscript
                  fileOutStream = createObject>("java", 
                    "java.io.FileOutputStream").init("#arguments.fileName#");
                  workBook.write(fileOutStream);
                  fileOutStream.close();
            </cfscript>
      </cfif>
</cffunction>

Explanation

For writing to an excel sheet, in the above function, we need four arguments – tableName which is the name of the table from which we need to extract the data and save to the excel sheet, dsnName is the datasource name for connecting to the database where the table is present, fileName is the full path along with the excel sheet name where the data is to be written, and sheetName is the name of the worksheet to be created inside the excel sheet. In the above function we are making the use of the Apache POI model. The POI project is used for developing pure Java ports of file formats based on Microsoft's OLE 2 Compound Document Format which is used by Microsoft Office Documents. This project contains APIs such as HSSF and XSSF responsible for reading and writing Microsoft Excel 97-2007 and OOXML spreadsheets using (only) Java. HSSF is the POI Project's pure Java implementation of the Excel '97(-2007) file format which provides different ways to create, modify, read and write XLS spreadsheets. As it can be seen in the above function, we are using the usermodel package of HSSF which maps HSSF low level structures to familiar workbook/sheet model. Worksheets are created by creating an instance of org.apache.poi.ss.usermodel.Workbook. org.apache.poi.hssf.usermodel.HSSFWorkbook creates a concrete class directly. After creating an instance of Workbook, sheets can be created by calling createSheet method from the Workbook instance. The created sheet automatically adds itself in sequence to the workbook. To provide a sheet name to the sheet created using CreateSheet method, we are taking the help of setSheetName method which takes three arguments – first one is sheetindex (which in our function is 0, which means the first index or first sheet), second argument is the sheet name that we want to give, and third one is the encoding which for HSSF is 8bit per char by default. After creating the instance of the sheet, rows can be created by calling createRow which takes one parameter which is the row number. Cells within a row can be created by calling createCell method which takes the column number and the type. It should be kept in mind that only rows that have cell values should be added to the sheet and cells that have values should be added to the row; cells should also have their cell type set to either Cell.CELL_TYPE_NUMERIC or Cell.CELL_TYPE_STRING depending on whether they contain a numeric or textual value. To set the value of a cell, setCellValue method can be called with either a string or double as a parameter. After the workbook has been generated, it can be written out by calling write(outputStream) from the instance of workbook created, or by passing it an OutputStream (for instance, a FileOutputStream or ServletOutputStream). Since HSSF does not close the OutputStream by itself, in our function we are explicitly closing the connection by calling the close function. Reading can also be done by using POI. For this, first we need to create a new instance of org.apache.poi.poifs.Filesystem, passing in an open InputStream, such as a FileInputStream for the excel sheet, to the constructor. Then we need to construct a new instance of org.apache.poi.hssf.usermodel.HSSFWorkbook passing the Filesystem instance to the constructor. From there we can access all of the high level model objects through their assessor methods like getSheet, getRow, etc.


View Entire Article

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
Product Spotlight 





Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2024 ASPAlliance.com  |  Page Processed at 2024-03-29 10:09:41 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search