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.

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.

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.

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.

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

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.

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 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).

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.

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

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.

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.

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.

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.

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