5

Today I came across a very odd case while reading a vlue from a spreadsheet and trying to filter them on a condition and a create a spreadsheet from the filtered data. Here are my steps

  1. Read Excel sheet

    <cfspreadsheet action="read" src="#local.sFilePath#"    excludeHeaderRow="true" headerrow ="1" query="local.qExcelData" sheet="1" />

  2. Create a holding Query

    <cfset local.columnNames = "LoanNumber,Product," />
<cfset local.qSuccessData = queryNew(local.columnNames,"VarChar,VarChar") />

  3. Filter the Excel returned query on a condition and add the valid ones into the new Holding query

    <cfloop query="local.qExcelData" >
        <cfif ListFind(local.nExceptionRowList,local.qExcelData.currentrow) EQ 0>
          <cfset queryAddRow(local.qSuccessData) />
          <cfset querySetCell(local.qSuccessData, 'LoanNumber', local.qExcelData['Loan Number']) />
          <cfset querySetCell(local.qSuccessData, 'Product', local.qExcelData['Product']) />
        </cfif>
</cfloop>

  4. Create the new spreadsheet

    <cfspreadsheet action="write" query="local.qSuccessData" filename="#local.sTempSuccessFile#" overwrite="true">

However I am getting the following content in my excel sheet

Loannumber                           Product
coldfusion.sql.column@87875656we    coldfusion.sql.column@89989ER

Please help on this to get it work.

shemy
  • 573
  • 1
  • 5
  • 15
  • 2
    FWIW, `coldfusion.sql.column@87875656we` is the string representation of a complex object (like a whole query column, rather than one of the individual values within that column). The first part is the object's class name: `coldfusion.sql.column` and the second is the object's [hashcode value](https://en.wikipedia.org/wiki/Java_hashCode%28%29). – Leigh Oct 08 '15 at 16:14
  • @Leigh, Thanks for your clarification. – shemy Oct 08 '15 at 16:18
  • 1
    @Leigh, that was an informative que – Yoosaf Abdulla Oct 08 '15 at 16:19
  • 1
    @shemy - You are welcome and +1 for a "well written" question :) – Leigh Oct 08 '15 at 16:43

1 Answers1

5

I believe the query loop is not mapping values to the Holding-Query properly.

Please modify your loop as below:

<cfloop query="local.qExcelData" >
    <cfif ListFind(local.nExceptionRowList,local.qExcelData.currentrow) EQ 0>
      <cfset queryAddRow(local.qSuccessData) />
      <cfset querySetCell(local.qSuccessData, 'LoanNumber', local.qExcelData['Loan Number'][currentRow]) />
      <cfset querySetCell(local.qSuccessData, 'Product', local.qExcelData['Product'][currentRow]) />
    </cfif>
</cfloop>
Yoosaf Abdulla
  • 3,722
  • 4
  • 31
  • 34