How To Write Data In Selected Cell Of An Excel From Datatable C#

A to Z Full Forms and Acronyms

How to export DataTable to Excel in C#

In this article, we'll learn how to export a dataTable returned in a windows form application to excel using the C# lawmaking. In this procedure, nosotros'll import various libraries which will help us out in the process.

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, nosotros'll use Ado.net. Finally, a push button would be placed on the form in order to consign the dataTable to the excel canvass. For exporting dataTable to Excel we use a dll i.eastward. Microsoft.Office.Interop.Excel. Now let's understand the unabridged procedure in an elaborative style performing each step.

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

Step two : 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 : Equally soon as, the Reference Manager opens up, we search for Microsoft.Function.Interop.excel. But it displays no result. This means the assembly is not existing in the Visual Studio

Footstep iv : The other pick to go the assembly is by using the Nuget Packages to install the required assembly. Right-click the 'References' and click manage NuGet packages.

Step five : Click on Browse to search for Microsoft.Function.Interop.Excel dll. Information technology can exist installed to avail of the interoperability in the application.

STEP six : Add a new class to the projection. This class contains the code for connectivity with the database. Likewise, nosotros add together 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 adult a method 'GetRecords()' in the SQL helper class which takes the SQL query equally a parameter. And this method returns the dataTable afterward executing the SQL query from the database.

Stride 7 : The code for the grade is shown below. As presently equally the form loads, the datagridview is to be populated with the information. And so we phone call the'GetRecords()' method in the load issue of the form. The method takes the SQL query as the parameter.

Step 8 : Now nosotros write the lawmaking for the button clicking on which the data tabular array would be exported to the excel sheet. We make the instance of the Microsoft.Office.Interop.Excel.Application to access the objects. The Add together() method takes the type of template of the workbook every bit a parameter. We run loops for setting the heading in the excel sheet which comes from the column name of the data table. The 2nd loop adds values to the respective jail cell in the excel sheet. These values come from the rows of the datagridview.