Hi ashfaq,

Check the below sample code.

The Excel File

HTML

<asp:FileUpload ID="fuUpload" runat="server" />
<asp:Button Text="Upload" OnClick="Button1_Click" runat="server" />

Namespaces

C#

using System.Data;
using System.IO;
using System.Linq;
using ClosedXML.Excel;

VB.Net

Imports System.Data
Imports System.IO
Imports System.Linq
Imports ClosedXML.Excel

Code

C#

protected void Button1_Click(object sender, EventArgs e)
{
    DataTable dt = GetDataTableFromExcel();
    List<DataTable> dts = dt.AsEnumerable()
        .GroupBy(row => row.Field<string>("Name"))
        .Select(g => g.CopyToDataTable()).ToList();

    string path = Server.MapPath("~/Excel/");
    if (!Directory.Exists(path))
    {
        Directory.CreateDirectory(path);
    }
    using (XLWorkbook wb = new XLWorkbook())
    {
        for (int i = 0; i < dts.Count; i++)
        {
            if (!string.IsNullOrEmpty(dts[i].Rows[0][0].ToString()))
            {
                wb.Worksheets.Add(dts[i], dts[i].Rows[0][0].ToString());
            }
        }
        wb.SaveAs(path + "OrderDetails.xlsx");
    }

    DownloadFile(path + "OrderDetails.xlsx");
}

public void DownloadFile(string path)
{
    Response.Clear();
    Response.ContentType = "application/octet-stream";
    Response.AddHeader("content-disposition", "attachment;filename=" + Path.GetFileName(path));
    Response.WriteFile(path);
    Response.End();
}

private DataTable GetDataTableFromExcel()
{
    DataTable dt = new DataTable();
    string myfile_name = Path.GetFileName(fuUpload.PostedFile.FileName);
    fuUpload.SaveAs(Server.MapPath("~/Excel/") + myfile_name);
    string filePath = Server.MapPath("~/Excel/") + myfile_name;
    using (XLWorkbook workBook = new XLWorkbook(filePath))
    {
        IXLWorksheet workSheet = workBook.Worksheet(1);
        bool firstRow = true;
        foreach (IXLRow row in workSheet.Rows())
        {
            if (firstRow)
            {
                foreach (IXLCell cell in row.Cells())
                {
                    dt.Columns.Add(cell.Value.ToString());
                }
                firstRow = false;
            }
            else
            {
                dt.Rows.Add();
                int i = 0;
                foreach (IXLCell cell in row.Cells())
                {
                    dt.Rows[dt.Rows.Count - 1][i] = cell.Value.ToString();
                    i++;
                }
            }
        }
    }

    return dt;
}

VB.Net

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs)
    Dim dt As DataTable = GetDataTableFromExcel()
    Dim dts As List(Of DataTable) = dt.AsEnumerable() _
                                    .GroupBy(Function(row) row.Field(Of String)("Name")) _
                                    .Select(Function(g) g.CopyToDataTable()).ToList()
    Dim path As String = Server.MapPath("~/Excel/")
    If Not Directory.Exists(path) Then
        Directory.CreateDirectory(path)
    End If
    Using wb As XLWorkbook = New XLWorkbook()
        For i As Integer = 0 To dts.Count - 1
            If Not String.IsNullOrEmpty(dts(i).Rows(0)(0).ToString()) Then
                wb.Worksheets.Add(dts(i), dts(i).Rows(0)(0).ToString())
            End If
        Next
        wb.SaveAs(path & "OrderDetails.xlsx")
    End Using
    DownloadFile(path & "OrderDetails.xlsx")
End Sub

Public Sub DownloadFile(ByVal path As String)
    Response.Clear()
    Response.ContentType = "application/octet-stream"
    Response.AddHeader("content-disposition", "attachment;filename=" & System.IO.Path.GetFileName(path))
    Response.WriteFile(path)
    Response.End()
End Sub

Private Function GetDataTableFromExcel() As DataTable
    Dim dt As DataTable = New DataTable()
    Dim myfile_name As String = Path.GetFileName(fuUpload.PostedFile.FileName)
    fuUpload.SaveAs(Server.MapPath("~/Excel/") & myfile_name)
    Dim filePath As String = Server.MapPath("~/Excel/") & myfile_name

    Using workBook As XLWorkbook = New XLWorkbook(filePath)
        Dim workSheet As IXLWorksheet = workBook.Worksheet(1)
        Dim firstRow As Boolean = True
        For Each row As IXLRow In workSheet.Rows()
            If firstRow Then
                For Each cell As IXLCell In row.Cells()
                    dt.Columns.Add(cell.Value.ToString())
                Next
                firstRow = False
            Else
                dt.Rows.Add()
                Dim i As Integer = 0
                For Each cell As IXLCell In row.Cells()
                    dt.Rows(dt.Rows.Count - 1)(i) = cell.Value.ToString()
                    i += 1
                Next
            End If
        Next
    End Using

    Return dt
End Function

Screenshot