Skip navigation
Ryan Dias
Currently Being Moderated

Images in Excel: SpreadsheetAddImage problem

Mar 19, 2012 3:10 AM

Tags: #excel #spreadsheet #spreadsheetaddimage

Hi All

 

I am trying to add an image in an excel spreadsheet using SpreadsheetAddImage, but there seems to be a problem with this function. Using it seems to clear the entire spreadsheet of all existing images, even ones that have just been added, thus resulting in only one image displaying in the final excel document. Is this a bug? Is there a workaround for this problem?

 

My stripped-down code:

 

</cfscript>

     var sheet = SpreadSheetRead(xxxxxxx.xls');

</cfscript>

 

<cfloop from="1" to="#athletesLen#" index="i">

 

               <cfloop from="1" to="#chartsLen#" index="chartsIndex">

                        <cfchart

                            format="png"

                            name="idChart">

                            <cfchartseries type="bar">

                                <!--- chart code here --->

                            </cfchartseries>

                        </cfchart>

 

                        <cfscript>

                            var anchor = rowNr & ",1," & rowNr+10 & ",5";

                            SpreadsheetAddImage(sheet, idChart, "png", anchor);

 

                            rowNr += 11;

                        </cfscript>

                    </cfloop>

</cfloop>

 
Replies
  • Currently Being Moderated
    Mar 19, 2012 7:39 PM   in reply to Ryan Dias

    Is this a bug? Is there a workaround for this problem?

     

     

    Yes, it is a limitation of the underlying library POI. CF uses the method HSSFSheet.getDrawingPatriarch() which probably cannot parse the previous images, so they disappear.

     

    You could try <cfspreadsheet action="update" ..> but I think it will suffer from the same problem. I think the best you can do is either A) start with a brand new workbook or B) create a new drawingPatriarch before calling SpreadSheetAddImage. That erases any images that existed within the workbook, but would maintain those added within the loop.

     

    Message was edited by: -==cfSearching==-

     
    |
    Mark as:
  • Currently Being Moderated
    Dec 19, 2012 8:27 AM   in reply to -==cfSearching==-

    This is incorect, at least in CF 9.0.1, you can add multiple images this way without issue at all, just write the chart to disk first, then include it from there in spreadSheetAddImage()

     
    |
    Mark as:
  • Currently Being Moderated
    Dec 19, 2012 1:01 PM   in reply to ChivertonT

    This is incorect, at least in CF 9.0.1, you can add multiple images this

     

    way without issue at all, just write the chart to disk first, then

    include

    it from there in spreadSheetAddImage()

     

    I cannot test it right now, but are we talking about the same issue? It has been a while but IIRC the original issue only occurred when adding images to an existing workbook on disk - which contained images. Adding images to a new workbook, or one without images, worked fine.

     
    |
    Mark as:

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points