Post
Ask Question

How To Add And Remove Multi Style & Multiple Rich Text In Excel Comment Using EPPlus Library (C#) - PART Eleven

I strongly encourage everyone to watch these suggested videos before reading this blog.
How to add Rich Text (Single or Multiple) in Excel Comment using EPPlus?

First, we need to attach one more namespace "OfficeOpenXml.Style".

We have seen in Part-8 of this video series the AddComment() method of ExcelRange class accepts two things as a parameter. 1st parameter is string comment & the second is the string author. Here, the first string parameter is a rich text. Whenever we assign text is Excel comment, rich text is involved automatically. This rich text takes a first position of comment text that means zero index position.

Actually, ExcelComment class object accepts one or more rich text. We can add multiple rich texts by using RichText property of ExcelComment class. This RichText property is the type of ExcelRichTextCollection class. Indirectly, this collection class is involve by the add() method of this collection class. We can assign each rich text one by one, starting from zero based index positions.

Please see this below code.
  1. using(ExcelRange Rng = wsSheet1.Cells["B5"]) {  
  2.     Rng.Value = "Add Multiple Rich Text in Excel Comment by Index Position.";  
  3.     Rng.Style.Font.Color.SetColor(Color.Red);  
  4.     ExcelComment cmd = Rng.AddComment("Rich Text [Index 0]\n", "Rajdip");  
  5.     //We can Add multiple Rich Text in ExcelComment object   
  6.     cmd.RichText.Add("Rich Text [Index 1]\n");  
  7.     cmd.RichText.Add("Rich Text [Index 2]\n");  
  8.     cmd.RichText.Add("Rich Text [Index 3]");  
  9.     cmd.Visible = true;  
  10. }  
How to add Multi Style Rich Text in Excel Comment using EPPlus?

Please see this below code.
  1. using(ExcelRange Rng = wsSheet1.Cells["B10"]) {  
  2.     Rng.Value = "Add Multi Style Rich Text in Excel Comment.";  
  3.     Rng.Style.Font.Color.SetColor(Color.Red);  
  4.     ExcelComment cmd = Rng.AddComment("Everyday Be Coding ", "Rajdip");  
  5.     cmd.Font.Bold = true;  
  6.     cmd.Font.UnderLine = true;  
  7.     cmd.Font.Italic = true;  
  8.     cmd.Font.Color = Color.Red;  
  9.     cmd.Font.FontName = "Arial Rounded MT Bold";  
  10.     //RichText at Position 1   
  11.     ExcelRichText RichText1 = cmd.RichText.Add("is my ");  
  12.     RichText1.Bold = true;  
  13.     RichText1.Italic = false;  
  14.     RichText1.Color = Color.Green;  
  15.     RichText1.FontName = "Arial Narrow";  
  16.     //RichText at Position 2   
  17.     ExcelRichText RichText2 = cmd.RichText.Add("YouTube ");  
  18.     RichText2.Bold = true;  
  19.     RichText2.Italic = false;  
  20.     RichText2.Color = Color.Blue;  
  21.     RichText2.FontName = "Arial";  
  22.     //RichText at Position 3   
  23.     ExcelRichText RichText3 = cmd.RichText.Add("Channel.");  
  24.     RichText3.Bold = true;  
  25.     RichText3.Italic = false;  
  26.     RichText3.Color = Color.Brown;  
  27.     RichText3.FontName = "Arial Black";  
  28.     cmd.Visible = true;  
  29. }  
We can add style of excel comment rich text in two way, first we need to use Font (This property is the type of ExcelRichText class) property of ExcelComment class (we have seen in Part-10(B) of this video series) or we can directly use ExcelRichText class. This ExcelRichText following properties. Please see this below picture.


How Remove Rich Text in Excel Comment using EPPlus?

We can also remove ExcelRichText object using RemoveAt(int Index) method (by index position) of ExcelRichTextCollection class.

In this code, RichText is the property of ExcelComment class & RichText property is the type of ExcelRichTextCollection class.

Please see this below code.
  1. using(ExcelRange Rng = wsSheet1.Cells["B15"]) {  
  2.     Rng.Value = "Remove Rich Text in Excel Comment by Index Position.";  
  3.     Rng.Style.Font.Color.SetColor(Color.Red);  
  4.     ExcelComment cmd = Rng.AddComment("Rich Text [Index 0]\n", "Rajdip");  
  5.     cmd.RichText.Add("Rich Text [Index 1]\n");  
  6.     cmd.RichText.Add("Rich Text [Index 2]\n");  
  7.     cmd.RichText.Add("Rich Text [Index 3]");  
  8.     //Remove Rich Text at Second position.  
  9.     cmd.RichText.RemoveAt(2);  
  10.     //string count = cmd.RichText.Count.ToString();  
  11.     cmd.Visible = true;  
  12. }  
Output in Excel Sheet


Full Source code
  1. using OfficeOpenXml;  
  2. using System.IO;  
  3. using System.Drawing;  
  4. using OfficeOpenXml.Style;  
  5. namespace EpplusDemo {  
  6.     class Program {  
  7.         static void Main(string[] args) {  
  8.             //Code download from: https://everyday-be-coding.blogspot.in/p/epplus-library-part-11.html  
  9.             //Author: Rajdip Sarkar.  
  10.             //Date : 3rd July 2017.  
  11.             //My YouTube Channel Link : https://www.youtube.com/channel/UCpGuQx5rDbWnc7i_qKDTRSQ  
  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 Formatting using EPPlus .Net Library";  
  16.                 Rng.Style.Font.Size = 16;  
  17.                 Rng.Style.Font.Bold = true;  
  18.                 Rng.Style.Font.Italic = true;  
  19.             }  
  20.             //Add Multiple Rich Text Box in Excel Comment by Index Position.  
  21.             using(ExcelRange Rng = wsSheet1.Cells["B5"]) {  
  22.                 Rng.Value = "Add Multiple Rich Text in Excel Comment by Index Position.";  
  23.                 Rng.Style.Font.Color.SetColor(Color.Red);  
  24.                 ExcelComment cmd = Rng.AddComment("Rich Text [Index 0]\n", "Rajdip");  
  25.                 //We can Add multiple Rich Text in ExcelComment object   
  26.                 cmd.RichText.Add("Rich Text [Index 1]\n");  
  27.                 cmd.RichText.Add("Rich Text [Index 2]\n");  
  28.                 cmd.RichText.Add("Rich Text [Index 3]");  
  29.                 cmd.Visible = true;  
  30.             }  
  31.             //How to Add Multi Style Rich Text in Excel Comment   
  32.             using(ExcelRange Rng = wsSheet1.Cells["B10"]) {  
  33.                 Rng.Value = "Add Multi Style Rich Text in Excel Comment.";  
  34.                 Rng.Style.Font.Color.SetColor(Color.Red);  
  35.                 ExcelComment cmd = Rng.AddComment("Everyday Be Coding ", "Rajdip");  
  36.                 cmd.Font.Bold = true;  
  37.                 cmd.Font.UnderLine = true;  
  38.                 cmd.Font.Italic = true;  
  39.                 cmd.Font.Color = Color.Red;  
  40.                 cmd.Font.FontName = "Arial Rounded MT Bold";  
  41.                 //RichText at Position 1   
  42.                 ExcelRichText RichText1 = cmd.RichText.Add("is my ");  
  43.                 RichText1.Bold = true;  
  44.                 RichText1.Italic = false;  
  45.                 RichText1.Color = Color.Green;  
  46.                 RichText1.FontName = "Arial Narrow";  
  47.                 //RichText at Position 2   
  48.                 ExcelRichText RichText2 = cmd.RichText.Add("YouTube ");  
  49.                 RichText2.Bold = true;  
  50.                 RichText2.Italic = false;  
  51.                 RichText2.Color = Color.Blue;  
  52.                 RichText2.FontName = "Arial";  
  53.                 //RichText at Position 3   
  54.                 ExcelRichText RichText3 = cmd.RichText.Add("Channel.");  
  55.                 RichText3.Bold = true;  
  56.                 RichText3.Italic = false;  
  57.                 RichText3.Color = Color.Brown;  
  58.                 RichText3.FontName = "Arial Black";  
  59.                 cmd.Visible = true;  
  60.             }  
  61.             //Remove Rich Text in Excel Comment by Index Position.   
  62.             using(ExcelRange Rng = wsSheet1.Cells["B15"]) {  
  63.                 Rng.Value = "Remove Rich Text in Excel Comment by Index Position.";  
  64.                 Rng.Style.Font.Color.SetColor(Color.Red);  
  65.                 ExcelComment cmd = Rng.AddComment("Rich Text [Index 0]\n", "Rajdip");  
  66.                 cmd.RichText.Add("Rich Text [Index 1]\n");  
  67.                 cmd.RichText.Add("Rich Text [Index 2]\n");  
  68.                 cmd.RichText.Add("Rich Text [Index 3]");  
  69.                 //Remove Rich Text at Second position.  
  70.                 cmd.RichText.RemoveAt(2);  
  71.                 //string count = cmd.RichText.Count.ToString();  
  72.                 cmd.Visible = true;  
  73.             }  
  74.             wsSheet1.Cells[wsSheet1.Dimension.Address].AutoFitColumns();  
  75.             ExcelPkg.SaveAs(new FileInfo(@ "D:\FormatExcelComments.xlsx"));  
  76.         }  
  77.     }  
  78. }  
Now, build & execute this code. File is (FormatExcelComments.xlsx) stored 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
Next Recommended Reading How To Format Excel Table Using EPPlus Library Part Fifteen - C#