Back To .NET Standard Tutorials

SpreadsheetGear Engine for .NET Tutorials

ASP.NET Core MVC - Excel Reporting with Template using Visual Studio for Windows

Follow these steps to create a simple ASP.NET Core MVC Web App 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.

Create a new ASP.NET Core MVC Web App

  1. Launch Visual Studio 2015 or later.
  2. On the File menu, navigate to New → Project...
  3. Under Project Types / Templates, choose either:
    • Visual C# → .NET Core → ASP.NET Core Web Application (.NET Core), which will target .NET Core.
    • Visual C# → Web → ASP.NET Core Web Application (.NET Framework), which will target the full .NET Framework.
  4. Change the Name and Location as desired and click OK.
  5. On the next screen, select Web Application and click OK to create the project.

Add SpreadsheetGear Engine for .NET to your project

  1. On the Project menu, click Manage NuGet Packages. The NuGet Package Manager window appears.
  2. In the NuGet Package Manager window, click the Browse tab and enter "SpreadsheetGear" in the search field.
  3. In the list of NuGet packages, select "SpreadsheetGear" and click Install.
  4. You might be prompted to confirm this change to your project. Click OK.
  5. You will be prompted to accept SpreadsheetGear's License Agreement. Click I Accept if you wish to proceed.
  6. Visual Studio should begin the installation process and add a reference to SpreadsheetGear in your Project's References folder.
  7. Close the NuGet Package Manager UI Tab.

Add Excel Template file to your project

  1. Download and save this Excel template file to a location on your computer.
  2. Create a "Files" folder under your Web App's root project folder by right-clicking on the Project name in the Solution Explorer pane and selecting Add → New Folder.
  3. Add the ReportTemplate.xlsx to this Files folder by right clicking on the folder and navigating to Add → Existing Item... and locating and selecting the ReportTemplate.xlsx file from the dialog box and clicking Add.

Inject IHostingEnvironment

  1. 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 (open from the Solution Explorer pane at /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

  1. 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");
}
  2. Save HomeController.cs

Add hyperlink to trigger "Download Report" Action

  1. Using the Solution Explorer, expand the Views → Home folders and open the Index.cshtml file.
  2. Find a place to add the following hyperlink:
    <a asp-action="DownloadReportFrom Template">Download Report From Template</a>
  3. Save Index.cshtml

Build and run the application

  1. From the Build menu, select Build Solution. The solution should build without errors.
  2. On the Debug menu, click Start Without Debugging. Your browser should launch and display the Home Page of your ASP.NET MVC Web App.
  3. Click on the "Download Report From Template" hyperlink created in the above step.
  4. Your browser should either prompt you to save an Excel file to your computer, or automatically save the file to the designated "Downloads" folder.
  5. Open the downloaded Excel report with Microsoft Excel.
Back To Tutorials