Images in Excel: SpreadsheetAddImage problem

[Ryan_Dias]

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>

[-__cfSearching__-]

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==-

[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()

[-__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()

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.