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 files, 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 the code 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#.

Step 1. Open Visual Studio. Select New Project, then Windows Form Application. I am using the Visual Studio 2022 community version.

Step 2. Name it as you want. My application name is ReadExcelFileApp.

Step 3. First of all, add a Reference to the Excel library - IronXL. Right-click on the Solution in the solution explorer and click the “Manage NuGet packages” option.

Step 4. Browse the tab and type the “IronXL.Excel” keyword. The following screen will appear. Click the install button. Visual Studio will prompt you that VS is installing the following components and it related libraries (dependencies) in this project. Click on OK. NuGet package will be installed.

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

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

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

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

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

Step 10. Now create a method ReadExcel, which returns a data table 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 the ReadExcel() method.

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

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

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

Step 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 a .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 the code, and modify it according to your requirements. I tried to make this tutorial as development-friendly friendly as simple as I could. Thank you, and enjoy coding!!!