C#

How To Add Multi Style And Multiple Rich Text In Excel Cell And Comment Using ExcelRichTextCollection Collection Class In EPPlus .Net Library (C#) - Part - Twelve (D)

I strongly encourage everyone to watch these suggested videos before reading this blog.
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(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.
  • Set Line or Border Style in a Comment. (We will discuss on Part 13(E))
What is ExcelRichTextCollection Class?

ExcelRichTextCollection class is a collection, containing the ExcelRichText objects. This class belongs to OfficeOpenXml.Style namespace.


This class has five important methods, these are,
  1. Add(String Text)
  2. Insert(Integer Index, String Text) 
    By using these two above methods, we can add or insert an ExcelRichText object in ExcelRichTextCollection class. 
  3. Remove(ExcelRichText Item) 
  4. RemoveAt(int index)
    by using these two above methods, we can remove ExcelRichText objects by object name & Index position in ExcelRichTextCollection class. 
  5. Clear() 
    by using this method, we can remove all ExcelRichText objects within this ExcelRichTextCollection class.
This class has three important property, these are,
  1. Count (Read only Property)
    It returns integer count value of how many ExcelRichText object present in a single ExcelRichTextCollection class at runtime. 

  2. Text (Read & Write Both)
    By using this property, we assign string text in ExcelRichTextCollection object. 

  3. ExcelRichText this [int Index]
    This is a very important property. Here, [int Index] is the indexer. 
If you don't know what index member of class is, please visit this link.https://www.dotnetperls.com/indexer.
 
By using this property, we assign specific ExcelRichText object by it's index position.

How to Add Multi Style Text in Excel Cell using EPPlus?

ExcelRange Class has property RichText (if you don't know what is ExcelRange Class, please watch my previous video Part-1 first). This RichText property is the type of ExcelRichTextCollection class.

First, we assign Rng.RichText property to the object of ExcelRichTextCollectionClass. Here, Rng is the object of ExceRange class & RichTxtCollection is the object of ExcelRichTextCollection class.

*After that, we use Add() method of ExcelRichTextCollection class for adding String text for individually applied each RichText style. We assign this Collection object to the ExcelRichText class object. Here, RichText is the object of ExcelRichText class. Using this object property, we set different styles in the text.

Please see this below code.
  1. using(ExcelRange Rng = wsSheet1.Cells["B5"]) {  
  2.     Rng.Style.Font.Size = 20;  
  3.     //How to add multi style text in excel cell text  
  4.     ExcelRichTextCollection RichTxtCollection = Rng.RichText;  
  5.     ExcelRichText RichText = RichTxtCollection.Add("H");  
  6.     RichText.Color = Color.Red;  
  7.     RichText.Italic = true;  
  8.     //RichTxtCollection.Remove(RichText);  
  9.     RichText = RichTxtCollection.Add("2");  
  10.     //RichText = RichTxtCollection.Insert(1, "2");  
  11.     RichText.Color = Color.Red;  
  12.     RichText.Italic = true;  
  13.     RichText.VerticalAlign = ExcelVerticalAlignmentFont.Subscript;  
  14.     RichText = RichTxtCollection.Add("O");  
  15.     RichText.Color = Color.Red;  
  16.     RichText.Italic = false;  
  17.     RichText = RichTxtCollection.Add(" & ");  
  18.     RichText.Color = Color.Black;  
  19.     RichText.Italic = false;  
  20.     RichText = RichTxtCollection.Add("E=MC");  
  21.     RichText.Color = Color.Blue;  
  22.     RichText.Italic = false;  
  23.     RichText = RichTxtCollection.Add("2");  
  24.     RichText.Color = Color.Blue;  
  25.     RichText.Italic = false;  
  26.     RichText.VerticalAlign = ExcelVerticalAlignmentFont.Superscript;  
  27.     //RichTxtCollection.Clear();  
  28. }  
How to Add Multi Style in Excel Comment Text Using EPPlus?

We have seen in Part-11(C) of this video series that the RichText property of ExcelComment class object can create individual ExcelRichText class object & add those objects as a collection object. This Collection Object is nothing but an ExcelRichTextCollection class because the RichText property is the type of ExcelRichTextCollecion class. So, ExcelRichTextCollecion class is indirectly involved by the ExcelComment class.

Please see the below code.
  1. using(ExcelRange Rng = wsSheet1.Cells["B5"]) {  
  2.     //Apply ExcelRichTextCollection Class on Excel Comment  
  3.     ExcelComment cmd = Rng.AddComment("Water Symbol : ", "Rajdip");  
  4.     ExcelRichTextCollection RichTxtCollectionComment = cmd.RichText;  
  5.     cmd.RichText[0].PreserveSpace = false;  
  6.     cmd.Font.Bold = true;  
  7.     cmd.Font.Color = Color.Blue;  
  8.     ExcelRichText RichTextComment = RichTxtCollectionComment.Add("H");  
  9.     RichTextComment.Color = Color.Red;  
  10.     RichTextComment = RichTxtCollectionComment.Add("2");  
  11.     RichTextComment.Color = Color.Red;  
  12.     RichTextComment.VerticalAlign = ExcelVerticalAlignmentFont.Subscript;  
  13.     RichTextComment = RichTxtCollectionComment.Add("O");  
  14.     RichTextComment.Color = Color.Red;  
  15.     RichTextComment = RichTxtCollectionComment.Add(" & \n");  
  16.     RichTextComment.Color = Color.Purple;  
  17.     RichTextComment = RichTxtCollectionComment.Add("Formula : ");  
  18.     RichTextComment.Bold = true;  
  19.     RichTextComment.Color = Color.Blue;  
  20.     RichTextComment = RichTxtCollectionComment.Add("E=MC");  
  21.     RichTextComment.Color = Color.Red;  
  22.     RichTextComment = RichTxtCollectionComment.Add("2");  
  23.     RichTextComment.VerticalAlign = ExcelVerticalAlignmentFont.Superscript;  
  24.     RichTextComment.Color = Color.Red;  
  25.     cmd.Visible = true;  
  26. }  
Output in Excel Sheet.


Full Source code
  1. using OfficeOpenXml;  
  2. using System.IO;  
  3. using System.Drawing;  
  4. using OfficeOpenXml.Style;  
  5. using OfficeOpenXml.Drawing.Vml;  
  6. namespace EpplusDemo {  
  7.     class Program {  
  8.         static void Main(string[] args) {  
  9.             //Code download from: https://everyday-be-coding.blogspot.in/p/epplus-library-part-12.html  
  10.             //Author: Rajdip Sarkar.  
  11.             //Date : 16th July 2017.  
  12.             //My YouTube Channel Link : https://www.youtube.com/channel/UCpGuQx5rDbWnc7i_qKDTRSQ  
  13.             ExcelPackage ExcelPkg = new ExcelPackage();  
  14.             ExcelWorksheet wsSheet1 = ExcelPkg.Workbook.Worksheets.Add("Sheet1");  
  15.             using(ExcelRange Rng = wsSheet1.Cells[2, 2, 2, 2]) {  
  16.                 Rng.Value = "Everyday Be Coding - Excel COMMENTS Formatting using EPPlus .Net Library";  
  17.                 Rng.Style.Font.Size = 16;  
  18.                 Rng.Style.Font.Bold = true;  
  19.                 Rng.Style.Font.Italic = true;  
  20.             }  
  21.             //How to add multiple Style text in Excel Cell & Excel Comment  
  22.             using(ExcelRange Rng = wsSheet1.Cells["B5"]) {  
  23.                 Rng.Style.Font.Size = 20;  
  24.                 ExcelRichTextCollection RichTxtCollection = Rng.RichText;  
  25.                 ExcelRichText RichText = RichTxtCollection.Add("H");  
  26.                 RichText.Color = Color.Red;  
  27.                 RichText.Italic = true;  
  28.                 //RichTxtCollection.Remove(RichText);  
  29.                 RichText = RichTxtCollection.Add("2");  
  30.                 //RichText = RichTxtCollection.Insert(1, "2");  
  31.                 RichText.Color = Color.Red;  
  32.                 RichText.Italic = true;  
  33.                 RichText.VerticalAlign = ExcelVerticalAlignmentFont.Subscript;  
  34.                 RichText = RichTxtCollection.Add("O");  
  35.                 RichText.Color = Color.Red;  
  36.                 RichText.Italic = false;  
  37.                 RichText = RichTxtCollection.Add(" & ");  
  38.                 RichText.Color = Color.Black;  
  39.                 RichText.Italic = false;  
  40.                 RichText = RichTxtCollection.Add("E=MC");  
  41.                 RichText.Color = Color.Blue;  
  42.                 RichText.Italic = false;  
  43.                 RichText = RichTxtCollection.Add("2");  
  44.                 RichText.Color = Color.Blue;  
  45.                 RichText.Italic = false;  
  46.                 RichText.VerticalAlign = ExcelVerticalAlignmentFont.Superscript;  
  47.                 //RichTxtCollection.Clear();  
  48.                 //Apply ExcelRichTextCollection Class on Excel Comment  
  49.                 ExcelComment cmd = Rng.AddComment("Water Symbol : ", "Rajdip");  
  50.                 ExcelRichTextCollection RichTxtCollectionComment = cmd.RichText;  
  51.                 cmd.RichText[0].PreserveSpace = false;  
  52.                 cmd.Font.Bold = true;  
  53.                 cmd.Font.Color = Color.Blue;  
  54.                 ExcelRichText RichTextComment = RichTxtCollectionComment.Add("H");  
  55.                 RichTextComment.Color = Color.Red;  
  56.                 RichTextComment = RichTxtCollectionComment.Add("2");  
  57.                 RichTextComment.Color = Color.Red;  
  58.                 RichTextComment.VerticalAlign = ExcelVerticalAlignmentFont.Subscript;  
  59.                 RichTextComment = RichTxtCollectionComment.Add("O");  
  60.                 RichTextComment.Color = Color.Red;  
  61.                 RichTextComment = RichTxtCollectionComment.Add(" & \n");  
  62.                 RichTextComment.Color = Color.Purple;  
  63.                 RichTextComment = RichTxtCollectionComment.Add("Formula : ");  
  64.                 RichTextComment.Bold = true;  
  65.                 RichTextComment.Color = Color.Blue;  
  66.                 RichTextComment = RichTxtCollectionComment.Add("E=MC");  
  67.                 RichTextComment.Color = Color.Red;  
  68.                 RichTextComment = RichTxtCollectionComment.Add("2");  
  69.                 RichTextComment.VerticalAlign = ExcelVerticalAlignmentFont.Superscript;  
  70.                 RichTextComment.Color = Color.Red;  
  71.             }  
  72.             wsSheet1.Cells[wsSheet1.Dimension.Address].AutoFitColumns();  
  73.             ExcelPkg.SaveAs(new FileInfo(@ "D:\FormatCell&Comments.xlsx"));  
  74.         }  
  75.     }  
  76. }  
Now, build & execute this code. File is (FormatCell&Comments.xlsx) stored on D: drive of the 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
Ebook Download
View all
Learn
View all
Get Started

You need to be a premium member to use this feature. To access it, you'll have to upgrade your membership.

Become a sharper developer and jumpstart your career.

Basic

$0

$

. 00

monthly

For Basic members:

  • Standard Profile
  • Access learning courses
  • Access free e-books
  • 1x point
Premium

$20

$

. 00

monthly

For Premium members:

  • Earn 2x reward points
  • Premium profile with Verified check
  • Premium Avatar with status update
  • Learning courses
  • Unlimited Certifications and badges on profile
  • Unlimited Book downloads
  • Access to Premium content
  • Online Training
  • Unlimited PDF conversions and downloads
  • Professional Resume Writing
  • Interview Preparations Guide
  • 25 messages per month
  • 1 "Kodzu" avatar, basic
Elite

$45

$

. 00

monthly

For Elite members:

  • Bundle E-books
  • Access to all Learn Series
  • Unlimited Apply Jobs