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

Last Reply 16 days ago By dharmendr

Posted 17 days ago

Example we having 200 records in excel. Suppose we have only 100 record set with unique name. So 100 excel to be created for each record set.

I am using windows application

Name Books Price Author
Sanjay SAS1 232 Sanjay
Raj RA1 434 Raj
Sanjay SAS2 232 Sanjay
Sanjay Sas3 343 Sanjay
Ramu RA3 343 Raj
Sanjay SAS4 122 Sanjay
Ajith Affsd 343 Ajith
Raj SDSD 542 Raj
Ramu DA 412 Ramu

As we have 9 records.These records are in excel. I am uploading this record into database and want to get 4 unique excel as we have 4 unique names as Raj,Sanjay,Ramu and Ajith group by name.

Can you share idea regarding this

Posted 16 days ago


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.



using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.IO;
using System.Linq;
using System.Windows.Forms;
using ClosedXML.Excel;


Imports System.Collections.Generic
Imports System.ComponentModel
Imports System.Data
Imports System.IO
Imports System.Linq
Imports System.Windows.Forms
Imports ClosedXML.Excel



private void btnGenerateExcel_Click(object sender, EventArgs e)

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();

    for (int i = 0; i < dts.Count; i++)
        //Exporting to Excel.
        string folderPath = "D:\\Excel\\";
        if (!Directory.Exists(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");

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())
                firstRow = false;
                int i = 0;
                foreach (IXLCell cell in row.Cells())
                    dt.Rows[dt.Rows.Count - 1][i] = cell.Value.ToString();

    return dt;


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

Private Sub openFileDialog1_FileOk(sender As System.Object, e As 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()
    For i As Integer = 0 To dts.Count - 1
        ' Exporting to Excel.
        Dim folderPath As String = "D:\Excel\"
        If Not Directory.Exists(folderPath) Then
        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
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()
                firstRow = False
                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
            End If
    End Using

    Return dt
End Function


The Excel

The Generated Excels with Names