Hi luckydead,
Please refer below sample.
Namespaces
C#
using ClosedXML.Excel;
using System.ComponentModel;
VB.Net
Imports ClosedXML.Excel
Imports System.ComponentModel
Code
C#
private void btnImport_Click(object sender, EventArgs e)
{
openFileDialog1.ShowDialog();
}
private void openFileDialog1_FileOk(object sender, CancelEventArgs e)
{
DataSet ds = GetDataTableFromExcel();
foreach (DataTable dt in ds.Tables)
{
DataGridView dgv = new DataGridView();
dgv.DefaultCellStyle.BackColor = Color.White;
dgv.DefaultCellStyle.ForeColor = Color.Black;
dgv.DefaultCellStyle.SelectionBackColor = Color.FromArgb(0, 177, 89);
dgv.DefaultCellStyle.SelectionForeColor = Color.White;
dgv.RowHeadersVisible = true;
dgv.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.AutoSize;
dgv.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
dgv.Anchor = AnchorStyles.Top & AnchorStyles.Left & AnchorStyles.Right;
dgv.ScrollBars = ScrollBars.Both;
dgv.Dock = DockStyle.Fill;
dgv.DataSource = dt;
dgv.AllowUserToAddRows = true;
dgv.CellBeginEdit += new DataGridViewCellCancelEventHandler(DataGridView_CellBeginEdit);
dgv.CellContentClick += new DataGridViewCellEventHandler(DataGridView_CellContentClick);
TabPage tabPage = new TabPage(dt.TableName);
tabPage.Controls.Add(dgv);
tabControl1.TabPages.Add(tabPage);
DataGridViewButtonColumn buttonColumn = new DataGridViewButtonColumn()
{
HeaderText = "Delete",
Width = 10,
Name = "buttonDelete",
Text = "Delete",
UseColumnTextForButtonValue = true
};
dgv.Columns.Insert(2, buttonColumn);
}
}
private void DataGridView_CellBeginEdit(object sender, DataGridViewCellCancelEventArgs e)
{
if (e.ColumnIndex == 1)
{
e.Cancel = true;
}
}
private void DataGridView_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
if (e.ColumnIndex == 2)
{
DataGridView dgv = sender as DataGridView;
DataGridViewRow row = dgv.Rows[e.RowIndex];
if (MessageBox.Show(string.Format("Do you want to delete WorkCenter: {0}? ", row.Cells[0].Value), "Confirmation", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
MessageBox.Show("Deleted");
}
}
}
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;
}
}
VB.Net
Private Sub btnImport_Click(ByVal sender As Object, ByVal e As EventArgs)
OpenFileDialog1.ShowDialog()
End Sub
Private Sub openFileDialog1_FileOk(ByVal sender As Object, ByVal e As CancelEventArgs) Handles OpenFileDialog1.FileOk
Dim ds As DataSet = GetDataTableFromExcel()
For Each dt As DataTable In ds.Tables
Dim dgv As DataGridView = New DataGridView()
dgv.DefaultCellStyle.BackColor = Color.White
dgv.DefaultCellStyle.ForeColor = Color.Black
dgv.DefaultCellStyle.SelectionBackColor = Color.FromArgb(0, 177, 89)
dgv.DefaultCellStyle.SelectionForeColor = Color.White
dgv.RowHeadersVisible = True
dgv.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.AutoSize
dgv.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill
dgv.Anchor = AnchorStyles.Top And AnchorStyles.Left And AnchorStyles.Right
dgv.ScrollBars = ScrollBars.Both
dgv.Dock = DockStyle.Fill
dgv.DataSource = dt
dgv.AllowUserToAddRows = True
AddHandler dgv.CellBeginEdit, AddressOf DataGridView_CellBeginEdit
AddHandler dgv.CellContentClick, AddressOf DataGridView_CellContentClick
Dim tabPage As TabPage = New TabPage(dt.TableName)
tabPage.Controls.Add(dgv)
tabControl1.TabPages.Add(tabPage)
Dim buttonColumn As DataGridViewButtonColumn = New DataGridViewButtonColumn() With {
.HeaderText = "Delete",
.Width = 10,
.Name = "buttonDelete",
.Text = "Delete",
.UseColumnTextForButtonValue = True
}
dgv.Columns.Insert(2, buttonColumn)
Next
End Sub
Private Sub DataGridView_CellBeginEdit(ByVal sender As Object, ByVal e As DataGridViewCellCancelEventArgs)
If e.ColumnIndex = 1 Then
e.Cancel = True
End If
End Sub
Private Sub DataGridView_CellContentClick(ByVal sender As Object, ByVal e As DataGridViewCellEventArgs)
If e.ColumnIndex = 2 Then
Dim dgv As DataGridView = TryCast(sender, DataGridView)
Dim row As DataGridViewRow = dgv.Rows(e.RowIndex)
If MessageBox.Show(String.Format("Do you want to delete WorkCenter: {0}? ", row.Cells(0).Value), "Confirmation", MessageBoxButtons.YesNo) = DialogResult.Yes Then
MessageBox.Show("Deleted")
End If
End If
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 worksheet1 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
Screenshot
