Recently I've being asked by friend to show him a simple way to read/write data from/to MS Excel files. I found my old project with very simple method of working with excel files by using Microsoft.Jet.OLEDB.4.0 Provider (similar to MS Access). This is one of many methods to work with excel files.
Read data from Excel Sheet:
| ''' <summary> ''' Reads the data from excel file. ''' </summary> ''' <param name="strFilePath">The file path.</param> ''' <param name="strSheetName">Name of the sheet.</param> ''' <returns></returns> Public Function ReadDataFromExcelFile(ByVal strFilePath As String, ByVal strSheetName As String) As Data.DataSet If strFilePath Is Nothing Then Throw New NullReferenceException("strFilePath") If File.Exists(strFilePath) = False Then Throw New FileNotFoundException(strFilePath) If String.IsNullOrEmpty(strSheetName) Then Throw New Exception("Empty sheet name.") ' define con. params Dim str As String = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;""", strFilePath) ' init con. Dim cnn As New OleDb.OleDbConnection(str) ' open con. to xl file Try cnn.Open() Catch ex As Exception cnn.Dispose() Throw ex End Try ' query str. (like any ordinary SQL) Dim strSQL As String = String.Format("SELECT * FROM [{0}$];", strSheetName) ' init data adapter Dim da As New OleDb.OleDbDataAdapter(strSQL, cnn) ' init data set Dim ds As New DataSet(strSheetName) ' read xl data Try da.Fill(ds) Catch ex As Exception da.Dispose() ds.Dispose() cnn.Close() Throw ex End Try ' close connections and dispose objects da.Dispose() cnn.Close() cnn.Dispose() Return ds End Function |
Write data to Excel Sheet:
| ''' <summary> ''' Saves the data to excel file. ''' </summary> ''' <param name="strFilePath">The file path.</param> ''' <param name="strSheetName">Name of the sheet.</param> ''' <param name="data">The sheet data.</param> Public Sub SaveDataToExcelFile(ByVal strFilePath As String, ByVal strSheetName As String, ByVal data As Data.DataSet) If strFilePath Is Nothing Then Throw New NullReferenceException("strFilePath") If String.IsNullOrEmpty(strSheetName) Then Throw New Exception("Empty sheet name.") If data Is Nothing Then Throw New NullReferenceException("data") ' define con. params Dim str As String = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;""", strFilePath) ' init con. Dim cnn As New OleDb.OleDbConnection(str) ' init cmd. Dim cmd As New OleDb.OleDbCommand() cmd.Connection = cnn ' open con. to xl file Try cnn.Open() Catch ex As Exception cmd.Dispose() cnn.Close() cnn.Dispose() Throw ex End Try ' if cur. xl file contains sheet with same name, delete the old one Try ' query str. (like any ordinary SQL) cmd.CommandText = String.Format("DROP TABLE [{0}];", strSheetName) ' exec. query cmd.ExecuteNonQuery() Catch ex As Exception Console.WriteLine(ex) End Try ' init str. builders Dim bColNames As New System.Text.StringBuilder Dim bColDefs As New System.Text.StringBuilder ' read coloumn names and define query params For i As Integer = 0 To data.Tables(0).Columns.Count - 1 bColDefs.AppendFormat("{0} nvarchar(255),", data.Tables(0).Columns(i).ColumnName) bColNames.AppendFormat("{0},", data.Tables(0).Columns(i).ColumnName) Next ' remove last ',' (comma) bColDefs.Remove(bColDefs.Length - 1, 1) bColNames.Remove(bColNames.Length - 1, 1) ' query str. (like any ordinary SQL) cmd.CommandText = String.Format("CREATE TABLE [{0}] ({1});", strSheetName, bColDefs) ' exec. query Try cmd.ExecuteNonQuery() Catch ex As Exception cmd.Dispose() cnn.Close() cnn.Dispose() Throw ex End Try Try ' insert values from 'data' into sheet in opened xl file For i As Integer = 0 To data.Tables(0).Rows.Count - 1 ' init str. builder Dim bVals As New Text.StringBuilder ' collect row vals. from src. sheet For j As Integer = 0 To data.Tables(0).Rows(i).ItemArray.Length - 1 bVals.AppendFormat("'{0}',", data.Tables(0).Rows(i).ItemArray(j)) Next ' remove last ',' (comma) bVals.Remove(bVals.Length - 1, 1) ' query str. (like any ordinary SQL) cmd.CommandText = String.Format("INSERT INTO [{0}$] ({1}) " & _ "VALUES({2});", strSheetName, bColNames, bVals) ' exec. query cmd.ExecuteNonQuery() Next Catch ex As Exception cmd.Dispose() cnn.Close() cnn.Dispose() Throw ex End Try cmd.Dispose() cnn.Close() cnn.Dispose() End Sub |
Download example project from here.
Hope this post was helpful :)
