Help please - Read an image to excel

Hi,

How can I read an image in excel?

<cfoutput> 

 <CFIF isDefined("StatusReptChart") and #StatusReptChart.RecordCount# gt 0>   
 <cfsavecontent variable="ChartSource"> 
    <cfchart
         	format="png"
       		query="StatusReptChart"
			showlegend="true"
         	pieslicestyle="solid"
         	chartheight="400" 
            chartwidth="650" 
            tipBGColor = "##912222"
            seriesPlacement = "percent" 
  			showBorder = "no"
            name="statusChart" >
                    
		<cfchartseries
	             type="pie"
	             serieslabel="Status Result">            
                <cfloop query="StatusReptChart">
                       <cfchartdata item="#StatusReptChart.FinalResult# - #val(StatusReptChart.Score)#%" 
                        value="#val(StatusReptChart.Score)#">        
              	</cfloop>
		</cfchartseries>
	</cfchart>
</cfsavecontent>  
    
 </CFIF>

</cfoutput>


<cffile  
    action="WRITE"  
    file="OutputChart.png"  
    output="#toBinary(ChartSource)#">  
    
    
<img src="OutputChart.png" height=240 width=320>

Open in new window

lulu50Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LajuanTaylorCommented:
@lulu50 - Are you trying to insert the chart that you generated into Excel?
0
lulu50Author Commented:
yes please
0
lulu50Author Commented:
I tryed to save the image to a file then output it to the excel file but that didn't work either

<cffile  
    action="WRITE"  
    file="#expandpath('OutputChart.png')#"  
    output="#statusChart#"> 

<!--- it save the chart to a file fine --->


<!--- but I can't get it to display ---> 

<cfimage action="writeToBrowser" source="OutputChart.png" >

Open in new window

0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

LajuanTaylorCommented:
The following example using ColdFusion 9 creates a PNG format chart, puts it in a new spread sheet as rows 5-12 and column 5-10, and saves the sheet to disk.
<cfchart format="png"
    scalefrom="-100" scaleTo="100"
    gridlines="5"
    name="test"> 
    <cfchartseries type="line"> 
        <cfchartdata item="Point1" value="-50"> 
        <cfchartdata item="Point2" value="-25"> 
        <cfchartdata item="Point3" value="1"> 
        <cfchartdata item="Point4" value="25"> 
        <cfchartdata item="Point5" value="50"> 
        <cfchartdata item="Point6" value="75"> 
        <cfchartdata item="Point7" value="99"> 
    </cfchartseries> 
</cfchart> 
  
<cfscript> 
       theDir=GetDirectoryFromPath(GetCurrentTemplatePath()); 
    SpreadsheetObj=SpreadsheetNew(); 
    SpreadsheetAddImage(SpreadsheetObj,test,"png","5,5,12,10"); 
</cfscript> 
  
<cfspreadsheet action="write" name=SpreadsheetObj filename="#theDir#imagesheet.xls"
        sheetname="chart" overwrite=true>

Open in new window

0
LajuanTaylorCommented:
Here's a link to some documentation for the function
SpreadsheetAddImag
https://wikidocs.adobe.com/wiki/display/coldfusionen/SpreadsheetAddImage

I also tweaked the snippet to create a pie chart instead of a line graph...

<cfchart
         	format="png"       		
			showlegend="true"
         	pieslicestyle="solid"
         	chartheight="400" 
            chartwidth="650" 
            tipBGColor = "##912222"
            seriesPlacement = "percent" 
  			showBorder = "no"
            name="statusChart" >    
    
    <cfchartseries type="pie" serieslabel="Status Result"> 
        <cfchartdata item="Point1" value="75"> 
        <cfchartdata item="Point2" value="80"> 
        <cfchartdata item="Point3" value="95"> 
        <cfchartdata item="Point4" value="72"> 
        <cfchartdata item="Point5" value="69"> 
        <cfchartdata item="Point6" value="78"> 
        <cfchartdata item="Point7" value="91"> 
    </cfchartseries> 
</cfchart> 
  
<cfscript> 
       theDir=GetDirectoryFromPath(GetCurrentTemplatePath()); 
    SpreadsheetObj=SpreadsheetNew(); 
    SpreadsheetAddImage(SpreadsheetObj,statusChart,"png","5,5,12,10"); 
</cfscript> 
  
<cfspreadsheet action="write" name=SpreadsheetObj filename="#theDir#imagesheet.xls"
        sheetname="chart" overwrite=true>

<cfset filePath = GetDirectoryFromPath(GetCurrentTemplatePath()) & "imagesheet.xls">
        
<cfif FileExists(filePath)>
  <a href="imagesheet.xls">Download Spreadsheet</a>
<cfelse>
  <p>No file found!</p>
</cfif>

Open in new window

0
lulu50Author Commented:
It's not working and I don't know what I'm doing wrong.

<cfscript> 
       theDir=GetDirectoryFromPath(GetCurrentTemplatePath()); 
    SpreadsheetObj=SpreadsheetNew(); 
    SpreadsheetAddImage(SpreadsheetObj,statusChart,"png","5,5,12,10");   
</cfscript> 
  
<cfspreadsheet action="write" name=SpreadsheetObj filename="#expandpath('SOReport.xls')#"
        sheetname="chart" overwrite=true>

<cfset filePath = GetDirectoryFromPath(GetCurrentTemplatePath()) & "SOReport.xls">
        
<cfif FileExists(filePath)>
  <a href="SOReport.xls">Download Spreadsheet</a>
<cfelse>
  <p>No file found!</p>
</cfif> 

Open in new window

0
lulu50Author Commented:
How about after I do SaveContent to the chart send it to excel


 <cfsavecontent variable="ChartSource"> 
    <cfchart
...........
	</cfchart>
</cfsavecontent>  


here how can I send my saved chart to excel?

Open in new window

0
lulu50Author Commented:
 <cfheader name="content-disposition" value="attachment; filename=SOReport.xls">
  <cfcontent type="application/vnd.ms-excel"> 


<table>
-----

</table>

<cfsavecontent
<cfchart>
--------
</cfchart>
</cfsavecontent>

here I should display the chart to excel 

Open in new window

0
LajuanTaylorCommented:
If you save the chart to an image first, I believe you will have to read the binary image from disk first, add it to the workbook, anchor the image to the desired cell, then save the modified workbook back to disk. You have to work at a lower level code with this approach though. This is possible using the JAVA library POI which is bundled with ColdFusion.

"SpreadSheetAddImage" just saves you some work.

What error message were you getting when using the example code?
0
LajuanTaylorCommented:
@lulu50 - Can you provide a fake data set for the pie chart that you wish to add to Excel?

What version of ColdFusion are you using? Are you testing locally or are you in a hosted environment?
0
lulu50Author Commented:
I'm not getting any error but I don't see the chart

I see this "Download Spreadsheet" link. when I click on it nothing happens.

this is the structure of the page.
I first have the <cfheader
then
table with data pulled from the database
then
it generate the <cfchart
then
it should display my chart that it is not doing it.

<cfheader name="content-disposition" value="attachment; filename=SOReport.xls">
  <cfcontent type="application/vnd.ms-excel">


<table>
-----

</table>

<cfchart>

</cfchart>

display the chart.
0
lulu50Author Commented:
I think version 9
0
LajuanTaylorCommented:
@lulu50 - I have to step away, but I'll respond around noon if you haven't solved the problem by then.

Regards
0
lulu50Author Commented:
  <cfchart
         	format="png"
       		query="StatusReptChart"
			showlegend="true"
         	pieslicestyle="solid"
         	chartheight="400" 
            chartwidth="650" 
            tipBGColor = "##912222"
            seriesPlacement = "percent" 
  			showBorder = "no"
            name="statusChart" >
                    
		<cfchartseries
	             type="pie"
	             serieslabel="Status Result">            
						<cfchartdata item="Completed 14%" value="14">  
						<cfchartdata item="In Progress 85%" value="85">					   
		</cfchartseries>
	</cfchart>

Open in new window

0
lulu50Author Commented:
LajuanTaylor,

I can see my chart now but I don't see my table data

so, in the excel file I see the chart in cell 1 but I want the chart to go after my "table"

How can I set my chart "1,1,7,6" to be displayed after my data get uploaded.

<cfif (isDefined("StatusResultRept") and #StatusResultRept.RecordCount# gt 0) >

 <cfheader name="content-disposition" value="attachment; filename=SOReport.xls">
  <cfcontent type="application/vnd.ms-excel"> 
    
 <table border="1">
     <tr> 
        <td colspan="7" align="center" class="ListGridHdrRow" style="background-color:#4a83ca;">
                      <span style="font-weight:bold;font-size:20px;color:#fff;"> 
                      <cfif isdefined("form.StatOut") and #form.StatOut# eq 'Outcome' >
                      OUTCOME REPORT
                      <cfelse>
                      STATUS REPORT
                      </cfif>
                      </span>
       </td>  
     </tr>
     <tr> 
         <td colspan="7">&nbsp; </td>
     </tr>
     
 
            <tr> 
            		<th align="left" style="width:200px;font-weight:bold;background-color:#edecec;" filter="All"><div class="HeaderStyle">VERSION</div></th>
       		  <th align="left" style="width:250px;font-weight:bold;background-color:#edecec;" filter="All"><div class="HeaderStyle">CQ TICKET</div></th>
           	  <th align="left" style="width:250px;font-weight:bold;background-color:#edecec;" filter="All"><div class="HeaderStyle">RULESET</div></th>
              <th align="left" style="width:250px;font-weight:bold;background-color:#edecec;" filter="All"><div class="HeaderStyle">RULE IDENTIFIER</div></th>   
     <cfif isdefined("form.StatOut") and #form.StatOut# eq 'Status' >       
        <th align="left" style="width:150px;font-weight:bold;background-color:#edecec;" filter="All"><div class="HeaderStyle">RULE STATUS</div></th>
     </cfif>
     <cfif isdefined("form.StatOut") and #form.StatOut# eq 'Outcome' >
       <th align="left" style="width:150px;font-weight:bold;background-color:#edecec;" filter="All"><div class="HeaderStyle">PASS/FAIL</div></th>
   </cfif>
            		<th align="left" style="width:150px;font-weight:bold;background-color:#edecec;" filter="All"><div class="HeaderStyle">REVIEW DATE</div></th>
       		  <th align="left" style="width:150px;font-weight:bold;background-color:#edecec;" filter="All"><div class="HeaderStyle">CREATE DATE</div></th>
           		</tr>

      <cfif (isDefined("StatusResultRept") and #StatusResultRept.RecordCount# gt 0) >
                    <cfoutput query="StatusResultRept"> 
              <tr class="RotateRow">
            		<td align="left" style="font-weight:normal;border-top: 1px solid;border-left: 1px solid;border-left: 1px solid;border-bottom: 1px solid;"><cfif #StatusResultRept.VERSION# neq "">#trim(StatusResultRept.VERSION)#<cfelse>&nbsp;</cfif></td>
           		  <td style="font-weight:normal;border-top: 1px solid;border-left: 1px solid;border-right: 1px solid;border-bottom: 1px solid;"><cfif #StatusResultRept.CQTICKETNUMBER# neq "">#StatusResultRept.CQTICKETNUMBER#<cfelse>&nbsp;</cfif></td>
            		                    		<td style="font-weight:normal;border-top: 1px solid;border-left: 1px solid;border-right: 1px solid;border-bottom: 1px solid;"><cfif #StatusResultRept.RULESET# neq "">#StatusResultRept.RULESET#<cfelse>&nbsp;</cfif></td>
                <td style="font-weight:normal;border-top: 1px solid;border-left: 1px solid;border-right: 1px solid;border-bottom: 1px solid;"><cfif #StatusResultRept.RULEIDENTIFIER# neq "">#StatusResultRept.RULEIDENTIFIER#<cfelse>&nbsp;</cfif></td>
             <cfif isdefined("form.StatOut") and #form.StatOut# eq 'Status' >   
           	   <td style="font-weight:normal;border-top: 1px solid;border-left: 1px solid;border-right: 1px solid;border-bottom: 1px solid;"><cfif #StatusResultRept.RuleStatus# neq "">#StatusResultRept.RuleStatus#<cfelse>&nbsp;</cfif></td>
            </cfif>
            
            <cfif isdefined("form.StatOut") and #form.StatOut# eq 'Outcome' >        
               	<td style="font-weight:normal;border-top: 1px solid;border-left: 1px solid;border-right: 1px solid;border-bottom: 1px solid;"><cfif #StatusResultRept.PassFail# neq "">#StatusResultRept.PassFail#<cfelse>&nbsp;</cfif></td>            
                </cfif>    
                    
           		<td style="font-weight:normal;border-top: 1px solid;border-left: 1px solid;border-right: 1px solid;border-bottom: 1px solid;">
                   #Trim(DateFormat(StatusResultRept.REVIEWEDDATE, "mm/dd/yyyy"))#
                </td>
           		  <td style="font-weight:normal;border-top: 1px solid;border-left: 1px solid;border-right: 1px solid;border-bottom: 1px solid;"><cfif #StatusResultRept.CreatedDate# neq "">
                  #Trim(DateFormat(StatusResultRept.CreatedDate, "mm/dd/yyyy"))#
                <cfelse>&nbsp;</cfif></td>
				</tr>  
				   </cfoutput>
				</cfif> 
		 
 </table> 
 </cfif>
 
<cfoutput> 
 <CFIF isDefined("StatusReptChart") and #StatusReptChart.RecordCount# gt 0>   
 <cfsavecontent variable="ChartSource"> 
    <cfchart
         	format="png"
       		query="StatusReptChart"
			showlegend="true"
         	pieslicestyle="solid"
         	chartheight="400" 
            chartwidth="650" 
            tipBGColor = "##912222"
            seriesPlacement = "percent" 
  			showBorder = "no"
            name="statusChart" >
                    
		<cfchartseries
	             type="pie"
	             serieslabel="Status Result">            
                <cfloop query="StatusReptChart">
                       <cfchartdata item="#StatusReptChart.FinalResult# - #val(StatusReptChart.Score)#%" 
                        value="#val(StatusReptChart.Score)#">        
              	</cfloop>
		</cfchartseries>
	</cfchart> 
 </CFIF>

</cfoutput>


 
 <cfset sObj = SpreadsheetNew()>
<cfset SpreadsheetAddRow(sObj, "Report")>
<cfset SpreadsheetAddImage(sObj,statusChart,"png","1,1,7,6")>

<cfheader name="Content-Disposition" value="inline; filename=SOReport.xls">
<cfcontent type="application/vnd.msexcel" variable="#SpreadSheetReadBinary(sObj)#">
 
 
 
 

Open in new window

0
LajuanTaylorCommented:
@lulu50 -
How can I set my chart "1,1,7,6" to be displayed after my data get uploaded.

If you talking about position of chart in spreadsheet, you just have to tweak the "1,1,7,6". Which means "Vertical start, Horizontal Start, Chart Height, Chart Width".

If you mean display the chart after your table data has been added, I think you need to this in one pass... Something like
<cfchart format="png"
    showlegend="true"
         	pieslicestyle="solid"
         	chartheight="400" 
            chartwidth="650" 
            tipBGColor = "##912222"
            seriesPlacement = "percent" 
  			showBorder = "no"
            name="statusChart" > 
    <cfchartseries type="pie" serieslabel="Status Result"> 
       <cfchartdata item="Completed 14%" value="14">  
	 <cfchartdata item="In Progress 85%" value="85"> 
    </cfchartseries> 
</cfchart> 
  
<cfscript> 
       	theDir=GetDirectoryFromPath(GetCurrentTemplatePath()); 
    	SpreadsheetObj=SpreadsheetNew(); 
		SpreadsheetAddRow(SpreadsheetObj,"Completed 14%,In Progress 85%"); 
    	SpreadsheetAddColumn(SpreadsheetObj, 
   		 "14,85", 
    3,2,true); 
    	SpreadsheetAddImage(SpreadsheetObj,statusChart,"png","10,5,10,20"); 
</cfscript> 
  
<cfspreadsheet action="write" name=SpreadsheetObj filename="#theDir#SOReport.xls"
        sheetname="chart" overwrite=true>

Open in new window

0
LajuanTaylorCommented:
@lulu50 - I refactored the sample script to use a fake query. This script puts the query data in Excel, adds the chart image, and downloads the file. This was tested using Google Chrome with CF9.   Script should run as is. Just replace the query with your own.

<!--- Optional date info added to spreadsheet --->
<cfset FirstOfMonth = #Month(Now())# & "/1/" & #Year(Now())#>
<cfset vFirstOfMonth = DateFormat(FirstOfMonth,"mm/dd/yyyy")>

<cfparam name="StartDate" default="#vFirstOfMonth#">
<cfparam name="EndDate" default="#DateFormat(Now(),'mm/dd/yyyy')#">

<cfset vStartDate = "#DateFormat(StartDate, 'mm/dd/yyyy')#">
<cfset vEndDate = "#DateFormat(EndDate, 'mm/dd/yyyy')#">

<!--- Pass start & end date to stored proc to return query result set --->
<cflock timeout="15" throwontimeout="No" type="READONLY" scope="APPLICATION">
	<cfset qChartData = queryNew("resultstatus,score","cf_sql_varchar,cf_sql_integer")>
    <cfset queryAddRow(qChartData)>
    <cfset querySetCell(qChartData, "resultstatus","Completed")>
    <cfset querySetCell(qChartData, "score",70)>
    <cfset queryAddRow(qChartData)>
    <cfset querySetCell(qChartData, "resultstatus","In Progress")>
    <cfset querySetCell(qChartData, "score",15)>
    <cfset queryAddRow(qChartData)>
    <cfset querySetCell(qChartData, "resultstatus","On Hold")>
    <cfset querySetCell(qChartData, "score",10)>
    <cfset queryAddRow(qChartData)>
    <cfset querySetCell(qChartData, "resultstatus","Incomplete")>
    <cfset querySetCell(qChartData, "score",5)>
</cflock>

<!--- Create new spreadsheet object --->
<cfset sObj=SpreadsheetNew()>

<!--- Create header row labels --->
<cfset SpreadsheetAddRow(sObj, "Result Status,Score")>

<!--- Format header row --->
<cfset SpreadsheetFormatRow(sObj, {bold=TRUE, fgcolor="pale_blue", alignment="center"}, 1)>

<!--- Freeze header row --->
<cfset SpreadSheetAddFreezePane(sObj, 0, 1)>

<!--- Add records from query results --->
<cfset SpreadsheetAddRows(sObj, qChartData)>

<!--- Formulas to calculate row positions --->
<cfset rowDataStart=2>
<cfset rowDataEnd=qChartData.recordCount+1>
<cfset rowEnd=qChartData.recordCount+1>
<cfset rowTotal=rowDataEnd+2>
<cfset totalFormula="SUM(D#rowDataStart#:D#rowDataEnd#)">

<!--- Bold last row of query results data --->
<!---<cfset SpreadsheetFormatRow(sObj, {bold=TRUE, alignment="right"}, rowEnd)>--->

<!--- Add row to display date range of spreadsheet --->
<cfset SpreadsheetAddRow(sObj, "Date Range: #vStartDate# thru #vEndDate#",rowTotal,1,2 )>

<!--- Generate chart --->
<cfchart chartheight="400" chartwidth="650" format="png" showlegend="true" name="statusChart">
	<cfchartseries type="pie" query="qChartData" itemcolumn="resultstatus" valuecolumn="score" datalabelstyle="pattern" >
</cfchart>

<!--- Add chart image to spreadsheet --->
<cfset SpreadsheetAddImage(sObj,statusChart,"png","10,5,30,15")>

<!--- Add custom formatting to data range display row --->
<cfscript> 
	format2 = structNew();
    format2.font="Courier"; 
    format2.fontsize="10"; 
    format2.color="dark_blue;"; 
    format2.italic="false"; 
    format2.bold="true"; 
    format2.alignment="left"; 
    format2.textwrap="false"; 
    format2.fgcolor="pale_blue"; 
    format2.bottomborder="thick"; 
    format2.bottombordercolor="blue_grey"; 
    format2.topbordercolor="blue_grey"; 
    format2.topborder="thick"; 
    format2.leftborder="dotted"; 
    format2.leftbordercolor="blue_grey"; 
    format2.rightborder="dotted"; 
    format2.rightbordercolor="blue_grey"; 
    //SpreadsheetFormatRow(sObj,format2,rowTotal); 
</cfscript> 
 

<!--- Force download and set default filename for spreadsheet --->
<cfheader name="Content-Disposition" value="filename=#Chr(34)#SOReport-#TimeFormat(Now(),"HH:mm:ss")#.xls#Chr(34)#">
<!--- set the content-type so MS Excel is invoked --->
<cfcontent type="application/vnd.ms-excel" variable="#SpreadsheetReadBinary(sObj)#">

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
lulu50Author Commented:
LajuanTaylor,

Thank you for the example and your help

It's working now !!!!

the last example that you gave me helped me a lot.

I redesigned my file just like the example above and it worked great!!!

Thank you so much for your help!!!

Thank you again maybe one more time Thank you again lol

Lulu
0
lulu50Author Commented:
Thank you could not have done it without your help
0
LajuanTaylorCommented:
@lulu50 - No problem. I'm glad that it worked for you!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ColdFusion Language

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.