Coldfusion (Railo 3.3.4)使用 POI 元件 寫出 XLSX 檔案

補充前一篇 Coldfusion (Railo 3.3.4) 使用 POI 元件 讀取 xls/xlsx 修改記錄

我在 POIUtility.cfc 內加入一個 Function 用來處理寫出 XLSX 格式檔案

cfc 程式碼(增加)：


<!--- 自訂寫出 XLSX 的 EXCEL 檔案--->
    <cffunction name="WriteExcelX" 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();
 
<!--- 使用 XSSFWorkbook 處理 XLSX 格式 --->
var WorkBookFactory = CreateObject("java"," org.apache.poi.xssf.usermodel.XSSFWorkbook").Init();

           
LOCAL.WorkBook = WorkBookFactory;


            // 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>



測試程式：


<!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#">

<!--- 一個 Excel 檔案裡面有多個 Sheet ---->
<!--- 產生一個空的陣列用來接收多個 Sheet 資料 --->
<cfset newExcel = ArrayNew()>
<cfset sheets = 0>
<cfscript>

for(sheet in myExcel){
sheets = sheets + 1;
objSheet = POIobj.GetNewSheetStruct();

objSheet.ColumnList = sheet.QUERY.ColumnList;
objSheet.ColumnNames = ArrayToList(sheet.COLUMNNAMES,",");
objSheet.Query = sheet.Query;
objSheet.SheetName = sheet.NAME;
newExcel[sheets] = objSheet;
}

POIobj.WriteExcelX(FilePath ="C:/Temp/exercises-1.xlsx",
   Sheets = newExcel
);
</cfscript>

<!--- 如果只有一個 Sheet 就不需要產生 Array --->
<!--- 我只從來源取出一個 Sheet 來輸出 --->
<cfscript>
  objSheet = POIobj.GetNewSheetStruct();

  objSheet.ColumnList = myExcel[1].QUERY.ColumnList;
  objSheet.ColumnNames = ArrayToList(myExcel[1].COLUMNNAMES,",");
  objSheet.Query = myExcel[1].Query;
  objSheet.SheetName = myExcel[1].NAME;

  POIobj.WriteExcelX(FilePath ="C:/Temp/exercises-2.xlsx",
   Sheets = objSheet
);
</cfscript>

</body>
</html>


原始檔案內容：

輸出檔案內容：
exercises-1.xlsx

exercises-2.xlsx

這樣確實可以輸出，

只是要注意的是從 ReadExcelX 讀出來的 myExcel 物件不可以直接丟到 WriteExcelX 去，畢竟這兩個 Function 產出和輸入的結構上有些不一樣，所以必須重新處理過才能使用。

還有一點就是 ReadExcelX 可以自動辨識 XLS 或 XLSX 檔案，但是 WriteExcelX 只能寫出 XLSX 檔案，所以標題少了 XLS 字樣。

原因是讀取時使用 org.apache.poi.ss.usermodel.WorkbookFactory 可以自動識別來源格式，但是寫出檔案時，必須指明使用 HSSF 還是 XSSF 來產生檔案，才能確定是產出 XLS 或 XLSX 格式。

而 WriteExcelX 內部是使用 XSSF 來處理，因此只能輸出 XLSX 檔案。