PDF & Excel Generation — Dynamic Reporting

Enterprise applications run on reports. Generating pixel-perfect invoices (PDF) or data-rich financial ledgers (Excel) dynamically from C# code is a highly requested feature. However, choosing the right library requires balancing performance, development speed, and complex commercial licensing.

1. Generating Excel Spreadsheets (ClosedXML)

While EPPlus is historically famous, it requires a commercial license for business use. ClosedXML is the industry-standard, free, open-source wrapper around Microsoft's OpenXML SDK. It makes generating `.xlsx` files incredibly intuitive.

Installation

dotnet add package ClosedXML

Real-Time Production Example: Exporting Data to Excel

The best practice is to generate the entire Excel file in physical memory (RAM), avoiding slow disk I/O operations.

using ClosedXML.Excel;

public class ReportController : Controller
{
    public IActionResult DownloadUsersExcel()
    {
        var users = _db.Users.ToList(); // Simulated data

        // 1. Create a virtual Workbook
        using (var workbook = new XLWorkbook())
        {
            // 2. Add a Worksheet
            var worksheet = workbook.Worksheets.Add("User Report");

            // 3. Generate Headers with Styling
            worksheet.Cell(1, 1).Value = "User ID";
            worksheet.Cell(1, 2).Value = "Full Name";
            worksheet.Cell(1, 3).Value = "Email";
            var headerRow = worksheet.Row(1);
            headerRow.Style.Font.Bold = true;
            headerRow.Style.Fill.BackgroundColor = XLColor.AirForceBlue;
            headerRow.Style.Font.FontColor = XLColor.White;

            // 4. Populate Data Rows
            int currentRow = 2;
            foreach (var user in users)
            {
                worksheet.Cell(currentRow, 1).Value = user.Id;
                worksheet.Cell(currentRow, 2).Value = user.FullName;
                worksheet.Cell(currentRow, 3).Value = user.Email;
                currentRow++;
            }

            // Adjust column widths automatically
            worksheet.Columns().AdjustToContents();

            // 5. Stream the Workbook to the memory buffer
            using (var stream = new MemoryStream())
            {
                workbook.SaveAs(stream);
                var content = stream.ToArray();

                // 6. Return standard File Content
                return File(content, 
                    "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", 
                    "UsersReport.xlsx");
            }
        }
    }
}

2. Generating PDF Invoices (QuestPDF)

PDF generation is notoriously difficult. iText7 is powerful but carries strict commercial licensing obligations. Generating PDFs by converting HTML strings is prone to layout breaks. The modern .NET standard is QuestPDF, a fluent-API library perfect for invoices and structured reports.

Installation

dotnet add package QuestPDF

Real-Time Production Example: Fluent PDF Generation

using QuestPDF.Fluent;
using QuestPDF.Helpers;
using QuestPDF.Infrastructure;

public class InvoiceController : Controller
{
    public IActionResult DownloadInvoice(int orderId)
    {
        // Must opt-in to QuestPDF licensing agreement (Community license available)
        QuestPDF.Settings.License = LicenseType.Community;

        var document = Document.Create(container =>
        {
            container.Page(page =>
            {
                page.Size(PageSizes.A4);
                page.Margin(2, Unit.Centimetre);
                page.PageColor(Colors.White);
                page.DefaultTextStyle(x => x.FontSize(12));
                
                // HEADER SECTION
                page.Header().Text($"Invoice #{orderId}")
                    .SemiBold().FontSize(30).FontColor(Colors.Blue.Darken2);

                // CONTENT SECTION (Table)
                page.Content()
                    .PaddingVertical(1, Unit.Centimetre)
                    .Table(table =>
                {
                    table.ColumnsDefinition(columns =>
                    {
                        columns.ConstantColumn(50); // Qty
                        columns.RelativeColumn();   // Item Description
                        columns.ConstantColumn(100); // Price
                    });

                    // Define Table Header
                    table.Header(header =>
                    {
                        header.Cell().Text("Qty").SemiBold();
                        header.Cell().Text("Product").SemiBold();
                        header.Cell().AlignRight().Text("Price").SemiBold();
                    });

                    // Add a Data Row
                    table.Cell().Text("2");
                    table.Cell().Text("Enterprise Developer License");
                    table.Cell().AlignRight().Text("$450.00");
                });

                // FOOTER SECTION
                page.Footer()
                    .AlignCenter()
                    .Text(x => {
                        x.Span("Page ");
                        x.CurrentPageNumber();
                    });
            });
        });

        // 1. Generate directly to a memory stream
        byte[] pdfBytes = document.GeneratePdf();

        // 2. Return to user
        return File(pdfBytes, "application/pdf", $"Invoice_{orderId}.pdf");
    }
}

3. The Architecture for Heavy Reporting

4. Interview Mastery

Q: "Why might you choose CSV generation over Excel `.xlsx` generation for exporting simple database tables?"

Architect Answer: "Memory efficiency and dependency reduction. Generating an OpenXML `.xlsx` file requires a massive in-memory representation of worksheet definitions, styles, and rows using libraries like ClosedXML. This causes massive garbage collection pressure. A CSV (Comma-Separated Values) file, however, is a raw text file. We can iterate over our database via an `IAsyncEnumerable`, format the string `Id,Name `, and stream the bytes *directly* into the HTTP Response stream using `StreamWriter`. This uses virtually zero RAM, requires zero third-party NuGets, and exports millions of rows effortlessly."