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 MultiSheet Report

C# .Net Core 2.2. EPPlus Console App - Excel File with Multiple Worksheets

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 package in the Repository to generate the Byte Stream and output SQL Server data into a MultiSheet Excel File.  Main () in the Program file of the Console app uses Autofac to inject the Repository Class into the Service Class in the project.

 

Create a SQL Server Stored Procedure

Create a stored procedure in SQL Server from the Adventure Works database using the View vVendorWithAddresses. This one provides a list of Vendors and Addresses for output into a list in a Mailing Address Label format in a Excel file.

SQL Stored Proc for Multiple Excel Sheets

The output in SSMS from the Stored Procedure.  The first data set returned is the list of worksheet names to create.  The subsequent data sets are the Vendors located in each state.

SQL DataSet Output SSMS 2017

Create a C# .Net Core version 2.2 Console Application

Create a new C# Console Application in Visual Studio 2017. Add the NuGet packages to use Dependency Injection and EPPlus.

Create .Net Core Console App

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

Create the DI Interfaces

Service Interface - Create an Interface for the service to Write the data and return a string containing the report name for Excel.

Service Interface

Repository Interface - Create an Interface for the repository to WriteData the data and return a string for the Excel report name mentioned above.

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 a SQL connection, a Stored Procedure, and a local Path to the new EPPlus Excel file report. Implement the Repository Interface member WriteBytes which will generate the byte array for the report and pass a string with the Report Name of the new Excel file. Add 2 additional functions to Get the Data from the DataSet and to Get the EPPlus data generated into an Excel 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 to Retrieve Data, Repository Class

Add the EPPlus function - Add a function to retrieve the byte array containing the formatted EPPlus data for output to Excel. The code receives the SQL connection string and stored procedure name and passes it to the getDataSet function above.

The first DataTable retrieved from the DataSet is the one which has the State Names (to be used as Worksheet names).

Additional function for EPPlus to iterate through worksheets in Repository Class

The first DataTable from the DataSet is then used in a For Loop to iterate through each row of data to create a new worksheet with a name.  The remaining tables are iterated through to Load the data into separate worksheets.  A bit of formatting the Headers is added.  Also added is an If statement to determine if there are any rows in the individual Data Tables for the Vendors in each State.  If there is no data, the Worksheet is populated with the Header Column names only.

Building the EPPlus Header Row

The last item in each worksheet is to create Printer Settings including a Header, Margins, etc.

Printer Settings in Excel from EPPlus

The final function to call is WriteBytes which calls the above GetExcelSheets() function and writes out the data from EPPlus into an Excel file. It returns the complete fileName with path to Main.

WriteBytes in the Repository Class

Service Class - Create a new Class that implements the Workbook Service Interface. Add a field of the local variable of the Workbook Interface Repository type. Create the required Service Interface member Write which calls the Repository Interface member WriteBytes to return the file name string to Main in Program.cs.

Service Class

Creating the Main Code

In Main in Program.cs, add ConfigurationBuilder code and assign the config information from the Build() into the a variable named configuration.  Add a ContainerBuilder to Register the Instance of the WkbkRepository and create a new instance of it and add the Configuration data to the Public variables.

Use the builder to Register the Service instance with the WkbkRepository and Build it to a new _container. Resolve the service and call the Write function to retrieve the report name.

The call to Process.Start will open Excel on the local PC to open the file for viewing.

Main Program Code

Build the app in VS 2017 and Run the application to view the results. The Worksheets have the Header Columns with a complete address in each row.

Excel Worksheets Created

Print preview mode in Excel on the Oregon worksheet shows the print formatting from the GetExcelSheets function.

Excel Worksheets Print Formatting