Hello All
Today i will demonstrate the process of Excel data read with both DataReader & DataSet . Though the Code is not optimize but its very simple to read a excel file in OLEDB.
In the example i have hard-coded the Excel file location named “Book1.xls“, and the selected sheet is “Sheet1“. We also have a database and a table in it named “TableInfo” where we will insert data after reading from excel file.
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Collections;
namespace ExcelExport
{
class GenerateExcel
{
public ArrayList ColumnNames;
public DataSet ReadExcell()
{
String conStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + @"Data Source=C:\Documents and Settings\tanowar\Desktop\Excel\WinApp\ExcelExport\ExcelExport\Book1.xls;" + "Extended Properties=Excel 8.0;";
string sqlConString = "Data Source=DtaBase;Initial Catalog=GPXPO;User ID=User;Password=XXX ";
SqlConnection sqlCon = new SqlConnection(sqlConString);
OleDbConnection oleCon = new OleDbConnection(conStr);
DataSet ds = new DataSet();
try
{
#region DataSet
oleCon.Open();
OleDbCommand oleCmd = new OleDbCommand("select * from [Sheet1$]", oleCon);
OleDbDataAdapter oleAd = new OleDbDataAdapter();
oleAd.SelectCommand = oleCmd;
oleAd.Fill(ds);
oleCon.Close();
#endregion
#region DataReader
OleDbDataReader reader = oleCmd.ExecuteReader();
while (reader.Read())
{
sqlCon.Open();
SqlCommand sqlCmd = new SqlCommand("insert into TableInfo (AllotNo,BankCode,LotteryNo,BONo,Name,Shares)values('"+reader[0].ToString() +"','"+reader[1].ToString() +"','"+reader[2].ToString() +"','"+reader[3].ToString() +"','"+reader[4].ToString() +"',"+reader[5].ToString() +")",sqlCon);
sqlCmd.ExecuteNonQuery();
sqlCon.Close();
}
#endregion
}
catch (Exception exc)
{
exc.ToString();
}
return ds;
}
}
}