The Blog Of Ben Nadel On Obsessively Thorough Web Application Development

Loading....

Prev Next

Ben Nadel at CFUNITED 2008 (Washington, D.C.) with: ( photo link )

Peter Bell
Website | @peterbell

I am the chief software engineer at Epicenter Consulting, a boutique software company in New York City that specializes in developing high-end, customized business solutions using ColdFusion and jQuery. ( more )

Find me on: LinkedIn - Facebook - Twitter

Loading....

ColdFusion Component Wrapper For POI To Read And Write Excel Files

Posted January 15, 2007 at 6:47 PM

Tags: ColdFusion

I have been working with ColdFusion and POI to read and write Microsoft Excel files. It has completely opened up a new world for me. I have begun to formalize a methodology for using it that I have wrapped in to a beta ColdFusion component, POIUtility.cfc. I have just started using it, so I am not sure how bullet-proof it is - I can almost assure you that there are bugs :) I will be using it intensively for the next week or so as I work on a TON of reporting from ColdFusion to Excel. I am sure this will be more fine-tuned in a week or two.

There are several main functions:

GetNewSheetStruct()

This returns a structure that defines the Excel sheet object. Write functions expect either an array of these types of object (one per sheet) or a single instance (defining a one-sheet document).

ReadExcel()

This reads an Excel file into an array of structures that contains the Excel file information OR if a specific sheet index is passed in, only that sheet object is returned.

ReadExcelSheet()

This takes a given WorkBook instance and reads the given sheet into a Sheet structure. It can be access by the public, but is meant to be used primarily by the ReadExcel() method.

WriteExcel()

This takes an array of Sheet structure objects and writes each of them to a tab in the resulting Excel file OR it takes a single Sheet object and writes that to the first tab of the resulting Excel file.

WriteExcelSheet()

This takes a workbook and writes the given sheet data to the Sheet of an Excel file. This can be used by the public but is meant to be used primarily by WriteExcel() method.

WriteSingleExcel()

This allows the user to write a single query to an Excel file without having to create an intermediary Sheet object. This is just a convenient short hand that allows you to bypass the intermediary "Sheet" structure - underneath, it just packages the data and, in-turn, calls the WriteExcel() method.

Here is the code for the beta POI utiltiy ColdFusion component, POIUtility.cfc:

Launch code in new window » Download code as text file »

<cfcomponent
displayname="POIUtility"
output="false"
hint="Handles the reading and writing of Microsoft Excel files using POI and ColdFusion.">
<cffunction name="Init" access="public" returntype="POIUtility" output="false"
hint="Returns an initialized POI Utility instance.">
<cfreturn THIS />
</cffunction>
<cffunction name="GetNewSheetStruct" access="public" returntype="struct" output="false"
hint="Returns a default structure of what this Component is expecting for a sheet definition when WRITING Excel files.">
<cfset var LOCAL = StructNew() />
<cfscript>
// This is the query that will hold the data.
LOCAL.Query = "";
// THis is the list of columns (in a given order) that will be
// used to output data.
LOCAL.ColumnList = "";
// These are the names of the columns used when creating a header
// row in the Excel file.
LOCAL.ColumnNames = "";
// This is the name of the sheet as it appears in the bottom Excel tab.
LOCAL.SheetName = "";
// Return the local structure containing the sheet info.
return( LOCAL );
</cfscript>
</cffunction>
<cffunction name="ReadExcel" access="public" returntype="any" output="false"
hint="Reads an Excel file into an array of strutures that contains the Excel file information OR if a specific sheet index is passed in, only that sheet object is returned.">
<cfargument
name="FilePath"
type="string"
required="true"
hint="The expanded file path of the Excel file."
/>
<cfargument
name="HasHeaderRow"
type="boolean"
required="false"
default="false"
hint="Flags the Excel files has using the first data row a header column. If so, this column will be excluded from the resultant query."
/>
<cfargument
name="SheetIndex"
type="numeric"
required="false"
default="-1"
hint="If passed in, only that sheet object will be returned (not an array of sheet objects)."
/>
<cfscript>
// Define the local scope.
var LOCAL = StructNew();
// Create the Excel file system object. This object is responsible
// for reading in the given Excel file.
LOCAL.ExcelFileSystem = CreateObject(
"java",
"org.apache.poi.poifs.filesystem.POIFSFileSystem"
).Init(
// Create the file input stream.
CreateObject(
"java",
"java.io.FileInputStream"
).Init(
ARGUMENTS.FilePath
)
);
// Get the workbook from the Excel file system.
LOCAL.WorkBook = CreateObject(
"java",
"org.apache.poi.hssf.usermodel.HSSFWorkbook"
).Init(
LOCAL.ExcelFileSystem
);
// Check to see if we are returning an array of sheets OR just
// a given sheet.
if (ARGUMENTS.SheetIndex GTE 0){
// We just want a given sheet, so return that.
return(
ReadExcelSheet(
LOCAL.WorkBook,
ARGUMENTS.SheetIndex,
ARGUMENTS.HasHeaderRow
)
);
} else {
// No specific sheet was requested. We are going to return an array
// of sheets within the Excel document.
// Create an array to return.
LOCAL.Sheets = ArrayNew( 1 );
// Loop over the sheets in the documnet.
for (
LOCAL.SheetIndex = 0 ;
LOCAL.SheetIndex LT LOCAL.WorkBook.GetNumberOfSheets() ;
LOCAL.SheetIndex = (LOCAL.SheetIndex + 1)
){
// Add the sheet information.
ArrayAppend(
LOCAL.Sheets,
ReadExcelSheet(
LOCAL.WorkBook,
LOCAL.SheetIndex,
ARGUMENTS.HasHeaderRow
)
);
}
// Return the array of sheets.
return( LOCAL.Sheets );
}
</cfscript>
</cffunction>
<cffunction name="ReadExcelSheet" access="public" returntype="struct" output="false"
hint="Takes an Excel workbook and reads the given sheet (by index) into a structure.">
<cfargument
name="WorkBook"
type="any"
required="true"
hint="This is a workbook object created by the POI API."
/>
<cfargument
name="SheetIndex"
type="numeric"
required="false"
default="0"
hint="This is the index of the sheet within the passed in workbook. This is a ZERO-based index (coming from a Java object)."
/>
<cfargument
name="HasHeaderRow"
type="boolean"
required="false"
default="false"
hint="This flags the sheet as having a header row or not (if so, it will NOT be read into the query)."
/>
<cfscript>
// Define the local scope.
var LOCAL = StructNew();
// Set up the default return structure.
LOCAL.SheetData = StructNew();
// This is the index of the sheet within the workbook.
LOCAL.SheetData.Index = ARGUMENTS.SheetIndex;
// This is the name of the sheet tab.
LOCAL.SheetData.Name = ARGUMENTS.WorkBook.GetSheetName(
JavaCast( "int", ARGUMENTS.SheetIndex )
);
// This is the query created from the sheet.
LOCAL.SheetData.Query = "";
// This is a flag for the header row.
LOCAL.SheetData.HasHeaderRow = ARGUMENTS.HasHeaderRow;
// An array of header columns names.
LOCAL.SheetData.ColumnNames = ArrayNew( 1 );
// This keeps track of the min number of data columns.
LOCAL.SheetData.MinColumnCount = 0;
// This keeps track of the max number of data columns.
LOCAL.SheetData.MaxColumnCount = 0;
// Get the sheet object at this index of the
// workbook. This is based on the passed in data.
LOCAL.Sheet = ARGUMENTS.WorkBook.GetSheetAt(
JavaCast( "int", ARGUMENTS.SheetIndex )
);
// Loop over the rows in the Excel sheet. For each
// row, we simply want to capture the number of
// physical columns we are working with that are NOT
// blank. We will then use that data to figure out
// how many columns we should be using in our query.
for (
LOCAL.RowIndex = 0 ;
LOCAL.RowIndex LT LOCAL.Sheet.GetPhysicalNumberOfRows() ;
LOCAL.RowIndex = (LOCAL.RowIndex + 1)
){
// Get a reference to the current row.
LOCAL.Row = LOCAL.Sheet.GetRow(
JavaCast( "int", LOCAL.RowIndex )
);
// Get the number of physical cells in this row. While I think that
// this can possibly change from row to row, for the purposes of
// simplicity, I am going to assume that all rows are uniform and
// that this row is a model of how the rest of the data will be
// displayed.
LOCAL.ColumnCount = LOCAL.Row.GetPhysicalNumberOfCells();
// Check to see if the query variable we have it actually a query.
// If we have not done anything to it yet, then it should still
// just be a string value (Yahoo for dynamic typing!!!). If that
// is the case, then let's use this first data row to set up the
// query object.
if (NOT IsQuery( LOCAL.SheetData.Query )){
// Create an empty query. Doing it this way creates a query
// with neither column nor row values.
LOCAL.SheetData.Query = QueryNew( "" );
// Now that we have an empty query, we are going to loop over
// the cells COUNT for this data row and for each cell, we are
// going to create a query column of type VARCHAR. I understand
// that cells are going to have different data types, but I am
// chosing to store everything as a string to make it easier.
for (
LOCAL.ColumnIndex = 0 ;
LOCAL.ColumnIndex LT LOCAL.ColumnCount ;
LOCAL.ColumnIndex = (LOCAL.ColumnIndex + 1)
){
// Add the column. Notice that the name of the column is
// the text "column" plus the column index. I am starting
// my column indexes at ONE rather than ZERO to get it back
// into a more ColdFusion standard notation.
QueryAddColumn(
LOCAL.SheetData.Query,
"column#(LOCAL.ColumnIndex + 1)#",
"CF_SQL_VARCHAR",
ArrayNew( 1 )
);
// Check to see if we are using a header row. If so, we
// want to capture the header row values into an array
// of header column names.
if (ARGUMENTS.HasHeaderRow){
// Try to get a header column name (it might throw
// an error).
try {
ArrayAppend(
LOCAL.SheetData.ColumnNames,
LOCAL.Row.GetCell(
JavaCast( "int", LOCAL.ColumnIndex )
).GetStringCellValue()
);
} catch (any ErrorHeader){
// There was an error grabbing the text of the header
// column type. Just add an empty string to make up
// for it.
ArrayAppend(
LOCAL.SheetData.ColumnNames,
""
);
}
}
}
// Set the default min and max column count based on this first row.
LOCAL.SheetData.MinColumnCount = LOCAL.ColumnCount;
LOCAL.SheetData.MaxColumnCount = LOCAL.ColumnCount;
}
// ASSERT: Whether we are on our first Excel data row or
// our Nth data row, at this point, we have a ColdFusion
// query object that has the proper columns defined.
// Update the running min column count.
LOCAL.SheetData.MinColumnCount = Min(
LOCAL.SheetData.MinColumnCount,
LOCAL.ColumnCount
);
// Update the running max column count.
LOCAL.SheetData.MaxColumnCount = Max(
LOCAL.SheetData.MaxColumnCount,
LOCAL.ColumnCount
);
// Add a row to the query so that we can store this row's
// data values.
QueryAddRow( LOCAL.SheetData.Query );
// Loop over the cells in this row to find values.
for (
LOCAL.ColumnIndex = 0 ;
LOCAL.ColumnIndex LT LOCAL.ColumnCount ;
LOCAL.ColumnIndex = (LOCAL.ColumnIndex + 1)
){
// When getting the value of a cell, it is important to know
// what type of cell value we are dealing with. If you try
// to grab the wrong value type, an error might be thrown.
// For that reason, we must check to see what type of cell
// we are working with. These are the cell types and they
// are constants of the cell object itself:
//
// 0 - CELL_TYPE_NUMERIC
// 1 - CELL_TYPE_STRING
// 2 - CELL_TYPE_FORMULA
// 3 - CELL_TYPE_BLANK
// 4 - CELL_TYPE_BOOLEAN
// 5 - CELL_TYPE_ERROR
// Get the cell from the row object.
LOCAL.Cell = LOCAL.Row.GetCell(
JavaCast( "int", LOCAL.ColumnIndex )
);
// Get the type of data in this cell.
LOCAL.CellType = LOCAL.Cell.GetCellType();
// Get teh value of the cell based on the data type. The thing
// to worry about here is cell forumlas and cell dates. Formulas
// can be strange and dates are stored as numeric types. For
// this demo, I am not going to worry about that at all. I will
// just grab dates as floats and formulas I will try to grab as
// numeric values.
if (LOCAL.CellType EQ LOCAL.Cell.CELL_TYPE_NUMERIC) {
// Get numeric cell data. This could be a standard number,
// could also be a date value. I am going to leave it up to
// the calling program to decide.
LOCAL.CellValue = LOCAL.Cell.GetNumericCellValue();
} else if (LOCAL.CellType EQ LOCAL.Cell.CELL_TYPE_STRING){
LOCAL.CellValue = LOCAL.Cell.GetStringCellValue();
} else if (LOCAL.CellType EQ LOCAL.Cell.CELL_TYPE_FORMULA){
// Since most forumlas deal with numbers, I am going to try
// to grab the value as a number. If that throws an error, I
// will just grab it as a string value.
try {
LOCAL.CellValue = LOCAL.Cell.GetNumericCellValue();
} catch (any Error1){
// The numeric grab failed. Try to get the value as a
// string. If this fails, just force the empty string.
try {
LOCAL.CellValue = LOCAL.Cell.GetStringCellValue();
} catch (any Error2){
// Force empty string.
LOCAL.CellValue = "";
}
}
} else if (LOCAL.CellType EQ LOCAL.Cell.CELL_TYPE_BLANK){
LOCAL.CellValue = "";
} else if (LOCAL.CellType EQ LOCAL.Cell.CELL_TYPE_BOOLEAN){
LOCAL.CellValue = LOCAL.Cell.GetBooleanCellValue();
} else {
// If all else fails, get empty string.
LOCAL.CellValue = "";
}
// ASSERT: At this point, we either got the cell value out of the
// Excel data cell or we have thrown an error or didn't get a
// matching type and just have the empty string by default.
// No matter what, the object LOCAL.CellValue is defined and
// has some sort of SIMPLE ColdFusion value in it.
// Now that we have a value, store it as a string in the ColdFusion
// query object. Remember again that my query names are ONE based
// for ColdFusion standards. That is why I am adding 1 to the
// cell index.
LOCAL.SheetData.Query[ "column#(LOCAL.ColumnIndex + 1)#" ][ LOCAL.SheetData.Query.RecordCount ] = JavaCast( "string", LOCAL.CellValue );
}
}
// At this point we should have a full query of data. However, if
// we were using a header row, then the header row was included in
// the final query. We do NOT want this. If we are using a header
// row, delete the first row of the query.
if (
ARGUMENTS.HasHeaderRow AND
LOCAL.SheetData.Query.RecordCount
){
// Delete the first row which is the header row.
LOCAL.SheetData.Query.RemoveRows(
JavaCast( "int", 0 ),
JavaCast( "int", 1 )
);
}
// Return the sheet object that contains all the Excel data.
return(
LOCAL.SheetData
);
</cfscript>
</cffunction>
<cffunction name="WriteExcel" access="public" returntype="void" output="false"
hint="Takes an array of 'Sheet' structure objects and writes each of them to a tab in the Excel file.">
<cfargument
name="FilePath"
type="string"
required="true"
hint="This is the expanded path of the Excel file."
/>
<cfargument
name="Sheets"
type="any"
required="true"
hint="This is an array of the data that is needed for each sheet of the excel OR it is a single Sheet object. Each 'Sheet' will be a structure containing the Query, ColumnList, ColumnNames, and SheetName."
/>
<cfargument
name="Delimiters"
type="string"
required="false"
default=","
hint="The list of delimiters used for the column list and column name arguments."
/>
<cfscript>
// Set up local scope.
var LOCAL = StructNew();
// Create Excel workbook.
LOCAL.WorkBook = CreateObject(
"java",
"org.apache.poi.hssf.usermodel.HSSFWorkbook"
).Init();
// Check to see if we are dealing with an array of sheets or if we were
// passed in a single sheet.
if (IsArray( ARGUMENTS.Sheets )){
// This is an array of sheets. We are going to write each one of them
// as a tab to the Excel file. Loop over the sheet array to create each
// sheet for the already created workbook.
for (
LOCAL.SheetIndex = 1 ;
LOCAL.SheetIndex LTE ArrayLen( ARGUMENTS.Sheets ) ;
LOCAL.SheetIndex = (LOCAL.SheetIndex + 1)
){
// Create sheet for the given query information..
WriteExcelSheet(
WorkBook = LOCAL.WorkBook,
Query = ARGUMENTS.Sheets[ LOCAL.SheetIndex ].Query,
ColumnList = ARGUMENTS.Sheets[ LOCAL.SheetIndex ].ColumnList,
ColumnNames = ARGUMENTS.Sheets[ LOCAL.SheetIndex ].ColumnNames,
SheetName = ARGUMENTS.Sheets[ LOCAL.SheetIndex ].SheetName,
Delimiters = ARGUMENTS.Delimiters
);
}
} else {
// We were passed in a single sheet object. Write this sheet as the
// first and only sheet in the already created workbook.
WriteExcelSheet(
WorkBook = LOCAL.WorkBook,
Query = ARGUMENTS.Sheets.Query,
ColumnList = ARGUMENTS.Sheets.ColumnList,
ColumnNames = ARGUMENTS.Sheets.ColumnNames,
SheetName = ARGUMENTS.Sheets.SheetName,
Delimiters = ARGUMENTS.Delimiters
);
}
// ASSERT: At this point, either we were passed a single Sheet object
// or we were passed an array of sheets. Either way, we now have all
// of sheets written to the WorkBook object.
// Create a file based on the path that was passed in. We will stream
// the work data to the file via a file output stream.
LOCAL.FileOutputStream = CreateObject(
"java",
"java.io.FileOutputStream"
).Init(
JavaCast(
"string",
ARGUMENTS.FilePath
)
);
// Write the workout data to the file stream.
LOCAL.WorkBook.Write(
LOCAL.FileOutputStream
);
// Close the file output stream. This will release any locks on
// the file and finalize the process.
LOCAL.FileOutputStream.Close();
// Return out.
return;
</cfscript>
</cffunction>
<cffunction name="WriteExcelSheet" access="public" returntype="void" output="false"
hint="Writes the given 'Sheet' structure to the given workbook.">
<cfargument
name="WorkBook"
type="any"
required="true"
hint="This is the Excel workbook that will create the sheets."
/>
<cfargument
name="Query"
type="any"
required="true"
hint="This is the query from which we will get the data."
/>
<cfargument
name="ColumnList"
type="string"
required="false"
default="#ARGUMENTS.Query.ColumnList#"
hint="This is list of columns provided in custom-ordered."
/>
<cfargument
name="ColumnNames"
type="string"
required="false"
default=""
hint="This the the list of optional header-row column names. If this is not provided, no header row is used."
/>
<cfargument
name="SheetName"
type="string"
required="false"
default="Sheet #(ARGUMENTS.WorkBook.GetNumberOfSheets() + 1)#"
hint="This is the optional name that appears in this sheet's tab."
/>
<cfargument
name="Delimiters"
type="string"
required="false"
default=","
hint="The list of delimiters used for the column list and column name arguments."
/>
<cfscript>
// Set up local scope.
var LOCAL = StructNew();
// Set up data type map so that we can map each column name to
// the type of data contained.
LOCAL.DataMap = StructNew();
// Get the meta data of the query to help us create the data mappings.
LOCAL.MetaData = GetMetaData( ARGUMENTS.Query );
// Loop over meta data values to set up the data mapping.
for (
LOCAL.MetaIndex = 1 ;
LOCAL.MetaIndex LTE ArrayLen( LOCAL.MetaData ) ;
LOCAL.MetaIndex = (LOCAL.MetaIndex + 1)
){
// Map the column name to the data type.
LOCAL.DataMap[ LOCAL.MetaData[ LOCAL.MetaIndex ].Name ] = LOCAL.MetaData[ LOCAL.MetaIndex ].TypeName;
}
// Create the sheet in the workbook.
LOCAL.Sheet = ARGUMENTS.WorkBook.CreateSheet(
JavaCast(
"string",
ARGUMENTS.SheetName
)
);
// Set a default row offset so that we can keep add the header
// column without worrying about it later.
LOCAL.RowOffset = -1;
// Check to see if we have any column names. If we do, then we
// are going to create a header row with these names in order
// based on the passed in delimiter.
if (Len( ARGUMENTS.ColumnNames )){
// Convert the column names to an array for easier
// indexing and faster access.
LOCAL.ColumnNames = ListToArray(
ARGUMENTS.ColumnNames,
ARGUMENTS.Delimiters
);
// Create a header row.
LOCAL.Row = LOCAL.Sheet.CreateRow(
JavaCast( "int", 0 )
);
// Loop over the column names.
for (
LOCAL.ColumnIndex = 1 ;
LOCAL.ColumnIndex LTE ArrayLen( LOCAL.ColumnNames ) ;
LOCAL.ColumnIndex = (LOCAL.ColumnIndex + 1)
){
// Create a cell for this column header.
LOCAL.Cell = LOCAL.Row.CreateCell(
JavaCast( "int", (LOCAL.ColumnIndex - 1) )
);
// Set the cell value.
LOCAL.Cell.SetCellValue(
JavaCast(
"string",
LOCAL.ColumnNames[ LOCAL.ColumnIndex ]
)
);
}
// Set the row offset to zero since this will take care of
// the zero-based index for the rest of the query records.
LOCAL.RowOffset = 0;
}
// Convert the list of columns to the an array for easier
// indexing and faster access.
LOCAL.Columns = ListToArray(
ARGUMENTS.ColumnList,
ARGUMENTS.Delimiters
);
// Loop over the query records to add each one to the
// current sheet.
for (
LOCAL.RowIndex = 1 ;
LOCAL.RowIndex LTE ARGUMENTS.Query.RecordCount ;
LOCAL.RowIndex = (LOCAL.RowIndex + 1)
){
// Create a row for this query record.
LOCAL.Row = LOCAL.Sheet.CreateRow(
JavaCast(
"int",
(LOCAL.RowIndex + LOCAL.RowOffset)
)
);
// Loop over the columns to create the individual data cells
// and set the values.
for (
LOCAL.ColumnIndex = 1 ;
LOCAL.ColumnIndex LTE ArrayLen( LOCAL.Columns ) ;
LOCAL.ColumnIndex = (LOCAL.ColumnIndex + 1)
){
// Create a cell for this query cell.
LOCAL.Cell = LOCAL.Row.CreateCell(
JavaCast( "int", (LOCAL.ColumnIndex - 1) )
);
// Get the generic cell value (short hand).
LOCAL.CellValue = ARGUMENTS.Query[
LOCAL.Columns[ LOCAL.ColumnIndex ]
][ LOCAL.RowIndex ];
// Check to see how we want to set the value. Meaning, what
// kind of data mapping do we want to apply? Get the data
// mapping value.
LOCAL.DataMapValue = LOCAL.DataMap[ LOCAL.Columns[ LOCAL.ColumnIndex ] ];
// Check to see what value type we are working with. I am
// not sure what the set of values are, so trying to keep
// it general.
if (REFindNoCase( "int", LOCAL.DataMapValue )){
LOCAL.DataMapCast = "int";
} else if (REFindNoCase( "long", LOCAL.DataMapValue )){
LOCAL.DataMapCast = "long";
} else if (REFindNoCase( "double", LOCAL.DataMapValue )){
LOCAL.DataMapCast = "double";
} else if (REFindNoCase( "float|decimal|real|date|time", LOCAL.DataMapValue )){
LOCAL.DataMapCast = "float";
} else if (REFindNoCase( "bit", LOCAL.DataMapValue )){
LOCAL.DataMapCast = "boolean";
} else if (REFindNoCase( "char|text|memo", LOCAL.DataMapValue )){
LOCAL.DataMapCast = "string";
} else if (IsNumeric( LOCAL.CellValue )){
LOCAL.DataMapCast = "float";
} else {
LOCAL.DataMapCast = "string";
}
// Cet the cell value using the data map casting that we
// just determined and the value that we previously grabbed
// (for short hand).
LOCAL.Cell.SetCellValue(
JavaCast(
LOCAL.DataMapCast,
LOCAL.CellValue
)
);
}
}
// Return out.
return;
</cfscript>
</cffunction>
<cffunction name="WriteSingleExcel" access="public" returntype="void" output="false"
hint="Write the given query to an Excel file.">
<cfargument
name="FilePath"
type="string"
required="true"
hint="This is the expanded path of the Excel file."
/>
<cfargument
name="Query"
type="query"
required="true"
hint="This is the query from which we will get the data for the Excel file."
/>
<cfargument
name="ColumnList"
type="string"
required="false"
default="#ARGUMENTS.Query.ColumnList#"
hint="This is list of columns provided in custom-order."
/>
<cfargument
name="ColumnNames"
type="string"
required="false"
default=""
hint="This the the list of optional header-row column names. If this is not provided, no header row is used."
/>
<cfargument
name="SheetName"
type="string"
required="false"
default="Sheet 1"
hint="This is the optional name that appears in the first (and only) workbook tab."
/>
<cfargument
name="Delimiters"
type="string"
required="false"
default=","
hint="The list of delimiters used for the column list and column name arguments."
/>
<cfscript>
// Set up local scope.
var LOCAL = StructNew();
// Get a new sheet object.
LOCAL.Sheet = GetNewSheetStruct();
// Set the sheet properties.
LOCAL.Sheet.Query = ARGUMENTS.Query;
LOCAL.Sheet.ColumnList = ARGUMENTS.ColumnList;
LOCAL.Sheet.ColumnNames = ARGUMENTS.ColumnNames;
LOCAL.Sheet.SheetName = ARGUMENTS.SheetName;
// Write this sheet to an Excel file.
WriteExcel(
FilePath = ARGUMENTS.FilePath,
Sheets = LOCAL.Sheet,
Delimiters = ARGUMENTS.Delimiters
);
// Return out.
return;
</cfscript>
</cffunction>
</cfcomponent>

To test this, I created a multi page Excel file that has some food information for different imaginary meals:

To read the Excel file above, I would do this:

Launch code in new window » Download code as text file »

<cfset objPOIUtility = CreateObject(
"component",
"POIUtility"
).Init()
/>
<cfset strFilePath = ExpandPath( "./meals.xls" ) />
<!---
Read the Excel document into an array of Sheet objects.
Each sheet object will contain the data in the Excel
sheet as well as some other property-type information.
--->
<cfset arrExcel = objPOIUtility.ReadExcel(
FilePath = strFilePath,
HasHeaderRow = true
) />

Dumping out the arrExcel array, we get to take a look at the data that gets returned:

Notice that when reading in the Excel file, I only passed in the FilePath and the HasHeaderRow flag. This will precipitate all sheets to be read in. If, however, I passed in the optional argument, SheetIndex, only the given sheet would be read in and the return structure would be a single Sheet objects as follows:

Launch code in new window » Download code as text file »

<!---
Read in only the Lunch sheet. This is the seocnd Sheet
of the Excel file, but since Java is ZERO-based, we are
going to request the first sheet. This will return a
Sheet object rather than an array of Sheet objects.
--->
<cfset objSheet = objPOIUtility.ReadExcel(
FilePath = strFilePath,
HasHeaderRow = true,
SheetIndex = 1
) />

Notice that the only difference in this example is that we passed in the SheetIndex. CFDumping out the objSheet, we get:

Now, what you can't see is that all values from the Excel are stored in the resultant ColdFusion queries as CF_SQL_VARCHAR values. I figure this is the easiest way to deal with the data. I don't mind leaving it up to the ColdFusion programmer to figure out how to use this data. This might be fixed going forward, but so far I am fine with handling it that way.

Now, that covers reading in the Excel files, which is an arguably easier task. Writing Excel files is a bit more complicated. To simplify things, especially while I am learning how to use POI with ColdFusion, I am not giving any formatting options. You can set up header rows, but other than that, data is written to the Excel sheet based on the SQL column type and nothing else. No additionally formatting is applied. One step at a time, please!

Writing works in a similar way to the reading of Excel files; you can write an array of query "objects" to multiple tabs or you can write a single query to a file.

Let' start off writing the meals.xls query data that we read in before (since we already have those ColdFusion queries in memory). We can't just send those objects back into the Write methods as the required structures are not quite the same. Let's create an array of new Sheet objects and then write those to the Excel:

Launch code in new window » Download code as text file »

<!---
Create an array to define the sheets that we
want to pass in. We are going to use the queries that
we read in previously.
--->
<cfset arrSheets = ArrayNew( 1 ) />
<!---
Set up a sheet for the Breakfast meal. We can get a default
structure from the POI utility (as below) or we could just
create our own struct of the same type (but this is a nice
short hand and easy to debug).
--->
<cfset arrSheets[ 1 ] = objPOIUtility.GetNewSheetStruct() />
<cfset arrSheets[ 1 ].Query = objSheet[ 1 ].Query />
<cfset arrSheets[ 1 ].SheetName = "NEW Breakfast" />
<cfset arrSheets[ 1 ].ColumnList = "column1,column2,column3" />
<cfset arrSheets[ 1 ].ColumnNames = "Food,Quantity,Tastiness" />
<cfset arrSheets[ 2 ] = objPOIUtility.GetNewSheetStruct() />
<cfset arrSheets[ 2 ].Query = objSheet[ 2 ].Query />
<cfset arrSheets[ 2 ].SheetName = "NEW Lunch" />
<cfset arrSheets[ 2 ].ColumnList = "column1,column2,column3" />
<cfset arrSheets[ 2 ].ColumnNames = "Food,Quantity,Tastiness" />
<cfset arrSheets[ 3 ] = objPOIUtility.GetNewSheetStruct() />
<cfset arrSheets[ 3 ].Query = objSheet[ 3 ].Query />
<cfset arrSheets[ 3 ].SheetName = "NEW Dinner" />
<cfset arrSheets[ 3 ].ColumnList = "column1,column2,column3" />
<cfset arrSheets[ 3 ].ColumnNames = "Food,Quantity,Tastiness" />
<!---
Now that we have our array of Sheet objects, we can write
them to a new Excel file.
--->
<cfset objPOIUtility.WriteExcel(
FilePath = ExpandPath( "./new_meals.xls" ),
Sheets = arrSheets
) />

Opening the resultant new_meals.xls file, you will see that it is a duplicate of the original XLS file with new Tab names:

You may notice that in the original Excel file, the Quantity column had numeric values and that in the new Excel file, the quantity column has numbers stored as string values. This is because when the Excel file gets read in, all values get stored as numbers. Then, when writing the queries back to Excel, the POIUtility.cfc ColdFusion component sees that the query column has VARCHAR values and writes them back to the Excel file as strings. This is a byproduct of the demo (reading and writing the same file), not of an error in the Write methods.

The Sheets object that gets passed in was an array, but this could have been a single Sheet object as well that would have written a single-tab Excel file. If you are interested in writing just a single-tab file without creating the intermediary Struct, you could use the WriteSingleExcel() method:

Launch code in new window » Download code as text file »

<!---
When writing a single file, just grab the breakfast
meal from the previous read.
--->
<cfset objPOIUtility.WriteSingleExcel(
FilePath = ExpandPath( "./single_meal.xls" ),
Query = objSheet[ 1 ].Query,
ColumnList = "column1,column2,column3",
ColumnNames = "Food,Quantity,Tastiness",
SheetName = "SINGLE Breakfast"
) />

When we open up the resultant Excel file, you will see that we have a single tab with the NEW tab name:

That about sums it up. Like I said before, I wrote this this morning so it has not been field testing. But, I have used it to generate some sweet-ass multi-tab reports so far and I am loving it. Hope this can help some people.

Download Code Snippet ZIP File

Post Comment | Ask Ben | Permalink | Other Searches | Print Page

You Might Also Be Interested In:

Newer Post
Different Use-Style Of FOR Loop In ColdFusion CFScrpit Tag

Older Post
SQL ISZERO() And NULLIF() For Dividing By Zero

Reader Comments

Derek Perez

Jan 15, 2007 at 7:17 PM // reply »

4 Comments

1 Points

Jesus Christ, this is amazing!!!!11!!1

Make a project on RIAForge NOW! :)

Ken Auenson, II

Jan 15, 2007 at 7:17 PM // reply »

30 Comments

30 Points

Looks good Ben!
I should have some free time later this week to play with it.

Boyan

Jan 15, 2007 at 7:42 PM // reply »

95 Comments

1 Points

Damn, this is nice! Where were you a few weeks ago when I needed to write native Excel?! Doh! I'll definitely use this in the future. Great job!

Matthew

Jan 15, 2007 at 8:40 PM // reply »

41 Comments

78 Points

Well Done Ben! Thank you for sharing your work. Keep it coming.

Ameen

Jan 16, 2007 at 2:18 AM // reply »

15 Comments

36 Points

BIG THANKS Ben, Just in this week we were thinking about how to read Excel XML Exported File bu coldfusion, But even WRITING; thats !!!AMAZING!!!.

Ben Nadel

Jan 16, 2007 at 7:35 AM // reply »

5,406 Comments

14,070 Points

Thanks Fellas. Glad that this could help.

I will look into RIAForge. I have downloaded stuff from it, but never looked into setting stuff up.

Antony

Jan 30, 2007 at 5:15 PM // reply »

8 Comments

1 Points

Hi Ben

I looked into the POI stuff for Excel a while ago and came up with a bunch of methods for writing - didn't look into reading as I didn't need it at that time.

I ended up with methods that created styles (date, currency, etc), write sheet headers and footers for printed output, freeze panes, add sheets, rows and cells and setting column widths.

A great resource for me was at http://www.d-ross.org/index.cfm?objectid=9C65ECEC-508B-E116-6F8A9F878188D7CA and http://www.d-ross.org/index.cfm?objectid=9C65ED5A-508B-E116-6F4F7F38C6AE167C

Let me know if you want to 'innovate' any of this stuff.

Antony

Cenk

Feb 8, 2007 at 7:54 AM // reply »

2 Comments

1 Points

Hi Ben,

Thanks for the great work, are you thinking about including the cell comments feature?

Cenk

Ben Nadel

Feb 8, 2007 at 8:24 AM // reply »

5,406 Comments

14,070 Points

@Cenk,

I am not sure what you mean by cell comments feature? I am not an Excel master, so I keep my stuff mostly to reading and writing. What are cell comments?

Ben Nadel

Feb 8, 2007 at 8:26 AM // reply »

5,406 Comments

14,070 Points

@Antony,

Yeah, I first found POI information on d-ross's site. It was a great starting place. Once I saw that, I then found the actual POI Java docs and kind of just started dreaming from there.

Antony

Feb 8, 2007 at 7:48 PM // reply »

8 Comments

1 Points

Yeah, sounds like we followed the same path. It's a great hammer to have in your toolkit, makes all the other excel generation options look decidely crap.

Ben Nadel

Feb 8, 2007 at 8:02 PM // reply »

5,406 Comments

14,070 Points

Yeah, POI is awesome... and best of all it COMES WITH ColdFusion. That right there makes it a step above the rest.

Cenk

Feb 8, 2007 at 11:45 PM // reply »

2 Comments

1 Points

Ben,

"Excel cell comment" is basically a text entry which can pop-up on mouse-over.

In excel, you can try to add a cell comment by selecting "Insert Comment" from right mouse button menu.

You can see the updated documents about using it via poi hssf at http://jakarta.apache.org/poi/hssf/quick-guide.html#CellComments

Cheers,

Cenk

Ben Nadel

Feb 9, 2007 at 7:33 AM // reply »

5,406 Comments

14,070 Points

Hmmm, good to know (even for regular Excel usage). I will take a look into this. Thanks.

David Cooke

Feb 11, 2007 at 2:51 PM // reply »

4 Comments

1 Points

Hi Ben,

I'm just familiarising myself with this cool component, however, I'm running into an issue when reading data in from a spreadsheet and then writing it out again. It's all fine reading the data in, but writing it is throwing an error because the meta data of the query is an object and not an array:

Object of type class java.lang.Class cannot be used as an array, line 666.

// Loop over meta data values to set up the data mapping.
for (
LOCAL.MetaIndex = 1 ;
LOCAL.MetaIndex LTE ArrayLen( LOCAL.MetaData ) ;
LOCAL.MetaIndex = (LOCAL.MetaIndex + 1)
)

Abhishek

Feb 12, 2007 at 7:27 AM // reply »

1 Comments

1 Points

Hi Ben,

I was trying to read the xls file using this component but it ended up with an error that ELEMENT ROW NOT DEFINED IN LOCAL...

Could you please help me out!!!1

Ben Nadel

Feb 12, 2007 at 8:36 AM // reply »

5,406 Comments

14,070 Points

Abhishek,

Please send me the XLS file that you are working with and I will debug. Send to ben [ at ] bennadel [ dot ] com. Thanks.

Ben Nadel

Feb 12, 2007 at 8:38 AM // reply »

5,406 Comments

14,070 Points

@David,

That is strange. I have not encountered that issue before. Please send me the XLS file you are working with and I will debug. Thanks.

Send to ben [ at ] bennadel [ dot ] com. Thanks.

Bryan

Feb 12, 2007 at 3:56 PM // reply »

2 Comments

1 Points

Do you have any comparative metrics on processing time using your component versus interracting with the MS Excel COM object directly from CF? I wrote a component a couple years ago for my work that managed the various difficulties inherent in dealing with the MS COM directly.

This worked well for quite a while, but eventually the sheer volume of data was too much for my CFC to handle. We've since abandoned the CF-to-Excel COM in favor of writing our own COM wrappers in VB and C++ and accessing them as using CFOBJECT in CF.

The obvious downsides to this is 1) requires MS be installed on server, 2) requires our DLLs be installed on server (not a problem for us though) and 3) debugging and development requires a recompile and CF server bounce on each iterration.

Essentially, I'm wondering if this method is fast-enough to handle the creation of 5000+ line spreadsheets?

Ben Nadel

Feb 12, 2007 at 4:00 PM // reply »

5,406 Comments

14,070 Points

I don't have much in the way of metrics. I created a 9,000 record file the other day and the page took about 7 seconds to execute. But, reading, I assume is faster than writing (I am not basing that on experience). I am not sure how well that scales or if that number is accurate as far as multiple runs of the page.

So far though, it seems like the SQL statements to get the data seem to be the limiting factor in most of the reports that I write.

Ben Nadel

Feb 12, 2007 at 4:02 PM // reply »

5,406 Comments

14,070 Points

To All,

The reason Dave is erroring out is that GetMetaData() only added Query support in MX 7. So, if you are not using MX 7, this will fail. I am working on adding an optional argument that will replace the GetMetaData() for people using MX6.

Bryan

Feb 12, 2007 at 4:10 PM // reply »

2 Comments

1 Points

Thanks Ben, I'll check it out then. I don't think it's right for converting all our existing exports and imports to (there's too many and they work fine for now), but we're developing a new architecture for all future clients, and this looks like it could provide a nice advantage in development time. You don't of any techniques for doing this type of automation for PPT do you? LOL

Ben Nadel

Feb 12, 2007 at 4:11 PM // reply »

5,406 Comments

14,070 Points

Ha ha ha, PPT. Give me a year :)

Emmim44

Mar 20, 2007 at 11:36 AM // reply »

1 Comments

1 Points

I am getting this err when trying to read the excel file..

Parameter validation error for function QUERYADDCOLUMN.
The function takes 3 parameters.

The error occurred in D:\CFusionMX\wwwroot\POIUtility.cfc: line 285

283 : // my column indexes at ONE rather than ZERO to get it back
284 : // into a more ColdFusion standard notation.
285 : QueryAddColumn(
286 : LOCAL.SheetData.Query,
287 : "column#(LOCAL.ColumnIndex + 1)#",

Ben Nadel

Mar 20, 2007 at 12:08 PM // reply »

5,406 Comments

14,070 Points

@Emmim44,

That is a really strange error. The number of arguments are not variable (in the code). Can you maybe send me the XLS document you are using so I can track down the error?

ben[ at ]bennadel[ dot ]com

Phillip Senn

Mar 21, 2007 at 12:48 PM // reply »

16 Comments

1 Points

Ben,

I'm on a shared hosting environment (Hostmysite.com).

You reference org.apache.poi.poifs.filesystem.POIFSFileSystem in your component.
I'm assuming I should
download the Apache POI library poi-bin-2.5.1-final-20040804.zip file from http://jakarta.apache.org/poi/
and extract the zip file to a new subdirectory called org?

Ben Nadel

Mar 21, 2007 at 12:55 PM // reply »

5,406 Comments

14,070 Points

The POI stuff ships with ColdFusion. As long as it has not removed from the installation (can that even be done???) you should be fine.

Brian

Apr 5, 2007 at 8:21 AM // reply »

11 Comments

72 Points

I have used a number of the POI methods successfully, but I have been searching for a couple more or their equivelant without success.

1) Essentially I'm trying to do a copy and paste on a range of cells. The Excel Macro equivelant is:
Range("D1:Q28").Select
Selection.Copy
Range("C1").Select
ActiveSheet.Paste

2) Remove Columns (like the RemoveRows method example shown below)
// Delete the first row which is the header row.
LOCAL.SheetData.Query.RemoveRows(
JavaCast( "int", 0 ),
JavaCast( "int", 1 )
);

Any suggestions?

Matthew

Apr 5, 2007 at 4:59 PM // reply »

41 Comments

78 Points

@Brian,

I dont know of a way right now where you can delete a column like you can a row with POI. you could do a loop and use the removeCell() for that column. that isnt the best solution im sure. I dont know whats available in version 3.0 of POI.

Im not sure about the other part of your question. I dont really know how you can do that.

-Matthew

Brian Oeding

Apr 6, 2007 at 5:56 PM // reply »

11 Comments

72 Points

I have your wrapper (04/04/2007 version) working for reading my multisheet workbook and I was able to manipulate the query data. Now I want to overwrite the spreadsheet data back into the old spreadsheet (about the 4th sheet). I tried the WriteExcelSheet method, but realize that this won't work because the filepath is not given if I use the WriteExcelSheet directly. I get "The selected method CreateCellStyle was not found." at 75 : LOCAL.Style = ARGUMENTS.WorkBook.CreateCellStyle();

I also tried using WriteSingleExcel because I am only interested in the one sheet, but it blows away my other sheets.

Ideas how I can do what I want here? Thanks for your help.

Ben Nadel

Apr 9, 2007 at 8:56 AM // reply »

5,406 Comments

14,070 Points

@Brian,

What you want to do is certainly doable. POI Utility cannot currently handle that though. I can try to update it, but other than telling you it is possible, I have no better advice at this time. Sorry.

Brian Oeding

Apr 9, 2007 at 9:02 AM // reply »

11 Comments

72 Points

That's fine. However, it would be great if you could update it. Thanks.

Jean

Apr 24, 2007 at 10:49 AM // reply »

7 Comments

1 Points

Ben,

When I sue your writeExcel method I get the following error:

Element TYPENAME is undefined in a CFML structure referenced as part of an expression.

which occurs here:

1542 : // Map the column name to the data type.
1543 : LOCAL.DataMap[ LOCAL.MetaData[ LOCAL.MetaIndex ].Name ] = LOCAL.MetaData[ LOCAL.MetaIndex ].TypeName;

I'm running CFMX 7, do you know how to get around this issue?

Ben Nadel

Apr 24, 2007 at 10:58 AM // reply »

5,406 Comments

14,070 Points

@Jean,

Are you positive that you are using MX 7? GetMetaData() only started working nicely for Query objects in 7. If you are using 6, this will break.

Jean

Apr 24, 2007 at 11:07 AM // reply »

7 Comments

1 Points

Ben,

It's really weird. When I call WriteSingleExcel it works but when I call Write Excel it won't... And my Server.ColdFusion.ProductVersion = 7,0,2,142559

Do you want me to email you my code?

Ben Nadel

Apr 24, 2007 at 11:10 AM // reply »

5,406 Comments

14,070 Points

Email me your Excel document, let me try it on my end... ben [ at ] bennadel [ dot ] com.

David

May 25, 2007 at 9:50 AM // reply »

8 Comments

45 Points

Ben, I am also getting the typename error, and I am running version 7. This is based on the exact demo code you have listed on this site.

Did you figure out how to help jean?

David

May 25, 2007 at 9:54 AM // reply »

8 Comments

45 Points

Fixed my issue.

If you are using your own Query, be sure to add the optional parametters to the QueryNew function that defined the typenames of the columns.

This will NOT work
<cfset testQuery = QueryNew("column1,column2,column3")>

This will work
<cfset testQuery = QueryNew("column1,column2,column3","VarChar,VarChar,VarChar")>

Ben Nadel

May 25, 2007 at 10:19 AM // reply »

5,406 Comments

14,070 Points

@Dave,

Yeah, that is a subtle point. If you do not explicitly define your column types, then ColdFusion does its best to guess the data by looking at the first 50 rows (I think). It then decides what the common data type is and uses that.

Jean

May 25, 2007 at 10:50 AM // reply »

7 Comments

1 Points

The problem comes from queries of queries and the type of metadata passed along with your Column Headers(POIUtility won't deal with char or NChar or int, they have to be comverted to varchar and integer). This might vary from databases but here we still use SQL Server 2000. I had to add this cfscript code and format the metadata before calling the POIUtility:

//Extract Columns MetaData out of rs_report
LOCAL = StructNew();
LOCAL.MetaData = GetMetaData(rs_report);
ColumnTypeString = "";

//Loop over reportHeader and find match in rs_reportMetadata before assigning to ColumnListType --->
for(i=1; i LTE ListLen(reportHeaders,","); i=i+1){

currentHeader = ListGetAt(reportHeaders,i,",");

//Loop over Each piece of Metadata until there is a match
for(j=1; j LTE ArrayLen(LOCAL.MetaData); j=j+1){

if (LOCAL.MetaData[ j ].Name EQ currentHeader){

//replace char types by varChar
if(LOCAL.MetaData[ j ].TypeName EQ "char"){

LOCAL.MetaData[ j ].TypeName = "VarChar";

}

//replace nChar types by varChar
if(LOCAL.MetaData[ j ].TypeName EQ "nchar"){

LOCAL.MetaData[ j ].TypeName = "VarChar";

}

//replace int by Integer
if(LOCAL.MetaData[ j ].TypeName EQ "int"){

LOCAL.MetaData[ j ].TypeName = "Integer";

}

if (ColumnTypeString EQ ""){

ColumnTypeString = LOCAL.MetaData[ j ].TypeName;
break;

}else{

ColumnTypeString = ColumnTypeString & "," & LOCAL.MetaData[ j ].TypeName;
break;

}

}

}//end for loop

Also note that when you call the utility you have to pass all arguments on one line as such:

<cfset poiArgs.ColumnNames = "Applicant,Certification Applied for,Command/Organization,Home State">

I tried to stack them for readability and I kept getting a "Query XX is nto indexable by Column YY" error. Also, if you can find the function to autosize columns when the excel template is generated please share ! =)

Jean

David

May 25, 2007 at 11:09 AM // reply »

8 Comments

45 Points

Jean I have my issue solved with what I posted. Question for everyone, I can't find any detail on how to right an excel sheet with formatting, is this possible?

Jean

May 25, 2007 at 11:55 AM // reply »

7 Comments

1 Points

David,
I know your solution fixes your problem but mine might benefit people who want to give their POIUtility more flexibility. Your solution works great if you know the type of data coming in and you are aware of the issue. The code above lets you use POIUtility with different queries and lets people worry about the "what" instead of the "how".

Cheers!

John Allen

May 30, 2007 at 10:08 AM // reply »

1 Comments

1 Points

THANK YOU THANK YOU THANK YOU THANK YOU!!!!

I love it! totally made me look super cool at work.

Your blog rocks as well.

John Allen

Ben Nadel

May 30, 2007 at 6:23 PM // reply »

5,406 Comments

14,070 Points

@John,

Thanks a lot :) Also, be sure you have the most up-to-date version of the POI Utility:

http://www.bennadel.com/blog/624-ColdFusion-POIUtility-cfc-Updates-And-Bug-Fixes.htm

Feras Nabulsi

Aug 8, 2007 at 2:34 PM // reply »

4 Comments

1 Points

This is a great start for what I am looking for. very impressive. one of the columns I am retrieving from the excel file is an ID that matches what I had in a database. Based on this ID, I want to query the database to update one of the fields with new values from the database. How can I modify your code to query my database based on that first column that has the unique ID?

Thank you
Feras

Ben Nadel

Aug 8, 2007 at 3:36 PM // reply »

5,406 Comments

14,070 Points

@Feras,

Take a look at this:

http://www.bennadel.com/index.cfm?dax=blog:517.view

It talks about doing just that (sort of)... at least it talks about Database stuff and POI.

Faith R. Sloan

Aug 20, 2007 at 11:58 AM // reply »

1 Comments

1 Points

Here is the solution to your problem Emmim44.

I Deleted third parameter of four, "CF_SQL_VARCHAR" since the QueryAddColumn function only takes on 3 parameters in CF 6.1. Only allows "query", "column-name", and "array-name" to be passed. CF MX 7+ takes on 4 parameters: "query", "column-name",optional "datatype", and "array-name". Since I implemented this within a client's CF MX 6.1 application, the reference to the "datatype" parameter was deleted. Faith R. Sloan 17August2007.

Thanx again, Ben for the awesome detailed job you've done with this. I implemented it in CSVToQuery function. (pre-POI Wrapper development)

Have Fun everyone!

-------
Emmim44 wrote the following and Ben's response is at the bottom.
------
I am getting this err when trying to read the excel file..

Parameter validation error for function QUERYADDCOLUMN.
The function takes 3 parameters.

The error occurred in D:\CFusionMX\wwwroot\POIUtility.cfc: line 285

283 : // my column indexes at ONE rather than ZERO to get it back
284 : // into a more ColdFusion standard notation.
285 : QueryAddColumn(
286 : LOCAL.SheetData.Query,
287 : "column#(LOCAL.ColumnIndex + 1)#",

Posted by Emmim44 on Mar 20, 2007 at 11:36 AM

@Emmim44,

That is a really strange error. The number of arguments are not variable (in the code). Can you maybe send me the XLS document you are using so I can track down the error?

ben[ at ]bennadel[ dot ]com

Posted by Ben Nadel on Mar 20, 2007 at 12:08 PM

Warren Koch

Jan 28, 2008 at 12:19 PM // reply »

1 Comments

1 Points

I have been using POI for quite some time with great success. I use it to read in a template file (since I include macros in the excel) and then write CF data to it. Works nice. The only thing I found was that I had to limit the rows to about 10000 since I am pushing out a lot of data. POI eats memory like candy and I needed to limit it otherwise the server would crash.

I've made my own CF wrappers and have fought my way through cell manipulation, etc. to do the job I needed. My stuff is not as well thought out as yours and is very much designed for something specific. However, there may be things in it of interest to you or that you may want to incorporate. If you wish the code, please let me know and I'll email it to you.

Simon Bingham

Feb 1, 2008 at 6:42 AM // reply »

3 Comments

1 Points

Hello,

I have just started using poiUtility.cfc, but I am getting an error message when I call the WriteSingleExcel function.

---------------
The QUERY argument passed to the WriteSingleExcel function is not of type query.

If the component name is specified as a type of this argument, its possible that a definition file for the component cannot be found or is not accessible.

The error occurred in C:\ColdFusion8\wwwroot\Websites\_NewWebsiteTemplate\components\poiUtility.cfc: line 1799
Called from C:\ColdFusion8\wwwroot\Websites\_NewWebsiteTemplate\parsed\c_subscribers.generateextractfile.cfm: line 30
Called from C:\ColdFusion8\wwwroot\Websites\_NewWebsiteTemplate\fusebox5\fusebox5.cfm: line 179
Called from C:\ColdFusion8\wwwroot\Websites\_NewWebsiteTemplate\index.cfm: line 14

1797 :
1798 :
1799 : <cffunction name="WriteSingleExcel" access="public" returntype="void" output="false"
1800 : hint="Write the given query to an Excel file.">
1801 :

---------------

However, the argument I am passing as the query is definately of type query. The query is being returned by another CFC.

Does anyone know what might be causing this error?

Jean Ducrot

Feb 1, 2008 at 8:39 AM // reply »

7 Comments

1 Points

Simon,

Are all your query column types defined? If you are creating a query from scratch or doing a query of query you want to make sure to define them when you create the new object or POI utility will give you an error.

Cheers,

Jean

Simon Bingham

Feb 1, 2008 at 8:54 AM // reply »

3 Comments

1 Points

Jean,

Thank you for your response. The query I am using is a query of a query. How would I ensure my query column types defined properly?

Jean

Feb 1, 2008 at 9:38 AM // reply »

7 Comments

1 Points

Here is the cfscript is wrote to extract column types out of another query object

//Extract Columns MetaData out of rs_report
LOCAL = StructNew();
LOCAL.MetaData = GetMetaData(rs_report);
ColumnTypeString = "";

//Loop over reportHeader and find match in rs_reportMetadata before assigning to ColumnListType
for(i=1; i LTE ListLen(reportColumNames,","); i=i+1) {
currentHeader = ListGetAt(reportColumNames,i,",");
//Loop over Each piece of Metadata until there is a match
for(j=1; j LTE ArrayLen(LOCAL.MetaData); j=j+1) {
if (LOCAL.MetaData[ j ].Name EQ currentHeader) {
//replace char types by varChar
if(LOCAL.MetaData[ j ].TypeName EQ "char") {
LOCAL.MetaData[ j ].TypeName = "VarChar";
}
//replace nChar types by varChar
if(LOCAL.MetaData[ j ].TypeName EQ "nchar") {
LOCAL.MetaData[ j ].TypeName = "VarChar";
}
//replace int by Integer
if(LOCAL.MetaData[ j ].TypeName EQ "int") {
LOCAL.MetaData[ j ].TypeName = "Integer";
}
if (ColumnTypeString EQ "") {
ColumnTypeString = LOCAL.MetaData[ j ].TypeName;
break;
} else {
ColumnTypeString = ColumnTypeString & "," & LOCAL.MetaData[ j ].TypeName;
break;
}
}
}//end for loop
}//end loop

Let me know if you need it by email

Jean

Simon Bingham

Feb 1, 2008 at 9:56 AM // reply »

3 Comments

1 Points

Jean,

Thank you for the code. Are you able to provide an example of how I can integrate this into my code?

Thanks,

Simon

Jean

Feb 1, 2008 at 10:07 AM // reply »

7 Comments

1 Points

OK here is the entire script and I might have used some of Ben's code in there too. Basically I plug this in after retrieving some data from an SQL 2000 server (rs_report) and before passing the new query object to POIutility.cfc. I create a new query object for each excel sheet if we have more than one. I did that because excel get really flustered if you try to stick more than 50000 rows in one sheet (64k is the max). I posted the first part earlier but the full monty should show you how i extract the types and assign them to the right columns in the new query object.

<cfscript>
reportMaxRows = 50000; //64k is Excel maxrow 50k doesn't test it and improves load time in case it gets there
if(rs_Report.RecordCount LT reportMaxRows) {
//set all arguments to be pass to POIUtility
poiArgs = StructNew();
poiArgs.FilePath = reportFilePath;
poiArgs.Query = rs_report;
poiArgs.ColumnNames = reportColumNames;
poiArgs.ColumnList = reportColumList;
poiArgs.SheetName = Mid(reportTitle,1,30);
poiArgs.HeaderCSS = "font: bold 10pt arial; text-align: center";

//pass them
APPLICATION.camp.POIUtility.WriteSingleExcel(argumentCollection = poiArgs);
//Multiple Sheets Excel Doc
} else if (rs_Report.RecordCount GTE reportMaxRows) {
//Set Loop Vars
myStartRow = 1;
myEndRow = reportMaxRows;
totalSheets = Ceiling(rs_report.recordCount/reportMaxRows);

//Extract Columns MetaData out of rs_report
LOCAL = StructNew();
LOCAL.MetaData = GetMetaData(rs_report);
ColumnTypeString = "";

//Loop over reportHeader and find match in rs_reportMetadata before assigning to ColumnListType
for(i=1; i LTE ListLen(reportColumNames,","); i=i+1) {
currentHeader = ListGetAt(reportColumNames,i,",");
//Loop over Each piece of Metadata until there is a match
for(j=1; j LTE ArrayLen(LOCAL.MetaData); j=j+1) {
if (LOCAL.MetaData[ j ].Name EQ currentHeader) {
//replace char types by varChar
if(LOCAL.MetaData[ j ].TypeName EQ "char") {
LOCAL.MetaData[ j ].TypeName = "VarChar";
}
//replace nChar types by varChar
if(LOCAL.MetaData[ j ].TypeName EQ "nchar") {
LOCAL.MetaData[ j ].TypeName = "VarChar";
}
//replace int by Integer
if(LOCAL.MetaData[ j ].TypeName EQ "int") {
LOCAL.MetaData[ j ].TypeName = "Integer";
}
if (ColumnTypeString EQ "") {
ColumnTypeString = LOCAL.MetaData[ j ].TypeName;
break;
} else {
ColumnTypeString = ColumnTypeString & "," & LOCAL.MetaData[ j ].TypeName;
break;
}
}
}//end for loop
}//end loop

//Create Array to Hold Sheets Data
arrSheets = ArrayNew(1);

//Loop over rs_report to populate arrSheets.Query--->
for(Sheet=1; Sheet LTE totalSheets; Sheet = Sheet + 1) {
SheetName = Mid(reportTitle,1,28) & "_" & Sheet;
//Get Sheet Structure from POIUtility
arrSheets[ Sheet ] = APPLICATION.camp.POIUtility.GetNewSheetStruct();
arrSheets[ Sheet ].SheetName = SheetName;
arrSheets[ Sheet ].ColumnList = reportColumNames;
arrSheets[ Sheet ].ColumnNames = reportColumList;
//query object to hold slice of rs_report
arrSheets[ Sheet ].Query = QueryNew(reportColumNames,ColumnTypeString);
Row = 1;
loopEndRow = myEndRow - myStartRow;

//Loop over rs_report
for(Row=1; Row LTE loopEndRow; Row = Row + 1) {
QueryAddRow(arrSheets[ Sheet ].Query);
//set rs-report current row
rs_row = (myStartRow + Row) - 1;
//loop over each columns
for(k=1; k LTE ListLen(reportColumNames,","); k=k+1) {
arrSheets[ Sheet ].Query[ ListGetAT(reportColumNames,k) ][ Row ] = rs_report[ ListGetAT(reportColumNames,k) ][ rs_row ];
}//end column loop
}//end row loop

//Update start and end rows
myStartRow = myEndRow + 1;
if((myEndRow + reportMaxRows) GT rs_report.recordCount) {
myEndRow = rs_report.recordCount;
} else {
myEndRow = myEndRow + reportMaxRows;
}
}//End for loop over sheets

//set all arguments to be pass to POIUtility
poiArgs = StructNew();
poiArgs.FilePath = reportFilePath;
poiArgs.Sheets = arrSheets;
poiArgs.HeaderCSS = "font: bold 10pt arial; text-align: center";
//Create Excel Doc and append sheets
APPLICATION.camp.POIUtility.WriteExcel(argumentCollection = poiArgs);
}
</cfscript>

Josh

Mar 10, 2008 at 3:11 PM // reply »

4 Comments

1 Points

What's the difference between min column count and max column count?
I see in your dump you have 3 and 3. I have a test Excel file that's showing 0 and 4. I have 4 columns.

Ben Nadel

Mar 10, 2008 at 3:18 PM // reply »

5,406 Comments

14,070 Points

@Josh,

Not all rows will have the same number of cells in them as they are being read from the sheet. These values are simply done to demonstrate the extremes on either end. They don't necessarily serve a purpose.

Josh

Mar 10, 2008 at 3:40 PM // reply »

4 Comments

1 Points

@Ben

I was kind of figuring they didn't serve a purpose but I wanted to double-check before deleting them from code (to speed up processing a bit) as I don't think they'll serve a purpose for my needs (at least, at the moment).

Thanks for the prompt response.

Ben Nadel

Mar 10, 2008 at 3:48 PM // reply »

5,406 Comments

14,070 Points

Yeah yeah, rip em out. I think the only thing they *MIGHT* do is the Max column might be used to build the QueryNew( "" ) column list. Just do a search in the code for the value.

Esanjor

Jul 9, 2008 at 3:58 AM // reply »

1 Comments

1 Points

Is there any problem for excel any version or just the latest one is applicable.

Ben Nadel

Jul 9, 2008 at 8:16 AM // reply »

5,406 Comments

14,070 Points

@Esanjor,

Excel 2007 does not work for *reading* XLS files with the current POI version that ships with ColdFusion. However, I believe that 2007 can read files created by the current POI package.

Gov

Sep 5, 2008 at 12:35 PM // reply »

6 Comments

1 Points

Who uses Excel 2007 anyway ;o)
Just wanted to say: Amazing work, Ben! Your work is once again so much appreciated!

Alexander Reichl

Dec 11, 2008 at 6:30 AM // reply »

4 Comments

22 Points

Hi all,

thanks for sharing such great work, although it didn't work for me. I tried several components based on the POI, but none has worked for me perfectly and I'm not skilled enough yet, to find the error.

Unfortunately, if my sheet contains empty cells, the function getCellType() returns me undefined. If I fill those fields I receive the next error:
Element SHEETDATA.QUERY.RECORDCOUNT is undefined in LOCAL

Is anyone able to help me?

Thanks in advance.

Alex

Ben Nadel

Dec 11, 2008 at 8:14 AM // reply »

5,406 Comments

14,070 Points

@Alex,

There was a NULL bug in the earlier versions of my project. Make sure you have the newest version:

http://www.bennadel.com/projects/poi-utility.htm

Alexander Reichl

Dec 11, 2008 at 8:20 AM // reply »

4 Comments

22 Points

Hi Ben and thank you for support. I downloaded the latest version and now I can read all excel files.

It is a really great job you've done and if you need a donation, I'm willing to make one, because I'm earning my money with these kind of scripts.

Just let me know.

Kind regards,
Alex

Ben Nadel

Dec 11, 2008 at 8:23 AM // reply »

5,406 Comments

14,070 Points

@Alex,

Thanks man. I'm just happy to help out.

Don

Feb 24, 2009 at 2:03 PM // reply »

16 Comments

161 Points

Gol durn it Ben, I should have known just to come to your site. My big boss came down to me yesterday with these 2 HUGE spread sheets and wants me to merge them and come up with a bunch of reports based off of them.
I was thinking "I wonder if I can just load them into CF, make a query of each, and then do all the work on them?"
So I searched the internet high and low and finally say .... your site. Doh.

->Erasing all other CF links<-

Maybe I can figure out how to do this and XSLT to make a way kewl (COLD!) app for the future.

Ben Nadel

Feb 24, 2009 at 2:48 PM // reply »

5,406 Comments

14,070 Points

@Don,

Ha ha, awesome! Glad I could help. If you get stuck, let me know.

Anabel Fernandez

Apr 9, 2009 at 4:53 PM // reply »

4 Comments

38 Points

Hello to everyone:

This has been a great tool so far but I am having a problem with the aliases in the cell.cfm look where it reads

It is giving me the error "The element at position 0 cannot be found." ...because the code below evaluates to 0.

VARIABLES.RowTag.CellIndex MOD ArrayLen( VARIABLES.DocumentTag.ColumnLookup )

Is it there a quick way to fix this?

VARIABLES.RowTag.CellIndex=52
ArrayLen( VARIABLES.DocumentTag.ColumnLookup ) =26

Thanks in advance.

Anabel Fernandez

Apr 9, 2009 at 10:10 PM // reply »

4 Comments

38 Points

Hi guys:

This is how I fixed the issue with aliases.

IIF(VARIABLES.RowTag.CellIndex MOD ArrayLen( VARIABLES.DocumentTag.ColumnLookup ) eq 0,"VARIABLES.DocumentTag.ColumnLookup[ ArrayLen( VARIABLES.DocumentTag.ColumnLookup ) ]" ,"VARIABLES.DocumentTag.ColumnLookup[ VARIABLES.RowTag.CellIndex MOD ArrayLen( VARIABLES.DocumentTag.ColumnLookup ) ]")

Ahsan Shahzad

Jun 16, 2009 at 10:08 AM // reply »

1 Comments

10 Points

hi Ben,

Great Great work you did!!!
Thanks for that and the way you explain the stuff is quite cool.

Just one suggestion, please update the code for POIUtility on this main article too as i was banging my head at wall to capture an error but while reading all comments i came across this url:
http://www.bennadel.com/blog/624-ColdFusion-POIUtility-cfc-Updates-And-Bug-Fixes.htm

that solved my issues.

Thanks again for articles and please keep them coming.

Ben Nadel

Jun 19, 2009 at 7:32 PM // reply »

5,406 Comments

14,070 Points

@Ahsan,

Yeah, updating the old posts is a good idea, but definitely something very error prone. I don't have a good way to deal with that; one might argue it's not inline with the concept of a blog.

I added related posts to bottom of the blog, which should help. Also, this POI wrapper has been broken out into its own project:

http://www.bennadel.com/projects/poi-utility.htm

veeru

Jul 1, 2009 at 4:49 AM // reply »

1 Comments

10 Points

Hi ben,
Iam able to upload xls docs perfectly.But have a problem if that xl sheet have a filter on it.

Could you plz advise.

Thanks in advance.

Ben Nadel

Jul 1, 2009 at 8:28 AM // reply »

5,406 Comments

14,070 Points

@Veeru,

I am sorry, I do not have a good solution for this.

Post Comment | Ask Ben

Author Name:
Author Email:
Author Website:
Comment:	Formatting: <strong>bold</strong> <em>italic<em>
	Remember my information
	Subscribe to entry *
	Send me a copy of this comment (as part of the comment thread)

matt catt

Jul 4, 2009 at 4:00 AM

thanks ben, I'm not a big fan of contracts so to find your no no-nesense ToS generator has helped me no end. all the best matt ... read »

Justice

Jul 3, 2009 at 11:10 PM

Create A Running Average Without Storing Individual Values

@Ben, I think you're going about this the wrong way. You're trying to use complicated techniques when there is a simple and beautiful technique readily available (a la Gary Funk's comment). Instead ... read »

Bob

Jul 3, 2009 at 9:19 PM

Project HUGE: Huge In A Hurry - Get Big - Phase 3 / Week 1

a good technical explanation http://crossfitphoenix.typepad.com/crossfit_phoenix_forging_/the-overhead-squat.html ... read »

Gary Funk

Jul 3, 2009 at 9:03 PM

Create A Running Average Without Storing Individual Values

If I wanted to do this and only carry two numbers, I'd keep track of the sum and N. Then you are pretty much accurate all the time. average = (sum + new_number) / (N + 1) But all this was in a for ... read »

Roland Collins

Jul 3, 2009 at 8:58 PM

Create A Running Average Without Storing Individual Values

@Martin - not just floating point though. Depending on what langauge you're working in, decimals can cause just as many headaches if they're not precise enough. But again, for most applications, th ... read »

Isnogood

Jul 3, 2009 at 7:16 PM

Project HUGE: Huge In A Hurry - Get Big - Phase 3 / Week 1

Watch this http://www.nsca-lift.org/videos/default.shtml ... read »

Aaron

Jul 3, 2009 at 7:13 PM

Project HUGE: Get Big, Phase One (Chat Waterbury - Huge In A Hurry)

I've just finished the 3rd week of phase 3, and have to agree that the overhead squats are hard. I think this is most due to the wide grip on which places more pressure on your upper back. Only this ... read »

Isnogood

Jul 3, 2009 at 7:11 PM

Project HUGE: Huge In A Hurry - Get Big - Phase 3 / Week 1

Very good, there were some near perfect reps, and there were some dodgy ones, but you're getting there your body position is good. Work on your depth and do not let the bar move forward or backward, ... read »

ColdFusion Component Wrapper For POI To Read And Write Excel Files

GetNewSheetStruct()

ReadExcel()

ReadExcelSheet()

WriteExcel()

WriteExcelSheet()

WriteSingleExcel()

What Other People Are Searching For

You Might Also Be Interested In:

Reader Comments

Post A Comment