EPPlus Library Part-2

EPPLUS Library - Beginners Guide Part-2
How to apply cell text & background color in excel sheet?




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

Cell text color:
  • In Epplus ExcelRange class has a style property. This style property is the type of ExcelStyle (seal) class. 
  • ExcelStyle class has another ExcelFont (seal) type  font properties. 
  • ExcelFont class has ExcelColor (seal) type color propertyExcelColor has SetColor method. This method can accept Color structure object as a parameter. 
Example:
  • ExcelRange Rng = new ExcelRange();
  • Rng.Style.Font.Color.SetColor(Color.Red)
*By default EPPlus support black font color, if you are not specify any font color. 

Now next question in our mind, how to set cell background color? 
Example: 
  • Rng.Style.Fill.PatternType = ExcelFillStyle.Solid;
  • Rng.Style.Fill.BackgroundColor.SetColor(Color.Green);
  • Here SetColor method can support structure Color property as a parameter. 
  • You can also specify the HTML Color code.
*By default Epplus support white background color.
For Example:
  • Color DeepBlueHexCode = ColorTranslator.FromHtml("#254061");
  • Rng.Style.Fill.BackgroundColor.SetColor(DeepBlueHexCode);
  • In this example FromHtml() directly accept HTML RGB (Red Green Blue) color Code.
Output in Excel Sheet:


Source code:
using OfficeOpenXml;
using System.IO;
using System;
//add two new namespace
using OfficeOpenXml.Style;
using System.Drawing;

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 = "Welcome to Everyday be coding - tutorials for beginners";
                Rng.Style.Font.Size = 16;
                Rng.Style.Font.Bold = true;
                Rng.Style.Font.Italic = true;
            }

            //First Border Box
            using (ExcelRange Rng = wsSheet1.Cells[5, 2, 8, 4])
            {
                Rng.Value = "Text Color & Background Color";
                Rng.Merge = true;
                Rng.Style.Font.Bold = true;

                Rng.Style.Font.Color.SetColor(Color.Red); 
                Rng.Style.Fill.PatternType = ExcelFillStyle.Solid; 
                Rng.Style.Fill.BackgroundColor.SetColor(Color.LightBlue);       
            }
            wsSheet1.Protection.IsProtected = false;
            wsSheet1.Protection.AllowSelectLockedCells = false;
            ExcelPkg.SaveAs(new FileInfo(@"D:\New.xlsx"));
        }
    }
  • Now build & execute this code. File is (New.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

3 comments: