Export DataTable with images to Excel in C#, VB.NET
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
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
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.
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
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.
