cfspreadsheet causing variable undefined error

Question

I'm attempting to read a spreadsheet that has predefined charts on multiple tabs using cfspreadsheet, but when it comes to processing the data, I get variable is undefined.

I've used the example from Adobe - http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec17cba-7f87.html

I've also looked at other examples, but those examples use a combination of cfspreadsheet and poi or java and I would prefer to only use cfspreadsheet if possible. Any idea looking at my code below where the problem is coming from? Thanks.

<cfcontent type="application/vnd.ms-excel" reset="Yes">

<cfquery name="GetData" datasource="#request.dsn#">
SELECT *
FROM v_Occurrences
</cfquery>

<cfset strFileName = "OccurrenceData" & "#now().getTime()#" & "#UserID#">
<!---
<cfdump var="#GetData#">
--->

<cfset filepath = "file:///...OccurenceData.xls">
<!--- Write the two sheets to a single file --->
<cfspreadsheet 
    action  ="read" 
    columnnames = "Occurence_Date,Full_Name_Client"
    columns = "2"
        excludeHeaderRow = "false" 
    headerrow = "1"
    query="GetData" 
    rows    = "2"
    src ="#filepath#">  

<cfscript> 
    OccurrenceData = SpreadsheetNew("Data"); 
    Month = SpreadsheetNew("Month"); 
    Person = SpreadsheetNew("Person");  
    SpreadsheetAddRows(occurrencedata,getdata); 
</cfscript> 

<!--- Write the two sheets to a single file --->
<cfspreadsheet 
    action="write" 
    filename="#filepath#"
    name="OccurrenceData"   
    overwrite="true"
        sheetname="Data" > 


<cfspreadsheet 
    action="Update" 
    filename="#filepath#" 
    name="Month"
        sheetname="Month"> 

<cfspreadsheet 
    action="Update" 
    filename="#filepath#" 
        name="Person" 
        sheetname="Person" >

<cfspreadsheet 
    action="read" 
    src="#filepath#" 
    sheetname="occurrencedata" 
    query="GetData">

Here's where the error occurs: variable Occurrence_Data is undefined

<cfscript> 
    SpreadsheetAddRow(OccurrenceData,"Date,Name",1,1); 
    SpreadsheetAddRow(OccurrenceData, 
    "#Dateformat(Occurrence_Date,'mm/dd/yyyy')#,#Full_Name_Client#", 
    2,1,true); 
</cfscript> 

<cfspreadsheet 
    action="write" 
    autosize="true"
    filename="#strFileName#.xls"
    overwrite="true"
    password="password"
    query="GetData"
        sheetname="Data" >

Dan Bracuk · Accepted Answer · 2017-02-15 20:26:32Z

0

Problem number 1. This is probably doing more harm than good.

<cfcontent type="application/vnd.ms-excel" reset="Yes">

Problem number 2. If you want to write two sheets to a single file, you don't do this:

OccurrenceData = SpreadsheetNew("Data"); 
Month = SpreadsheetNew("Month");

You do something like this:

OccurrenceData = SpreadsheetNew("Data"); 
// do stuff with this sheet
SpreadsheetCreateSheet(OccurrenceData, "Month");
SpreadSheetSetActiveSheetNumber(OccurrenceData, 2);
// do stuff with this sheet

SpreadSheetWrite(OccurrenceData, whateverYourFileNameIs);

I also suggest that you do one thing at a time. Write a bit of code, run it, and look at the results. Then add a bit more code, run it, and look at the results. This will help you isolate the code that causes the error.

answered Feb 15, 2017 at 20:26

Dan Bracuk

20.8k5 gold badges30 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

J Vaden Over a year ago

So I adjusted my code and simplified it to the below and am not getting any errors, but I'm also not getting an excel sheet - just a blank screen. I knew I needed to adjust the SpreadsheetAddRow line to loop my data so you can see the changes below: <cfscript> spObj = SpreadsheetNew("Data"); SpreadsheetAddRows(spObj,#getdata#); SpreadsheetAddRow(spObj,"Date,Name",1,1); SpreadsheetCreateSheet(spObj, "Month"); SpreadSheetSetActiveSheetNumber(spObj, 2); </cfscript>

J Vaden Over a year ago

<cfloop query="#GetData#"> <cfscript> spObj = SpreadsheetNew("Data"); for (i = 1; i <= GetData.recordcount; i ++) { SpreadsheetAddRow(spObj,"#Dateformat(getdata.Occurrence_Date,'mm/dd/yyyy')#,#GetData.Full_Name_Client#",2,1,true); } </cfscript> </cfloop> <cfscript> SpreadSheetWrite(spObj, "#filepath##strFileName#.xls","yes"); </cfscript>

Collectives™ on Stack Overflow

cfspreadsheet causing variable undefined error

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related