Hi tex,

Check this sample. now take its reference.

I have created this sample using below article.

Export DataGridView to Excel with Formatting using C# and VB.Net

Namespaces

C#

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

VB.Net

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

Code

C#

private void btnMergeFiles_Click(object sender, EventArgs e)
{
    string path = "C:\\Desktop\\Files\\";
    string[] folderPath = Directory.GetFiles(path);
    DataSet ds = new DataSet();
    foreach (string filePath in folderPath)
    {
        ds.Tables.Add(GetDataTableFromExcel(filePath));
    }
    DataTable dtMerged = ds.Tables[0].Clone();
    foreach (DataTable table in ds.Tables)
    {
        dtMerged.Merge(table);
    }
    using (XLWorkbook wb = new XLWorkbook())
    {
        wb.Worksheets.Add(dtMerged);
        wb.Worksheet(1).Columns().AdjustToContents();

        //Save the Excel file.
        wb.SaveAs(path + "DataGridViewExport.xlsx");
    }
    MessageBox.Show("Files Merged Successfully", MessageBoxIcon.Information.ToString(), MessageBoxButtons.OK);
}

private DataTable GetDataTableFromExcel(string filePath)
{
    using (XLWorkbook workBook = new XLWorkbook(filePath))
    {
        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++;
                }
            }
        }
        return dt;
    }
}

VB.Net

Private Sub btnMergeFiles_Click(sender As System.Object, e As System.EventArgs) Handles btnMergeFiles.Click
    Dim path As String = "C:\Desktop\Files\"
    Dim folderPath As String() = Directory.GetFiles(path)
    Dim ds As DataSet = New DataSet()
    For Each filePath As String In folderPath
        ds.Tables.Add(GetDataTableFromExcel(filePath))
    Next
    Dim dtMerged As DataTable = ds.Tables(0).Clone()
    For Each table As DataTable In ds.Tables
        dtMerged.Merge(table)
    Next
    Using wb As XLWorkbook = New XLWorkbook()
        wb.Worksheets.Add(dtMerged)
        wb.Worksheet(1).Columns().AdjustToContents()
        wb.SaveAs(path & "DataGridViewExport.xlsx")
        MessageBox.Show("Files Merged Successfully", MessageBoxIcon.Information.ToString(), MessageBoxButtons.OK)
    End Using
End Sub

Private Function GetDataTableFromExcel(ByVal filePath As String) As DataTable
    Using workBook As XLWorkbook = New XLWorkbook(filePath)
        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
        Return dt
    End Using
End Function

Screenshot

Excel File 1

Excel File 2

Excel File 3

Merge Excel File