Coldfusion Spreadsheet Blank Cells

902 views Asked by At

I'm trying to write a script to upload an excel sheet into a database, but I'm stuck on a small issue. The script needs to be dynamic so that it doesn't need to change to upload a different excel sheet, simply the sheet must follow the same convention as before.

So, by doing:

<cfloop from="2" to="#sheet.rowcount#" index="row">

It will loop to the end of the rows, no?

Plot twist: I have to convert blank cells into "N/A"

Easy enough:

<cfif SpreadsheetGetCellValue(sheet,row,col) EQ "">
    <cfset SpreadsheetSetCellValue(sheet,"N/A",row,col) />
</cfif>

Issue: this causes dozens of rows to appear on my dummy output (at the end, after the rows have stopped containing anything)

N/A
N/A
N/A
N/A
N/A
N/A
N/A
N/A
N/A
N/A
N/A
N/A
N/A
N/A
N/A
N/A
N/A

N/A
N/A
N/A
N/A
N/A
N/A
N/A
N/A
N/A
N/A
N/A
N/A
N/A
N/A
N/A
N/A
N/A

Very much repeated. The sheet is 56 rows long, but this has to exceed 100. I fear changing the blank cells into "N/A" is the culprit, but I'm not sure how to fix it. Here is the complete file, and unfortunately I must not show the excel sheet. However it is 56 rows, and 17 columns.

<cfspreadsheet action="read" src="derp.xlsx" name="sheet"> <!--- edit src --->
<!--- 17 columns wide 56 rows --->
<cfoutput>
    <cfloop from="2" to="#sheet.rowcount#" index="row">
        <cfloop from="1" to="17" index="col"> <!--- multi row selection (edit based on excel sheet) --->
        <cfif SpreadsheetGetCellValue(sheet,row,col) EQ "">
            <cfset SpreadsheetSetCellValue(sheet,"N/A",row,col) />
        </cfif>
        <cfoutput>#SpreadSheetGetCellValue(sheet,row,col)#</cfoutput><br />
        </cfloop>
        <br />
    </cfloop>
</cfoutput>

How can I convert blank cells to "N/A" without the script surpassing the rowcount?

0

There are 0 answers