EPPlus Library Part-9

EPPLUS Library - Beginners Guide Part-9(A)

How to Change Text, Background Color and Resize Excel Comment Using EPPlus?

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

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

Different types of Format for Excel Comment ?

• Add. Move, Hide & Remove Comment to an Excel Sheet cell (We already discussed in Part 8)
• 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 or Border Style in a Comment. 

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

• When we apply a long text as a Comments in an Excel Comment Box, By default Excel sheet set a static default size of the comment box. As you can see in this below picture. 

• After Resizing or Autofit the Comments box is look like. Please see this below picture. 

So, Next Question in our mind, How adjust resize the comment box. Please see the below code.

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 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 comment box, but AutoFit adjusts the whole text within a single line. So next question is how to show multiple and complete text within a comment box. We used \n new line character or 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 & the type of this property is ExcelRichText class. This class has Color (structure) type color property. so that why we assign Color structure property green to the ExcelRichText class property color. 

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

Output in Excel Sheet:

Source code:
using OfficeOpenXml;
using System.IO;
using System.Drawing;

namespace EpplusDemo
{
    class Program
    {
        static void Main(string[] args)
        {
            //Code download from: https://everyday-be-coding.blogspot.in/p/epplus-library-part-9.html
            //Author: Rajdip Sarkar.
            //Date : 6th June 2017.
            //My YouTube Channel Link : https://www.youtube.com/channel/UCpGuQx5rDbWnc7i_qKDTRSQ
            
            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 as well as experience candidates & also we are focusing on very uncommon & specific topics those are extremely useful in real life software development.";
            string LongText = "We are offering very easy level beginner tutorials\n on Microsoft.NET based 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 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;
            }

            //How to Change the Text & Background Color of 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) store 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