SpreadsheetGear

SpreadsheetGear Engine for .NET Tutorials

ASP.NET Core MVC - Excel Reporting with Template using .NET Core CLI

Follow these steps to create a simple ASP.NET Core MVC Web App with the .NET Core Command Line Interface (CLI) that utilizes SpreadsheetGear Engine for .NET to stream a Microsoft Excel workbook to the browser. The downloaded workbook is populated from an Excel template file that utilizes a variety of Conditional Formatting options, such as Color Scales and Icon Sets.

Prerequisites:

This tutorial assumes you already have the .NET Core SDK installed on your machine. If not, please go to https://dotnet.microsoft.com/download to install it now.

Create a new ASP.NET Core MVC Web App

Open up a Command Prompt and navigate the folder where you want to add this project (note: the next step will create a subfolder fully containing this project).
Enter the following commands to create a new ASP.NET Core MVC Web App with a name of "SpreadsheetGearWebApp":
```
dotnet new mvc -o SpreadsheetGearWebApp
cd SpreadsheetGearWebApp
```

Add SpreadsheetGear Engine for .NET to your project

Enter the following commands in the Command Prompt:

dotnet add package SpreadsheetGear
dotnet restore
dotnet run

The above commands should add a dependency to SpreadsheetGear Engine for .NET, restore all NuGet dependencies in your project, then build and run the web app on localhost, port 5000
Navigate to http://localhost:5000 in your browser to ensure the app is running correctly.
Go back to the Command Prompt and type Ctrl+C to shut down the web app.

Add Excel Template file to your project

Create a "Files" folder under your Web App's root project folder.
Download and save this Excel template file to this "Files" folder.

Inject IHostingEnvironment

Opening and processing "Files/ReportTemplate.xlsx" requires accessing the web server's local file system, which can be done with IWebHostEnvironment. Use MVC's built-in Dependency Injection to get an instance of this object in the HomeController class by adding the following to the beginning of the class (located in /Controllers/HomeController.cs):
```
...
public class HomeController : Controller
{
    Microsoft.AspNetCore.Hosting.IWebHostEnvironment _env;

    public HomeController(Microsoft.AspNetCore.Hosting.IWebHostEnvironment env)
    {
        _env = env;
    }
    ...
}
```

Add new Action Method to HomeController

Add the following action method to the HomeController class:

public IActionResult DownloadReportFromTemplate()
{
    // Open a stream to the template workbook file.
    Microsoft.Extensions.FileProviders.IFileInfo fileInfo = 
      _env.ContentRootFileProvider.GetFileInfo("Files/ReportTemplate.xlsx");
    System.IO.Stream readStream = fileInfo.CreateReadStream();

    // Create a new "workbook set" object and open the above file stream.
    SpreadsheetGear.IWorkbookSet workbookSet = 
      SpreadsheetGear.Factory.GetWorkbookSet();
    SpreadsheetGear.IWorkbook workbook = 
      workbookSet.Workbooks.OpenFromStream(readStream);

    // The defined name "DataRange" will be used to obtain the range to be populated.
    SpreadsheetGear.IName namedRange = workbook.Names["DataRange"];
    SpreadsheetGear.IRange dataRange = namedRange.RefersToRange;

    // Load some random data into the range.
    dataRange.Formula = "=1000 + RAND() * 10000";

    // Save workbook to an Open XML (XLSX) workbook stream.
    System.IO.Stream stream = workbook.SaveToStream(
        SpreadsheetGear.FileFormat.OpenXMLWorkbook);

    // Reset stream's current position back to the beginning.
    stream.Seek(0, System.IO.SeekOrigin.Begin);

    // Stream the Excel workbook to the client in the Open XML file
    // format compatible with Excel 2007-2019 and Excel for Office 365.
    return new FileStreamResult(stream,
        "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
}

Save HomeController.cs

Add hyperlink to trigger "Download Report From Template" Action

Using a text editor, open Index.cshtml located under your project's /Views/Home/ subfolder.

Find a place to add the following hyperlink:

<a asp-action="DownloadReportFromTemplate">Download Report From Template</a>

Save Index.cshtml

Build and run the application

From the Command Prompt, enter the following command to build and run your application:
```
dotnet run
```
Navigate to http://localhost:5000 in your browser.
Click on the "Download Report From Template" hyperlink created in the above step.
Your browser should either prompt you to save an Excel file to your computer, or automatically save the file to the designated "Downloads" folder.
Open the downloaded Excel report with Microsoft Excel.

Back To Tutorials