EPPlus Library Part-8

EPPLUS Library - Beginners Guide Part-8

How to Add, Move, Hide & Remove Comments to an Excel Worksheet Cell using EPPlus library?

Suggested video :  EPPLUS Library - Beginners Guide Part-9(A)
Suggested video :  EPPLUS Library - Beginners Guide Part-10(B)
Suggested video :  EPPLUS Library - Beginners Guide Part-11(C)
Suggested video :  EPPLUS Library - Beginners Guide Part-12(D)

Hindi Video :  Click here
Previous Video :  Click here [English]
Previous Video :  Click here [Hindi]
Source Code :  Click to download [675 KB]

Different types of Format for Excel Comment ?

• Resize/Auto fit of Comment.  (We already discussed in Part 9(A))
• Add Text a Background Color in Comment. (We already discussed in Part 9(A))
• Set Text Alignments in Comment.  (We already discussed in Part 10(B))
• Set Font Style in Excel Comment   (We already discussed in Part 10(B))
• Add Rich Text in Excel Comment. (We already discussed in Part 11(C))
• Add Multiple Rich Text in Excel Comment. (We already discussed in Part 11(C))
• Remove Rich Text in Excel Comment. (We already discussed in Part 11(C))
• Multi Style Excel Cell & Comment Text using ExcelRichTextCollection Class. (We already discussed in Part 12(D))
• Set Line or Border Style in a Comment. 

First of All, If we are add comments to an Excel sheet using EPPlus, first we need to know the comments. 
What is Comments ?

• Comments are basically notes that can be inserted into any cell in Excel. It’s very useful for reminders & notes for others subject.
• When a cell has a comment, a red indicator appears in the corner of the cell, when your mouse pointer hover on the cell, the comment appears.

What do you want to do with Comments using EPPlus ?

• Add a comment.
• Move a comment box.
• Display or hide comments and their indicators.
• Delete a comment.
• Format a comment. (We will be discuss on this topic in Part-9 of this video series)

How to Add a Comment on Excel using EPPlus ? 
There are two ways to create Comments in excel sheet.  

• Option 1 - Using AddComment() method of ExcelRange class. 
• Option 2 - Using Add() method of ExcelCommentCollection class and it assign to the Comments property of ExcelWorksheet class.

Option 1 : Using AddComment() method of ExcelRange class.      

   ExcelRange Rng = wsSheet1.Cells["B5"];                
   Rng.Value = "Everyday Be Coding";                 
   ExcelComment cmd = Rng.AddComment("Comment Text", "Rajdip");    

Above example we can see AddComment() method of ExcelRange class accept two things as parameter, First string Text and Second string Author & It assign by ExcelComment class object cmd . Here ExcelComment class can control this comments behavior.

Option 2 : Using Add() method of ExcelCommentCollection class. 

  ExcelRange Rng = wsSheet1.Cells["B10"];
  Rng.Value = "https://everyday-be-coding.blogspot.in/";
  ExcelComment cmd = wsSheet1.Comments.Add(Rng, "Comment Text", "Rajdip");

Above example we can see Add() method of ExcelCommentCollection class accept three things as parameter, First is object of ExcelRange class & second is string Text & third is string Author. It assign by ExcelComment class object cmd. Here ExcelComment class can control this comments behavior.

Move Comments Box in Excel Sheet :

  ExcelRange Rng = wsSheet1.Cells["B5"]                
  Rng.Value = "Everyday Be Coding";                 
  ExcelComment cmd = Rng.AddComment("Comment Text", "Rajdip");
  
  cmd.From.Column = 1; //Zero Index base
  cmd.To.Column = 2;
  cmd.From.Row = 12;
  cmd.To.Row = 14;

Class ExcelComment object is cmd. This class is inherited form ExcelVmlDrawingComment  class & it has two property From & To. Class ExcelComment access From & To property from base class. These  From, To properties are type of ExcelVmlDrawingPosition class & ExcelVmlDrawingPosition class has two integer type property Column & Row. We are assign it to specific row and column for moving comment box in excel worksheet.

*Note: assign the integer value of row and columns are zero index based. 

Display and Hide Comments and their Indicators :

  ExcelRange Rng = wsSheet1.Cells["B5"];               
  Rng.Value = "Everyday Be Coding";                 
  ExcelComment cmd = Rng.AddComment("Comment Text", "Rajdip");
  cmd.Visible = true;

ExcelComment has Visible property, It accept bool value for comment is display or not. 

Delete Comments in Excel Sheet:

  ExcelRange Rng = wsSheet1.Cells["B10"];
  ExcelComment cmd = wsSheet1.Cells["B10"].Comment;
  wsSheet1.Comments.Remove(cmd); 

ExcelWorkSheet class has a property Comments & It has a  Remove() method. This method accept ExcelComment object as a parameter for delete the comment in excel sheet. 

Output in Excel Sheet:

Source code:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using OfficeOpenXml;
using System.IO;
using System.Drawing;

namespace EpplusDemo
{
    class Program
    {
        static void Main(string[] args)
        {
            ExcelPackage ExcelPkg = new ExcelPackage();
            ExcelWorksheet wsSheet1 = ExcelPkg.Workbook.Worksheets.Add("Sheet1");

            using (ExcelRange Rng = wsSheet1.Cells[2, 2, 2, 2])
            {
                Rng.Value = "Everyday Be Coding - Excel COMMENTS using EPPlus .Net Library";
                Rng.Style.Font.Size = 16;
                Rng.Style.Font.Bold = true;
                Rng.Style.Font.Italic = true;
            }

            //Option 1 : Using AddComment() method of ExcelRange class.
            using (ExcelRange Rng = wsSheet1.Cells["A5"])
            {
                Rng.Value = "Option 1 :";
                Rng.Style.Font.Bold = true;
                Rng.Style.Font.Color.SetColor(Color.Red);
            }

            string CommentText = "We are offering very easy level beginner tutorials on Microsoft .NET base platform, basically for fresher as well as experience candidates & also we are focusing on very uncommon & specific topics those are extremely useful on real life software development.";

            using (ExcelRange Rng = wsSheet1.Cells["B5"])
            {
                Rng.Value = "Everyday Be Coding";
                ExcelComment cmd = Rng.AddComment(CommentText, "Rajdip");
                //cmd.Visible = true;
            }

            //Option 2 : Using Add() method of ExcelCommentCollection class. 
            using (ExcelRange Rng = wsSheet1.Cells["A10"])
            {
                Rng.Value = "Option 2 :";
                Rng.Style.Font.Bold = true;
                Rng.Style.Font.Color.SetColor(Color.Red);
            }

            using (ExcelRange Rng = wsSheet1.Cells["B10"])
            {
                Rng.Value = "https://everyday-be-coding.blogspot.in/";
                ExcelComment cmd = wsSheet1.Comments.Add(Rng, "This a blog URL of my YouTube Channel: Everyday Be Coding", "Rajdip");
                
                //Display and Hide Comments and their Indicators :
                cmd.Visible = true;

                //Moving Comment Box
                cmd.From.Column = 1;
                cmd.To.Column = 2;
                cmd.From.Row = 12;
                cmd.To.Row = 14;
            }

            ////Remove Comments in Excel Worksheet 
            //using (ExcelRange Rng = wsSheet1.Cells["B10"])
            //{
            //    ExcelComment cmd = wsSheet1.Cells["B10"].Comment;
            //    wsSheet1.Comments.Remove(cmd);
            //}

            wsSheet1.Cells[wsSheet1.Dimension.Address].AutoFitColumns();
            ExcelPkg.SaveAs(new FileInfo(@"D:\Comments.xlsx"));
        }
    }
}

• Now build & execute this code. File is (Comments.xlsx) store on D: drive of computer.

Thank you for reading this article. 
Please subscribe my YouTube Channel & don't forget to like and share. 

YouTube :	https://goo.gl/rt4tHH
Facebook :	https://goo.gl/m2skDb
Twitter :	https://goo.gl/nUwGnf