Coldfusion (Railo 3.3.4) 使用 POI 元件 讀取 xls/xlsx 修改記錄
這是配合 上一篇 『Coldfusion直接讀取/產生Excel檔案 (使用POI元件)』進階改版
主要原因是上一篇的功能只能讀取 XLS檔案，但是對於XLSX卻沒有辦法處理。
如果升級 POI的版本到最新版，會造成 RAILO 內建的 Apache 無法支援該套件版本。
經過一翻苦戰，終於搞定如何處理：
首先，RAILO 3.3.4(註一) 以後因為有支援 cfspreadsheet 指令，所以內建了 POI 套件，但是這個指令僅能處理 XLS 檔案，所以可想而知這個套件是非常陽春的，更不用說要能夠讀取 XLSX 檔案了。
也因為這個原本就存在的套件，導致如果自己擴充 POI 套件，常常會不能正常運作，因為舊指令功能函數不完全，但是在系統上又是非常高優先執行權，因此會和自行擴充 POI 套件函數衝突。
所以必須進行取捨，如果要使用自己的 POI 套件，就必須刪除系統內建的 POI 套件，並且盡量不使用 cfspreadsheet 指令。
首先，找到 Railo 系統路徑下 lib/ext/apache-poi-tm-extractors.jar 這個檔案，先將其刪除。(服務必須先停止)
然後，到 Apache POI 官網 下載下面幾個檔案
poi-3.11.jar
poi-ooxml-3.11.jar
ooxml-schemas-1.1.jar
xmlbeans-bin-3.1.0.zip
poi-ooxml-3.11.jar
ooxml-schemas-1.1.jar
xmlbeans-bin-3.1.0.zip
上面 3 個 jar 可以直接丟到 lib/ext 資料夾內
xmlbeans-bin-3.1.0.zip必須先解壓縮，解開後裡面有 lib 資料夾，把裡面4個jar檔
resolver.jar
xmlbeans-3.1.0.jar
xmlbeans-3.1.0-javadoc.jar
xmlbeans-3.1.0-sources.jar
xmlbeans-3.1.0.jar
xmlbeans-3.1.0-javadoc.jar
xmlbeans-3.1.0-sources.jar
複製到 Railo下 lib/ext 內
然後啟動服務，只要沒有甚麼錯誤就大致完成。
由於，我的 POI 套件使用 3.11 版本，因此必須使用 3.11 版本的操作方式。
這個 POIUtility.cfc 出處來自 BENEL ，中間有中文註解的是我新創的功能函數，其他都沒改動
<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.">
<!--- Return This reference. --->
<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.">
<!--- Define the local scope. --->
<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.">
<!--- Define arguments. --->
<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>
<!---自創通用讀取 XLS/XLSX 檔案--->
<cffunction name="ReadExcelX" 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.">
<!--- Define arguments. --->
<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();
var FileStream = CreateObject(
"java",
"java.io.FileInputStream"
).Init(
ARGUMENTS.FilePath
);
<!--- 使用 WorkbookFactory 通盤處理 --->
var WorkBookFactory = CreateObject("java"," org.apache.poi.ss.usermodel.WorkbookFactory").Init();
LOCAL.WorkBook = WorkBookFactory.create(FileStream);
// 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.">
<!--- Define arguments. --->
<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.">
<!--- Define arguments. --->
<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.">
<!--- Define arguments. --->
<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.">
<!--- Define arguments. --->
<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>
使用方式如下：
因此，讀取 XLS/XLSX 都可以通用。
讀取XLSX檔：
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=big5" />
<title>無標題文件</title>
</head>
<body>
<!--- 建立並初始化POI物件 --->
<cfset POIobj = CreateObject("component","COM.POIUtility").Init() >
<!--- 取得我要測試的Excel檔案路徑. --->
<cfset myFile = ExpandPath( "./exercises.xlsx" ) >
<cfoutput>#myFile#<br /></cfoutput>
<!--- 測試：透過POI物件讀取Excel檔案，並指定此excel檔案有標題列(第一行) --->
<cfset myExcel = POIobj.ReadExcelX(FilePath = myFile,HasHeaderRow = true) >
<!--- 傾印出內容 --->
<cfdump var="#myExcel#">
</body>
</html>
讀取XLS檔：
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=big5" />
<title>無標題文件</title>
</head>
<body>
<!--- 建立並初始化POI物件 --->
<cfset POIobj = CreateObject("component","COM.POIUtility").Init() >
<!--- 取得我要測試的Excel檔案路徑. --->
<cfset myFile = ExpandPath( "./exercises.xls" ) >
<cfoutput>#myFile#<br /></cfoutput>
<!--- 測試：透過POI物件讀取Excel檔案，並指定此excel檔案有標題列(第一行) --->
<cfset myExcel = POIobj.ReadExcelX(FilePath = myFile,HasHeaderRow = true) >
<!--- 傾印出內容 --->
<cfdump var="#myExcel#">
</body>
</html>
至於寫出檔案，在這一篇 Coldfusion (Railo 3.3.4)使用 POI 元件 寫出 XLSX 檔案
- 註一：Railo 4.0.4 以後版本因為自帶完整POI套件，所以不需要安裝上述的POI元件
留言