'ClosedXML' allows you to create Excel files without installing the Excel. Here's a step-by-step tutorial for using 'ClosedXML' in conjunction with a Query Helper to create a monthly sales report to, let's say, marketing department. This guide will walk through the setup, data preparation, view creation, and generating Excel report.
Step 1: Data Preparation
First, create the necessary tables in your SQL database. Use the following SQL scripts to set up the Customers, Products, Orders, and OrderItems tables.
CREATE TABLE Customers (
CustomerID BIGINT IDENTITY(1,1) NOT NULL,
LastName NVARCHAR(100) NOT NULL,
FirstName NVARCHAR(100) NOT NULL,
DOB DATE NOT NULL,
IsDeleted BIT NOT NULL DEFAULT 0,
CreateBy NVARCHAR(100) NOT NULL DEFAULT 'SYSTEM',
CreateDate DATETIME NOT NULL DEFAULT GETDATE(),
ModifyBy NVARCHAR(100) NOT NULL DEFAULT 'SYSTEM',
ModifyDate DATETIME NOT NULL DEFAULT GETDATE(),
CHECK (YEAR(DOB) >= 1900),
PRIMARY KEY (CustomerID)
);
CREATE TABLE Products (
ProductID BIGINT IDENTITY(1,1) NOT NULL,
ProductName NVARCHAR(1000) NOT NULL,
ProductCode NVARCHAR(1000) NOT NULL,
AvailableQuantity INT NOT NULL,
IsDeleted BIT NOT NULL DEFAULT 0,
CreateBy NVARCHAR(100) NOT NULL DEFAULT 'SYSTEM',
CreateDate DATETIME NOT NULL DEFAULT GETDATE(),
ModifyBy NVARCHAR(100) NOT NULL DEFAULT 'SYSTEM',
ModifyDate DATETIME NOT NULL DEFAULT GETDATE(),
CHECK (AvailableQuantity >= 0),
PRIMARY KEY (ProductID)
);
CREATE TABLE Orders (
OrderID BIGINT IDENTITY(1,1) NOT NULL,
CustomerID BIGINT,
OrderNumber NVARCHAR(1000) NOT NULL,
OrderDate DATETIME NOT NULL,
IsDeleted BIT NOT NULL DEFAULT 0,
CreateBy NVARCHAR(100) NOT NULL DEFAULT 'SYSTEM',
CreateDate DATETIME NOT NULL DEFAULT GETDATE(),
ModifyBy NVARCHAR(100) NOT NULL DEFAULT 'SYSTEM',
ModifyDate DATETIME NOT NULL DEFAULT GETDATE(),
PRIMARY KEY (OrderID),
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);
CREATE TABLE OrderItems (
OrderItemID BIGINT IDENTITY(1,1) NOT NULL,
OrderID BIGINT,
ProductID BIGINT,
Quantity INT NOT NULL,
IsDeleted BIT NOT NULL DEFAULT 0,
CreateBy NVARCHAR(100) NOT NULL DEFAULT 'SYSTEM',
CreateDate DATETIME NOT NULL DEFAULT GETDATE(),
ModifyBy NVARCHAR(100) NOT NULL DEFAULT 'SYSTEM',
ModifyDate DATETIME NOT NULL DEFAULT GETDATE(),
PRIMARY KEY (OrderItemID),
FOREIGN KEY (OrderID) REFERENCES Orders(OrderID),
FOREIGN KEY (ProductID) REFERENCES Products(ProductID),
);
--Create customers
INSERT INTO Customers (LastName, FirstName, DOB) VALUES
('Au Yeung', 'David', '19801231')
, ('Chan', 'Peter', '19820115')
--Create products
INSERT INTO Products (ProductName, ProductCode, AvailableQuantity) VALUES
('Android Phone', 'A0001', 100)
, ('iPhone', 'I0001', 100)
, ('iPad', 'I0002', 100)
, ('iPad Mini', 'I0003', 100)
--David bought 10 iPhone
INSERT INTO Orders (CustomerID, OrderNumber, OrderDate) VALUES
((SELECT TOP 1 CustomerID FROM Customers WHERE FirstName = 'David' AND IsDeleted = 0)
, 'ORD0001'
, GETDATE())
INSERT INTO OrderItems (OrderID, ProductID, Quantity) VALUES
((SELECT TOP 1 OrderID FROM Orders WHERE OrderNumber = 'ORD0001' AND IsDeleted = 0)
, (SELECT TOP 1 ProductID FROM Products WHERE ProductCode = 'I0001' AND IsDeleted = 0)
, 10)
--Peter bought 1 Android Phone
INSERT INTO Orders (CustomerID, OrderNumber, OrderDate) VALUES
((SELECT TOP 1 CustomerID FROM Customers WHERE FirstName = 'Peter' AND IsDeleted = 0)
, 'ORD0002'
, GETDATE())
INSERT INTO OrderItems (OrderID, ProductID, Quantity) VALUES
((SELECT TOP 1 OrderID FROM Orders WHERE OrderNumber = 'ORD0002' AND IsDeleted = 0)
, (SELECT TOP 1 ProductID FROM Products WHERE ProductCode = 'A0001' AND IsDeleted = 0)
, 1)
--David bought 1 more iPhone next month
INSERT INTO Orders (CustomerID, OrderNumber, OrderDate) VALUES
((SELECT TOP 1 CustomerID FROM Customers WHERE FirstName = 'David' AND IsDeleted = 0)
, 'ORD0003'
, DATEADD(MONTH, 1,GETDATE()))
INSERT INTO OrderItems (OrderID, ProductID, Quantity) VALUES
((SELECT TOP 1 OrderID FROM Orders WHERE OrderNumber = 'ORD0003' AND IsDeleted = 0)
, (SELECT TOP 1 ProductID FROM Products WHERE ProductCode = 'I0001' AND IsDeleted = 0)
, 1)
SELECT * FROM Customers
SELECT * FROM Products
SELECT * FROM Orders
SELECT * FROM OrderItems
Step 2: Create a View for Reporting
Create a SQL view to summarize the top product for each month based on sales. This will help encapsulate your data for reporting.
CREATE VIEW [v_Product_Top_1] AS
WITH cte AS (
SELECT
YEAR(o.OrderDate) AS SalesYear,
MONTH(o.OrderDate) AS SalesMonth,
p.ProductName,
SUM(oi.Quantity) AS TotalSales,
RANK() OVER(PARTITION BY YEAR(o.OrderDate), MoNTH(o.OrderDate) ORDER BY SUM(oi.Quantity) DESC) AS rn
FROM OrderItems oi
INNER JOIN Orders o ON o.OrderID = oi.OrderID AND o.IsDeleted = 0
INNER JOIN Products p ON p.ProductID = oi.ProductID AND p.IsDeleted = 0
WHERE oi.IsDeleted = 0
GROUP BY p.ProductName, YEAR(o.OrderDate), MONTH(o.OrderDate)
HAVING SUM(oi.Quantity) > 0
)
SELECT SalesYear, SalesMonth, ProductName, TotalSales
FROM cte
WHERE rn = 1;
Step 3: Implement the ReportHelper Class
Use the ClosedXML library to create an Excel report. Below is the code for the ReportHelper class that will generate the Excel file. (This helper not only supports the datatable but also the dataset.)
using System.Data;
using ClosedXML.Excel;
using System.Text.RegularExpressions;
namespace MyProgram.Util
{
public class ReportHelper
{
private XLWorkbook workbook;
public ReportHelper()
{
this.workbook = new XLWorkbook();
}
public void GenerateExcel(DataTable dataTable, string path, string fileName)
{
string tableName = SanitizeSheetName(dataTable.TableName);
var worksheet = this.workbook.Worksheets.Add(tableName);
worksheet.Cell(1, 1).InsertTable(dataTable);
worksheet.Columns("A", "Z").AdjustToContents();
workbook.SaveAs($"{path}\\{fileName}.xlsx");
}
public void GenerateExcel(DataSet dataSet, string path, string fileName)
{
foreach (DataTable dt in dataSet.Tables)
{
string tableName = SanitizeSheetName(dt.TableName);
var worksheet = this.workbook.Worksheets.Add(tableName);
worksheet.Cell(1, 1).InsertTable(dt);
worksheet.Columns("A", "Z").AdjustToContents();
}
workbook.SaveAs($"{path}\\{fileName}.xlsx");
}
private string SanitizeSheetName(string sheetName)
{
if (string.IsNullOrEmpty(sheetName))
return "Sheet1";
string pattern = @"[\\/:*?""<>|\[\]]";
string sanitized = Regex.Replace(sheetName, pattern, "_").Trim();
return sanitized.Length > 31 ? sanitized.Substring(0, 31) : sanitized;
}
}
}
Step 4: Demo to Generate Report
Now, create a method to execute the SQL query to get data from [v_Product_Top_1] and generate the report using the ReportHelper class.
using System;
using System.Data;
namespace MyProgram
{
public static class ReportDemo
{
public static void Demo()
{
SQLStrategy sqlHelper = new(new TSqlQuery());
ReportHelper reportHelper = new ReportHelper();
string fileLocation = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
string fileName = $"Demo_{DateTime.Now:yyyyMMdd}";
DataTable result = sqlHelper.GetDataTable("SELECT * FROM [v_Product_Top_1] ORDER BY SalesYear, SalesMonth, TotalSales DESC");
result.TableName = $"ResultOf{DateTime.Now:yyyyMMdd}";
reportHelper.GenerateExcel(result, fileLocation, fileName);
}
}
}
Result
Conclusion
Using views for reporting in SQL databases offers several significant benefits:
- Encapsulation of Logic: Views encapsulate complex queries, making them reusable and easier to manage. By defining a view, you can abstract away the underlying table structures and join logic, allowing users to access data without needing to understand the complexity of the SQL behind it.
- Data Security: Views can provide a layer of security by restricting access to sensitive data. You can expose only the necessary columns and rows to users, effectively hiding the underlying tables and their sensitive information.
- Simplified Querying: Users can query views just like tables, which simplifies the process of retrieving data. This is particularly helpful for users who may not be proficient in SQL, as they can work with straightforward view names instead of complex queries.
- Consistency: Using views helps ensure that users always access consistent data. If the underlying table structures change, you can update the view definition without impacting the applications or users relying on that view.
- Easier Maintenance: With views, you centralize the logic for data retrieval. If business rules change, you can modify the view without needing to change multiple applications or scripts that rely on it.
You now have a simple yet effective reporting tool using 'ClosedXML' and a database helper with the Strategy design pattern! This setup allows you to easily generate Excel reports from your database queries.
Make sure you have installed the necessary NuGet packages for 'ClosedXML' and any database providers you are using (e.g., MySql.Data, Npgsql, System.Data.SqlClient). Happy coding!
Love C#!
Top comments (1)
If you have any questions, please let me know π