SpreadsheetGear Engine for .NET Tutorials

ASP.NET Core MVC - Excel Reporting with Template using Visual Studio Code for Mac / Linux / Windows

Follow these steps to create a simple ASP.NET MVC Core Web App with Visual Studio Code for Mac / Linux / Windows 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 MVC Core Web App

1. Launch Visual Studio Code.
2. On the View menu, navigate to Integrated Terminal. A Terminal pane should appear at the bottom of the Visual Studio Code window.
3. Enter the following command to create a new project from the "ASP.NET MVC Core Web App" template, giving the Project a name of "SpreadsheetGearWebApp":

   dotnet new mvc -o SpreadsheetGearWebApp

   You should see a couple messages indicating the generation time and success of the command.
4. From the File menu, go to Open... and navigate to the folder where you just ran the above command from the Terminal.
5. Select the "SpreadsheetGearWebApp" folder and click the "Open".

Add SpreadsheetGear Engine for .NET to your project

1. Go back to the Terminal pane and enter the following command:

   dotnet add package SpreadsheetGear

2. SpreadsheetGear Engine for .NET is now added to your project.

Add Excel Template file to your project

1. Create a "Files" folder under your Web App's root project folder. This can be done by using an "Add Folder" icon near the top of the Explorer pane.
2. Download and save this Excel template file to the "Files" folder created in the previous step.

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 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 Explorer Pane, expand the /Views/Home/ folders and open the Index.cshtml file.
2. Find a place to add the following hyperlink:

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

3. Save Index.cshtml

Build and run the application

1. From the Terminal pane, run the following command:

   dotnet run

2. Several messages should output to the Terminal pane, one of which is the following...

   Now listening on: http://localhost:5000

   ...and for which http://localhost:5000 is clickable. Click on this URL to launch the 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.