Import Excel in C#

Introduction

Here we will learn how to Import Excel in C# in windows and web application. The code example provide here can be used in any application that supports c# language. I code can be used to store excel data into database as well.

Getting Started

Here in this blog, we will discuss the code example to read data from the excel sheet. We are not going to use any inbuilt library or class like excel Microsoft.office..interop.excel or excel.workbook.xlworkbook.

The below code example provide the best way to read excel file in c#. You can use this code to upload and read excel file in asp net c#, upload excel file in asp net c#, read excel file in c# net core, read data from excel sheet in mvc, in asp.net core read excel file, and much more application which supports c# language.

Code example of Import Excel in C#

 
private DataTable ReadExcelFile()   
    {   
     Console.WriteLine("Readig Excel File : " + ExcelFilePath + " completed");   
     string connectionString = this.GetConnectionString();   
     using (OleDbConnection conn = new OleDbConnection(connectionString))   
     {   
      conn.Open();   
      OleDbCommand cmd = new OleDbCommand();   
      cmd.Connection = conn;   
      string sheetName = "Student$";   
      cmd.CommandText = "SELECT * FROM [" + sheetName + "]"; 
      //data table excel
      DataTable dt = new DataTable();   
      dt.TableName = sheetName.Replace("$", string.Empty);   
      OleDbDataAdapter da = new OleDbDataAdapter(cmd);   
      da.Fill(dt);    
      cmd = null;   
      conn.Close();   
     }   
     Console.WriteLine("Readig Excel File completed");   
     return da;   
    }   

Import Excel to DataTable in C#

The above example uses OleDbDataAdapter and OleDbConnection from library microsoft.ace.oledb.12.o data to read excel workbooks and connect to excel sheet.

Then keeps the read excel data into SQL DataTable using OleDbDataAdapter, here you can do whatever you want with data.

The GetConnectionString method uses in the above code example, is nothing but a simple user-defined function to separate the build ConnectionString logic. See the example below for the GetConnectionString Method.

 private string GetConnectionString()   
    {   
     Dictionary<string, string> props = new Dictionary<string, string>();   
     // XLSX - Excel 2007, 2010, 2012, 2013   
     props["Provider"] = "Microsoft.ACE.OLEDB.12.0;";   
     props["Extended Properties"] = "Excel 12.0 XML";   
     props["Data Source"] = ExcelFilePath;   
     // XLS - Excel 2003 and Older   
     //props["Provider"] = "Microsoft.Jet.OLEDB.4.0";   
     //props["Extended Properties"] = "Excel 8.0";   
     //props["Data Source"] = "C:\\MyExcel.xls";   
     StringBuilder sb = new StringBuilder();   
     foreach (KeyValuePair<string, string> prop in props)   
     {   
      sb.Append(prop.Key);   
      sb.Append('=');   
      sb.Append(prop.Value);   
      sb.Append(';');   
     }   
     return sb.ToString();   
    }   

Import Excel in C#

Related Articles

1. Reading Excel file in AngularJS
2. Creating controller for File Upload
3. Import XML into SQL Table
4. Export to Excel in MVC
5. Export DataTable To Excel in C#
6. Validating Excel Sheet in C#
7. Export Table Data into XML in SQL
8. Export HTML Table to Excel Using JavaScript

Summary

In this blog, we learn to upload or import excel file in C#. I hope you have enjoyed it a lot.

Thanks