Create and Read Excel File Using Oledb provider

When working with excel file for data mining or export data to excel many time required to read excel file and also write file. One can use Oledb Provider to connect existing file or create new excel file for report in .net

1. Get Sheets Name from Excel File

      
System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection();
try
{
	string filePath = @"c:\myExcel2012.xlsx";
	conn.ConnectionString = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 	12.0 Xml;HDR=YES"";",filePath);
	conn.Open();
	DataTable dtSheets = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);                
	foreach (DataRow dr in dtSheets.Rows)
	{
	  Console.WriteLine(dr["TABLE_NAME"].ToString()); // Print Sheet Name
	}
}             
finally
{
	if(conn.State == ConnectionState.Open)
	conn.Close();
}

2. Read Sheet Data

System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection();
            try
            {
                string filePath = @"c:\myExcel2012.xlsx";
                conn.ConnectionString = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES"";", filePath);
                conn.Open();
                DataTable dtSheets = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
                foreach (DataRow dr in dtSheets.Rows)
                {
                    Console.WriteLine(dr["TABLE_NAME"].ToString()); 
                    var cmd = conn.CreateCommand();
                    cmd.CommandText = "SELECT * FROM " + dr["TABLE_NAME"].ToString();
                    var reader = cmd.ExecuteReader();           
                    DataTable dt = new DataTable(); 
                    dt.Load(reader); // This will load data from excel sheet to datatable.

                    // your code to work on sheet data.
                }
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }

3. Create and Write Data to Excel sheet

            System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection();
            try
            {
                string pathOfFileToCreate = "c:\newexcel.xlsx";
                conn.ConnectionString = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES"";",pathOfFileToCreate);
                conn.Open();
                var cmd = conn.CreateCommand();
                cmd.CommandText = "CREATE TABLE sheet1 (ID INTEGER,NAME NVARCHAR(100))"; // Create Sheet With Name Sheet1
                cmd.ExecuteNonQuery();
                for (int i = 0; i < 1000; i++) // Sample Data Insert 
                {
                    cmd.CommandText = String.Format("INSERT INTO sheet1 (ID,NAME) VALUES({0},'{1}')", i, "Name" + i.ToString());
                    cmd.ExecuteNonQuery(); // Execute insert query against excel file.
                }
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }

 

I feel that above sample code is best way to explain article.