Back To Tutorials

SpreadsheetGear for .NET Standard Tutorials

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

Prerequisites:

Create a new ASP.NET Core MVC Web App

  1. 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).
  2. Enter the following commands to create a new ASP.NET Core MVC Web App with a name of "SpreadsheetGearWebApp" and then move the current directory to this project's root:
        dotnet new mvc -o SpreadsheetGearWebApp
        cd SpreadsheetGearWebApp

Add SpreadsheetGear for .NET Standard to your project

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

Add new Action Method to HomeController

  1. Using your preferred text editor, open the HomeController.cs file located under your project's /SpreadsheetGearWebApp/Controllers/ subfolder.
  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 spreadsheet to the client in a format
            // compatible with Excel 97/2000/XP/2003/2007/2010/2013/2016.
            return new FileStreamResult(stream, 
                "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        }
  3. Save HomeController.cs

Add hyperlink to trigger "Download Report" Action

  1. Using a text editor again, open Index.cshtml located under your project's /SpreadsheetGearWebApp/Views/Home/ subfolder.
  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 Command Prompt, enter the following command to build and run your application:
        dotnet run
  2. Navigate to http://localhost:5000 in your browser.
  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.
Back To Tutorials