Export Excel with multiple sheets to multiple Excel file using ClosedXML in Windows Application

Hi SUJAYS,

Refer below sample.

Namespaces

C#

using ClosedXML.Excel;
using System.IO;

VB.Net

Imports System.IO
Imports System.ComponentModel
Imports ClosedXML.Excel

Code

C#

private void button1_Click(object sender, EventArgs e)
{
    openFileDialog1.ShowDialog();
}

private DataTable GetDataTableFromExcel()
{
    DataTable dt = new DataTable();
    string filePath = openFileDialog1.FileName;
    using (XLWorkbook workBook = new XLWorkbook(filePath))
    {
        bool firstRow = true;

        foreach (IXLWorksheet workSheet in workBook.Worksheets)
        {
            IXLWorksheet worksheet = workBook.Worksheet(workSheet.Name);
            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())
                    {
                        for (int k = 0; k < dt.Columns.Count; k++)
                        {
                            if (dt.Columns[k].ColumnName.ToLower() != cell.Value.ToString().ToLower())
                            {
                                dt.Rows[dt.Rows.Count - 1][i] = cell.Value.ToString();
                            }
                            else
                            {
                                break;
                            }
                        }
                        i++;
                    }
                }
            }
        }

        return dt;
    }
}

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

    for (int i = 0; i < dts.Count; i++)
    {
        string folderPath = "D:\\Excel\\";
        if (!Directory.Exists(folderPath))
        {
            Directory.CreateDirectory(folderPath);
        }
        using (XLWorkbook wb = new XLWorkbook())
        {
            if (!string.IsNullOrEmpty(dts[i].Rows[0][0].ToString()))
            {
                wb.Worksheets.Add(dts[i], dts[i].Rows[0][0].ToString());
                wb.SaveAs(folderPath + dts[i].Rows[0][0].ToString() + ".xlsx");
            }
        }
    }
}

VB.Net

Private Sub button1_Click(ByVal sender As Object, ByVal e As EventArgs)
    openFileDialog1.ShowDialog()
End Sub

Private Function GetDataTableFromExcel() As DataTable
    Dim dt As DataTable = New DataTable()
    Dim filePath As String = openFileDialog1.FileName
    Using workBook As XLWorkbook = New XLWorkbook(filePath)
        Dim firstRow As Boolean = True
        For Each workSheet As IXLWorksheet In workBook.Worksheets
            Dim worksheets As IXLWorksheet = workBook.Worksheet(workSheet.Name)
            For Each row As IXLRow In worksheets.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()
                        For k As Integer = 0 To dt.Columns.Count - 1
                            If dt.Columns(k).ColumnName.ToLower() <> cell.Value.ToString().ToLower() Then
                                dt.Rows(dt.Rows.Count - 1)(i) = cell.Value.ToString()
                            Else
                                Exit For
                            End If
                        Next
                        i += 1
                    Next
                End If
            Next
        Next
        Return dt
    End Using
End Function

Private Sub openFileDialog1_FileOk(ByVal sender As Object, ByVal e As CancelEventArgs)
    Dim dt As DataTable = GetDataTableFromExcel()
    Dim dts As List(Of DataTable) = dt.AsEnumerable().GroupBy(Function(row) row.Field(Of String)("Company Name")).[Select](Function(g) g.CopyToDataTable()).ToList()
    For i As Integer = 0 To dts.Count - 1
        Dim folderPath As String = "D:\Excel\"
        If Not Directory.Exists(folderPath) Then
            Directory.CreateDirectory(folderPath)
        End If
        Using wb As XLWorkbook = New XLWorkbook()

            If Not String.IsNullOrEmpty(dts(i).Rows(0)(0).ToString()) Then
                wb.Worksheets.Add(dts(i), dts(i).Rows(0)(0).ToString())
                wb.SaveAs(folderPath & dts(i).Rows(0)(0).ToString() & ".xlsx")
            End If
        End Using
    Next
End Sub

Screenshot