How To Create And Apply Border (Color, Width) Style In An Excel Comment Using EPPlus Library .Net C# Application

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

Source Code - Click to download [673 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))
  • 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/Border Style in a Comment.
How to Create & Apply Line/Border Style in an Excel Comment?

We need to attach one more namespace OfficeOpenXml.Drawing.Vml, because in this code we can see LineWidth, LineColor, LineStyle properties. These properties belong to ExcelVmlDrawingComment class & ExcelComment class is inherited from

ExcelVmlDrawingComment class so, that's why the object of ExcelComment class gets those properties from his base class.

Please see this below code.
  1. using(ExcelRange Rng = wsSheet1.Cells["B15"]) {  
  2.     ExcelComment cmd = Rng.AddComment("Comment Text""Rajdip");  
  3.     cmd.Font.Bold = true;  
  4.     cmd.Font.Color = Color.Blue;  
  5.     //Apply Line or Border Style in Excel Comment.  
  6.     cmd.LineWidth = 3;  
  7.     Color GreenHexCode = ColorTranslator.FromHtml("#008000");  
  8.     cmd.LineColor = GreenHexCode; //Color.Green;  
  9.     cmd.LineStyle = eLineStyleVml.DashDot;  
  10.     cmd.Visible = true;  
  11. }  
We can use custom color code by using ColorTranslator static class methods,
  • FromHtml (string htmlColor)
  • FromOle (int oleColor)
  • FromWin32 (int win32Color).
return type of these methods are Color struct object.

There is another enum eLineStyleVml is used in this above code. This enum has eight enumerator list. These are

eLineStyleVml

Here, I am using DashDot, that means 4 within enumerator list.

The output of Code,



Full Source Code
  1. using OfficeOpenXml;  
  2. using System.IO;  
  3. using System.Drawing;  
  4. using OfficeOpenXml.Drawing.Vml;  
  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-13.html  
  9.             //Author: Rajdip Sarkar.  
  10.             //Date : 29th 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 - Part 13(E)";  
  16.                 Rng.Style.Font.Size = 16;  
  17.                 Rng.Style.Font.Bold = true;  
  18.                 Rng.Style.Font.Italic = true;  
  19.             }  
  20.             using(ExcelRange Rng = wsSheet1.Cells["B5"]) {  
  21.                 Rng.Style.Font.Size = 20;  
  22.                 Rng.Value = "Excel Comment Border Style";  
  23.                 ExcelComment cmd = Rng.AddComment("Comment Text""Rajdip");  
  24.                 cmd.Font.Bold = true;  
  25.                 cmd.Font.Color = Color.Blue;  
  26.                 //Apply Line or Border Style in Excel Comment.  
  27.                 cmd.LineWidth = 3;  
  28.                 Color GreenHexCode = ColorTranslator.FromHtml("#008000");  
  29.                 cmd.LineColor = GreenHexCode; //Color.Green;  
  30.                 cmd.LineStyle = eLineStyleVml.DashDot;  
  31.                 cmd.Visible = true;  
  32.             }  
  33.             wsSheet1.Cells[wsSheet1.Dimension.Address].AutoFitColumns();  
  34.             ExcelPkg.SaveAs(new FileInfo(@ "D:\FormatComments.xlsx"));  
  35.         }  
  36.     }  
  37. }  
Now build & execute this code. The file is (FormatComments.xlsx) store on D: drive of the computer.

Thank you for reading this article.

X

Build smarter apps with Machine Learning, Bots, Cognitive Services - Start free.

Start Learning Now