Post
Ask Question

How To Apply Cell Text Alignment, Row Height, Column Width in An Excel Sheet (C#) - Part Four

Excel Cell Text alignment
  • EPPlus supports main two categories of (Horizontal & Vertical) text alignments.
  • Each alignment is respectively assigned by ExcelHorizontalAlignment& ExcelVerticalAlignment enum.


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

Excel Row Height
  • ExcelWorkSheet wsSheet1 = ExcelPkg.Workbook.Worksheets.Add("Sheet1");
  • wsSheet1.Row(4).Height = 30;
*In this example, Row method accepts value 4 as a parameter for row number & height properties assigns 30 as double type.

Column Width
  • wsSheet1.Cells[wsSheet1.Dimension.Address].AutoFitColumns();
*In this example, dimension property is the address of the worksheet from left to right cell & AutoFitColumns() is responsible to set columns width, as per the length of content of the cell range.

Output on an Excel sheet is given below.



Source code
  1. using OfficeOpenXml;  
  2. using System.IO;  
  3. using System;  
  4. //add two new namespace  
  5. using OfficeOpenXml.Style;  
  6. using System.Drawing;  
  7. class Program {  
  8.     static void Main(string[] args) {  
  9.         ExcelPackage ExcelPkg = new ExcelPackage();  
  10.         ExcelWorksheet wsSheet1 = ExcelPkg.Workbook.Worksheets.Add("Sheet1");  
  11.         using(ExcelRange Rng = wsSheet1.Cells[2, 2, 2, 2]) {  
  12.             Rng.Value = "Welcome to Everyday be coding - tutorials for beginners";  
  13.             Rng.Style.Font.Size = 16;  
  14.             Rng.Style.Font.Bold = true;  
  15.             Rng.Style.Font.Italic = true;  
  16.         }  
  17.         wsSheet1.Cells[wsSheet1.Dimension.Address].AutoFitColumns();  
  18.         using(ExcelRange Rng = wsSheet1.Cells[4, 2, 4, 2]) {  
  19.             wsSheet1.Row(4).Height = 30;  
  20.             Rng.Value = "Horizontal: CENTER & Vertical: CENTER";  
  21.             Rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;  
  22.             Rng.Style.VerticalAlignment = ExcelVerticalAlignment.Center;  
  23.         }  
  24.         using(ExcelRange Rng = wsSheet1.Cells[5, 2, 5, 2]) {  
  25.             wsSheet1.Row(5).Height = 30;  
  26.             Rng.Value = "Horizontal: LEFT & Vertical: TOP";  
  27.             Rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;  
  28.             Rng.Style.VerticalAlignment = ExcelVerticalAlignment.Top;  
  29.         }  
  30.         using(ExcelRange Rng = wsSheet1.Cells[6, 2, 6, 2]) {  
  31.             wsSheet1.Row(6).Height = 30;  
  32.             Rng.Value = "Horizontal: RIGHT & Vertical: BOTTOM";  
  33.             Rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;  
  34.             Rng.Style.VerticalAlignment = ExcelVerticalAlignment.Bottom;  
  35.         }  
  36.         using(ExcelRange Rng = wsSheet1.Cells[7, 2, 7, 2]) {  
  37.             wsSheet1.Row(7).Height = 30;  
  38.             Rng.Value = "Horizontal: FILL & Vertical: DISTRIBUTED";  
  39.             Rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Fill;  
  40.             Rng.Style.VerticalAlignment = ExcelVerticalAlignment.Distributed;  
  41.         }  
  42.         wsSheet1.Protection.IsProtected = false;  
  43.         wsSheet1.Protection.AllowSelectLockedCells = false;  
  44.         ExcelPkg.SaveAs(new FileInfo(@ "D:\New.xlsx"));  
  45.     }  
  46. }  
Now, build & execute the code. File is (New.xlsx), which is stored on D: drive of the computer.
Thank you for reading this blog.