Simple Excel Data read and save with C#.NET


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;
}
}
}


Leave a Reply