How To Add Pictures Or Images In Excel Sheet Using EPPlus .NET Application (C#) - Part Five

In this tutorial, we will learn step by step Excel development, using .NET library EPPlus.

First, we need to add two namespaces:

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

Example

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

Now, the next thing that hits our mind is that image is an abstract class and we never create an instance of abstract class. So, the above line violates the rules of Object Oriented Programming(OOP) concept.  

 

The answer is - No, OOP concept is still valid for this above line.

This is 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 return 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 and gives to the subclass of Image class. I think now your concept is much clearer.

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 and it is inherited from ExcelDrawing class. Here, Drawings property is the type of ExcelDrawings class and assigns a property of ExcelSheet class object wsSheet1.

In the next line, if we 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 two overloaded versions. We have already seen the first overload method; it accepts 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 versions). It can accept picture width & height as integer type parameter & second overload method accepts overall original picture width & height in percentage.

Note

Resizing columns and rows after using SetSize(int percent) function will affect the size of picture.

For example -

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

Output in Excel sheet will look like below.

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 and execute this code. File is (New.xlsx) stored on the D: drive of computer.

Thank you for reading this article. Please subscribe to 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