The following program
illustrates how to open an existing Excel spreadsheet in C#.
Step 1: Please download ‘AccessDatabaseEngine_X64’ file from here
This download will install
a set of components that can be used to facilitate transfer of data between Microsoft
Office System files and non-Microsoft Office applications such as Microsoft SQL
Server and it also installed ODBC and OLEDB drivers that helps developers to
use in developing their application.
Step 2: you have to add
the Microsoft Excel 15.0 Object Library to you project.
Step 3:If still you facing
following issue as shown in screen shot then you have to set the configuration management
as shown in screen shot below this issue.
Note: Rebuild your
application and run following code, if you face same problem again then close
your application and open it again. After all this I hope this code of .cs file
will read your excel file.
Step 4: Copy following
code to you console application. It will read your excel file.
using System.Data;
using System.Data.OleDb;
namespace ReadingExcelFile
{
class Program
{
static void Main(string[] args)
{
string[] tableName = {
"EMPLOYEE",
"DEPT"
};
for (int i = 0; i < tableName.Length; i++)
{
fnReadExcelFile(tableName[i]);
}
}
protected static void fnReadExcelFile(string TableName)
{
string path = @"D:\Projects\ReadingCSVFile\ReadingCSVFile\" + TableName + ".xlsx";
//Create
connection string to Excel work book
string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=" + path + ";Extended Properties=Excel 12.0;Persist Security
Info=False";
//Create
Connection to Excel work book
OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
//Create
OleDbCommand to fetch data from Excel
string _sql = "Select *
from [Employees$]";
OleDbCommand cmd = new OleDbCommand(_sql, excelConnection);
excelConnection.Open();
OleDbDataAdapter dReader = new OleDbDataAdapter(_sql, excelConnection);
DataSet ds = new DataSet();
dReader.Fill(ds);
fnInsertData(ds.Tables[0],
TableName);
}
private static void fnInsertData(DataTable dt, string TableName)
{
switch (TableName)
{
case "EMPLOYEES":
{
for (int i = 0; i < dt.Rows.Count;
i++)
{
//Your
function to insert record to database
//fnInsEMPLOYEES(dt.Rows[i]);
}
break;
}
case "DEPT":
{
for (int i = 0; i < dt.Rows.Count;
i++)
{
//Your function to insert record to database
//fnInsDEPT(dt.Rows[i]);
}
break;
}
}
}
}
}
No comments:
Post a Comment