Cannot get row/col info for images.

[cwjenkins]

I cannot figure out how to obtain row/col info with the current version. Am I missing something? If not then I'd like to add that functionality. I noticed _rels/drawing+.xml maps to a drawing/drawing+.xml sheet that contains twoCellAnchor tags with that info (tied with rId). Where would be the best place to insert this feature?

[weshatheleopard]

What do you mean by "row/col info"?

By default, rubyXL does not provide functionality to do absolutely everything with OpenXML Office documents; however, it enables the user to do what they need (if they spend some time researching how the format supports the task that they desire) by providing the basic framework, so they don't have to implement file access from the ground up.

Some research shows that, for instance, adding image to an Excel document isn't an easy task, and requires accessing a lot of properties: https://social.msdn.microsoft.com/Forums/office/en-US/5c6e7ebd-66e2-40fa-9194-aed5cdc3f0ae/adding-image-at-a-particular-cell-in-excel-spreadsheet?forum=oxmlsdk

If you can delineate what exactly you are trying to do, I might be able to implement a convenience method to accomplish that.

[cwjenkins]

Apologizes for the vague inquiry.

I have an excel sheet that contains a sku (number), an image (file), and the amount in inventory (number).
The numbers are retrieved just fine, but checking the 'cell' for the image returns 'nil'.
I noticed you store these in workbook[sheet_number].generic_storage[0].relationship_container.related_files, but they do not contain the cell number they belong too. For instance, image249.jpeg may be stored in row 2, column 2/cell(2,2), but the related_file only contains the path, data, and rId as the key.

I noticed this information, row and column are stored in drawing/drawing+.xml and associated by their rId+ which is already defined in the related_files.

What would be great is retrieving the file_path within the cell instead of nil or if the related_file had row and column attributes to tie in.

I'm simply trying to import an excel (xlsx) file that has images and keep those images mapped to their respective item/row.

Please let me know if you need any further clarification or if you'd like assistance.

[weshatheleopard]

Can you provide a minimal sample file that you are using?

The hard part is that there is no such thing as "image in a cell", images are just overlays that are tied to anchors, so the image may be visually in cell B2 but actually be tied to cell A1, and it is impossible to determine that programmatically without actually rendering the entire document.

[cwjenkins]

Certainly.
sample.xlsx
Any xlsx with an image will do.
After parsing wb = RubyXL::Parser.parse('sample.xlsx')

irb(main):014:0> wb[0][1][0]
=> #<RubyXL::Cell(1,0): "1234", datatype="n", style_index=0>
irb(main):015:0> wb[0][1][1]
=> nil
irb(main):016:0> wb[0][1][2]
=> #<RubyXL::Cell(1,2): "2", datatype="n", style_index=0>
irb(main):017:0> wb[0].generic_storage[0].relationship_container.related_files.keys
=> ["rId1"]

[cwjenkins]

As stated previously, that information is stored in drawing/drawing+.xml therefore it CAN be tied to it.

<xdr:\twoCellAnchor editAs="absolute"><xdr:from><xdr:col>1</xdr:col><xdr:colOff>0</xdr:colOff><xdr:row>1</xdr:row><xdr:rowOff>0</xdr:rowOff></xdr:from><xdr:to><xdr:col>1</xdr:col><xdr:colOff>1037520</xdr:colOff><xdr:row>1</xdr:row><xdr:rowOff>583560</xdr:rowOff></xdr:to><xdr:pic><xdr:nvPicPr><xdr:cNvPr id="0" name="Image 1" descr=""/><xdr:cNvPicPr/></xdr:nvPicPr><xdr:blipFill><a:blip r:embed="rId1"></a:blip><a:stretch/></xdr:blipFill><xdr:spPr><a:xfrm><a:off x="812520" y="162360"/><a:ext cx="1037520" cy="583560"/></a:xfrm><a:prstGeom prst="rect"><a:avLst/></a:prstGeom><a:ln><a:noFill/></a:ln></xdr:spPr></xdr:pic><xdr:clientData/></xdr:twoCellAnchor>

For the example I sent you.

[cwjenkins]

Notice the col <xdr:col>1</xdr:col>, the row <xdr:row>1</xdr:row>, and the rId <a:blip r:embed="rId1">. With that info you can tie the image back to the cell.

[weshatheleopard]

As stated previously, that information is stored in drawing/drawing+.xml therefore it CAN be tied to it.

It's the location of the anchor, not the location of the image. Image may be offset from the anchor.

[cwjenkins]

Cool, then the anchor (element referring to the image) is tied to a particular cell. They certainly could set an offset to make it 'appear' to be in another, but it truly resides in one particular cell and that's what I need. When excel, libre, openoffice 'renders' the document it programmatically determines where those images reside, it does so by these anchors apparently. If this is beyond the scope of what you think RubyXL should do then that's perfectly fine. I was just asking if I could add in this functionality, if so, where would be the best place. If not, sorry to have wasted your time.

Cheers,

Colton

[weshatheleopard]

Exposing the location of the anchor programmatically should be sufficiently easy. I'll look into it when I have time.

[cwjenkins]

Many thanks @weshatheleopard ! Awesome stuff you have here, very appreciative.

[weshatheleopard]

OK, here's the deal.

Obviously, anchor points are defined in drawing###.xml files.

The way to expose them programmatically is to make them load properly as part of OOXML structure (right now they are loaded as "generic storage object" which is by definition non-accessible and non-modifiable, it's saved "as is").

I started the branch https://github.com/weshatheleopard/rubyXL/tree/drawing_support, and started implementing it, but it requieres a lot of work to copy stuff from the spec to rubyXL's definition files, and it takes more time than I can spend right now.

However, the work is pretty mechanical. So if you want to help, you can look at https://github.com/weshatheleopard/rubyXL/blob/drawing_support/lib/rubyXL/objects/drawing.rb for hints on how that is done, and then continue my work, and submit a pull request. (It is okay if you make mistakes. I will be able to correct them, but it's the sheer amount of mechanical work that I can't take right now.)

[cwjenkins]

Sure. Please let me know if this gets you far enough cwjenkins@dc1b516

The above works for what I need, but it's an ugly way to go about it.

wb = RubyXL::Parser.parse(file)
wb[0].generic_storage[0].set_image_paths #Wasn't sure where this could be set so invoked explicitly 
wb[0].generic_storage[0].anchors.each do |anchor|
  wb[0][anchor.row][anchor.col] = anchor.image_path
end

[weshatheleopard]

A quick cursory glance tells me that it's exactly what I need, although it seems that it's not the complete spec implementation. But it's a good start. I should work on it more tomorrow.

[cwjenkins]

Okay cool. Let me know if you'd like me to help with anything. Thanks again.

[weshatheleopard]

@cwjenkins: https://github.com/weshatheleopard/rubyXL/tree/drawing_support should be more or less feature complete by now. Play around with it and see if it works for you.

[mhoofe]

Hi. That could be exactly the feature I was looking for. Do you plan to merge the changes sometime?

[Squareys]

Would also be interested in this being merged :)

For those who are here because of images not being moved when a row is inserted above, you can use:

offset = ... # amount of rows added
inserted_row_index = ... # index of row beneath which rows were added
workbook.each do |w|
    w.generic_storage.each do |g|
        g.xdr_two_cell_anchor.each do |anchor|
            # anchor below the inserted rows?
            if anchor.xdr_from.xdr_row.value > inserted_row_index
                # move anchor downwards by amount of rows inserted
                anchor.xdr_from.xdr_row.value += offset
                anchor.xdr_to.xdr_row.value += offset
            end
        end
    end
end

to fix that, if you use the drawing_support branch in your Gemfile:

git 'https://github.com/weshatheleopard/rubyXL', :branch => 'drawing_support' do
    gem 'rubyXL'
end

[sergio-rivas]

Can you give an example of how to use the feature from the drawing support branch?

I can find the images in sheet through relationship_container, mapped by the rid "rId1, rId2, ..." However, I can't find where the relationship is being connected to the cell. For example, how can I find that "rId3" relationship should take place in Cell E7?

Through which class can we find that information? Is it in the Drawing class? or the BinaryImageFile class?
I end up able to get to the files which are RubyXL::BinaryImageFile objects. Is there a separate route of objects that I need to go which has the mapping of which cell/row numbers are connected to which rId number?

[sergio-rivas]

@Squareys @mhoofe @cwjenkins
It seems the methods mentioned are not available in the current branch version.

ex: xdr_two_cell_anchor, anchors, etc.

@weshatheleopard
Is there a way to get the related anchors from the relationship?

[caramdache]

@weshatheleopard
I am trying to use this branch and it returns a type error.
It seems DrawingFile is defined both in drawing.rb and storage.rb:

-bash-4.2$ ruby ccs.rb
Traceback (most recent call last):
        11: from ccs.rb:4:in `<main>'
        10: from ccs.rb:4:in `require'
         9: from /usr/local/lib/ruby/gems/2.7.0/bundler/gems/rubyXL-32dbaf0b5ce4/lib/rubyXL.rb:1:in `<top (required)>'
         8: from /usr/local/lib/ruby/gems/2.7.0/bundler/gems/rubyXL-32dbaf0b5ce4/lib/rubyXL.rb:1:in `require'
         7: from /usr/local/lib/ruby/gems/2.7.0/bundler/gems/rubyXL-32dbaf0b5ce4/lib/rubyXL/objects/root.rb:5:in `<top (required)>'
         6: from /usr/local/lib/ruby/gems/2.7.0/bundler/gems/rubyXL-32dbaf0b5ce4/lib/rubyXL/objects/root.rb:5:in `require'
         5: from /usr/local/lib/ruby/gems/2.7.0/bundler/gems/rubyXL-32dbaf0b5ce4/lib/rubyXL/objects/workbook.rb:8:in `<top (required)>'
         4: from /usr/local/lib/ruby/gems/2.7.0/bundler/gems/rubyXL-32dbaf0b5ce4/lib/rubyXL/objects/workbook.rb:8:in `require'
         3: from /usr/local/lib/ruby/gems/2.7.0/bundler/gems/rubyXL-32dbaf0b5ce4/lib/rubyXL/objects/worksheet.rb:13:in `<top (required)>'
         2: from /usr/local/lib/ruby/gems/2.7.0/bundler/gems/rubyXL-32dbaf0b5ce4/lib/rubyXL/objects/worksheet.rb:13:in `require'
         1: from /usr/local/lib/ruby/gems/2.7.0/bundler/gems/rubyXL-32dbaf0b5ce4/lib/rubyXL/objects/drawing.rb:6:in `<top (required)>'
/usr/local/lib/ruby/gems/2.7.0/bundler/gems/rubyXL-32dbaf0b5ce4/lib/rubyXL/objects/drawing.rb:89:in `<module:RubyXL>': superclass mismatch for class DrawingFile (TypeError)

[caramdache]

@weshatheleopard could you also please sync this branch this master?