How to export DataTable to Excel in C#

Export DataTable to Excel in C#

We will develop windows form with a datagridview which would be getting data from a table existing in the database. For enabling connectivity with the database, we’ll use Ado.net. Finally, a button would be placed on the form in order to export the dataTable to the excel sheet. For exporting dataTable to Excel we use a dll i.e. Microsoft.Office.Interop.Excel. Now let's understand the entire process in an elaborative manner performing each step.

STEP 1: Open a windows form application in Visual Studio. Drag a datagridview and a button from the toolbox and drop them onto the form.

STEP 2: Right-click on the ‘References’ in the Solution Explorer to add the dll Microsoft.Office.Interop.Excel into the existing form. This assembly enables interoperability between Excel and C#. It enables creating/reading Excel files from a C# application.

STEP 3: As soon as, the Reference Manager opens up, we search for Microsoft.Office.Interop.excel. But it displays no result. This means the assembly is not existing in the Visual Studio

STEP 4: The other option to get the assembly is by using the Nuget Packages to install the required assembly. Right-click the ‘References’ and click manage NuGet packages.

STEP 5: Click on Browse to search for Microsoft.Office.Interop.Excel dll. It can be installed to avail of the interoperability in the application.

STEP 6: Add a new class to the project. This class contains the code for connectivity with the database. Also, we add the connection string in the App.config file so that the datagridview is populated with the data from the table ‘Empl’ existing in the database. Therefore, it is essential to maintain a connection with the database.

We developed a method ‘GetRecords()’ in the SQL helper class which takes the SQL query as a parameter. And this method returns the dataTable after executing the SQL query from the database.

STEP 7: The code for the form is shown below. As soon as the form loads, the datagridview is to be populated with the data. So we call the’GetRecords()’ method in the load event of the form. The method takes the SQL query as the parameter.

STEP 8: Now we write the code for the button clicking on which the data table would be exported to the excel sheet. We make the instance of the Microsoft.Office.Interop.Excel.Application to access the objects. The Add() method takes the type of template of the workbook as a parameter. We run loops for setting the heading in the excel sheet which comes from the column name of the data table. The second loop adds values to the corresponding cell in the excel sheet. These values come from the rows of the datagridview.

OUTPUT