Export Excel with multiple sheets to multiple DataTables using ClosedXML in Windows Application

Hi SUJAYS,

Refer below sample.

Namespaces

C#

using ClosedXML.Excel;

VB.Net

Imports ClosedXML.Excel
Imports System.ComponentModel

Code

C#

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

private DataSet GetDataTableFromExcel()
{
    DataSet ds = new DataSet();
    string filePath = openFileDialog1.FileName;
    using (XLWorkbook workBook = new XLWorkbook(filePath))
    {
        foreach (IXLWorksheet workSheet in workBook.Worksheets)
        {
            bool firstRow = true;
            DataTable dt = new DataTable();
            IXLWorksheet worksheet = workBook.Worksheet(workSheet.Name);
            dt.TableName = workSheet.Name;
            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())
                    {
                        for (int k = 0; k < dt.Columns.Count; k++)
                        {
                            if (dt.Columns[k].ColumnName.ToLower() != cell.Value.ToString().ToLower())
                            {
                                dt.Rows[dt.Rows.Count - 1][i] = cell.Value.ToString();
                            }
                            else
                            {
                                break;
                            }
                        }
                        i++;
                    }
                }
            }
            ds.Tables.Add(dt);
        }
        return ds;
    }
}

private void openFileDialog1_FileOk_1(object sender, CancelEventArgs e)
{
    DataSet ds = GetDataTableFromExcel();
    dataGridView1.DataSource = ds.Tables[0];
    dataGridView2.DataSource = ds.Tables[1];


}

VB.Net

Private Sub button1_Click(ByVal sender As Object, ByVal e As EventArgs)
    openFileDialog1.ShowDialog()
End Sub

Private Function GetDataTableFromExcel() As DataSet
    Dim ds As DataSet = New DataSet()
    Dim filePath As String = openFileDialog1.FileName
    Using workBook As XLWorkbook = New XLWorkbook(filePath)
        For Each workSheet As IXLWorksheet In workBook.Worksheets
            Dim firstRow As Boolean = True
            Dim dt As DataTable = New DataTable()
            Dim worksheets As IXLWorksheet = workBook.Worksheet(workSheet.Name)
            dt.TableName = workSheet.Name
            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()
                        For k As Integer = 0 To dt.Columns.Count - 1
                            If dt.Columns(k).ColumnName.ToLower() <> cell.Value.ToString().ToLower() Then
                                dt.Rows(dt.Rows.Count - 1)(i) = cell.Value.ToString()
                            Else
                                Exit For
                            End If
                        Next
                        i += 1
                    Next
                End If
            Next
            ds.Tables.Add(dt)
        Next
        Return ds
    End Using
End Function

Private Sub openFileDialog1_FileOk_1(ByVal sender As Object, ByVal e As CancelEventArgs)
    Dim ds As DataSet = GetDataTableFromExcel()
    dataGridView1.DataSource = ds.Tables(0)
    dataGridView2.DataSource = ds.Tables(1)
End Sub

Screenshot

Sheet1

Sheet2