Programming Samples

Click here to go to:



Excel VBA

Word VBA

MS Access

T-SQL

SSIS

SSRS

Power BI

Crystal Reports

SSAS

SQL Replication

C# Code

ASP .NET Code

Oracle PL/SQL

Database Diagramming


Back to Home Page


.Net Core 2.2 Console Application - EPPlus

C# .Net Core 2.2. EPPlus Console Application

This article describes how to create a .Net Core 2.2 Console Application to create an Excel file using EPPlus. The console application uses the EPPlus.Core package in the Repository to generate the Byte Stream and output SQL Server data in Excel. The Main section of the Console app will use Autofac to inject the Repository Class into the Service Class in the project and will be registered in a Container.

 

Create a SQL Server Stored Procedure

Create a stored procedure in SQL Server from the Adventure Works database using the View vVendorWithAddresses. This one uses a variable to limit the number of Vendors returned for this article.

SQL Server Stored Procedure for Data

Create a C# .Net Core version 2.2 Console Application

Create a new C# Console Application in Visual Studio 2017 that will handle any errors thrown back by SQL Server (or in the code itself).

Create .Net Core Console App

This application will require Interfaces and Classes for a Service and a Repository and a Class for the ConfigContainer. In the upper part of the code, add references to use the System.Data.SqlClient and the Configuration assemblies below.

Create the DI Interfaces

Service Interface - Create an Interface for the service to Write the data and return the File Name in a string.

Service Interface

Repository Interface - Create an Interface for the repository to WriteData to return a string containing the File Name via the Service.

Repository Interface

Create the DI Classes

Repository Class - Create a new Class that implements the Repository Interface with a generic Constructor and 3 variables for the SQL connection and the Stored Procedure name + the local Path to the EPPlus Excel file report being created. Implement the Repository Interface member WriteData which will return a a string which will be the full name of the new Excel file. Add 2 additional functions to 1) Get the Data for the DataSet and 2) Get the Excel sheet bytes for the report file.

First, add the repository public variables and constructor.

Repository Class

Add the DataSet Function - Add a Function GetData which takes the Sql Connection String and Stored Proc name to execute the SqlCommand and Fill the DataAdapter with the DataSet for the Repository.

Additional function for Data Retrieval in Repository Class

Add the Excel function - Add a function to retrieve the byte array containing the formatted EPPlus data for output to Excel. The code Adds a Worksheet, Loads the DataSet DataTable into the Workbook Sheet, and Autofits the Columns. In addition to Loading the Data, the PrinterSettings code formats the worksheet for printing with Gridlines, margins, and a Print Header.

Additional function for EPPlus Excel processing in Repository Class

Service Class - Create a new Class that implements the Service Interface. Add a field of the local variable of the Interface Repository type. Create the required Service Interface member Write which calls the Repository Interface member WriteBytes to process the byte array into a Report and pass the Report Name as a string to Main in Program.cs.

Service Class

Creating the Main Code

ConfigurationBuilder Class - Add a JSON Configuration file to the project (appsettings.json). Add the ConnectionString and user Stored Proc name to the file.  Also add a rpt_path variable to the configuration file for the data output.

JSON Configuration File

In Main add the ConfigurationBuilder code and assign the config information from the Build() into the RptRepository Public variables within a Class Constructor to Register the new Instance.

ConfigurationBuilder Code 

ContainerBuilder Dependency Injection - Add code to Main to Register the RptService class, Build the _container to process the byte array and extract the report name to open the Excel file by Main.

ContainerConfig Code

Use a System.Diagnostics.Process call to display Excel Output.

Console Application Data Output

Build and Run the application to view the results.

Console Application EPPlus Output

Click on Print Preview in Excel to view the Formatted Printer output from EPPlus.

Console Application EPPlus Formatted Print Output