How To Format Comment (Resize And Text, Background Color) In Excel Worksheet Using EPPlus Library - Part Nine

In this tutorial, we will learn step by step Excel Sheet development, using .NET library EPPlus and Language - C#.

Different Type of Format for Excel Comment

• Resize/Auto fit of Comment Box.
• Add Text or a Background Color in Comment Box.
• Set Text Alignments in Comment Box. (We will be discussing in Part-10(B) blog)
• Set Font Style in Comment Box. (We will be discussing in Part-10(B) blog)
• Add Rich Text in Comment box. (We will be discussing in Part-11(C) blog)
• Add Multi Color Rich Text in Excel Cell & Comment Box. (We will be discussing in Part-11(C) blog)
• Set Line or Border Style in Comment box. (We will be discussing in Part-12(D))

How to apply Resize/Autofit in Excel Comment Box using EPPlus

When we are applying a long text comment in an Excel Comment Box, by default Excel sheet sets a static default size of comment box, as you can see in the picture below.

After resizing, the comment box looks like  this below picture.

So, the next question in our mind is how to resize the Excel comment box. Please see the below code.

String LongText = "We are offering very easy level beginner tutorials on Microsoft .NET base platforms, basically for freshers as well as experienced candidates & also we are focusing on very uncommon & specific topics that are extremely useful in real life software development."

using(ExcelRange Rng = wsSheet1.Cells["B4"]) {
    Rng.Value = "Everyday Be Coding";
    ExcelComment cmd = Rng.AddComment(LongText, "Rajdip");
    cmd.AutoFit = true;
    cmd.Visible = true;
}

In this code AutoFit is the property of ExcelRange class. It's responsible for auto adjusting text within the comment box, but AutoFit adjusts the whole text within a single line. So the next question is how to show multiple and complete text within a comment box. We used new line character or you can use Environment.NewLine for line breaking. Here Environment is a static class & NewLine is the property of the class.

How to Set Text & Background color in Excel Comments box using EPPlus ? 

As you can see in this picture. 

using(ExcelRange Rng = wsSheet1.Cells["B10"]) {
    Rng.Value = "https://www.facebook.com/EverydayBeCoding";
    ExcelComment cmd = wsSheet1.Comments.Add(Rng, "This a facebook page URL of my YouTube Channel", "Rajdip");
    cmd.Visible = true;
    cmd.BackgroundColor = Color.Green;
    cmd.Font.Color = Color.White;
    cmd.Visible = true;
}

In this code Font is the property of ExcelComment class and the type of this property is ExcelRichText class. This class has Color (structure) type color property. So that is why we assign Color structure property green to the ExcelRichText class property color.

For applying the background color in the Excel comment box, we need to assign Color structure property to BackgroundColor property of ExcelComment (cmd) class.

Output in Excel Sheet

Source code

using OfficeOpenXml;
using System.IO;
using System.Drawing;
namespace EpplusDemo {
    class Program {
        static void Main(string[] args) {
            ExcelPackage ExcelPkg = new ExcelPackage();
            ExcelWorksheet wsSheet1 = ExcelPkg.Workbook.Worksheets.Add("Sheet1");
            using(ExcelRange Rng = wsSheet1.Cells[2, 2, 2, 2]) {
                Rng.Value = "Everyday Be Coding - Excel COMMENTS Formatting using EPPlus .Net Library";
                Rng.Style.Font.Size = 16;
                Rng.Style.Font.Bold = true;
                Rng.Style.Font.Italic = true;
            }
            //How to Resize or AutoFit Excel Comment Box for Long Text
            string LongText = "We are offering very easy level beginner tutorials\n on Microsoft .NET base platform, basically for fresher\n as well as experience candidates & also we are focusing\n on very uncommon & specific topics those are extremely\n useful on real life software development.";
            using(ExcelRange Rng = wsSheet1.Cells["B4"]) {
                Rng.Value = "Everyday Be Coding";
                ExcelComment cmd = Rng.AddComment(LongText, "Rajdip");
                cmd.AutoFit = true;
                cmd.Visible = true;
            }
            //How to Change the Text & Background Color of Excel Comment Box
            using(ExcelRange Rng = wsSheet1.Cells["B10"]) {
                Rng.Value = "https://www.facebook.com/EverydayBeCoding";
                ExcelComment cmd = wsSheet1.Comments.Add(Rng, "This a facebook page URL of my YouTube Channel", "Rajdip");
                cmd.BackgroundColor = Color.Green;
                cmd.Font.Color = Color.White;
                cmd.Visible = true;
            }
            wsSheet1.Cells[wsSheet1.Dimension.Address].AutoFitColumns();
            ExcelPkg.SaveAs(new FileInfo(@ "D:\FormatComments.xlsx"));
        }
    }
}

Now build & execute this code. File is (FormatComments.xlsx) stored on D: drive of computer.

Thank you for reading this blog.

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