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

Last Reply one year ago By pandeyism

Posted one year ago

I am loading the excel worksheet into datatable. In excel there are two worksheet, loaded into datatable but the two worksheet are loaded as single datable.

While loading itself can we able to spilit as two datatable and load into datatable?

Posted one year ago

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