<--- --------------------------------------------------------------------------------------- ---- Blog Entry: ColdFusion Component Wrapper For POI To Read And Write Excel Files Author: Ben Nadel / Kinky Solutions Link: http://www.bennadel.com/index.cfm?dax=blog:474.view Date Posted: Jan 15, 2007 at 6:47 PM ---- --------------------------------------------------------------------------------------- ---> // 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 ); // 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 ); } // 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 ); // 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; // 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; // 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;