EPPlus Library Part-5

EPPLUS Library - Beginners Guide Part-5
How to adding pictures or images in excel sheet?

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

First adding two namespace:

• using System.Drawing;
• using OfficeOpenXml.Drawing;

Example:

• Image img = Image.FromFile(@"D:\sample.png");                    

Now next question in our mind is that Image is a abstract class & we know that we never create an instance of abstract class. so above line is violate the rules of Object Oriented Programming(OOP) concept? 
The answer is no, OOP concept is still valid for this above line. 
because the real fact that Image.FromFile(@"D:\sample.png") returns an image reference variable, It does not mean that it returns an instance of abstract class. It can only returns a subclass (means that class is inherited) of abstract Image class. 
The method FromFile(@"D:\sample.png") determines what it returns based on what it finds in the file & given to the subclass of Image class. I think now your concept is much clear.

Next lines:                  

• int RowIndex = 4;
• int ColIndex = 2;
• ExcelPicture pic = wsSheet1.Drawings.AddPicture("Picture_Name", img);
• pic.SetPosition(RowIndex, 0, ColIndex, 0);

Here ExcelPicture is seal class & it inherited from ExcelDrawing class. Here Drawings property is the type of  ExcelDrawings class & assign a property of ExcelSheet class object wsSheet1. 

Next line if we are go to the definition of  ExcelPicture class, As we can see it has no SetPosition(RowIndex - 1, 0, ColIndex - 1, 5) method. this method is given from parent class ExcelDrawing.

Here SetPosition() is self two overloaded version. we are already seen first overload method it accept SetPosition(int RowIndex, int RowOffsetPixels, int ColIndex, int ColumnOffsetPixels) & second overload is SetPosition(PixelTop, PixelLeft), both parameters are int type. PixelTop count the pixel from top & PixelLeft count the pixel from the left size of your computer screen.

For Example:  

• ExcelPicture pic = wsSheet1.Drawings.AddPicture("Picture_Name", img);
• int PixelTop = 88;
• int PixelLeft = 129;
• pic.SetPosition(PixelTop, PixelLeft);

At the end ExcelPicture has own method SetSize(int Width, int Height) & SetSize(int percent) (two overloaded version). It can accept picture width & height as integer type parameter & second overload method accept overall original picture width & height in percent. 

*Note resizing columns and rows after using SetSize(int percent) function will effect the size of picture.

For Example: 

• int Width = 320;
• int Height= 200;
• pic.SetSize(Width, Height);
• pic.SetSize(40);

Output in Excel Sheet:

Source code:
using OfficeOpenXml;
using System.IO;
//add two new namespace
using OfficeOpenXml.Drawing;
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.Merge = true;
                Rng.Style.Font.Size = 16;
                Rng.Style.Font.Bold = true;
                Rng.Style.Font.Italic = true;
            }
               int rowIndex = 4;
               int colIndex = 2;
               
               int PixelTop = 88;
               int PixelLeft = 129;
               
               int Height = 320;
               int Width = 200;

               Image img = Image.FromFile(@"D:\Sample.png");
               ExcelPicture pic = wsSheet1.Drawings.AddPicture("Sample", img);
               pic.SetPosition(rowIndex, 0, colIndex, 0);
               //pic.SetPosition(PixelTop, PixelLeft);
               pic.SetSize(Height, Width);
               //pic.SetSize(40);

            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