EVERYDAY BE CODING: EPPlus Library Part-10

EPPLUS Library - Beginners Guide Part-10(B)

How to Set Text Alignment & Font Style in Excel Comment using EPPlus library?

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

Apply Text Alignment in Excel Comment 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 comment. There is two type of alignments 1) VerticalAlignment & 2) HorizontalAlignment.
These alignments are property of ExcelComment class & These are assign 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 assign 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, VerticalAlign properties. These are properties of ExcelRichText class. We discuss the ExcelRichtText class in very great details in our 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 in 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) 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

EVERYDAY BE CODING

EPPlus Library Part-10

No comments:

Post a Comment