How to read an excel (.xls/.xlsx) file in C#
By FoxLearn 10/5/2024 3:55:43 AM 6.5K
How to read an excel (.xls/.xlsx) file in C#?
Click New Project, then select Visual C# on the left, then Windows and then select Windows Forms Application. Name your project "ReadExcelFile" and then click OK
You need to add the ExcelDataReader
NuGet package to your project. You can do this via the NuGet Package Manager Console.
Install-Package ExcelDataReader Install-Package ExcelDataReader.DataSet
You can also add the ExcelDataReader
through the NuGet Package Manager GUI by right-clicking on your project select Manage NuGet Packages -> Search ExcelDataReader -> Install
Drag and drop the Label, Button, Combobox and DataGridView controls from your Visual Studio toolbox onto your form designer, then you can layout your form as shown below.
Create an excel file, then copy data from the Northwind database to the excel file.
Double-click the button to create an event handler for the Click
event.
DataSet result; private void btnOpen_Click(object sender, EventArgs e) { using (OpenFileDialog ofd = new OpenFileDialog() { Filter = "Excel Workbook 97-2003|*.xls|Excel Workbook|*.xlsx", ValidateNames = true }) { if (ofd.ShowDialog() == DialogResult.OK) { FileStream fs = File.Open(ofd.FileName, FileMode.Open, FileAccess.Read); IExcelDataReader reader; if (ofd.FilterIndex == 1) reader = ExcelReaderFactory.CreateBinaryReader(fs);//Read excel 97-2003 else reader = ExcelReaderFactory.CreateOpenXmlReader(fs);//Read excel 2007 reader.IsFirstRowAsColumnNames = true; result = reader.AsDataSet(); cboSheet.Items.Clear(); //Add sheet to comboxbox foreach (DataTable dt in result.Tables) cboSheet.Items.Add(dt.TableName); reader.Close(); } } } private void cboSheet_SelectedIndexChanged(object sender, EventArgs e) { //Fill data from excel into DataGridView based on sheet selection dataGridView.DataSource = result.Tables[cboSheet.SelectedIndex]; }
If your Excel file has multiple sheets and you want to read a specific one, you can iterate through result.Tables
to access different sheets.
Run the application, then click the button to open an Excel file and select an .xls
or .xlsx
file.
You need to select your excel sheet from the combobox to see the content displayed in the DataGridView
.
The ExcelDataReader
library is suitable for reading Excel files, but it doesn't support writing Excel files. If you need to create or modify Excel files, consider using libraries like EPPlus
or ClosedXML
.
This setup provides a straightforward way to read and display Excel data in a Windows Forms application.
VIDEO TUTORIAL