Export Query to Excel - Multiple Worksheets Seperated by Month?

The code is below, need your logic to seperate the data by year. You need POI, version 2.5.1 Final, otherwise the latest stable version is 3.2 Final, you can download it here http://poi.apache.org/.

Check your ColdFusion8\lib, CFusionMX7\lib (or any other version of CF) directory for poi**.

I created before I found similar solution from Ben Nadel - POI Utility Component, would have use it to save time writing the code.

Use at your own risk It has been tested by the client that I work for so it should be fairly stable.

Edited: For some reason, HTML block hides the code, see the attached file instead.

<!---
WARNING: Use it at your own risk, I should not be held responsible for what the code does.

@function excelToQuery - convert data from excel to query object.
@function queryToExcel - convert a query object to an Excel spreadsheet.

@author: Phong Kien
--->

<cfcomponent>
     <!--- error message property --->
     <cfproperty name="message" required="false" type="string" default="">

     <!--- method to retrieve error message if excelToQuery or queryToExcel return false --->
     <cffunction name="getMessage" returntype="string">
          <cfreturn message/>
     </cffunction>

     <!--- method to convert an excel document to a query object --->
     <cffunction name="excelToQuery"
                    access="public"
                    description="Convert excel format cell to a Query Object, fileName must be an exact location. Date field will be return as numeric value, use CreateODBCDate(value) to convert it to date."
                    output="true"
                    returntype="Any">

          <cfargument name="fileName" type="string" required="true"/>
          <cfargument name="columnList" type="String" required="true"/>
          <cfargument name="sheetNumber" type="numeric" required="false" default="0"/>

          <cfset retQuery = false>

          <cfscript>
               try {
                    //open the excel file.
                    fileStream = CreateObject("java", "java.io.FileInputStream").init(fileName);
                    poiHSSFFile = CreateObject("java", "org.apache.poi.poifs.filesystem.POIFSFileSystem");
                    poiHSSFFile.init(fileStream);

                    //retrieve an excel work book
                    workBook = CreateObject("java", "org.apache.poi.hssf.usermodel.HSSFWorkbook");
                    workBook.init(poiHSSFFile);

                    //retrieve first excel sheet and run validation check.
                    excelSheet = workBook.getSheetAt(sheetNumber);

                    //hssfRow = excelSheet.getRow(0);
                    numberOfRow = excelSheet.getLastRowNum() + 1;

                    //get column header for query attributes

                    retQuery = QueryNew(columnList);
                    queryRow = QueryAddRow(retQuery, numberOfRow - 1);
                    failure = false;

                    //lastRowNum is the last row index.
                    //this first loop will run validation check.
                    //getRow and getCell only accept "short" and "int", must convert
                    //double to short or int.
                    //first row (row index 0) is header
                    for (rowCount = 1; rowCount LTE excelSheet.getLastRowNum(); rowCount = rowCount + 1){
                         tempDouble = CreateObject("java", "java.lang.Double").init(rowCount);
                         hssfRow = excelSheet.getRow(tempDouble.shortValue());

                         //lastCellNum is the lastCellIndex + 1
                         if (isDefined("hssfRow")){
                              for (cellCount = 0; cellCount LT hssfRow.getLastCellNum(); cellCount = cellCount + 1){
                                   tempDouble = CreateObject("java", "java.lang.Double").init(cellCount);
                                   hssfCell = hssfRow.getCell(tempDouble.shortValue());
                                   cellContent = "";

                                   if (isDefined("hssfCell")) {
                                        cellType = hssfCell.getCellType();

                                        if (cellType eq 0) {
                                             cellContent = hssfCell.getNumericCellValue();
                                        } else {
                                             cellContent = hssfCell.getStringCellValue();
                                        }
                                   }

                                   status = QuerySetCell(retQuery, ListGetAt(columnList, cellCount + 1), cellContent, rowCount);

                                   if (status eq false) {
                                        failure = true;
                                        break;
                                   }
                              }
                         }

                         if (failure eq true) {
                              retQuery = false;
                              break;
                         }
                    }

                    //close open stream.
                    fileStream.close();
               } catch (Any exception) {
                    fileStream.close();
                    retQuery = false;
                    message = exception.message;
               }
          </cfscript>

          <cfreturn retQuery/>
     </cffunction>

     <!--- function to convert a query object to an excel document. --->
     <cffunction name="queryToExcel"
                    access="public"
                    description="This function will convert a query object to an excel document. It will return true if succeed, false otherwise."
                    output="true"
                    returntype="boolean">

          <cfargument name="queryObject" type="query" required="true">
          <cfargument name="sheetNumber" type="numeric" required="false" default="0"/>
          <cfargument name="columnHeaders" type="string" required="false" default="">
          <cfargument name="numericFieldList" type="string" required="false" default="">

          <!--- reading the template file --->
          <cftry>
               <cfset numberOfColumn = ListLen(queryObject.columnList)>
               <cfset hssfWorkbook = CreateObject("java", "org.apache.poi.hssf.usermodel.HSSFWorkbook").init()/>


               <!--- TODO
                    Your loop and logics to seperate the year here.
                    createSheet() can take in a string which is the shee title. For example
                    <cfset hssfSheet = hssfWorkbook.createSheet('Jan 09')/>
               --->
               <cfset hssfSheet = hssfWorkbook.createSheet()/>
               <cfset hssfRow = hssfSheet.createRow(0)>

               <cfif columnHeaders EQ "">
                    <cfset columnHeaders = queryObject.columnList/>
               </cfif>

               <cfloop from="0" to="#numberOfColumn - 1#" index="columnCounter">
                    <cfset tempDouble = CreateObject("java", "java.lang.Double").init(columnCounter)>
                    <cfset tempCellContent = ListGetAt(columnHeaders, columnCounter + 1)>
                    <cfset tempCellContent = REReplace(tempCellContent, "^[a-zA-Z+]_", "")>
                    <cfset width = CreateObject("java", "java.lang.Double").init(256 * (Len(tempCellContent) + 6))>
                    <cfset hssfSheet.setColumnWidth(tempDouble.intValue(), width.intValue())>
                    <cfset hssfCell = hssfRow.createCell(tempDouble.shortValue())>
                    <cfset hssfCell.setCellValue(JavaCast("string",tempCellContent))>
                    <cfset hssfCell = "">
               </cfloop>

               <cfset retVal = false>

               <!--- begin inserting cells into row  --->
               <cfset rowCount = 1/>
               <cfloop query="queryObject">
                    <cfset tempDouble = CreateObject("java", "java.lang.Double").init(rowCount)>
                    <cfset hssfRow = hssfSheet.createRow(tempDouble.intValue())>
                    <cfloop from="0" to="#numberOfColumn - 1#" index="columnCounter">
                         <cfset tempDouble = CreateObject("java", "java.lang.Double").init(columnCounter)>
                         <cfset tempColumnName = Ucase(ListGetAt(queryObject.columnList, columnCounter + 1))>
                         <cfset tempCellContent = Evaluate(tempColumnName)>

                         <cfif (Len(numericFieldList) GT 0) AND (ListFind(numericFieldList, tempColumnName) GT 0)>
                              <cfset hssfCell = hssfRow.createCell(tempDouble.shortValue())>
                              <cfset hssfCell.setCellValue(JavaCast("double",tempCellContent))>
                         <cfelse>
                              <cfset hssfCell = hssfRow.createCell(tempDouble.shortValue())>
                              <cfset hssfCell.setCellValue(JavaCast("string",tempCellContent))>
                         </cfif>
                         <cfset hssfCell = "">
                    </cfloop>
                    <cfset rowCount = rowCount + 1>
                    <cfset retVal = true>
               </cfloop>

               <!--- TODO END LOOP --->

               <!--- create a java.lang.System object so that nanoTime() method can be used to
               as a temporary filename for the new excel file, this make the file *ALMOST* unique --->
               <cfset tempSystem = CreateObject("java", "java.lang.System")>
               <cfset tempFileName = tempSystem.nanoTime() & ".xls">
               <cfset tempAbsoluteFileName = getTempDirectory() & tempFileName>
               <cfset outputStream = CreateObject("java", "java.io.FileOutputStream")>
               <cfset outputStream.init(JavaCast("string",tempAbsoluteFileName))>
               <cfset hssfWorkbook.write(outputStream)>
               <cfset outputStream.close()>

               <!--- display the content to screen, user can decide to open it or save it, temp file
               will be delete after open/save.--->

               <cfdirectory action="list" directory="#getTempDirectory()#" filter="#tempFileName#" name="myTempDir">
               <cfheader name="Content-Length" value="#myTempDir.size#">
               <cfheader name="Content-Type" value="application/vnd.ms-excel">
               <cfheader name="Content-Location" value="#tempFileName#">
               <cfheader name="Expires" value="#GetHttpTimeString(now() + CreateTime(0, 5, 0))#">
               <cfheader name="Content-Disposition" value="attachment; filename=#tempFileName#">
               <cfcontent type="application/vnd.ms-excel" file="#tempAbsoluteFileName#" deletefile="true">
          <cfcatch type="Any">
               <cfset retVal = false/>
               <cftry>
                    <cfset fileStream.close()/>
               <cfcatch type="any"></cfcatch>
               </cftry>
          </cfcatch>
          </cftry>
          <cfreturn retVal />
     </cffunction>
</cfcomponent>