Articles in this section
Category / Section

Export DataTable with images to Excel in C#, VB.NET

4 mins read

Syncfusion Excel (XlsIO) library is a .NET Excel library used to create, read, and edit Excel documents. Also, converts Excel documents to PDF files. Using this library, you can start importing DataTable with images into Excel using Template Marker feature.

You can generate reports more appealingly with image support in template markers. Following are the possible image formats.

  • GIF
  • JPEG
  • PNG
  • BMP
  • TIFF

XlsIO detects the property as image, when its type is System.Drawing.Image or byte[].

Steps to export Collection Objects with image to Excel, programmatically:

Step 1: Create a new C# console application project.

Create a new C# console application project

Create a new C# console application project

Step 2: Install the Syncfusion.XlsIO.WinForms NuGet package as reference to your .NET Framework application from NuGet.org.

Install NuGet package to the project

Install NuGet package to the project

Step 3: Include the following namespaces in Program.cs file.

C#

using Syncfusion.XlsIO;
using System.Collections.Generic;
using System.IO;

 

VB.NET

Imports Syncfusion.XlsIO
Imports System.Collections.Generic
Imports System.IO

 

Step 4: Include the following code snippet in main method of Program.cs file to export DataTable with images to Excel document.

C#

//Instantiate the spreadsheet creation engine
using (ExcelEngine excelEngine = new ExcelEngine())
{
    //Instantiate the Excel application object
    IApplication application = excelEngine.Excel;
 
    //Create a new workbook and add a worksheet
    IWorkbook workbook = application.Workbooks.Create(1);
    IWorksheet worksheet = workbook.Worksheets[0];
 
    //Add the header text and assign cell style
    worksheet["A3"].Text = "Image";
    worksheet["B3"].Text = "Name";
    worksheet["C3"].Text = "Id";
    worksheet["D3"].Text = "Age";
    worksheet["A3:D3"].CellStyle.Font.Bold = true;
 
    worksheet["B4"].Text = "%Employee.Name";
    worksheet["C4"].Text = "%Employee.Id";
    worksheet["D4"].Text = "%Employee.Age";
    worksheet["A4"].Text = "%Employee.Image";
 
    //Create template marker processor
    ITemplateMarkersProcessor marker = workbook.CreateTemplateMarkersProcessor();
 
    //Add marker variable
    marker.AddVariable("Employee", GetEmployeeDetails());
 
    //Apply markers
    marker.ApplyMarkers();
 
    //Autofit the columns
    worksheet["B1:D10"].AutofitColumns();
 
    //Save the workbook
    workbook.SaveAs("Output.xlsx");
}

 

VB.NET

'Instantiate the spreadsheet creation engine
Using excelEngine As ExcelEngine = New ExcelEngine()
    'Instantiate the Excel application object
    Dim application As IApplication = excelEngine.Excel
 
    'Create a new workbook and add a worksheet
    Dim workbook As IWorkbook = application.Workbooks.Create(1)
    Dim worksheet As IWorksheet = workbook.Worksheets(0)
 
    'Add the header text and assign cell style
    worksheet("A3").Text = "Image"
    worksheet("B3").Text = "Name"
    worksheet("C3").Text = "Id"
    worksheet("D3").Text = "Age"
    worksheet("A3:D3").CellStyle.Font.Bold = True
 
    worksheet("B4").Text = "%Employee.Name"
    worksheet("C4").Text = "%Employee.Id"
    worksheet("D4").Text = "%Employee.Age"
    worksheet("A4").Text = "%Employee.Image"
 
    'Create template marker processor
    Dim marker As ITemplateMarkersProcessor = workbook.CreateTemplateMarkersProcessor()
 
    'Add marker variable
    marker.AddVariable("Employee", GetEmployeeDetails())
 
    'Apply markers
    marker.ApplyMarkers()
 
    'Autofit the columns
    worksheet("B1:D10").AutofitColumns()
 
    'Save the workbook
    workbook.SaveAs("Output.xlsx")
End Using

 

Step 5: Create a public class with name as Employee and have the required properties.

C#

public class Employee
{
    private byte[] m_image;
    private string m_name;
    private int m_id;
    private int m_age;
 
    //Employee Image
    public byte[] Image
    {
        get
        {
            return m_image;
        }
        set
        {
            m_image = value;
        }
    }
    //Employee Name
    public string Name
    {
        get
        {
            return m_name;
        }
        set
        {
            m_name = value;
        }
    }
    //Employee ID
    public int Id
    {
        get
        {
            return m_id;
        }
        set
        {
            m_id = value;
        }
    }
    //Employee Age
    public int Age
    {
        get
        {
            return m_age;
        }
        set
        {
            m_age = value;
        }
    }
}

 

VB.NET

Public Class Employee
    Dim m_image() As Byte
    Dim m_name As String
    Dim m_id As Integer
    Dim m_age As Integer
 
    'Employee Image
    Public Property Image As Byte()
        Get
            Return m_image
        End Get
        Set
            m_image = Value
        End Set
    End Property
 
    'Employee Name
    Public Property Name As String
        Get
            Return m_name
        End Get
        Set
            m_name = Value
        End Set
    End Property
 
    'Employee ID
    Public Property Id As Integer
        Get
            Return m_id
        End Get
        Set
            m_id = Value
        End Set
    End Property
 
    'Employee Age
    Public Property Age As Integer
        Get
            Return m_age
        End Get
        Set
            m_age = Value
        End Set
    End Property
End Class

 

Step 6: Load the list object collection employeeList using the following simple static method.

C#

private static List<Employee> GetEmployeeDetails()
{
    //Get the images from folder
    byte[] image1 = File.ReadAllBytes(@"../../Data/Man1.jpg");
    byte[] image2 = File.ReadAllBytes(@"../../Data/Man2.png");
    byte[] image3 = File.ReadAllBytes(@"../../Data/Woman1.jpg");
 
    //Instantiate employee list
    List<Employee> employeeList = new List<Employee>();
 
    //Set the details of employee and into employee list
    Employee emp = new Employee();
    emp.Image = image1;
    emp.Name = "Andy Bernard";
    emp.Id = 1011;
    emp.Age = 35;
    employeeList.Add(emp);
 
    //Set the details of employee and into employee list
    emp = new Employee();
    emp.Image = image2;
    emp.Name = "Karen Fillippelli";
    emp.Id = 1012;
    emp.Age = 26;
    employeeList.Add(emp);
 
    //Set the details of employee and into employee list
    emp = new Employee();
    emp.Image = image3;
    emp.Name = "Patricia Mckenna";
    emp.Id = 1013;
    emp.Age = 28;
    employeeList.Add(emp);
 
    //Return the employee list
    return employeeList;
}

 

VB.NET

Private Function GetEmployeeDetails() As List(Of Employee)
    'Get the images from folder
    Dim image1() As Byte = File.ReadAllBytes("../../Data/Man1.jpg")
    Dim image2() As Byte = File.ReadAllBytes("../../Data/Man2.png")
    Dim image3() As Byte = File.ReadAllBytes("../../Data/Woman1.jpg")
 
    'Instantiate employee list
    Dim employeeList As List(Of Employee) = New List(Of Employee)
 
    'Set the details of employee and into employee list
    Dim emp As Employee = New Employee()
    emp.Image = image1
    emp.Name = "Andy Bernard"
    emp.Id = 1011
    emp.Age = 35
    employeeList.Add(emp)
 
    'Set the details of employee and into employee list
    emp = New Employee()
    emp.Image = image2
    emp.Name = "Karen Fillippelli"
    emp.Id = 1012
    emp.Age = 26
    employeeList.Add(emp)
 
    'Set the details of employee and into employee list
    emp = New Employee()
    emp.Image = image3
    emp.Name = "Patricia Mckenna"
    emp.Id = 1013
    emp.Age = 28
    employeeList.Add(emp)
 
    'Return the employee list
    Return employeeList
End Function

 

A complete working sample of how to export DataTable with Images to Excel document can be downloaded from ExportDataTableWithImagesToExcel.zip.

By executing the program, you will get the output Excel file as shown below.

Export DataTable with Images to Excel

Output Excel document

Take a moment to peruse the documentation where you will find other options importing and exporting in DataTable, exporting worksheet to Collection Objects, and more with respective code examples.

Click here to explore the rich set of Syncfusion Excel (XlsIO) library features.

An online sample link to export DataTable with images to Excel

Note:

Starting with v16.2.0.x, if you reference Syncfusion assemblies from trial setup or from the NuGet feed, include a license key in your projects. Refer the link to learn about generating and registering Syncfusion license key in your application to use the components without trail message.

 

Did you find this information helpful?
Yes
No
Help us improve this page
Please provide feedback or comments
Comments (0)
Please  to leave a comment
Access denied
Access denied