Split Excel data into multiple Excel sheet using C# and VB.Net in Windows Application

Last Reply 16 days ago By dharmendr

Posted 16 days ago

How to Copy the Excel Sheet wise to another multiple excel sheet?

Split Excel data into multiple Excel using C# and VB.Net in Windows Application

the group wise record in sheet to be spilit in multiple sheet.

Posted 16 days ago Modified on 16 days ago

Hi SUJAYS,

Refering the below articles i have created the example.

Read and import Excel file data to DataGridView using ClosedXml using C# and VB.Net

Export Windows Forms DataGridView to Excel using C# and VB.Net

Check this example. Now please take its reference and correct your code.

Form Design

In Windows Form i have taken a Button for Generate excel and OpenFileDialog to select file.

Namespaces

C#

using System.IO;
using ClosedXML.Excel;

VB.Net

Imports System.IO
Imports ClosedXML.Excel

Code

C#

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

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

    string path = "D:\\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 + "Author.xlsx");
    }
}

private DataTable GetDataTableFromExcel()
{
    DataTable dt = new DataTable();
    string filePath = openFileDialog1.FileName;
    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

Private Sub btnGenerateExcel_Click(sender As System.Object, e As System.EventArgs) Handles btnGenerateExcel.Click
    openFileDialog1.ShowDialog()
End Sub

Private Sub openFileDialog1_FileOk(sender As System.Object, e As System.ComponentModel.CancelEventArgs) Handles openFileDialog1.FileOk
    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 = "D:\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 & "Author.xlsx")
    End Using

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 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

Screenshots

The Excel

The Generated Excel Sheets with Names