.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.
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).
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.
Repository Interface - Create an Interface for the repository to WriteData to return a string containing the File Name via the Service.
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.
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.
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.
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.
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.
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.
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.
Use a System.Diagnostics.Process call to display Excel Output.
Build and Run the application to view the results.
Click on Print Preview in Excel to view the Formatted Printer output from EPPlus.
