ASPForums.Net RSS Feed additions to the content that appears on ASPForums.Net(c) 2019 All rights reserved.Split Excel data into multiple Excel using C# and VB.Net in Windows Application<p>Hi&nbsp;<a class="username" rel="SUJAYS">SUJAYS,</a></p> <p>Refering the&nbsp;below articles i have created the example.</p> <h1 class="header"><a title="Read and import Excel file data to DataGridView using ClosedXml using C# and VB.Net'a" href="">Read and import Excel file data to DataGridView using ClosedXml using C# and VB.Net</a></h1> <h1 class="header"><a title="Export Windows Forms DataGridView to Excel using C# and VB.Net'a" href="">Export Windows Forms DataGridView to Excel using C# and VB.Net</a></h1> <p>Check this example. Now please take its reference and correct your code.</p> <p><strong><span style="text-decoration: underline;">Form Design</span></strong></p> <p>In Windows Form i have taken&nbsp;a Button for Generate excel and OpenFileDialog to select file.</p> <p><strong><span style="text-decoration: underline;">Namespaces</span></strong></p> <p><strong>C#</strong></p> <pre class="brush: csharp">using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.IO; using System.Linq; using System.Windows.Forms; using ClosedXML.Excel;</pre> <p><strong>VB.Net</strong></p> <pre class="brush: vb">Imports System.Collections.Generic Imports System.ComponentModel Imports System.Data Imports System.IO Imports System.Linq Imports System.Windows.Forms Imports ClosedXML.Excel</pre> <p><strong><span style="text-decoration: underline;">Code</span></strong></p> <p><strong>C#</strong></p> <pre class="brush: csharp">private void btnGenerateExcel_Click(object sender, EventArgs e) { openFileDialog1.ShowDialog(); } private void openFileDialog1_FileOk(object sender, CancelEventArgs e) { DataTable dt = GetDataTableFromExcel(); List&lt;DataTable&gt; dts = dt.AsEnumerable() .GroupBy(row =&gt; row.Field&lt;string&gt;(&#34;Name&#34;)) .Select(g =&gt; g.CopyToDataTable()).ToList(); for (int i = 0; i &lt; dts.Count; i++) { //Exporting to Excel. string folderPath = &#34;D:\\Excel\\&#34;; 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() + &#34;.xlsx&#34;); } } } } 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; }</pre> <p><strong>VB.Net</strong></p> <pre class="brush: vb">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)(&#34;Name&#34;)) _ .Select(Function(g) g.CopyToDataTable()).ToList() For i As Integer = 0 To dts.Count - 1 &#39; Exporting to Excel. Dim folderPath As String = &#34;D:\Excel\&#34; 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 &amp; dts(i).Rows(0)(0).ToString() &amp; &#34;.xlsx&#34;) 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</pre> <p><strong><span style="text-decoration: underline;">Screenshots</span></strong></p> <p><span style="text-decoration: underline;">The Excel</span></p> <p><span style="text-decoration: underline;"><img src="" alt="" width="246" height="317" /></span></p> <p><span style="text-decoration: underline;">The Generated Excels with Names</span></p> <p><span style="text-decoration: underline;"><img src="" alt="" width="472" height="304" /></span></p>, 10 Jun 2019 03:37:36 GMT