• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

Export Query to Excel - Multiple Worksheets Seperated by Month?

New Here ,
Apr 08, 2009 Apr 08, 2009

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*

TOPICS
Advanced techniques

Views

1.1K

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Apr 09, 2009 Apr 09, 2009

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Apr 10, 2009 Apr 10, 2009

Copy link to clipboard

Copied

Very interested! Thank you. Is the link below to the latest and greatest?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Apr 10, 2009 Apr 10, 2009

Copy link to clipboard

Copied

LATEST

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>

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation