0

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" >
J Vaden
  • 1
  • 1

1 Answers1

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.

Dan Bracuk
  • 20,699
  • 4
  • 26
  • 43
  • 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: spObj = SpreadsheetNew("Data"); SpreadsheetAddRows(spObj,#getdata#); SpreadsheetAddRow(spObj,"Date,Name",1,1); SpreadsheetCreateSheet(spObj, "Month"); SpreadSheetSetActiveSheetNumber(spObj, 2); – J Vaden Feb 17 '17 at 15:41
  • 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); } SpreadSheetWrite(spObj, "#filepath##strFileName#.xls","yes"); – J Vaden Feb 17 '17 at 15:54