SpreadsheetGear Engine for .NET Tutorials

ASP.NET Core MVC - Excel Reporting 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 generate and stream a Microsoft Excel workbook to the browser.

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 new Action Method to HomeController

1. Using the Explorer Pane, expand the /Controllers/ folder and open the HomeController.cs file.
2. Scroll to the bottom of HomeController.cs and add the following action method:

   public IActionResult DownloadReport()
   {
       // Create a new workbook.
       SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook();
       SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets["Sheet1"];
       SpreadsheetGear.IRange cells = worksheet.Cells;
   
       // Set the worksheet name.
       worksheet.Name = "2005 Sales";
               
       // Load column titles and center.
       cells["B1"].Formula = "North";
       cells["C1"].Formula = "South";
       cells["D1"].Formula = "East";
       cells["E1"].Formula = "West";
       cells["B1:E1"].HorizontalAlignment = SpreadsheetGear.HAlign.Center;
   
       // Load row titles using multiple cell text reference and iteration.
       int quarter = 1;
       foreach (SpreadsheetGear.IRange cell in cells["A2:A5"])
           cell.Formula = "Q" + quarter++;
   
       // Load random data and format as $ using a multiple cell range.
       SpreadsheetGear.IRange body = cells[1, 1, 4, 4];
       body.Formula = "=RAND() * 10000";
       body.NumberFormat = "$#,##0_);($#,##0)";
   
               
       // 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");
   }

3. 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="DownloadReport">Download Report</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 includes...

   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" 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.