cfspreadsheet

Weird.  What's your CF version? It seemed to work ok w/CF11.  I started with a blank workbook with 6 sheets.


Then ran the test code:

<cfspreadsheet action="read"
        src="c:\temp\MyBook.xlsm"
        name="RawData"
        SHEET="6" />

<cfdump var="#RawData#">		

<!--- populate demo query with a few rows of data --->
<cfset q = queryNew("PropertyID","cf_sql_varchar")>
<cfloop from="1" to="5" index="row">
	<cfset queryAddRow(q, 1)>
	<cfset q.PropertyID[row] = "PropertyID "& row>
</cfloop>

<cfdump var="#q#">

<cfscript>
SpreadsheetAddRows(RawData,q); 
</cfscript>
        
		
<cfspreadsheet action="write" overwrite="true"
        filename="c:\temp\MyBook.xlsm"
        name="RawData" />

Select allOpen in new window

           
And the data was added to the 6th sheet as expected:

Just for grins, any difference if you use functions instead of cfspreadsheet ?

        <!--- use a different file name for testing, just to rule out possible file locking issues --->
        <cfset SpreadSheetWrite(RawData, "c:\temp\DifferentFileName.xlsm", true)>

So I have been trying a whole bunch of things trying to figure this out.   The problem I think is that I have null values in the query - not all of of them are populated.     I changed it out to a straight, clean CFQUERY and then a straight

<cfspreadsheet action="write" overwrite="true"
        filename="#application.loadpath#\#Session.NewWorking#"
        sheetname="Data-HUDStat Report" query="PropertiesClosed" />

Select allOpen in new window

and get


An exception occurred while using action=write.

java.lang.NullPointerException

I tried

spreadsheetSetCellValue(sObj,#propertyid#,#currentrow#+1,1);
spreadsheetSetCellValue(sObj,#propertyname#,#currentrow#+1,2);
spreadsheetSetCellValue(sObj,#pha_name#,#currentrow#+1,3);
spreadsheetSetCellValue(sObj,#dateformat(complete180, "mm/dd/yyyy")#,#currentrow#+1,4);
spreadsheetSetCellValue(sObj,#dateformat(accepted, "mm/dd/yyyy")#,#currentrow#+1,5);
spreadsheetSetCellValue(sObj,#dateformat(RCCIssuedDate, "mm/dd/yyyy")#,#currentrow#+1,6);
spreadsheetSetCellValue(sObj,#dateformat(actclosedate, "mm/dd/yyyy")#,#currentrow#+1,7);
spreadsheetSetCellValue(sObj,#nounitconvert#,#currentrow#+1,8);
spreadsheetSetCellValue(sObj,#ReReplaceNoCase(cccost,"[^0-9.]","","ALL")#,#currentrow#+1,9);

Select allOpen in new window

and that works but I didn't know how to loop the query in cfscript and read/write for 1500 lines is stupid.   So these null values...

Your sample didn't work either.   I just tried it.   WTH!!!

I have 10 locally and 11 on the production server and it doesn't work there either.

My file is an .xlsm file - maybe that is the issue?

The rest of it writes though - why would that be an issue?

Didn't even work with the sample query? Now that's really weird...

I tried creating some null values manually:

         <!--- deliberately make these NULL --->
         <cfset row = queryAddRow(q, 1)>
          <cfset q.RCCIssuedDate[row] = javacast("null", "")>
         <cfset q.PropertyID[row] = javacast("null", "")>

and via a db query - but so far no luck reproducing that error.

<cfquery name="q" datasource="#myDSN#">
      SELECT NULL AS PropertyID, NULL AS RCCIssuedDate UNION ALL
      SELECT '1' AS PropertyID, getDate() AS RCCIssuedDate
</cfquery>

What version of CF? There's some major differences in the spreadsheet stuff between 9, 10 and 11.

>> My file is an .xlsm file - maybe that is the issue?

I don't see why, but ... yes I was wondering that too.  I was going to ask if you get the same error with .xlsx and/or .xls files too.

I just tried .xlsx and that didn't work either - I can write all I want but I can't write a row.    The only thing that has worked is a loop but I don't know how to loop a query in cfscript so I only have to write once.    It is too slow to read/write each time - 1500 lines...   We are using 10 and 11.  Doesn't work on either of them.

spreadsheetSetCellValue(sObj,#propertyid#,#currentrow#+1,1);
spreadsheetSetCellValue(sObj,#propertyname#,#currentrow#+1,2);
spreadsheetSetCellValue(sObj,#pha_name#,#currentrow#+1,3);
spreadsheetSetCellValue(sObj,#dateformat(complete180, "mm/dd/yyyy")#,#currentrow#+1,4);
spreadsheetSetCellValue(sObj,#dateformat(accepted, "mm/dd/yyyy")#,#currentrow#+1,5);
spreadsheetSetCellValue(sObj,#dateformat(RCCIssuedDate, "mm/dd/yyyy")#,#currentrow#+1,6);
spreadsheetSetCellValue(sObj,#dateformat(actclosedate, "mm/dd/yyyy")#,#currentrow#+1,7);
spreadsheetSetCellValue(sObj,#nounitconvert#,#currentrow#+1,8);
spreadsheetSetCellValue(sObj,#ReReplaceNoCase(cccost,"[^0-9.]","","ALL")#,#currentrow#+1,9);

Select allOpen in new window

I think CF10 supports query for loops.  Does this test work for you under CF10?

<cfscript>
q  = queryNew("");
queryAddColumn(q, "colA", [1,18,46,45]);
queryAddColumn(q, "colB", [1,18,46,45]);
for (row in q ){
    writeDump(row.colA);
    writeDump(row.colB);
}
</cfscript>

That does - so how do I translate writeDump for the code above?

As usual - THANK YOU!

You're welcome. Anytime :)