C Sharp .Net 7 EPPlus Console App - Excel Worksheet Pivot Tables
C Sharp .Net 7 EPPlus Console App - Excel Worksheet Pivot Tables - Excel File with Multiple Worksheets
This article describes how to create a .Net 7 Console Application to create an Excel file with a PivotTable using EPPlus. The console application uses the EPPlus package generate the Excel report with a Pivot Table using a SQL Server stored procedure. Main () in the Program file of the Console app uses Autofac.
Create a SQL Server Stored Procedure in the AdventureWorks database
Create a stored procedure in SQL Server from the Adventure Works database using the tables Purchasing.PurchaseOrderHeader, Purchasing.Vendor,Person.BusinessEntityAddress, Person.Address,Person.StateProvince. This provides a list of Vendors and Purchase Order information.
The output in SSMS from the Stored Procedure. The data set returned is a list of Vendor Names, States, Purchase Order totals.
Create a C# .Net 7 Console Application
Create a new C# Console Application in Visual Studio 2022. Add the NuGet packages to use Dependency Injection and EPPlus.
Create the Containers, Building, Dependency Injection Classes
Repository & Service Classes - for details, see the information here: https://oakdome.com/programming/CSharp_DotNetCore_ConsoleApp.php
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 Sheet function - Add a function (GetExcelSheets). The first part of this code will pull the dataset into a formatted EPPlus worksheet object.
The second part of the GetExcelSheets code will generate a sheet with the Summarized Pivot Table. The Code for xrow and xcol will capture the count of the rows and columns from Sheet1. Additional Code will add the PivotTable, Row Field (State) and Column Field (Order Status). The PivotTable fields use the Count of the Purchase Order ID and the Sum of the TotalDue for the Values.
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.
Creating the Main Code
In Main in Program.cs, the ConfigurationBuilder code assigns 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 Visual Studio and Run the application to view the results. The Worksheet Purchase Orders has the columns Purchase Order ID, Total Due, Order Date, Order Status, Vendor Name, and State.
The Worksheet Summary contains the Pivot Table with each State listed in Column A + Purchase Order Count and Purchase Order Amount split by Order Status.
By Clicking in the Summary Worksheet, the PivotTable Fields will appear, if needed.
