How To Set Text Alignment And Font Style In Excel Comment Using EPPlus Library - Part Ten

How do we set text alignment & font style in Excel comments using EPPlus library? What are different types of formatting for Excel comments?

• Resize/Auto fit comment. (We have seen in Part 9(A))
• Add text or a background color in the comment box. (We have seen in Part 9(A))
• Set text alignments in Excel comments.
• Set font style in Excel comments.
• Add rich text in Excel comments. (We will be discuss in Part-11(C))
• Add multi color rich text in Excel cell & comments. (We will discuss in Part-11(C))
• Set line or border style in Excel comments. (We will be discuss in Part-12(C))

Apply text alignment in Excel comments using EPPlus 

• First, we need to attach OfficeOpenXml.Drawing.Vml, this namespace is required for text alignment of excel comment.
• In the Part-4 video, we have seen how to apply text alignment in Excel cell text but in this video I will show you how to apply text alignment in Excel comments. There are two type of alignments 1) VerticalAlignment & 2) HorizontalAlignment.
• These alignments are the property of Excel comment class and these are assigned by eTextAlignVerticalVml and eTextAlignHorizontalVml enum.
  
  
  

  using(ExcelRange Rng = wsSheet1.Cells["B5"]) {
      Rng.Value = "Everyday Be Coding";
      ExcelComment cmd = Rng.AddComment(LongText, "Rajdip");
      cmd.AutoFit = true;
      cmd.Visible = true;
      //set alignment in excel comment
      cmd.VerticalAlignment = eTextAlignVerticalVml.Center;
      cmd.HorizontalAlignment = eTextAlignHorizontalVml.Center;
  }

• In this above code VerticalAlignment & HorizontalAlignment properties are assigned by eTextAlignVerticalVml & eTextAlignHorizontalVml enum.

Apply Font Style in Excel Comment Box using EPPlus?



Please see this below Code

using(ExcelRange Rng = wsSheet1.Cells["B12"]) {
    Rng.Value = "https://www.facebook.com/EverydayBeCoding/";
    ExcelComment cmd = Rng.AddComment("This a facebook page URL of my YouTube Channel.", "Rajdip");
    cmd.Font.FontName = "Arial Black";
    cmd.Font.Color = Color.Red;
    cmd.Font.Size = 20;
    cmd.Font.Bold = true;
    cmd.Font.Italic = true;
    cmd.Font.UnderLine = true;
    //cmd.Font.Strike = true;
    cmd.Visible = true;
}

You can set Font style by using Font Property and this Font property has FontName, Size, Bold, Italic, UnderLine, Strike, VerticalAlignproperties. These are properties of ExcelRichText class. We discussed theExcelRichText class in very great detail in Part 11(C) of this video series.

Output in Excel Sheet

Full Source code

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using OfficeOpenXml;
using System.IO;
using System.Drawing;
using OfficeOpenXml.Drawing;
using OfficeOpenXml.Style;
using OfficeOpenXml.Drawing.Vml;
namespace EpplusDemo {
    class Program {
        static void Main(string[] args) {
            //Code download from: https://everyday-be-coding.blogspot.in/p/epplus-library-part-10.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;
            }
            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.";
            //How to Set Text Alignment in Excel Comment Box
            using(ExcelRange Rng = wsSheet1.Cells["B5"]) {
                Rng.Value = "Everyday Be Coding";
                ExcelComment cmd = Rng.AddComment(LongText, "Rajdip");
                cmd.AutoFit = true;
                cmd.Visible = true;
                cmd.VerticalAlignment = eTextAlignVerticalVml.Center;
                cmd.HorizontalAlignment = eTextAlignHorizontalVml.Right;
            }
            //How to Set Font Style in Excel Comment Box
            using(ExcelRange Rng = wsSheet1.Cells["B12"]) {
                Rng.Value = "https://www.facebook.com/EverydayBeCoding/";
                ExcelComment cmd = Rng.AddComment("This a facebook page URL of my YouTube Channel.", "Rajdip");
                cmd.Font.FontName = "Arial Black";
                cmd.Font.Size = 20;
                cmd.Font.Bold = true;
                cmd.Font.Italic = true;
                cmd.Font.UnderLine = true;
                //cmd.Font.Strike = true;
                cmd.Visible = true;
            }
            wsSheet1.Cells[wsSheet1.Dimension.Address].AutoFitColumns();
            ExcelPkg.SaveAs(new FileInfo(@ "D:\Comments.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