Read Excel File In Windows Application Using C#

Introduction

Generally, in development projects there is most common requirement is reading/editing/generating an excel file. There are lots of paid/free software components available nowadays to fulfill this requirement.

In recants projects, I also worked with the same kind of requirement. I have used the Microsoft Interop excel component to read excel file but the main problem appears when it is hosted in a shared hosting platform.

When the developer hosts that project in shared hosting (in the case of low-budget projects where a dedicated server is not necessary), then the developer will have limited access to server settings and he/she can’t install excel components (that are required for Microsoft Interop excel) on the server, here you need a component that will solve your problem.

After some research, I found a software component IronXL which solved my problem. We don’t need to install MS office or any Excel component on the hosting server (even on shared hosting) to make code the work. This is the biggest advantage of IronXL. I decided to share this component via this article with others. But this component is not free it has dedicated pricing.

Code playground

Let’s start with code. We will make a new Windows application using C#.

  1. Open Visual Studio. Select New Project, then Windows Form Application. I am using Visual Studio 2022 community version.
  2. Name it as you want. My application name is ReadExcelFileApp.
  3. First of all, add Reference of the Excel library - IronXL. Right click on the Solution in the solution explorer and click “Manage NuGet packages” option.
  4. Browse tab type “IronXL.Excel” keyword, following screen will appear. Click the install button. Visual Studio will prompt you that VS is installing following components and it related libraries (dependencies) in this project. Click on OK. NuGet package will be installed.

5. Now add two buttons Choose and Read File and Close on form by dragging the button control from Toolbox window.

6. Also add a DataGridView to see the result (excel data).

7. Right click on Windows Form and click “View code” option. This will open the code behind file of form.

8. You can also open the code behind file by double click on buttons. It will create the click event method of button.

9. You can also manage the click event of button in its Properties.

10. Now create a method ReadExcel who returns a datatable using the following logic.

 /// <summary>
 /// this method will read the excel file and copy its data into a datatable
 /// </summary>
 /// <param name="fileName">name of the file</param>
 /// <returns>DataTable</returns>
 private DataTable ReadExcel(string fileName) {
 WorkBook workbook = WorkBook.Load(fileName);
 //// Work with a single WorkSheet.
 ////you can pass static sheet name like Sheet1 to get that sheet
 ////WorkSheet sheet = workbook.GetWorkSheet("Sheet1");
 //You can also use workbook.DefaultWorkSheet to get default in case you want to get first sheet only
 WorkSheet sheet = workbook.DefaultWorkSheet;
 //Convert the worksheet to System.Data.DataTable
 //Boolean parameter sets the first row as column names of your table.
 return sheet.ToDataTable(true);
 }

Let's discuss something about ReadExcel() method.

This method will read the file data in Workbook class object. Here you can perform lot of operation on Workbook class object.

You can get the Workbook data in data set or data table directly by using 1 line of code.

Similarly, you can do lot of operations with this component.

11. Add the following logic in button click events.

 /// <summary>
 /// this method will choose and read the excel file
 /// </summary>
 /// <param name="sender"></param>
 /// <param name="e"></param>
 private void btnChoose_Click(object sender, EventArgs e) {
     OpenFileDialog file = new OpenFileDialog(); //open dialog to choose file
     if (file.ShowDialog() == DialogResult.OK) //if there is a file chosen by the user
     {
         string fileExt = Path.GetExtension(file.FileName); //get the file extension
         if (fileExt.CompareTo(".xls") == 0 || fileExt.CompareTo(".xlsx") == 0) {
             try {
                 DataTable dtExcel = ReadExcel(file.FileName); //read excel file
                 dataGrdView.Visible = true;
                 dataGrdView.DataSource = dtExcel;
             } catch (Exception ex) {
                 MessageBox.Show(ex.Message.ToString());
             }
         } else {
             MessageBox.Show("Please choose .xls or .xlsx file only.", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Error); //custom messageBox to show error
         }
     }
 }
 /// <summary>
 /// this method will close the windows form
 /// </summary>
 /// <param name="sender"></param>
 /// <param name="e"></param>
 private void btnCancel_Click(object sender, EventArgs e) {
     this.Close(); //to close the window(Form1)
 }

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

Conclusion

The IronXL is also helpful in other excel operations like editing/styling/generating excel files. The code of this tutorial is attached. You can download the code, play with code and modify according to your requirement. I tried to make this tutorial development friendly as simple I can make. Thank you and enjoy coding!!!