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
?