EPPlus Library Part-4

EPPLUS Library - Beginners Guide Part-4
How to apply cell text Alignment, Row Height, Column Width in excel sheet?




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

Cell Text alignment:
  • EPPlus is support main two categories of text alignments. First is HorizontalAlignment and second is VerticalAlignment.
  • These alignments are object of ExcelHorizontalAlignmentExcelVerticalAlignment class and also the property of ExcelStyle class.
  • Both are respectively assign by ExcelHorizontalAlignment ExcelVerticalAlignment enum.
Example:

  • ExcelRange Rng = new ExcelRange();                   
  • Rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
  • Rng.Style.VerticalAlignment = ExcelVerticalAlignment.Center;                    
*By default EPPlus support Left Horizontal & Bottom Vertical text alignment, if you are not specify any alignments. 

Excel Row Height:                  
  • ExcelWorkSheet wsSheet1 = ExcelPkg.Workbook.Worksheets.Add("Sheet1");
  • wsSheet1.Row(4).Height = 30;
In this example Row() method accept value 4 as a parameter. here 4 is the row number. It provides to access the individual row within a excel sheet.
Height property sets the height of row. It accepts the double value 30
Here Row() method and Height property both are belongs to ExcelRow class.

Column Width:
  • wsSheet1.Cells[wsSheet1.Dimension.Address].AutoFitColumns(); 
In this example dimension property is the address of worksheet from Top left to Bottom right cell & If the excel sheet has no cells, null is returned. 

AutoFitColumns() is responsible for set columns width from the content of range. Cells containing formulas are ignored since EPPlus don't have a calculation engine. Wrapped & merge cells are also ignored. 

Output on 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;
            }
           wsSheet1.Cells[wsSheet1.Dimension.Address].AutoFitColumns();
            using (ExcelRange Rng = wsSheet1.Cells[4, 2, 4, 2])
            {
                wsSheet1.Row(4).Height = 30;
                Rng.Value = "Horizontal: CENTER & Vertical: CENTER";
                Rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                Rng.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
            }
            using (ExcelRange Rng = wsSheet1.Cells[5, 2, 5, 2])
            {
                wsSheet1.Row(5).Height = 30;
                Rng.Value = "Horizontal: LEFT & Vertical: TOP";
                Rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
                Rng.Style.VerticalAlignment = ExcelVerticalAlignment.Top;
            }
            using (ExcelRange Rng = wsSheet1.Cells[6, 2, 6, 2])
            {
                wsSheet1.Row(6).Height = 30;
                Rng.Value = "Horizontal: RIGHT & Vertical: BOTTOM";
                Rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
                Rng.Style.VerticalAlignment = ExcelVerticalAlignment.Bottom;
            }
            using (ExcelRange Rng = wsSheet1.Cells[7, 2, 7, 2])
            {
                wsSheet1.Row(7).Height = 30;
                Rng.Value = "Horizontal: FILL & Vertical: DISTRIBUTED";
                Rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Fill;
                Rng.Style.VerticalAlignment = ExcelVerticalAlignment.Distributed;
            }
            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

No comments:

Post a Comment