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

Last Reply 3 months ago By dharmendr

Posted 3 months 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 3 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.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.IO;
using System.Linq;
using System.Windows.Forms;
using ClosedXML.Excel;

VB.Net

Imports System.Collections.Generic
Imports System.ComponentModel
Imports System.Data
Imports System.IO
Imports System.Linq
Imports System.Windows.Forms
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();

    for (int i = 0; i < dts.Count; i++)
    {
        //Exporting to Excel.
        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");
            }
        }
    }
}

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 EventArgs) Handles btnGenerateExcel.Click
    openFileDialog1.ShowDialog()
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
            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

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 Excels with Names