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. It also, converts Excel documents to PDF files. Using this library, you can start importing DataTable with images into Excel using the Template Marker feature.
You can generate reports more appealingly with image support in template markers. The following are the possible image formats.
- GIF
- JPEG
- PNG
- BMP
- TIFF
XlsIO detects the property as an image, when its type is System.Drawing.Image or byte[].
Steps to export Collection Objects with images to an 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 a reference to your .NET Framework application from NuGet.org.
Install NuGet package to the project
Step 3: Include the following namespaces in the 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 the main method of the Program.cs file to export DataTable with images to an 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
Note:
Starting with v16.2.0.x, if you reference Syncfusion® assemblies from a trial setup or from the NuGet feed, include a license key in your projects. Refer to the link to learn about generating and registering the Syncfusion® license key in your application to use the components without a trial message.
Conclusion
I hope you enjoyed learning about Export DataTable with images to Excel in C#, VB.NET.
You can refer to our XIsIO’s feature tour page to learn about its other groundbreaking features. Explore our UG documentation and online demos to understand how to manipulate data in Excel documents.
If you are an existing user, you can access our latest components from the License and Downloads page. For new users, you can try our 30-day free trial to check out XlsIO and other Syncfusion® components.
If you have any queries or require clarification, please let us know in the comments below or contact us through our support forums, Support Tickets, or feedback portal. We are always happy to assist you!