DEV Community

Cover image for How to Export Pivot Table Data to Excel in ASP.NET Core Without UI Rendering
Syncfusion, Inc. profile image Calvince Moth
Calvince Moth for Syncfusion, Inc.

Posted on • Originally published at syncfusion.com on

How to Export Pivot Table Data to Excel in ASP.NET Core Without UI Rendering

#aspnetcore #datavisualization #pivottable #webdev

TL;DR: Exporting pivot tables to Excel in ASP.NET Core without rendering them in the browser is essential for automation and scheduled tasks. This guide explains how to implement a server-side export process using optimized techniques for handling large datasets.

To export Pivot Table data to Excel without rendering the component in the user interface is a frequent need in automated reporting and backend workflows. Picture generating detailed Excel reports overnight or delivering scheduled summaries to stakeholders, completely hands-free.

This capability is ideal for scenarios such as automated business intelligence reporting, background services that run on a schedule, and server-side processes where UI rendering adds no value.

In this article, we’ll walk through how to export Pivot Table data to Excel in ASP.NET Core using the Syncfusion Pivot Table component, entirely on the server side. We’ll also show you how to automate the process with scheduled tasks, making your reporting pipeline more efficient and reliable.

Prerequisites

Before you begin, ensure you have the following:

  • An ASP.NET Core Empty Web Application built with .NET 8 or a later version.
  • The Syncfusion.XlsIO NuGet package installed to enable Excel export functionality.
  • A working understanding of controllers and dependency injection in ASP.NET Core.

Creating an empty ASP.NET Core application

To begin, set up a minimal ASP.NET Core project that provides a clean foundation for implementing your controller and export logic. Follow these steps:

  1. Open Visual Studio and select Create a new project.
  2. From the list of available project templates, choose ASP.NET Core Empty Web Application.
  3. Name your project as PivotController and click Create.

This creates a lightweight ASP.NET Core application without unnecessary pre-built components, giving you complete control over the structure and functionality.

Implementing Pivot Table export

The core idea is simple: configure a controller that generates Pivot Table data, export it to Excel using Syncfusion’s XlsIO library, and return the file as a download or save it for further use.

Start by adding a controller named PivotController inside the Controllers folder of your ASP.NET Core application. This controller will handle all server-side logic for exporting data.

If you like to skip the manual setup, you can download the PivotController from the GitHub repository. The accompanying user guide provides detailed instructions for the implementation process, which can help you save time.

Export the Pivot Table to the desired file location!

To enable Excel export functionality, install the Syncfusion.XlsIO NuGet package. This library provides all the tools required to create and manipulate Excel files seamlessly.

Configuring the Pivot Table report

Within the POST method of PivotController, configure the Pivot Table report using the FetchData class. This class allows you to define the table structure by specifying columns, rows, and values. After configuration, pass the settings to the Pivot Engine for processing. In this example, the data is provided as a List collection, retrieved from the GetVirtualData method of the DataSource.

You can bind various types of data sources depending on your application’s requirements. For a detailed list of supported data sources and integration options, refer to this documentation.

[PivotController.cs]

[Route("/api/pivot/post")]
[HttpPost]
public async Task<object> Post()
{
    // Step 1: Set up the settings for the Pivot Table report
    FetchData param = new FetchData
    {
        Action = "onRefresh",
        Hash = "a8016852-2c03-4f01-b7a8-cdbcfd820df1",
        ExportAllPages = true,
        DataSourceSettings = new DataOptions
        {
            EnableServerSideAggregation = true,
            Rows = new List<FieldOptions>
            {
                new FieldOptions
                {
                    Name = "ProductID",
                }
                },
                Columns = new List<FieldOptions>
                {
                    new FieldOptions
                    {
                        Name = "Country",
                    }
                },
                Values = new List<FieldOptions>
                {
                    new FieldOptions
                    {
                        Name = "Price",
                        Caption = "Price",
                    },
                    new FieldOptions
                    {
                        Name = "Sold",
                        Caption = "Units Sold",
                    }
                }
            },
            InternalProperties = new CustomProperties
            {
                Locale = "{\"Null\":\"null\",\"Years\":\"Years\",\"Quarters\":\"Quarters\",\"Months\":\"Months\",\"Days\":\"Days\",\"Hours\":\"Hours\",\"Minutes\":\"Minutes\",\"Seconds\":\"Seconds\",\"QuarterYear\":\"Quarter Year\",\"Of\":\"of\",\"Qtr\":\"Qtr\",\"Undefined\":\"undefined\",\"GroupOutOfRange\":\"Out of Range\",\"Group\":\"Group\",\"GrandTotal\":\"Grand Total\",\"Total\":\"Total\"}"
                },
            };
            PivotEngine.Data = new DataSource.PivotViewData().GetVirtualData();
            // Pass the configuration to the pivot engine to generate the report
            EngineProperties engine = engine = await PivotEngine.GetEngine(param);
            return null;
        }
Enter fullscreen mode Exit fullscreen mode

Set the action name for Excel exporting

Before initiating the Excel export, manually set param.Action to onExcelExport to indicate that the data should be prepared for export to Excel.

[PivotController.cs]

[Route("/api/pivot/post")]
[HttpPost]
    public async Task<object> Post()
    {
        ... // Here adds the above-mentioned code block.

            param.Action = "onExcelExport";
            ExcelEngine excelEngine = new ExcelEngine();
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Xlsx;
            return null;
        }
Enter fullscreen mode Exit fullscreen mode

Initialize workbook and worksheet

Use Syncfusion.XlsIO interfaces like IWorkbook and IWorksheet to create and configure an Excel workbook. This is where your Pivot Table data will be exported, saving the workbook to a memory stream during the process.

[PivotController.cs]

 [Route("/api/pivot/post")]
 [HttpPost]
 public async Task<object> Post()
 {
     ... // Here adds the above-mentioned code block.

     IWorkbook book = application.Workbooks.Create(1);
     IWorksheet workSheet = book.Worksheets[0];
     // Perform Excel exporting if the action matches
     if (param.Action == "onExcelExport")
     {
         // Handle specific situations like virtualization during export
         if (param.InternalProperties.EnableVirtualization && param.ExportAllPages)
         {
             engine = await PivotEngine.PerformAction(engine, param);
         }
         // Update worksheet with engine data
         excelExport.UpdateWorkSheet(string.Empty, engine, workSheet); // Here we update the work sheet
     }
     return null;
 }
Enter fullscreen mode Exit fullscreen mode

Save workbook to memory stream

After populating the workbook with Pivot data, store it in a MemoryStream. This acts as an in-memory buffer for the Excel file.

[PivotController.cs]

[Route("/api/pivot/post")]
[HttpPost]
public async Task<object> Post()
{
    ... // Here adds the above-mentioned code block.

    MemoryStream memoryStream = new MemoryStream(); // Saved the book as memory stream
    book.SaveAs(memoryStream);
    // Prepare to save the memory stream to a file
    MemoryStream copyOfStreamDoc1 = new MemoryStream(memoryStream.ToArray());
    return null;
}
Enter fullscreen mode Exit fullscreen mode

Write to a specific file location

Finally, use a FileStream to write the contents of the MemoryStream to a specific location, for example: D:\Export\Sample.xlsx.

[PivotController.cs]

[Route("/api/pivot/post")]
[HttpPost]
public async Task<object> Post()
{
    ... // Here adds the above-mentioned code block.

    string filePaths = @"D:\Export\Sample.xlsx";

    // Create a FileStream to write the memoryStream contents to a file
    using (FileStream fileStream = new FileStream(filePaths, FileMode.Create))
    {
        // Copy the MemoryStream data to the FileStream
        copyOfStreamDoc1.CopyTo(fileStream);
    }

    //Reset the memory stream position.
    memoryStream.Position = 0;
    return null;
}
Enter fullscreen mode Exit fullscreen mode

Trigger export on startup

To execute the export process automatically when your application starts, include a call to the POST method in your Program.cs file.

[Program.cs]

using PivotController.Controllers;
using Microsoft.Extensions.DependencyInjection;

var builder = WebApplication.CreateBuilder(args);
var CustomOrigins = "_customOrigins";

// Add services to the container.
builder.Services.AddControllers();
builder.Services.AddTransient<PivotController.Controllers.PivotController>();

builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();
builder.Services.AddCors(options =>
{
    options.AddPolicy(CustomOrigins,
    builder =>
    {
        builder.AllowAnyOrigin().AllowAnyHeader().AllowAnyMethod();
    });
});
builder.Services.AddMemoryCache((options) =>
{
    options.SizeLimit = 100;
});

var app = builder.Build();

// Configure the HTTP request pipeline.
if (app.Environment.IsDevelopment())
{
    app.UseSwagger();
    app.UseSwaggerUI();
}

app.UseHttpsRedirection();

app.UseAuthorization();

app.MapControllers();
app.UseCors(CustomOrigins);

// Trigger the export logic immediately when the application starts
var scope = app.Services.CreateScope();
var pivotController = scope.ServiceProvider.GetRequiredService<PivotController.Controllers.PivotController>();
await pivotController.Post(null); // Optionally, pass necessary args if required

app.Run();
Enter fullscreen mode Exit fullscreen mode

<alt-text>


Excel file exported to the specified location

<alt-text>


Exported document

Sending the Excel file via email

In addition to saving the file locally, you might want to send it as an email attachment. Sending the Excel file via email is a smart way to share your data quickly and easily, especially when working with people who are not nearby. In this continuation, we’ll take the Excel file already saved to a MemoryStream and seamlessly convert it into an email attachment, leveraging the capabilities built in the prior steps.

Create the email attachment

Start by converting your Excel data from the memory stream into an attachment. This step is crucial because it enables you to include the file in your email.

[PivotController.cs]

[Route("/api/pivot/post")]
[HttpPost]
public async Task<object> Post([FromBody] object args)
{
    ... // Here adds the previous code block.

    // Create the Email Attachment
    Attachment file = new Attachment(memoryStream, "PivotAttachment.xlsx", "application/xlsx");
    SendEMail("your email", "your email", "Pivot Excel document", "Create Excel MailBody", file);
    return null;
}
Enter fullscreen mode Exit fullscreen mode

Send Excel file via email

Once the Excel file is ready, the next step is to compose and send the email with the file attached.

Step 1: Compose the Email

Use the MailMessage class to create the email. This object contains essential details such as sender and recipient addresses, subject, and body.

Step 2: Attach the File

Add the Excel file to the Attachments collection of the MailMessage object. This ensures the file is included when the email is sent.

Step 3: Configure the SMTP Client

Use the SmtpClient class to send the email. Key configurations include:

  • SMTP Server: For Gmail, use smtp.gmail.com.
  • Port: Use 587 for TLS/SSL.
  • Enable SSL: Set EnableSsl = true.
  • Credentials: Use NetworkCredential with your email and app password for secure authentication.

Step 4: Sending the email

Finally, call the Send method on the SmtpClient to deliver the mail. This method sends the constructed email message to the intended recipient securely and reliably.

Here is a comprehensive code example that demonstrates the complete process of sending an Excel file as an email attachment:

[PivotController.cs]


private static void SendEMail(string from, string recipients, string subject, string body, Attachment attachment)
{
    //Creates the email message
    MailMessage emailMessage = new MailMessage(from, recipients);
    //Adds the subject for email
    emailMessage.Subject = subject;
    //Sets the HTML string as email body
    emailMessage.IsBodyHtml = false;
    emailMessage.Body = body;
    //Add the file attachment to this e-mail message.
    emailMessage.Attachments.Add(attachment);
    //Sends the email with the prepared message
    using (SmtpClient client = new SmtpClient())
    {
        //Update your SMTP Server address here"
        client.Host = "smtp.gmail.com";
        client.UseDefaultCredentials = false;
        //Update your email credentials here
        // Need to pass the app password here
        client.Credentials = new System.Net.NetworkCredential(from, "your-app-password");
        client.Port = 587;
        client.EnableSsl = true;
        client.Send(emailMessage);
    }
}
Enter fullscreen mode Exit fullscreen mode

<alt-text>


Excel file as an email attachment

<alt-text>


Exported file

Security considerations

When sending emails programmatically, protecting your credentials is critical. Avoid hardcoding sensitive information like SMTP usernames and passwords directly in your source code. Instead, use environment variables or secure services such as Azure Key Vault or AWS Secrets Manager to store credentials safely.

If you’re using Gmail, configure an app-specific password rather than your regular login credentials. This enhances security and complies with Gmail’s security policies. Following these practices ensures a secure and compliant email-sending process.

Automating scheduled Pivot Table exports via email

Sending pivot table reports by email every day can be a repetitive and time-consuming task. Wouldn’t it be great if you could automate this process? Why not automate it? By scheduling the export and email process, you can save time and guarantee that reports are delivered consistently.

In this section, we’ll show you how to automatically export Pivot Table data and send it as an email attachment at a specific time each day. This approach is ideal for businesses that rely on timely reporting.

Brief overview of the code

To accomplish this, we’ll set up a background service using the IHostedService interface in ASP.NET Core. This service runs continuously in the background and schedules tasks to execute at predefined intervals.

Here’s the workflow:

  1. The background service waits until the scheduled time.
  2. At the scheduled time, it triggers a POST method in the PivotController.
  3. The controller exports Pivot Table data into an Excel file.
  4. The service sends the Excel file via email automatically.

This setup ensures your reports are generated and delivered without manual intervention.

[TimedHostedService.cs]

using PivotController.Controllers;

namespace PivotController.Services
{
    public class TimedHostedService : IHostedService, IDisposable
    {
        private Timer _timer;
        private readonly IServiceProvider _serviceProvider;

        public TimedHostedService(IServiceProvider serviceProvider)
        {
            _serviceProvider = serviceProvider;
        }

        public Task StartAsync(CancellationToken cancellationToken)
        {
            // Calculate the initial delay until 8 PM
            ScheduleNextRun();
            return Task.CompletedTask;
        }

        private void ScheduleNextRun()
        {
            // Get the current time
            var now = DateTime.Now;
            // Calculate the next 8 PM
            var nextRunTime = new DateTime(now.Year, now.Month, now.Day, 20, 0, 0);
            // If it's already past 10 PM today, schedule for tomorrow
            if (now > nextRunTime)
            {
                nextRunTime = nextRunTime.AddDays(1);
            }

            // Calculate the time difference between now and the next run time
            var timeUntilNextRun = nextRunTime - now;
            // Schedule the timer to call the method at the calculated time
            _timer = new Timer(ExecuteTask, null, timeUntilNextRun, Timeout.InfiniteTimeSpan);
        }

        private async void ExecuteTask(object state)
        {
            // Call the post method
            await CallPostMethodAsync();
            // Schedule the next run for the next day at 10 PM
            ScheduleNextRun();
        }

        private async Task CallPostMethodAsync()
        {
            using (var scope = _serviceProvider.CreateScope())
            {
                var controller = scope.ServiceProvider.GetRequiredService<PivotController.Controllers.PivotController>();
                await controller.Post();
            }
        }

        public Task StopAsync(CancellationToken cancellationToken)
        {
            _timer?.Change(Timeout.Infinite, 0);
            return Task.CompletedTask;
        }

        public void Dispose()
        {
            _timer?.Dispose();
        }
    }
}
Enter fullscreen mode Exit fullscreen mode

Register the hosted service

To make sure your hosted service runs when the application starts, register it in Program.cs:

[Program.cs]

using PivotController.Services;

builder.Services.AddHostedService<TimedHostedService>();
Enter fullscreen mode Exit fullscreen mode

GitHub reference

For more details about exporting Pivot Table data to Excel without displaying the component in the UI, refer to the GitHub repository.

Conclusion

Thank you for reading! By following this guide, you can export Syncfusion Pivot Table data to Excel without rendering the component in your UI, perfect for automation and backend workflows. This approach simplifies data handling, whether you’re saving files locally or sending them as email attachments.

Combine this with scheduled email automation, and your reports will always reach stakeholders on time, every day. Syncfusion Pivot Table gives developers the flexibility to customize, automate, and streamline reporting, making it a powerful tool for modern applications.

If you’re a Syncfusion user, you can download the setup from the license and downloads page. Otherwise, you can download a free 30-day trial.

You can also contact us through our support forum, support portal, or feedback portal for queries. We are always happy to assist you!

Related links

Related Blogs

This article was originally published at Syncfusion.com.

Top comments (0)