Step-by-Step Guide: Easy Reporting 📋 with ClosedXML (Excel) and Database Views

'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: Generate Report to MyDocuments

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:

1. 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.
2. 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.
3. 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.
4. 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.
5. 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#!

Comments

[David Au Yeung]

If you have any questions, please let me know 😄