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

Last Reply 2 months ago By dharmendr

Posted 2 months 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.

You are viewing reply posted by: dharmendr 2 months ago.
Posted 2 months ago Modified on 2 months 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