La lecture de valeurs à partir d'un Fichier Excel

Vous pouvez utiliser ADO.NET pour lire les valeurs à partir d'une feuille Excel. Pour plus d'informations sur la chaîne de connexion, voir http://www.connectionstrings.com/excel-2007

<connectionStrings>
    <add name="Default"
         connectionString='Microsoft.ACE.OLEDB.12.0;Data Source=c:\your\folder\file.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES";'
         providerName="System.Data.OleDb" />
</connectionStrings>

Vous pouvez ensuite utiliser un standard System.Data.OleDb.OleDbConnection pour lire les valeurs de la source de données. Considérons, par exemple, un fichier Excel qui a une feuille nommée Users, avec deux colonnes, UserName et Age.

using System.Data;
using System.Data.Common;

public int UserExists(string userName, int age)
{
    var provider = ConfigurationManager.ConnectionStrings["Default"].ProviderName;
    var factory = DbProviderFactories.GetFactory(provider);

    var connectionString = ConfigurationManager.ConnectionStrings["Default"].ConnectionString;

    using (var connection = factory.CreateConnection())
    {
        connection.ConnectionString = connectionString;

        using (DbCommand command = connection.CreateCommand())
        {
            DbParameter userNameParameter = factory.CreateParameter();
            userNameParameter.ParameterName = "@UserName";
            userNameParameter.DbType = DbType.String;
            userNameParameter.Direction = ParameterDirection.Input;
            userNameParameter.IsNullable = false;
            userNameParameter.Value = userName;


            DbParameter ageParameter = factory.CreateParameter();
            ageParameter.ParameterName = "@Age";
            ageParameter.DbType = DbType.Int32;
            ageParameter.Direction = ParameterDirection.Input;
            ageParameter.IsNullable = false;
            ageParameter.Value = age;

            command.CommandText = "SELECT COUNT(*) FROM [Users$] WHERE UserName=@UserName AND Age=@Age";
            command.Parameters.Add(userNameParameter);
            command.Parameters.Add(ageParameter);
            connection.Open();

            int usersExits = (int) command.ExecuteScalar();

            return usersExits == 1;
        }
    }
}

OriginalL'auteur Alexander Groß

utiliser ce code pour que,

DimobjEXCELCon As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=EXCLE_FILE_PATH;Extended Properties=""Excel 12.0 Xml;HDR=Yes""")
ExcelConnection.Open()

Dim objQuery As String = "SELECT * FROM [Sheet1$]" 'get values from sheet1, here you can change your sheet name

Dim objCMD As OleDbCommand = New OleDbCommand(objQuery,objEXCELCon)
Dim objDR As OleDbDataReader

Dim SQLconn As New SqlConnection()
Dim szCON As String = "Connection string for database"
SQLconn.ConnectionString = szCON
SQLconn.Open()


Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(SQLConn)
bulkCopy.DestinationTableName = "TableToWriteToInSQLSERVER"

 Try
  objDR = objCMD.ExecuteReader
  bulCopy.WriteToServer(objDR)
  objDR.Close()
  SQLConn.Close()

 Catch ex As Exception
  MsgBox(ex.ToString)
 End Try

OriginalL'auteur ketan italiya