Post
Ask Question

How To Add, Move, Hide And Remove Comments To An Excel Worksheet Cell Using EPPlus .NET Application (C#) - Part Eight

In this tutorial, we will learn step by step Excel Sheet development, using EPPlus .NET library and  C#.
 
EPPLUS Library downlod Link - http://epplus.codeplex.com/

First of all, if we want to add comments to an Excel sheet using EPPlus, we need to know the comments.

Comments
  • Basically, Comments are notes that can be inserted into any Cell in Excel. Comments are very useful for reminders & notes for others subject.

  • When a cell has a comment, a red indicator appears in the corner of the cell, and at the mouse hover, the comment appears.

What you can 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)
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 assigning it to the Comments property of ExcelWorksheet class.
Option 1

Using AddComment() method of ExcelRange class.
  1. ExcelRange Rng = wsSheet1.Cells["B5"];   
  2. Rng.Value = "Everyday Be Coding";   
  3. ExcelComment cmd = Rng.AddComment("Comment Text", "Rajdip");   
In the above example, we can see that AddComment() method of ExcelRange class accepts two strings as parameter - Text and Author. Then, it is assigned by ExcelComment class object cmd. Here, ExcelComment class can control this comment's behavior.

Option 2

Using Add() method of ExcelCommentCollection class.
  1. ExcelRange Rng = wsSheet1.Cells["B10"];  
  2. Rng.Value = "https://everyday-be-coding.blogspot.in/";  
  3. ExcelComment cmd = wsSheet1.Comments.Add(Rng, "Comment Text","Rajdip");  
In the above example, we can see that Add() method of ExcelCommentCollection class accepts three things as parameter - object of ExcelRange class, string Text, and string Author. It is assigned by ExcelComment class object cmd. Here, ExcelComment class can control this comment's behavior.

Move Comments Box in Excel Sheet
  1. ExcelRange Rng = wsSheet1.Cells["B5"]   
  2. Rng.Value = "Everyday Be Coding";   
  3. ExcelComment cmd = Rng.AddComment("Comment Text", "Rajdip");  
  4. cmd.From.Column = 1; //Zero Index base  
  5. cmd.To.Column = 2;  
  6. cmd.From.Row = 12;  
  7. cmd.To.Row = 14;  
Class ExcelComment object is cmd. This class is inherited form ExcelVmlDrawingComment class & it has two properties - From & To. Class ExcelComment access From & To property from base class. These From, To properties are type of ExcelVmlDrawingPosition class. The ExcelVmlDrawingPosition class has two integer type properties - Column & Row. We are assigning it to specific row and column for moving comment box in Excel worksheet.

Note - The integer value of row and columns are zero index based.

Display and hide comments and their indicators,
  1. ExcelRange Rng = wsSheet1.Cells["B5"];   
  2. Rng.Value = "Everyday Be Coding";   
  3. ExcelComment cmd = Rng.AddComment("Comment Text", "Rajdip");  
  4. cmd.Visible = true;  
ExcelComment has Visible property, It accepts bool value for deciding if the comment is displayed or not.

Delete Comments in Excel Sheet
  1. ExcelRange Rng = wsSheet1.Cells["B10"];  
  2. ExcelComment cmd = wsSheet1.Cells["B10"].Comment;  
  3. wsSheet1.Comments.Remove(cmd);   
ExcelWorkSheet class has a property Comments, and it has a Remove() method. This method accepts ExcelComment object as a parameter for deleting the comment in Excel sheet.

Output in Excel Sheet


Source code
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Text;  
  5. using System.Threading.Tasks;  
  6. using OfficeOpenXml;  
  7. using System.IO;  
  8. using System.Drawing;  
  9. namespace EpplusDemo {  
  10.     class Program {  
  11.         static void Main(string[] args) {  
  12.             ExcelPackage ExcelPkg = new ExcelPackage();  
  13.             ExcelWorksheet wsSheet1 = ExcelPkg.Workbook.Worksheets.Add("Sheet1");  
  14.             using(ExcelRange Rng = wsSheet1.Cells[2, 2, 2, 2]) {  
  15.                 Rng.Value = "Everyday Be Coding - Excel COMMENTS using EPPlus .Net Library";  
  16.                 Rng.Style.Font.Size = 16;  
  17.                 Rng.Style.Font.Bold = true;  
  18.                 Rng.Style.Font.Italic = true;  
  19.             }  
  20.             //Option 1 : Using AddComment() method of ExcelRange class.  
  21.             using(ExcelRange Rng = wsSheet1.Cells["A5"]) {  
  22.                 Rng.Value = "Option 1 :";  
  23.                 Rng.Style.Font.Bold = true;  
  24.                 Rng.Style.Font.Color.SetColor(Color.Red);  
  25.             }  
  26.             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.";  
  27.             using(ExcelRange Rng = wsSheet1.Cells["B5"]) {  
  28.                 Rng.Value = "Everyday Be Coding";  
  29.                 ExcelComment cmd = Rng.AddComment(CommentText, "Rajdip");  
  30.                 //cmd.Visible = true;  
  31.             }  
  32.             //Option 2 : Using Add() method of ExcelCommentCollection class.   
  33.             using(ExcelRange Rng = wsSheet1.Cells["A10"]) {  
  34.                 Rng.Value = "Option 2 :";  
  35.                 Rng.Style.Font.Bold = true;  
  36.                 Rng.Style.Font.Color.SetColor(Color.Red);  
  37.             }  
  38.             using(ExcelRange Rng = wsSheet1.Cells["B10"]) {  
  39.                 Rng.Value = "https://everyday-be-coding.blogspot.in/";  
  40.                 ExcelComment cmd = wsSheet1.Comments.Add(Rng, "This a blog URL of my YouTube Channel: Everyday Be Coding", "Rajdip");  
  41.                 //Display and Hide Comments and their Indicators :  
  42.                 cmd.Visible = true;  
  43.                 //Moving Comment Box  
  44.                 cmd.From.Column = 1;  
  45.                 cmd.To.Column = 2;  
  46.                 cmd.From.Row = 12;  
  47.                 cmd.To.Row = 14;  
  48.             }  
  49.             ////Remove Comments in Excel Worksheet   
  50.             //using (ExcelRange Rng = wsSheet1.Cells["B10"])  
  51.             //{  
  52.             // ExcelComment cmd = wsSheet1.Cells["B10"].Comment;  
  53.             // wsSheet1.Comments.Remove(cmd);  
  54.             //}  
  55.             wsSheet1.Cells[wsSheet1.Dimension.Address].AutoFitColumns();  
  56.             ExcelPkg.SaveAs(new FileInfo(@ "D:\Comments.xlsx"));  
  57.         }  
  58.     }  
  59. }  
Now, build & execute this code. File is (Comments.xlsx) stored on D: drive of the computer.
Thank you for reading this article.

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