3 Replies Latest reply: Dec 19, 2012 1:01 PM by -==cfSearching==- RSS

    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>

        • 1. Re: Images in Excel: SpreadsheetAddImage problem
          -==cfSearching==- Community Member

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

          • 2. Re: Images in Excel: SpreadsheetAddImage problem
            ChivertonT CommunityMVP

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

            • 3. Re: Images in Excel: SpreadsheetAddImage problem
              -==cfSearching==- Community Member

              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.