Back To Tutorials

SpreadsheetGear for .NET Standard 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 for .NET Standard to generate and stream a Microsoft Excel workbook to the browser.

Prerequisites:

  • This tutorial assumes you already have both Visual Studio Code and the .NET Core SDK installed on your Mac. If not, please use the following links to install these now:

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 for .NET Standard to your project

  1. Go back to the Terminal pane and enter the following command:
    dotnet add package SpreadsheetGear
  2. SpreadsheetGear for .NET Standard 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 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 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.
Back To Tutorials