If new version of excel support more than 65000 rows to export then you can
OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\testing.xlsx;Extended Properties='Excel 12.0 Xml;HDR=Yes;IMEX=1;'");
OleDbCommand cmd = new OleDbCommand("select * from [Sheet1$]");
if (conn.State == System.Data.ConnectionState.Open)
{
conn.Close();
}
conn.Open();
OleDbDataAdapter adap = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();
adap.Fill(ds);
this.GridView1.DataSource = ds.Tables[0].DefaultView;
conn.Close();
another way
string[] array2 = Directory.GetFiles(@"D:\doc", "*.xls");
foreach (string name in array2)
{
string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;""";
excelConnectionString=string.Format(excelConnectionString,name).Trim();
Console.WriteLine(excelConnectionString);
using (OleDbConnection connection =new OleDbConnection(excelConnectionString))
{
OleDbCommand command = new OleDbCommand("Select Field1,Field2,Field3 FROM [Sheet1$]", connection);
connection.Open();
using (DbDataReader dr = command.ExecuteReader())
{
string sqlConnectionString = @"Data Source=Machine007\SQLEXPRESS;Initial Catalog=BankDB;Integrated Security=True";
using (SqlBulkCopy bulkCopy =
new SqlBulkCopy(sqlConnectionString))
{
bulkCopy.DestinationTableName = "BankCodes";
bulkCopy.WriteToServer(dr);
}
}
}
}