Wednesday, 14 August 2013

reading Excel (93-97) sheet with more than 65536 rows using cfspreadsheet

reading Excel (93-97) sheet with more than 65536 rows using cfspreadsheet

Hi I have to deal with data that is more than 65536 rows. and hence it
comes into 2 different Excel Sheets, named as "Details" and "Details_1".
I am using a component-function to read these 2 sheets. The idea is cfif
Query recordcount () from "cfspreadsheet" is more than 65533, then read
the second sheet too. Then use QoQ and UNION ALL to create a combined
query.
It worked fine for till for some time. then all of a sudden it stopped
working. I am not sure about wrong/error had crept into it that is causing
it to stop. the following is my code
<cffunction name="putExcel" access="remote" returnFormat="plain"
output="true">
<cfargument name="xclfile" required="no" type="string" default="0">
<cfset ins
=insertUserLog("#Session.user_name#","#Session.user_code#","putExcel
function called for Upload","","")>
<cftry>
<cfset fileEXCL =
"#ExpandPath('../folder')#/#arguments.xclfile#" />
<!---when there e 2 Sheets --->
<!---get info from sheet1 as a "query1"--->
<cfspreadsheet action="read" src="#fileEXCL#" sheet="1"
query="Query1" headerrow="1" />
<!--- recordcount for "sheet1" as "count1"--->
<cfset count1 =#Query1.recordcount#>
<!--- case when excel has more than 65533 rows
;THIS IMPLIES THAT THERE 2 SHEETS)--->
<cfif count1 gt 65533>
<!--- take info from sheet 2 as a "query2" and count as
"count2"--->
<cfspreadsheet action="read" src="#fileEXCL#"
sheet="2" query="Query2" headerrow="1" />
<cfset count2 =#Query2.recordcount#>
<!---club both query's using QoQ and call it
"excelQuery"--->
<cfquery dbtype="query" name="excelQuery">
SELECT * FROM Query1
UNION ALL
SELECT * FROM Query2
</cfquery>
<!---total record count for "sheet1" & "sheet2"--->
<cfset rowCount =#excelQuery.recordcount#>
<cfelse>
<!---this case there is just 1 query "Query1" ;rename
it "excelQuery"--->
<cfquery dbtype="query" name="excelQuery">
SELECT * FROM Query1
</cfquery>
<!--- recordcount for "sheet1"--->
<cfset rowCount =#excelQuery.recordcount#>
</cfif>
<cflog file="Collections" application="yes"
text="#Session.user_info.uname# logged in. Data file
#fileEXCL# read. Recordcount:#rowCount#" type="Information">
<cfset ins
=insertUserLog("#Session.user_name#","#Session.user_code#","file
#fileEXCL# read. ","Recordcount:#rowCount#","")>
<cfcatch type="any" >
<cflog file="Collections" application="yes" text="Error in
reading Data file #fileEXCL#." type="Error">
<cfset ins
=insertUserLog("#Session.user_name#","#Session.user_code#","error
file","failed","#cfcatch.Message#")>
<cfreturn 1>
</cfcatch>
</cftry>
Thanks in advance

No comments:

Post a Comment