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.

<cffunction name="readExcelSheet" access="public" 
  output="false" returntype="query">
      <cfargument name="fileName" required="true" type="string" />
      <cfargument name="sheetName" required="true" type="string" />
      <cfscript>
            // Declaration of variables
            var objInst = '';
            var stmnt = '';
            var sheetData = '';
            var sql = "Select * from [#sheetName#$]";
            var qryData = QueryNew('');
 
            // Processing the excel sheet to read the data
            if(len(trim(arguments.fileName)) and fileExists(arguments.fileName))
            {
                  CreateObject("java", 
                    "java.lang.Class").forName("sun.jdbc.odbc.JdbcOdbcDriver");
                  objInst = CreateObject("java", 
                    "java.sql.DriverManager").getConnection(
     "jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=" & arguments.fileName);
                  stmnt = objInst.createStatement();
                  sheetData = stmnt.executeQuery(sql);
                  qryData = CreateObject('java', 
                    'coldfusion.sql.QueryTable').init(sheetData);
                  objInst.Close();
            }
            return qryData;
</cfscript>
</cffunction>

Explanation

In the above function, we have two arguments – fileName which is the absolute path to the excel sheet along with the excel sheet name and sheetName is the name of the worksheet present in the excel sheet whose data we want to read. In Java, all classes are defined, self-referentially, as the instance of the class java.lang.Class. In order to load the java.lang.Class instance of a particular Java class, the forName method of java.lang.Class can be used. Every class of Java maintains a ProtectionDomain which in turn maintains a CodeSource object pointing to the location from which code for the particular domain was loaded. ProtectionDomain performs the functionality of defining the security access to the domain to which the class belongs. In the above function, we are creating an instance of the JdbcOdbcDriver. Then we are creating an object instance to refer to the driver manager. All those lines of code are being executed with the purpose of connecting to the target datasource. For connecting to a database, we need two things to be done. First one is the loading of the driver which is accomplished by class.forName method. When we call this method, an instance of a driver is automatically created and registered with the DriverManager. So its not required to create an instance of the class. After the driver is loaded, it can used to make a connection to the DBMS. So now the second step in establishing a connection with the target database is to have an appropriate driver connect to the DBMS. The DriverManager works closely with the Driver interface in order to determine the drivers available to the JDBC client. When a connection is requested by the client, the DriverManager takes the responsibility of finding the driver that recognizes the URL provided by the client and uses it to connect to the corresponding data source. In the above function, the getConnection method is used to establish a connection. This method requires a 'connectionstring' which depends on the type of database to be connected. The two common examples of connectionstring are:

Microsoft SQL
 "jdbc:odbc:DRIVER={SQL Server};Database="dbname";Server=ip;", "username", "password"
 
 Microsoft Access
 "Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\websites\folder\mydatabase.mdb;Uid=;Pwd=;" 

If the JDBC URL specified as the connection string in the getConnection method is recognized by one of the drivers loaded, then a connection is established by the driver with the DBMS specified in the URL. And here the DriverManager manages all the connection details. The connection returned by the getConnection method is an open connection which can be used to create JDBC statements which would pass the SQL statements to the DBMS. After the connection is made, we are executing the query. Now after the query is executed, how to return a ColdFusion query object from a Java class using a JDBC result set (java.sql.ResultSet). This can be accomplished by the use of the coldfusion.sql.QueryTable class. This class comes with ColdFusion MX and located in the cfusion.jar file. After the work is done, we are closing the connection by calling the close function.

<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.

<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" />
      <cfscript>
            var getRecords = '';
            var listOfColumns = '';
            var tabChar = '';
            var newLine = '';
            var index = '';
            var value = '';
      </cfscript>
      <cfsetting enablecfoutputonly="Yes">
      <cfquery name="getRecords" datasource="#arguments.dsnName#">
            select * from #arguments.tableName#
      </cfquery>
 
      <cfif getRecords.RecordCount>
            <cfsetting showdebugoutput="false">
            <cfset listOfColumns = getRecords.columnList>
            <cfset TabChar = Chr(9)>
            <cfset NewLine = Chr(13) & Chr(10)>
            <cfsavecontent variable="value">
                  <cfloop list="#listOfColumns#" index="index">
                        <cfoutput>#index##TabChar#</cfoutput>
                  </cfloop>
            </cfsavecontent>
            <cffile action="write" file="#arguments.fileName#" output="#value#" >
 
            <!--- output query data using cfloop & cfoutput --->
            <cfset value = ''>
            <cfsavecontent variable="value">
                  <cfloop query="getRecords">
                        <cfloop list="#listOfColumns#" index="index">
                              <cfoutput>#getRecords['#index#'][CurrentRow]##TabChar#</cfoutput>
                        </cfloop>
                        <cfoutput>#NewLine#</cfoutput>
                  </cfloop>
            </cfsavecontent>
            <cffile action="append" file="#arguments.fileName#" output="#value#">
      </cfif>
</cffunction>

Explanation

In the above function, we need three arguments – tableName which is the name of the table from which we need to extract the data and save to the excelsheet, dsnName is the datasource name for connecting to the database where the table is present, fileName is the full path along with the excelsheet name where the data is to be written. This uses all the ColdFusion functions to write to an excelsheet. Here we are simply querying the database and if we have records, we are setting the first row of the sheet with the column names of the table and then we are looping over the records, and creating one row for each record. We have used cfsavecontent tag to first save the contents in a variable and then we are writing the value of the variable to the sheet. The above method is much simpler and has an easy to understand code than that for the first method.

After reading this article, I hope that you must have become comfortable with the requirement of data import and export to excel sheet in applications. According to our convenience we can make use of any method that we want in our application to import and export data.

By Debjani Mallick