Refer the Below Sample code for your reference and implement it as per your code logic.
Also refer the below article link for your understanding to export to excel Export GridView data to multiple Sheets (Worksheets) of Excel file in ASP.Net using C# and VB.Net and zip multiple files Download multiple files at once in one HTTP Request in ASP.Net using C# and VB.Net.
HTML
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="gvUsers" runat="server" AutoGenerateColumns="false">
<Columns>
<asp:BoundField DataField="Id" HeaderText="Id" />
<asp:BoundField DataField="Name" HeaderText="Name" />
<asp:BoundField DataField="Country" HeaderText="Country" />
</Columns>
</asp:GridView>
<br />
<br />
<asp:Button ID="btnExportToZip" runat="server" Text="Export To Zip" OnClick="ExportToZip" />
</div>
</form>
</body>
</html>
Namespaces
C#
using System.Data;
using System.IO;
using ClosedXML.Excel;
using Ionic.Zip;
using System.Collections.Generic;
VB.Net
Imports System.Data
Imports ClosedXML.Excel
Imports System.IO
Imports Ionic.Zip
Code
C#
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
DataTable dt = new DataTable();
dt.Columns.AddRange(new DataColumn[3] { new DataColumn("Id", typeof(int)),
new DataColumn("Name", typeof(string)),
new DataColumn("Country",typeof(string)) });
dt.Rows.Add(1, "John Hammond", "United States");
dt.Rows.Add(2, "Mudassar Khan", "India");
dt.Rows.Add(3, "Suzanne Mathews", "France");
dt.Rows.Add(4, "Robert Schidner", "Russia");
gvUsers.DataSource = dt;
gvUsers.DataBind();
}
}
protected void ExportToZip(object sender, EventArgs e)
{
List<string> folderPaths = new List<string>();
for (int i = 0; i < gvUsers.Rows.Count; i++)
{
DataTable dt = new DataTable();
foreach (TableCell cell in gvUsers.HeaderRow.Cells)
{
dt.Columns.Add(cell.Text);
}
GridViewRow row = gvUsers.Rows[i] as GridViewRow;
dt.Rows.Add();
for (int j = 0; j < row.Cells.Count; j++)
{
dt.Rows[dt.Rows.Count - 1][j] = row.Cells[j].Text;
}
string folderPath = "D:\\Excel\\";
if (!Directory.Exists(folderPath))
{
Directory.CreateDirectory(folderPath);
}
string fileName = folderPath + "File" + i + ".xlsx";
SaveInExcel(dt, fileName);
folderPaths.Add(fileName);
}
ZipFile zip = new ZipFile();
zip.AlternateEncodingUsage = ZipOption.AsNecessary;
zip.AddDirectoryByName("Files");
foreach (string filepath in folderPaths)
{
zip.AddFile(filepath, "Files");
}
Response.Clear();
Response.BufferOutput = false;
string zipName = String.Format("Zip_{0}.zip", DateTime.Now.ToString("yyyy-MMM-dd-HHmmss"));
Response.ContentType = "application/zip";
Response.AddHeader("content-disposition", "attachment; filename=" + zipName);
zip.Save(Response.OutputStream);
Response.End();
}
private void SaveInExcel(DataTable dt, string fileName)
{
XLWorkbook wb = new XLWorkbook();
wb.Worksheets.Add(dt, "ExcelFile");
wb.SaveAs(fileName);
}
VB.Net
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
If Not Me.IsPostBack Then
Dim dt As DataTable = New DataTable
dt.Columns.AddRange(New DataColumn() {New DataColumn("Id", GetType(System.Int32)), New DataColumn("Name", GetType(System.String)), New DataColumn("Country", GetType(System.String))})
dt.Rows.Add(1, "John Hammond", "United States")
dt.Rows.Add(2, "Mudassar Khan", "India")
dt.Rows.Add(3, "Suzanne Mathews", "France")
dt.Rows.Add(4, "Robert Schidner", "Russia")
gvUsers.DataSource = dt
gvUsers.DataBind()
End If
End Sub
Private Sub SaveInExcel(ByVal dt As DataTable, ByVal fileName As String)
Dim wb As XLWorkbook = New XLWorkbook
wb.Worksheets.Add(dt, "ExcelFile")
wb.SaveAs(fileName)
End Sub
Protected Sub ExportToZip(ByVal sender As Object, ByVal e As EventArgs)
Dim folderPaths As List(Of String) = New List(Of String)
Dim i As Integer = 0
Do While (i < gvUsers.Rows.Count)
Dim dt As DataTable = New DataTable
For Each cell As TableCell In gvUsers.HeaderRow.Cells
dt.Columns.Add(cell.Text)
Next
Dim row As GridViewRow = CType(gvUsers.Rows(i), GridViewRow)
dt.Rows.Add()
Dim j As Integer = 0
Do While (j < row.Cells.Count)
dt.Rows((dt.Rows.Count - 1))(j) = row.Cells(j).Text
j = (j + 1)
Loop
Dim folderPath As String = "D:\Excel\"
If Not Directory.Exists(folderPath) Then
Directory.CreateDirectory(folderPath)
End If
Dim fileName As String = (folderPath & "File" & i & ".xlsx")
SaveInExcel(dt, fileName)
folderPaths.Add(fileName)
i = (i + 1)
Loop
Dim zip As ZipFile = New ZipFile
zip.AlternateEncodingUsage = ZipOption.AsNecessary
zip.AddDirectoryByName("Files")
For Each filepath As String In folderPaths
zip.AddFile(filepath, "Files")
Next
Response.Clear()
Response.BufferOutput = False
Dim zipName As String = String.Format("Zip_{0}.zip", DateTime.Now.ToString("yyyy-MMM-dd-HHmmss"))
Response.ContentType = "application/zip"
Response.AddHeader("content-disposition", ("attachment; filename=" + zipName))
zip.Save(Response.OutputStream)
Response.End()
End Sub
Screenshot