Anyone assist me to add comment in XLSX file using RubyXL?

[PriyankaRPathak]

I'm using rubyXL (3.3.17). I want to add comment in cell. It would be helpful if anyone assist me on it.

I need syntax to insert comment.

[weshatheleopard]

Currently there's no "easy" way to add a comment, since it requires to manipulate a few files, due to the fact that Excel's format is pretty complex.

Namely, you will need to:

• create a comment file;
• create an Author entity;
• create a RichText entity containing your comment — even if it's a single line of simple text.
• attach the comment file to your worksheet using the relationship file.

Presently, there is no convenience method that does all that, and there is no guarantee there will be in the future. RubyXL provides the necessary toolkit that allows people to implement nearly any function of Excel they need, but it's not a magic "genie" solution that will grant any every wish right out of the box.

Reading existing comments is much easier.

[ardavis]

I took a stab at creating comments and here's what I came up with (I imagine there might be better ways to do much of this, so please let me know of improvements!):

require 'rubyXL'

# Create a new Workbook
workbook = RubyXL::Workbook.new

# Grab the first sheet
worksheet = workbook[0]

# Add content to cell A1
cell = worksheet.add_cell(0, 0, "TEST CONTENT")

# Setup the Authors
authors = RubyXL::Authors.new
author = RubyXL::StringNode.new(value: 'Author Name')
authors << author

# Create the Comment's text via Rich Text
text = RubyXL::Text.new(value: 'Comment!')
rich_text_run = RubyXL::RichTextRun.new(t: text)
r_font = RubyXL::StringValue.new(val: 'Arial')
size = RubyXL::FloatValue.new(val: 10)
family = RubyXL::IntegerValue.new(val: 2)
run_prop = RubyXL::RunProperties.new(r_font: r_font, sz: size, family: family)
rich_text_run.r_pr = run_prop
rich_text = RubyXL::RichText.new
rich_text.r << rich_text_run

# Create the Comment object with the previously created Rich Text
# TODO Figure out how to dynamically set the author_id if needed. Is an author_id even required?
comment = RubyXL::Comment.new(text: rich_text, ref: cell.r.to_s, author_id: 0)

comment_list = RubyXL::CommentList.new
comment_list << comment

comment_file = RubyXL::CommentsFile.new
comment_file.authors = authors
comment_file.comment_list = comment_list

# Creates the comments1.xml
worksheet.comments << comment_file

# Associate the comments1.xml with the worksheet
# TODO Need to figure out how to dynamically set the id and target
rel = RubyXL::Relationship.new(id: 'rId1', target: '../comments1.xml', type: 'http://schemas.openxmlformats.org/officeDocument/2006/relationships/comments')
rf = RubyXL::OOXMLRelationshipsFile.new
rf.relationships << rel
worksheet.relationship_container = rf

# Create the XLSX workbook with a comment!
workbook.write('comments.xlsx')

I hope to ultimately write some helper methods to do all of this quickly, but current issue is that the comment is visible all the time. I need it to be hidden by default. My investigation shows that the comment gets hidden in the drawings/vmlDrawing1.vml file, but I don't know anything about that or how to create one of those via Ruby XL.

Thoughts?

[drthvdr]

In order to get a similar behavior to comments (where they don't show up by default) I used the Data Validation input message to function as my comments. Downside is that there is no comment indicator and in order for the comment to appear/disappear the cell must be clicked, but it's doing the trick for me.

module RubyXL
      module WorksheetConvenienceMethods
           def add_dropdown(row, col, content_list=nil, title=nil, prompt=nil)
               formula = RubyXL::Formula.new(expression: content_list)
               loc = if content_list # Indicates it is a dropdown.
                        RubyXL::Reference.new(row, 1048000, col, col)
                     else
                        RubyXL::Reference.new(row, col)
                     end
                val = RubyXL::DataValidation.new(prompt_title: title, prompt: prompt,
                           sqref: loc, formula1: content_list ? formula : nil,
                           type: content_list ? 'list' : nil, show_input_message: true)
               self.data_validations << val
           end
          alias_method :add_hint, :add_dropdown # Alias as to not confuse myself
     end
end 

then to add a hint/comment... Hint: Data Validations have to be initialized:

workbook = RubyXL::Workbook.new
worksheet = workbook['Worksheet Name']
vals = RubyXL::DataValidations.new
worksheet.data_validations = vals

worksheet.add_hint(0, 1, nil, 'Comment Title', 'comment) # nil content_list so it doesnt create the dropdown

and while we're at it, to add a drop down..

worksheet.add_dropdown(1,2, "\"TRUE, FALSE\"") # nil title, prompt to not generate hint

Hope this helps :)

[ardavis]

@weshatheleopard Any ideas on how to hide the comment based on my implementation above? I've been trying to dig in the RubyXL source to see what I might be missing, but no luck yet.

[weshatheleopard]

@ ardavis: sorry, I've been busy recently. Your implementation looks OK at the first sight; the only thing is, AFAIR the piece of code in "# Associate the comments1.xml with the worksheet" section is not needed as it should be done automatically upon the spreadsheet save.

I will review your code in more detail a bit later and add it to the main codebase. Thanks for taking time to implement it.

[ardavis]

Thanks for the response, sorry mine is also delayed! When I remove the part about "associating the comments1.xml with the worksheet", the comment wasn't appearing at all. Let me know if you're able to give it a shot, I completely understand that you're a busy guy too :)

Thanks again for your work on this gem, it's been extremely helpful for our application.

[weshatheleopard]

@ardavis : Today I tried your code in console and somehow it did not work. The flle got generated just fine, but when loaded into Excel the comment was not displaying. Also, it looks like generating just the comment is not enough, as MS Excel also generates some VML shapes to display those comments...

[ardavis]

Interesting. You're right, I'm seeing the same results in Excel on Windows. When I generate the file on my work computer (RHEL 6) I'm able to see the comment but it isn't hidden by default.

When I get back from my vacation (June 6), I'll post some code I wrote about trying to generate the VML shapes as well and I'll explain what issues I ran into. Hopefully we can get this figured out, would be an awesome feature to have.

[tham-1781]

In order to get a similar behavior to comments (where they don't show up by default) I used the Data Validation input message to function as my comments. Downside is that there is no comment indicator and in order for the comment to appear/disappear the cell must be clicked, but it's doing the trick for me.

module RubyXL
      module WorksheetConvenienceMethods
           def add_dropdown(row, col, content_list=nil, title=nil, prompt=nil)
               formula = RubyXL::Formula.new(expression: content_list)
               loc = if content_list # Indicates it is a dropdown.
                        RubyXL::Reference.new(row, 1048000, col, col)
                     else
                        RubyXL::Reference.new(row, col)
                     end
                val = RubyXL::DataValidation.new(prompt_title: title, prompt: prompt,
                           sqref: loc, formula1: content_list ? formula : nil,
                           type: content_list ? 'list' : nil, show_input_message: true)
               self.data_validations << val
           end
          alias_method :add_hint, :add_dropdown # Alias as to not confuse myself
     end
end 

then to add a hint/comment... Hint: Data Validations have to be initialized:

workbook = RubyXL::Workbook.new
worksheet = workbook['Worksheet Name']
vals = RubyXL::DataValidations.new
worksheet.data_validations = vals

worksheet.add_hint(0, 1, nil, 'Comment Title', 'comment) # nil content_list so it doesnt create the dropdown

and while we're at it, to add a drop down..

worksheet.add_dropdown(1,2, "\"TRUE, FALSE\"") # nil title, prompt to not generate hint

Hope this helps :)

@drthvdr I did the same thing with your example but it doesn't show dropdown list

[cesar82]

Hi @weshatheleopard and @ardavis I am wondering if you have any progress on adding a comment for a cell?

I really appreciate your help, thanks

[ardavis]

Hi @weshatheleopard and @ardavis I am wondering if you have any progress on adding a comment for a cell?

I really appreciate your help, thanks

Unfortunately no progress. I haven't worked on this in quite some time, sorry!!

[weshatheleopard]

@cesar82 No we were busy with our real lives. But you do understand that this is free and open source software, right? And the whole idea of free and open source software is that you, as a software developer, can implement whatever functionality that you need that is missing, and then offer it to the rest of the community as a patch, which (likely) may be included in the further releases of that software, right?

[cesar82]

@weshatheleopard sorry, my apologies, I know you are busy and it is open source, it was not my intention to bother you, I really appreciate your open source software, but I can't add the functionality that I need right now, it is not a high priority for now, but eventually I need to spend time on this feature.

Sincerely, my apologies and thanks for your gem.