Copy data from one Excel sheet into another Excel using ClosedXML in ASP.Net

Hi shalinip,

Please refer below sample.

You can download the dll from the below article.

Read Excel file directly from Stream in ASP.Net

HTML

<div>
    <asp:FileUpload ID="FileUpload1" runat="server" />
    <asp:Button ID="btnImport" runat="server" Text="CopyToExcel" OnClick="ImportExcel" />
</div>

Namespaces

C#

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

VB.Net

Imports System.IO
Imports System.Data
Imports System.Collections.Generic
Imports ClosedXML.Excel

Code

C#

protected void ImportExcel(object sender, EventArgs e)
{
    using (XLWorkbook workBook = new XLWorkbook(FileUpload1.PostedFile.InputStream))
    {
        IXLWorksheet workSheet = workBook.Worksheet(1);
        DataTable dt = new DataTable();
        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++;
                }
            }
        }
        using (XLWorkbook wb = new XLWorkbook())
        {
            wb.Worksheets.Add(dt, "Customers");

            Response.Clear();
            Response.Buffer = true;
            Response.Charset = "";
            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            Response.AddHeader("content-disposition", "attachment;filename=SqlExport.xlsx");
            using (MemoryStream MyMemoryStream = new MemoryStream())
            {
                wb.SaveAs(MyMemoryStream);
                MyMemoryStream.WriteTo(Response.OutputStream);
                Response.Flush();
                Response.End();
            }
        }
    }
}

VB.Net

Protected Sub ImportExcel(ByVal sender As Object, ByVal e As EventArgs)
    Using workBook As XLWorkbook = New XLWorkbook(FileUpload1.PostedFile.InputStream)
        Dim workSheet As IXLWorksheet = workBook.Worksheet(1)
        Dim dt As DataTable = New DataTable()
        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
        Using wb As XLWorkbook = New XLWorkbook()
            wb.Worksheets.Add(dt, "Customers")
            Response.Clear()
            Response.Buffer = True
            Response.Charset = ""
            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
            Response.AddHeader("content-disposition", "attachment;filename=SqlExport.xlsx")
            Using MyMemoryStream As MemoryStream = New MemoryStream()
                wb.SaveAs(MyMemoryStream)
                MyMemoryStream.WriteTo(Response.OutputStream)
                Response.Flush()
                Response.End()
            End Using
        End Using
    End Using
End Sub

Screenshot