Appending Record to Existing Spreadsheet

Today I am working appending records into existing spreadsheet. I don’t know if any direct method available in POIUtility to append record, but rather than doing googling on this I have applied below logic because of time constraint.

1. Read Spreadsheet and store data into query.
2. Append new record into query.
3. Overwrite existing file with new query data.

This might really simple but I have blog it to mention one bug with reading file from POIUtility. In my case I have 1000 records which I like to append into excel file in bunch of 100 records, means 10 times I have read spreadsheet and append data.

Let me come to original problem, first time I was able to write record without any problem but when I read files once created through POIUtility it seem that last column was missing. Spreadsheet data was correct but anyhow data read from spreadsheet through POIUtility object skip last column from it. As a solution I have added extra dummy column into spread sheet to get rid of it, but original spreadsheet was still have dummy column which client may not accept it.

Below is sample example to append data into spreadsheet.

[code:cf]<!— CREATE POI OBJECT —>
<cfset objPOI = CreateObject("component","cfc.POIUtility").Init()/>
<!— READ TEST.XLS FILE —>
<cfset arrSheets = objPOI.ReadExcel(FilePath = ExpandPath( "./test.xls" ),HasHeaderRow = true) />
<!— STORE TOTAL —>
<cfset totalRec = arrSheets[1].query.recordCount>
<cfset qExcel = arrSheets[1].query>
<!— Add extra dummy column in query object—>
<cfquery name="qAppend" dbtype="query">
select *,” as dummy from qExcel
</cfquery>
<!— APPEND SOME MORE ROWS IN QUERY —>
<cfloop from="1" to="5" index="i">
<cfset totalRec = totalRec + 1>
<cfset newRow = QueryAddRow(qAppend, 1)>
<cfset temp = QuerySetCell(qAppend, "column1", "Rec 1 #totalRec-1#", totalRec)>
<cfset temp = QuerySetCell(qAppend, "column2", "Rec 2 #totalRec-1#", totalRec)>
<cfset temp = QuerySetCell(qAppend, "column3", "Rec 3 #totalRec-1#", totalRec)>
<cfset temp = QuerySetCell(qAppend, "dummy", "", totalRec)>
</cfloop>
<!— WRITE QUERY DATA BACK TO SPREADSHEET. —>
<cfset objSheet = objPOI.GetNewSheetStruct() />
<cfset objSheet.Query = qAppend />
<cfset objSheet.ColumnList = "#qAppend.columnList#" />
<cfset objSheet.ColumnNames = "col1,col2,col3,dummy" />
<cfset objSheet.SheetName = "Sheet1" />
<cfset objPOI.WriteExcel(
FilePath = ExpandPath( "./test.xls" ),
Sheets = objSheet
) />[/code]