Hi hemma123,
Refering the below article i have created sample.
C#
private void btnOpenExcel_Click(object sender, EventArgs e)
{
DataTable dt = new DataTable();
if (openFileDialog1.ShowDialog() == DialogResult.OK)
{
string filePath = openFileDialog1.FileName;
string extension = "." + filePath.Split('.')[filePath.Split('.').Length - 1];
string conString = string.Empty;
switch (extension)
{
case ".xls": //Excel 97-03.
conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
break;
case ".xlsx": //Excel 07 and above.
conString = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
break;
}
conString = string.Format(conString, filePath);
using (OleDbConnection connExcel = new OleDbConnection(conString))
{
using (OleDbCommand cmdExcel = new OleDbCommand())
{
using (OleDbDataAdapter odaExcel = new OleDbDataAdapter())
{
cmdExcel.Connection = connExcel;
//Get the name of First Sheet.
connExcel.Open();
DataTable dtExcelSchema;
dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string sheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
connExcel.Close();
//Read Data from First Sheet.
connExcel.Open();
cmdExcel.CommandText = "SELECT * From [" + sheetName + "]";
odaExcel.SelectCommand = cmdExcel;
odaExcel.Fill(dt);
connExcel.Close();
}
}
}
// Convert empty cells in of DataTable to NULL for VARCHAR column and 0 for other.
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
if (String.IsNullOrEmpty(dt.Rows[i][j].ToString()))
{
if (dt.Columns[j].DataType == typeof(string))
{
dt.Rows[i][j] = "NULL";
}
else
{
dt.Rows[i][j] = 0;
}
}
}
}
if (dt.Rows.Count > 0)
{
string consString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(consString))
{
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
{
sqlBulkCopy.DestinationTableName = "dbo.Customers";
sqlBulkCopy.ColumnMappings.Add("CustomerId", "CustomerId");
sqlBulkCopy.ColumnMappings.Add("Name", "Name");
sqlBulkCopy.ColumnMappings.Add("Country", "Country");
try
{
con.Open();
sqlBulkCopy.WriteToServer(dt);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
con.Close();
MessageBox.Show("Record inserted successfully");
}
}
}
}
}
}
VB.Net
Private Sub btnOpenExcel_Click(sender As Object, e As EventArgs)
Dim dt As New DataTable()
If openFileDialog1.ShowDialog() = DialogResult.OK Then
Dim filePath As String = openFileDialog1.FileName
Dim extension As String = "." + filePath.Split("."C)(filePath.Split("."C).Length - 1)
Dim conString As String = String.Empty
Select Case extension
Case ".xls"
'Excel 97-03.
conString = ConfigurationManager.ConnectionStrings("Excel03ConString").ConnectionString
Exit Select
Case ".xlsx"
'Excel 07 and above.
conString = ConfigurationManager.ConnectionStrings("Excel07ConString").ConnectionString
Exit Select
End Select
conString = String.Format(conString, filePath)
Using connExcel As New OleDbConnection(conString)
Using cmdExcel As New OleDbCommand()
Using odaExcel As New OleDbDataAdapter()
cmdExcel.Connection = connExcel
'Get the name of First Sheet.
connExcel.Open()
Dim dtExcelSchema As DataTable
dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
Dim sheetName As String = dtExcelSchema.Rows(0)("TABLE_NAME").ToString()
connExcel.Close()
'Read Data from First Sheet.
connExcel.Open()
cmdExcel.CommandText = (Convert.ToString("SELECT * From [") & sheetName) + "]"
odaExcel.SelectCommand = cmdExcel
odaExcel.Fill(dt)
connExcel.Close()
End Using
End Using
End Using
' Convert empty cells in of DataTable to NULL for VARCHAR column and 0 for other.
For i As Integer = 0 To dt.Rows.Count - 1
For j As Integer = 0 To dt.Columns.Count - 1
If [String].IsNullOrEmpty(dt.Rows(i)(j).ToString()) Then
If dt.Columns(j).DataType = GetType(String) Then
dt.Rows(i)(j) = "NULL"
Else
dt.Rows(i)(j) = 0
End If
End If
Next
Next
If dt.Rows.Count > 0 Then
Dim consString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As New SqlConnection(consString)
Using sqlBulkCopy As New SqlBulkCopy(con)
sqlBulkCopy.DestinationTableName = "dbo.Customers"
sqlBulkCopy.ColumnMappings.Add("CustomerId", "CustomerId")
sqlBulkCopy.ColumnMappings.Add("Name", "Name")
sqlBulkCopy.ColumnMappings.Add("Country", "Country")
Try
con.Open()
sqlBulkCopy.WriteToServer(dt)
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
con.Close()
MessageBox.Show("Record inserted successfully")
End Try
End Using
End Using
End If
End If
End Sub
Screenshot
ConnectionString
<connectionStrings>
<add name="constr" connectionString="Server=.;DataBase=Test;UID=sa;PWD=pass" providerName="System.Data.SqlClient" />
<add name="Excel03ConString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'"/>
<add name="Excel07ConString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'"/>
</connectionStrings>
Using OpenXml
C#
private void btnOpenExcel_Click(object sender, EventArgs e)
{
DataTable dt = new DataTable();
if (openFileDialog1.ShowDialog() == DialogResult.OK)
{
string filePath = openFileDialog1.FileName;
using (SpreadsheetDocument doc = SpreadsheetDocument.Open(filePath, false))
{
Sheet sheet = doc.WorkbookPart.Workbook.Sheets.GetFirstChild<Sheet>();
Worksheet worksheet = (doc.WorkbookPart.GetPartById(sheet.Id.Value) as WorksheetPart).Worksheet;
IEnumerable<Row> rows = worksheet.GetFirstChild<SheetData>().Descendants<Row>();
foreach (Row row in rows)
{
if (row.RowIndex.Value == 1)
{
foreach (Cell cell in row.Descendants<Cell>())
{
dt.Columns.Add(GetValue(doc, cell));
}
}
else
{
dt.Rows.Add();
int i = 0;
foreach (Cell cell in row.Descendants<Cell>())
{
dt.Rows[dt.Rows.Count - 1][i] = GetValue(doc, cell);
i++;
}
}
}
}
}
if (dt.Rows.Count > 0)
{
string consString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(consString))
{
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
{
sqlBulkCopy.DestinationTableName = "dbo.Customers";
sqlBulkCopy.ColumnMappings.Add("CustomerId", "CustomerId");
sqlBulkCopy.ColumnMappings.Add("Name", "Name");
sqlBulkCopy.ColumnMappings.Add("Country", "Country");
try
{
con.Open();
sqlBulkCopy.WriteToServer(dt);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
con.Close();
MessageBox.Show("Record inserted successfully");
}
}
}
}
}
private string GetValue(SpreadsheetDocument doc, Cell cell)
{
string value = cell.CellValue.InnerText;
if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
{
return doc.WorkbookPart.SharedStringTablePart.SharedStringTable.ChildElements.GetItem(int.Parse(value)).InnerText;
}
return value;
}
VB.Net
Private Sub btnOpenExcel_Click(sender As Object, e As EventArgs)
Dim dt As New DataTable()
If openFileDialog1.ShowDialog() = DialogResult.OK Then
Dim filePath As String = openFileDialog1.FileName
Using doc As SpreadsheetDocument = SpreadsheetDocument.Open(filePath, False)
Dim sheet As Sheet = doc.WorkbookPart.Workbook.Sheets.GetFirstChild(Of Sheet)()
Dim worksheet As Worksheet = TryCast(doc.WorkbookPart.GetPartById(sheet.Id.Value), WorksheetPart).Worksheet
Dim rows As IEnumerable(Of Row) = worksheet.GetFirstChild(Of SheetData)().Descendants(Of Row)()
For Each row As Row In rows
If row.RowIndex.Value = 1 Then
For Each cell As Cell In row.Descendants(Of Cell)()
dt.Columns.Add(GetValue(doc, cell))
Next
Else
dt.Rows.Add()
Dim i As Integer = 0
For Each cell As Cell In row.Descendants(Of Cell)()
dt.Rows(dt.Rows.Count - 1)(i) = GetValue(doc, cell)
i += 1
Next
End If
Next
End Using
End If
If dt.Rows.Count > 0 Then
Dim consString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As New SqlConnection(consString)
Using sqlBulkCopy As New SqlBulkCopy(con)
sqlBulkCopy.DestinationTableName = "dbo.Customers"
sqlBulkCopy.ColumnMappings.Add("CustomerId", "CustomerId")
sqlBulkCopy.ColumnMappings.Add("Name", "Name")
sqlBulkCopy.ColumnMappings.Add("Country", "Country")
Try
con.Open()
sqlBulkCopy.WriteToServer(dt)
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
con.Close()
MessageBox.Show("Record inserted successfully")
End Try
End Using
End Using
End If
End Sub
Private Function GetValue(doc As SpreadsheetDocument, cell As Cell) As String
Dim value As String = cell.CellValue.InnerText
If cell.DataType IsNot Nothing AndAlso cell.DataType.Value = CellValues.SharedString Then
Return doc.WorkbookPart.SharedStringTablePart.SharedStringTable.ChildElements.GetItem(Integer.Parse(value)).InnerText
End If
Return value
End Function