Windows Forms: Read Excel file in C# using OleDb
By FoxLearn 12/26/2019 10:30:51 PM 9.34K
This post shows you How to Read Excel file in C# using oledb (without Interop) and How to get excel sheet names in c# using oledb provider for excel.
If you want to read both XLS and XLSX format, then this article will really help you read an excel file using OLEDB in C# .NET Windows Forms Application.
How to read excel file in c# windows application
Creating a new Windows Forms Application project, then open your form designer.
Dragging Label, TextBox, Button and Combobox controls from the Visual Studio Toolbox to your form designer.
You can design a simple user interface as shown below that allows you to open an excel file, then read all data in excel sheet based on the sheet name you selected.
C# Get all sheets in excel
Creating an AppHelper class allows you to get all sheet name in excel file.
public class AppHelper { private OleDbConnection cn = null; public AppHelper(string connectionString) { cn = new OleDbConnection(connectionString); } private void OpenConnection() { if (cn.State == ConnectionState.Closed) cn.Open(); } private void CloseConnection() { if (cn.State == ConnectionState.Open) cn.Close(); } public DataTable GetOleDbSchemaTable() { DataTable dt = new DataTable(); try { OpenConnection(); dt = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); } catch { return null; } finally { CloseConnection(); } return dt; } }
C# Get all sheet names in excel
Adding a click event handler that allows you to open an excel file, then get all sheet names and add the sheet names to the Combobox control.
private void btnBrowse_Click(object sender, EventArgs e) { using (OpenFileDialog ofd = new OpenFileDialog { Filter = "Excel 97-2003|*.xls|Excel Workbook|*.xlsx", Multiselect = false, ValidateNames = true }) { if (ofd.ShowDialog() == DialogResult.OK) { cboSheet.Items.Clear(); txtPath.Text = ofd.FileName; AppHelper h = new AppHelper(String.Format("Provider=Microsoft.Jet.OLEDB.4.0; Data Source={0};Extended Properties={1}Excel 8.0;Imex=2;HDR=yes{1}", ofd.FileName, Convert.ToChar(34))); DataTable sdt = h.GetOleDbSchemaTable(); if (sdt.Rows.Count < 1) return; try { foreach (DataRow dr in sdt.Rows) { if (!dr["TABLE_NAME"].ToString().EndsWith("_")) cboSheet.Items.Add(dr["TABLE_NAME"].ToString()); } } catch (Exception ex) { MessageBox.Show(ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Error); } } } }
As you can see, You can easily get excel sheet names in c# using oledb.
Oledb connection string for excel 2013 in c#
$"Provider=Microsoft.Jet.OLEDB.4.0; Data Source={0};Extended Properties={ofd.FileName}Excel 8.0;Imex=2;HDR=yes{Convert.ToChar(34)}"
To play the demo, Make sure that the Microsoft Access Database Engine needs to be installed.
C# Read excel sheet data
Adding a SelectionChangeCommited event handler to the Combobox allows you to get data from excel sheet when selecting sheet name.
private void cboSheet_SelectionChangeCommitted(object sender, EventArgs e) { using (OleDbConnection cn = new OleDbConnection(string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0; Data Source={0};Extended Properties={1}Excel 8.0;Imex=2;HDR=yes{1}", txtPath.Text, Convert.ToChar(34)))) { using (OleDbDataAdapter adapter = new OleDbDataAdapter("select * from [" + cboSheet.SelectedItem.ToString() + "]", cn)) { try { using (DataTable dt = new DataTable()) { adapter.Fill(dt); dataGridView.DataSource = dt; } } catch (Exception ex) { MessageBox.Show(ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Error); } } } }
Finally, Add a datasource to your DataGridView to display data read from excel file using OLEDB Data Provider in C#.
- How to protect an excel file
- Windows Forms: How to Open and Read Excel Files in VB.NET
- Windows Forms: How To Open and Read an Excel file into a ListView in C#
- Windows Forms: How to Export DataTable to Excel without Interop in C#
- Windows Forms: Read Excel file in C#
- Windows Forms: How to Read Excel file (*.xls, *.xlsx) in C#
- Windows Forms: How to Open and Read Excel Files in C#