ASPForums.Net RSS Feedhttp://www.aspforums.net/Handlers/RSS.ashxLatest additions to the content that appears on ASPForums.Net(c) 2019 www.aspforums.com. All rights reserved.Import Excel data to Database using C# and VB.Net in Windows Application<p>Hi <a class="username" rel="smile"> smile</a>,</p> <p>Check this example. Now please take its reference and correct your code.</p> <p>For importing excel refer below article.</p> <h1 class="header"><a title="Import data from Excel file to Windows Forms DataGridView using C# and VB.Net'a" href="https://www.aspsnippets.com/Articles/Import-data-from-Excel-file-to-Windows-Forms-DataGridView-using-C-and-VBNet.aspx">Import data from Excel file to Windows Forms DataGridView using C# and VB.Net</a></h1> <p><strong><span style="text-decoration: underline;">Form Design</span></strong></p> <p>In Windows Form i have taken&nbsp;a Button for Upload excel, OpenFileDialog to select file and DataGridView for displaying the uploaded record.</p> <p><span style="text-decoration: underline;"><strong>Namespaces</strong></span></p> <p><strong>C#</strong></p> <pre class="brush: csharp">using System.ComponentModel; using System.Data; using System.Data.OleDb; using System.Data.SqlClient; using System.IO;</pre> <p><strong>VB.Net</strong></p> <pre class="brush: vb">Imports System.ComponentModel Imports System.Data Imports System.Data.OleDb Imports System.Data.SqlClient Imports System.IO</pre> <p><span style="text-decoration: underline;"><strong>Code</strong></span></p> <p><strong>C#</strong></p> <pre class="brush: csharp">private void btnUpload_Click(object sender, EventArgs e) { openFileDialog1.ShowDialog(); } private void openFileDialog1_FileOk(object sender, CancelEventArgs e) { DataTable dtDb = new DataTable(); dtDb.Columns.Add(&#34;ClassID&#34;, typeof(int)); dtDb.Columns.Add(&#34;ClassName&#34;, typeof(string)); dtDb.Rows.Add(195, &#34;One&#34;); dtDb.Rows.Add(196, &#34;Two&#34;); string excelPath = openFileDialog1.FileName; string conString = string.Empty; string extension = Path.GetExtension(excelPath); switch (extension) { case &#34;.xls&#34;: conString = string.Format(&#34;Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=&#39;Excel 8.0;HDR=YES&#39;&#34;, excelPath); break; case &#34;.xlsx&#34;: conString = string.Format(&#34;Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=&#39;Excel 8.0;HDR=YES&#39;&#34;, excelPath); break; } DataTable dtExcelData = new DataTable(); conString = string.Format(conString, excelPath); using (OleDbConnection excel_con = new OleDbConnection(conString)) { excel_con.Open(); string sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0][&#34;TABLE_NAME&#34;].ToString(); using (OleDbDataAdapter oda = new OleDbDataAdapter(&#34;SELECT * FROM [&#34; + sheet1 + &#34;]&#34;, excel_con)) { oda.Fill(dtExcelData); } excel_con.Close(); for (int i = 0; i &lt; dtExcelData.Rows.Count; i++) { string className = dtExcelData.Rows[i][&#34;ClassName&#34;].ToString(); int classId = dtDb.Select(&#34;ClassName=&#39;&#34; + className + &#34;&#39;&#34;)[0].Field&lt;int&gt;(&#34;ClassID&#34;); dtExcelData.Rows[i][&#34;ClassName&#34;] = classId; } dtExcelData.Columns[&#34;ClassName&#34;].ColumnName = &#34;ClassID&#34;; } dataGridView1.DataSource = dtExcelData; dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.DisplayedCells; string consString = &#34;Data Source=.;Initial Catalog=Test;Integrated Security = true&#34;; using (SqlConnection con = new SqlConnection(consString)) { using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con)) { //Set the database table name. sqlBulkCopy.DestinationTableName = &#34;dbo.tblStu&#34;; //[OPTIONAL]: Map the Excel columns with that of the database table. sqlBulkCopy.ColumnMappings.Add(&#34;StuID&#34;, &#34;StuID&#34;); sqlBulkCopy.ColumnMappings.Add(&#34;SName&#34;, &#34;SName&#34;); sqlBulkCopy.ColumnMappings.Add(&#34;ClassID&#34;, &#34;ClassID&#34;); sqlBulkCopy.ColumnMappings.Add(&#34;Phone&#34;, &#34;Phone&#34;); con.Open(); sqlBulkCopy.WriteToServer(dtExcelData); con.Close(); } } }</pre> <p><strong>VB.Net</strong></p> <pre class="brush: vb">Private Sub btnUpload_Click(sender As System.Object, e As System.EventArgs) Handles btnUpload.Click openFileDialog1.ShowDialog() End Sub Private Sub openFileDialog1_FileOk(sender As System.Object, e As System.ComponentModel.CancelEventArgs) Handles openFileDialog1.FileOk Dim dtDb As DataTable = New DataTable() dtDb.Columns.Add(&#34;ClassID&#34;, GetType(Integer)) dtDb.Columns.Add(&#34;ClassName&#34;, GetType(String)) dtDb.Rows.Add(195, &#34;One&#34;) dtDb.Rows.Add(196, &#34;Two&#34;) Dim excelPath As String = openFileDialog1.FileName Dim conString As String = String.Empty Dim extension As String = Path.GetExtension(excelPath) Select Case extension Case &#34;.xls&#34; conString = String.Format(&#34;Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=&#39;Excel 8.0;HDR=YES&#39;&#34;, excelPath) Case &#34;.xlsx&#34; conString = String.Format(&#34;Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=&#39;Excel 8.0;HDR=YES&#39;&#34;, excelPath) End Select Dim dtExcelData As DataTable = New DataTable() conString = String.Format(conString, excelPath) Using excel_con As OleDbConnection = New OleDbConnection(conString) excel_con.Open() Dim sheet1 As String = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing).Rows(0)(&#34;TABLE_NAME&#34;).ToString() Using oda As OleDbDataAdapter = New OleDbDataAdapter(&#34;SELECT * FROM [&#34; &amp; sheet1 &amp; &#34;]&#34;, excel_con) oda.Fill(dtExcelData) End Using excel_con.Close() For i As Integer = 0 To dtExcelData.Rows.Count - 1 Dim className As String = dtExcelData.Rows(i)(&#34;ClassName&#34;).ToString() Dim classId As Integer = dtDb.[Select](&#34;ClassName=&#39;&#34; &amp; className &amp; &#34;&#39;&#34;)(0).Field(Of Integer)(&#34;ClassID&#34;) dtExcelData.Rows(i)(&#34;ClassName&#34;) = classId Next dtExcelData.Columns(&#34;ClassName&#34;).ColumnName = &#34;ClassID&#34; End Using dataGridView1.DataSource = dtExcelData dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.DisplayedCells Dim consString As String = &#34;Data Source=.;Initial Catalog=Test;Integrated Security = true&#34; Using con As SqlConnection = New SqlConnection(consString) Using sqlBulkCopy As SqlBulkCopy = New SqlBulkCopy(con) &#39;Set the database table name. sqlBulkCopy.DestinationTableName = &#34;dbo.tblStu&#34; &#39;[OPTIONAL]: Map the Excel columns with that of the database table. sqlBulkCopy.ColumnMappings.Add(&#34;StuID&#34;, &#34;StuID&#34;) sqlBulkCopy.ColumnMappings.Add(&#34;SName&#34;, &#34;SName&#34;) sqlBulkCopy.ColumnMappings.Add(&#34;ClassID&#34;, &#34;ClassID&#34;) sqlBulkCopy.ColumnMappings.Add(&#34;Phone&#34;, &#34;Phone&#34;) con.Open() sqlBulkCopy.WriteToServer(dtExcelData) con.Close() End Using End Using End Sub</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="https://i.imgur.com/BnN7GiZ.jpg" alt="" width="319" height="203" /></span></p> <p><span style="text-decoration: underline;">The imported record displayed in DataGridView</span></p> <p><span style="text-decoration: underline;"><img src="https://i.imgur.com/J5Zf8Zb.jpg" alt="" width="338" height="191" /></span></p>https://www.aspforums.net:443/Threads/138494/Import-Excel-data-to-Database-using-C-and-VBNet-in-Windows-Application/https://www.aspforums.net:443/Threads/138494/Import-Excel-data-to-Database-using-C-and-VBNet-in-Windows-Application/Mon, 10 Jun 2019 07:40:15 GMT