ExcelFileNameWithPath=@"C:\Test.xls";
.strConnectionString = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;IMEX=2"";", ExcelFileNameWithPath)
Sample code in VB.Net
Public Function UpdateExcelData(ByVal Name As String, ByVal DOB_DATE As String, ByVal ExcelFileNameWithPath As String) As Boolean
Dim bResult As Boolean = False
Dim strQuery As String = String.Format("UPDATE [Sheet1$] SET DOB= '{0}' where NAME_OF_EMP = '{1}' ", DOB_DATE, Name )
Dim strConnectionString As String = String.Empty
Dim cmd As System.Data.OleDb.OleDbCommand = Nothing
Dim ErrorMessage As String
Dim i As Integer
Try
'IMEX Values
'0 is Export mode
'1 is Import mode
'2 is Linked mode (full update capabilities)
strConnectionString = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;IMEX=2"";", ExcelFileNameWithPath)
If (Not System.IO.File.Exists(ExcelFileNameWithPath)) Then
Throw New System.Exception("File not found, source file does not exit!")
End If
Using con As New System.Data.OleDb.OleDbConnection(strConnectionString)
con.Open()
cmd = New System.Data.OleDb.OleDbCommand()
cmd.Connection = con
cmd.CommandType = CommandType.Text
cmd.CommandText = strQuery
i = cmd.ExecuteNonQuery()
If i > 0 then
bResult = True
Else
bResult = False
End If
End Using
Catch exp As Exception
bResult = False
Finally
If (Not cmd Is Nothing And cmd.Connection.State = ConnectionState.Open) Then
cmd.Connection.Close()
End If
End Try
Return bResult
End Function
