Excel Exporter In C#

In this article, you are going to see in detail the working process of the Excel Exporter tool which is created in C#. 

GitHub Source code: - https://github.com/saineshwar/ExcelExporter

It is an Excel Exporter Tool which is developed in C# using EPPlus library.

This Tool connects to 2 databases.

1. Microsoft SQL Database
2. MySQL Database

You can export a Table, Stored Procedure or Query (must not be a dynamic query) directly to Excel.

About the Platform Used

I developed the entire application using Microsoft Visual Studio 2015.

External packages which are used in this Project 

I have also added some packages from NuGet, here is the list:

1. EPPlus (Create advanced Excel spreadsheets using .NET)
2. Data (ADO.Net driver for MySQL)

Complete Project structure

Connection Form

This is a connection form which contains various tabs on it. Each tab has a unique importance.

Let’s start with a first tab, it's Microsoft SQL.

Microsoft SQL

In this form, you can see various textboxes which are asking for settings to connect to the SQL Server.

In Microsoft SQL, we have 2 authentications.

1. SQL Server Authentication
2. Windows Authentication

We can use both options to connect to the database. If we move a bit below in the connection form you will Export. Type the panel in that and you will see various options such as Tables, Stored Procedures, Query.

You can choose any options to Export your data.

Next, we are going to choose MySQL tab to see what details are required to connect to MySQL database.

MYSQL

MySQL tab has some different settings as compared to MSSQL Tab, here it will ask Host address which is different.

The Export Type panel is similar in both tabs. In Export Type panel, you will see various options, such as Tables, Stored Procedures, Query.

You can choose any of the options to Export your data.

Next, we are going to choose Export Path Settings tab to see how to set a path where all files will be exported will be stored.

Export Path Settings

In this section, we are going to choose a folder where all the files to be exported will be stored.

After getting an understanding of the  Export Path Settings Tabs, next, we are going to see a demo of SQL Server Data on how to export the data.

Export Table (SQL SERVER)

In this section, first, we are going to export the SQL table data to Excel.

After entering valid credentials, it will display different screens. In that, you will see all the SQL Databases.

Next, after selecting “TestDB”, click on Show Tables. It will display all tables from TestDB database.

In that, I am going to choose “Work Order” table which has “72591 Records” which we are going to export to Excel.

To export, just click on “Generate Excel” button.

After it has exported files, let us see the Output Location which we have to choose.

Export Table Output

Open files to see the data.

This was a Direct SQL Table Export; next, let’s see how to export the stored procedure.                             

Export Stored Procedure (SQL SERVER)

In this section, we are going to export SQL Stored Procedure Data to Excel.

For exporting a Stored Procedure, I am going to create a simple Stored Procedure which we are going to use for exporting the data.

Stored procedure

After creating Stored Procedure, next, we are going to run the application again and enter Connection settings and this time, in Export type options, we are going to choose “Store Procedure”.

After entering the valid credentials, it will display different screens and in that, you will see all SQL Databases.

Next, after selecting “TestDB”, click on Show. The “Stored Procedure” will display all the Stored Procedures from TestDB database. Here, we have only one Stored Procedure in this database; that's why it displays only one.

Note
It will not show the parameterized stored procedure in the List because we cannot pass any parameter to it.

Just choose “Usp_GetAllWorkOrderDetails” Stored Procedure and to export, just click on “Generate Excel” button.

After it has exported the files, let us see the Output Location which we have to choose.

Export Stored Procedure Output

Export Query (SQL SERVER)

In this section, we are going to export SQL Query data to Excel.

After entering the valid credentials, it will display different screens. There, you will see all SQL Databases along with it and you will see an "Enter Query" window in which we can enter the query to Export data.

I have written a simple query which will only get 500 records.

Export Query Output

Finally, we have completed getting a complete view of tools and how to use them. The entire source code is available to download and it is also on GitHub. You can also contribute to this application to make it better.