Hi democloud,
Check with the below sample code.
You need to convert the Byte array to image and then pass the path for inserting the image.
Make sure you have downloaded the latest ClosedXML dll. Because ClosedXML now has basic image/picture support.
Install ClosedXML via NuGet. Use Visual Studion NuGet Package Manager console to add the dll in your project.
Use the below command.
Install-Package ClosedXML -Version 0.94.2
Refer below link for more details.
https://www.nuget.org/packages/ClosedXML/
Database
For this example i have used tblFiles whose schema is defined as follows.
For insert you can refer below article.
Namespace
C#
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using ClosedXML.Excel;
VB.Net
Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Imports System.IO
Imports ClosedXML.Excel
Code
C#
protected void Page_Load(object sender, EventArgs e)
{
string conString = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
string query = "SELECT Id,Name,Data FROM tblFiles WHERE ContentType = 'image/jpeg'";
SqlCommand cmd = new SqlCommand(query);
using (SqlConnection con = new SqlConnection(conString))
{
using (SqlDataAdapter sda = new SqlDataAdapter())
{
cmd.Connection = con;
sda.SelectCommand = cmd;
DataTable dt = new DataTable();
sda.Fill(dt);
DataTable dtImage = new DataTable();
dtImage.Columns.AddRange(new DataColumn[3] { new DataColumn("Id", typeof(string)), new DataColumn("Name", typeof(string)), new DataColumn("Image", typeof(string)) });
for (int i = 0; i < dt.Rows.Count; i++)
{
byte[] bytes = (byte[])dt.Rows[i][2];
if (!Directory.Exists(Server.MapPath("~/Images/")))
{
Directory.CreateDirectory(Server.MapPath("~/Images/"));
}
File.WriteAllBytes(Server.MapPath("~/Images/") + dt.Rows[i][1], bytes);
dtImage.Rows.Add(dt.Rows[i][0], dt.Rows[i][1], Server.MapPath("~/Images/") + dt.Rows[i][1]);
}
XLWorkbook wb = new XLWorkbook();
{
IXLWorksheet ws = wb.Worksheets.Add("Staff_details");
// Adding Header column.
for (int column = 0; column < dtImage.Columns.Count; column++)
{
ws.Cell(1, column + 1).Value = dtImage.Columns[column].ColumnName;
}
// Adding Rows in cell.
for (int row = 0; row < dtImage.Rows.Count; row++)
{
for (int column = 0; column < dtImage.Columns.Count - 1; column++)
{
ws.Cell(row + 2, column + 1).Value = dtImage.Rows[row][column];
}
ws.Row(row + 2).Height = 40;
}
// Adding Image in cell.
for (int row = 0; row < dtImage.Rows.Count; row++)
{
for (int column = dtImage.Columns.Count - 1; column < dtImage.Columns.Count; column++)
{
var image = ws.AddPicture(dtImage.Rows[row][column].ToString()).MoveTo(ws.Cell(row + 2, column + 1));
image.Width = 50;
image.Height = 50;
}
}
// Delete generated files from Images Folder.
DirectoryInfo directoryInfo = new DirectoryInfo(Server.MapPath("~/Images/"));
foreach (FileInfo file in directoryInfo.GetFiles())
{
file.Delete();
}
Response.Clear();
Response.Buffer = true;
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
string strFileName = "Staff_details" + DateTime.Now.ToShortDateString() + ".xlsx";
Response.AddHeader("content-disposition", "attachment;filename=" + strFileName);
Response.Charset = "";
MemoryStream MyMemoryStream = new MemoryStream();
{
wb.SaveAs(MyMemoryStream);
MyMemoryStream.WriteTo(Response.OutputStream);
Response.Flush();
Response.End();
}
}
}
}
}
VB.Net
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
Dim conString As String = ConfigurationManager.ConnectionStrings("conString").ConnectionString
Dim query As String = "SELECT Id,Name,Data FROM tblFiles WHERE ContentType = 'image/jpeg'"
Dim cmd As SqlCommand = New SqlCommand(query)
Using con As SqlConnection = New SqlConnection(conString)
Using sda As SqlDataAdapter = New SqlDataAdapter()
cmd.Connection = con
sda.SelectCommand = cmd
Dim dt As DataTable = New DataTable()
sda.Fill(dt)
Dim dtImage As DataTable = New DataTable()
dtImage.Columns.AddRange(New DataColumn(2) {New DataColumn("Id", GetType(String)), New DataColumn("Name", GetType(String)), New DataColumn("Image", GetType(String))})
For i As Integer = 0 To dt.Rows.Count - 1
Dim bytes As Byte() = CType(dt.Rows(i)(2), Byte())
If Not Directory.Exists(Server.MapPath("~/Images/")) Then
Directory.CreateDirectory(Server.MapPath("~/Images/"))
End If
File.WriteAllBytes(Server.MapPath("~/Images/") + dt.Rows(i)(1), bytes)
dtImage.Rows.Add(dt.Rows(i)(0), dt.Rows(i)(1), Server.MapPath("~/Images/") + dt.Rows(i)(1))
Next
Dim wb As XLWorkbook = New XLWorkbook()
If True Then
Dim ws As IXLWorksheet = wb.Worksheets.Add("Staff_details")
' Adding Header column.
For column As Integer = 0 To dtImage.Columns.Count - 1
ws.Cell(1, column + 1).Value = dtImage.Columns(column).ColumnName
Next
' Adding Rows in cell.
For row As Integer = 0 To dtImage.Rows.Count - 1
For column As Integer = 0 To dtImage.Columns.Count - 1 - 1
ws.Cell(row + 2, column + 1).Value = dtImage.Rows(row)(column)
Next
ws.Row(row + 2).Height = 40
Next
' Adding Image in cell.
For row As Integer = 0 To dtImage.Rows.Count - 1
For column As Integer = dtImage.Columns.Count - 1 To dtImage.Columns.Count - 1
Dim image = ws.AddPicture(dtImage.Rows(row)(column).ToString()).MoveTo(ws.Cell(row + 2, column + 1))
image.Width = 50
image.Height = 50
Next
Next
' Delete generated files from Images Folder.
Dim directoryInfo As DirectoryInfo = New DirectoryInfo(Server.MapPath("~/Images/"))
For Each file As FileInfo In directoryInfo.GetFiles()
file.Delete()
Next
Response.Clear()
Response.Buffer = True
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
Dim strFileName As String = "Staff_details" & DateTime.Now.ToShortDateString() & ".xlsx"
Response.AddHeader("content-disposition", "attachment;filename=" & strFileName)
Response.Charset = ""
Dim MyMemoryStream As MemoryStream = New MemoryStream()
wb.SaveAs(MyMemoryStream)
MyMemoryStream.WriteTo(Response.OutputStream)
Response.Flush()
Response.End()
End If
End Using
End Using
End Sub
Screenshot
