HOW TO READ EXCEL DATA WITH C# WINDOWS FORM

READ EXCEL DATA 

1. Open Visual Studio. Select New Project, then Windows Form Application.
   
2. Name it as you want. My applications name is ReadExcelFileApp.
   
   
   
3. First of all add Reference of Excel library. Right click on the Reference in the solution explorer. 
   
   Assemblies, Extensions, then Microsoft.Office.Interop.Excel.
   
   If you are using Visual Studio 2010, then you can add Reference using two ways:
   
   Right click on References then select Add Reference. After that a dialog appears,
   
   
   1. Browse, C drive, Microsoft Office, Office12, then open EXCEL.EXE.
   2. Select .NET tab then choose Microsoft.Office.Interop.Excel.
   
   
   
4. After adding Reference add namespace using Excel = Microsoft.Office.Interop.Excel; and other namespaces as shown in the figure.
   
5. Add two buttons Choose and Read File and Close.
6. Add a DataGridView to see the result (excel data).
   
   
   
7. Create a method ReadExcel who returns a datatable using the following logic.
   
   1. public DataTable ReadExcel(string fileName, string fileExt) {  
   2.     string conn = string.Empty;  
   3.     DataTable dtexcel = new DataTable();  
   4.     if (fileExt.CompareTo(".xls") == 0)  
   5.         conn = @"provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties='Excel 8.0;HRD=Yes;IMEX=1';"; //for below excel 2007  
   6.     else  
   7.         conn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties='Excel 12.0;HDR=NO';"; //for above excel 2007  
   8.     using(OleDbConnection con = new OleDbConnection(conn)) {  
   9.         try {  
   10.             OleDbDataAdapter oleAdpt = new OleDbDataAdapter("select * from [Sheet1$]", con); //here we read data from sheet1  
   11.             oleAdpt.Fill(dtexcel); //fill excel data into dataTable  
   12.         } catch {}  
   13.     }  
   14.     return dtexcel;  
   15. }  
   Let's discuss something about ReadExcel() method.
   
   Firstly, we will decide whether the file has extension .xls or .xlsx because there is a difference between the connection strings of both the files.
   
   If the file has extension .xls, then the connection string will be the following:
   
   provider=Microsoft.Jet.OLEDB.4.0;Data Source='fileName';Extended Properties='Excel 8.0;HRD=Yes;IMEX=1';
   
   Otherwise
   
   Provider=Microsoft.ACE.OLEDB.12.0;Data Source='FileName';Extended Properties='Excel 12.0;HDR=NO';"
   
   Here HDR is the header field ,depend upon you,whether you want to add or not,
   
   IMEX=1 is used to retrieve the mixed data from the columns.
   
   Now by using the OleDbConnection define an OleDbDataAdapter.
   
   1. OleDbDataAdapter oleAdpt = new OleDbDataAdapter("select * from [Sheet1$]", con);  
   Here Sheet1 is the sheet number that you want to select, you can select any sheet e.g. Sheet2, Sheet3, etc. If you want to choose some specific columns,   then you can. For example, you want to read just 2 columns say Name and Salary from the excel file then your query be like the following:
   
   Select Name,Salary from [Sheet1$]
   
   If there are no headers in complex excel files then you can select columns like F1, F20 etc. In that case the query be like the following:
   
   Select F11,F41,F70 from [Sheet1$]
8. Add the following logic in button click events.
   
   1. private void btnChooseFile_Click(object sender, EventArgs e) {  
   2.     string filePath = string.Empty;  
   3.     string fileExt = string.Empty;  
   4.     OpenFileDialog file = new OpenFileDialog(); //open dialog to choose file  
   5.     if (file.ShowDialog() == System.Windows.Forms.DialogResult.OK) //if there is a file choosen by the user  
   6.     {  
   7.         filePath = file.FileName; //get the path of the file  
   8.         fileExt = Path.GetExtension(filePath); //get the file extension  
   9.         if (fileExt.CompareTo(".xls") == 0 || fileExt.CompareTo(".xlsx") == 0) {  
   10.             try {  
   11.                 DataTable dtExcel = new DataTable();  
   12.                 dtExcel = ReadExcel(filePath, fileExt); //read excel file  
   13.                 dataGridView1.Visible = true;  
   14.                 dataGridView1.DataSource = dtExcel;  
   15.             } catch (Exception ex) {  
   16.                 MessageBox.Show(ex.Message.ToString());  
   17.             }  
   18.         } else {  
   19.             MessageBox.Show("Please choose .xls or .xlsx file only.", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Error); //custom messageBox to show error  
   20.         }  
   21.     }  
   22. }  
   23.   
   24. private void btnClose_Click(object sender, EventArgs e) {  
   25.     this.Close(); //to close the window(Form1)  
   26. }  

After choosing the file Result will be like the following whether you upload .xls or .xlsx file.