EPPlus Library Part-11

EPPLUS Library - Beginners Guide Part-11(C)

How to Add Multi Style Rich Text in Excel Cell & Comment Using ExcelRichTextCollection class in EPPlus?  

Suggested video :  EPPLUS Library - Beginners Guide Part-8
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-12(D)
                                    


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

Different types of Format for Excel Comment
  • Add. Move, Hide & Remove Comment to an Excel Sheet cell (We already discussed in Part 8)
  • 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))
  • Multi Style Excel Cell & Comment Text using ExcelRichTextCollection Class. (We already discussed in Part 12(D))
  • Set Line or Border Style in a Comment. 

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 the 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 the RichText property of ExcelComment class. This RichText property is the type of ExcelRichTextCollection class. Indirectly, this collection class is involved 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

    No comments:

    Post a Comment