Two steps or solutions
1. Import excel to datagridview
2. import grid to database
or
Import excel in both..
First one looks better because you can load grid with excel then you can manually able to do correction in this..
After that you can save that to database.
Following shows how to load grid with excel data.
dgvdetails is datagridview
Private Sub Import_To_Grid()
Dim FilePath As String = "C:\Item Master-April2013.xlsx"
Dim conStr As String = ""
conStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=Yes;IMEX=1';"
conStr = String.Format(conStr, FilePath)
Dim connExcel As New OleDbConnection
connExcel.ConnectionString = conStr
Dim cmdExcel As New OleDbCommand()
Dim oda As New OleDbDataAdapter()
cmdExcel.Connection = connExcel
connExcel.Open()
Dim Exceldt As New DataTable()
Dim dtExcelSchema As DataTable
dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
Dim SheetName As String = dtExcelSchema.Rows(0)("TABLE_NAME").ToString()
connExcel.Close()
connExcel.Open()
cmdExcel.CommandText = "SELECT * From [" + SheetName + "]"
oda.SelectCommand = cmdExcel
oda.Fill(Exceldt)
connExcel.Close()
dgvDetails.DataSource = Exceldt
End Sub
Following shows how to save data of grid with specific columns and conditions
r as integer
sdcmd as sqlcommand with your query and connection. I Believe, at least you know this.
As i don't know your columns.. InvoiceNo,SrNo,ProductID,Parameters,Quantity are the columns.
For r = 0 To dgvDetails.RowCount - 1
If Val(dgvDetails.Rows(r).Cells("SrNo").Value) <> 0 Then
sdCmd.Parameters.Clear()
sdCmd.Parameters.Add("@InvoiceNo", OleDb.OleDbType.WChar).Value = r
sdCmd.Parameters.Add("@SrNo", OleDb.OleDbType.Numeric).Value = dgvDetails.Rows(r).Cells("SrNo").Value
sdCmd.Parameters.Add("@ProductID", OleDb.OleDbType.WChar).Value = dgvDetails.Rows(r).Cells("ProductID").Value
sdCmd.Parameters.Add("@Particulars", OleDb.OleDbType.WChar).Value = dgvDetails.Rows(r).Cells("Particulars").Value
sdCmd.Parameters.Add("@Quantity", OleDb.OleDbType.Currency).Value = dgvDetails.Rows(r).Cells("Quantity").Value
End If
sdCmd.ExecuteNonQuery()
Next