Copy link to clipboard
Copied
I need to Export Query Results to Multiple Excel Worksheets and seperate the results by the year/month for example
Query results contain results from 1/1/2009 to 2/15/2009. Jan 09 results need to be on one worksheet and the Feb 1st-15th 2009 on another.
Is this possible?
Thank you in advance!
*Hawaii Girl*
Copy link to clipboard
Copied
I have a cfcomponent that takes in query and export it directly to an *.xls spreadsheet. It is currently create onle a single sheet but it can be modify to do multiple.
Interested? Let me know and I'll upload it for ya (code is on the machine, have to burn it to a CD).
Otherwise, you can go here: http://www.google.com/url?sa=t&source=web&ct=&cd=1&url=http%3A%2F%2Fwww.bennadel.com%2Fprojects%2Fpoi-utility.htm&ei=yubdSZ-9KprMMpfozPIJ&usg=AFQjCNGZB4m35AIvsZ2tmBgSh9SPrCGogA&sig2=H3ZtvVjY-L-DdPGWO8FCVA
Copy link to clipboard
Copied
Very interested! Thank you. Is the link below to the latest and greatest?
Copy link to clipboard
Copied
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>