SpreadsheetGear Engine for .NET Tutorials
ASP.NET Core MVC - Excel Reporting using Visual Studio for Windows
Follow these steps to create a simple ASP.NET Core MVC Web App using Visual Studio for Windows that utilizes SpreadsheetGear Engine for .NET to generate and stream a Microsoft Excel workbook to the browser. Note that you can target either the full .NET Framework or .NET Core with this tutorial.
Create a new ASP.NET Core MVC Web App
- Launch Visual Studio 2015 or later.
- On the File menu, navigate to New → Project...
- Under Project Types / Templates, choose either:
- Visual C# → Web → ASP.NET Web Application (.NET Framework), which will target the full .NET Framework.
- Visual C# → .NET Core → ASP.NET Core Web Application (.NET Core), which will target .NET Core.
- Change the Name and Location as desired.
- Click OK to create the project.
Add SpreadsheetGear Engine for .NET to your project
- On the Project menu, click Manage NuGet Packages. The NuGet Package Manager window appears.
- In the NuGet Package Manager window, click the Browse tab and enter "SpreadsheetGear" in the search field.
- In the list of NuGet packages, select "SpreadsheetGear" and click Install.
- You might be prompted to confirm this change to your project. Click OK.
- You will be prompted to accept SpreadsheetGear's License Agreement. Click I Accept if you wish to proceed.
- Visual Studio should begin the installation process and add a reference to SpreadsheetGear in your Project's References folder.
- Close the NuGet Package Manager UI Tab.
Activate SpreadsheetGear Engine for .NET
This tutorial will work with the free version of SpreadsheetGear Engine for .NET (i.e., without activating SpreadsheetGear with your signed license. Learn more about signed licenses here). If you wish to use the trial or fully licensed version of SpreadsheetGear Engine for .NET, please follow the steps below.
- Using the Solution Explorer Panel, open the Startup.cs file in the root of your project folder.
- Add the highlighted line of code below to the ConfigureServices(...) method:
public void ConfigureServices(IServiceCollection services) { SpreadsheetGear.Factory.SetSignedLicense("YOUR SIGNED LICENSE HERE"); // Add framework and other services. services.AddMvc(); }
Add new Action Method to HomeController
- Using the Solution Explorer, expand the Controllers folder and open the HomeController.cs file.
- 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"); }
- Save HomeController.cs
Add hyperlink to trigger "Download Report" Action
- Using the Solution Explorer, expand the Views/Home/ folders and open the Index.cshtml file.
- Find a place to add the following hyperlink:
<a asp-action="DownloadReport">Download Report</a>
- Save Index.cshtml
Build and run the application
- From the Build menu, select Build Solution. The solution should build without errors.
- On the Debug menu, click Start Without Debugging. Your browser should launch and display the Home Page of your ASP.NET MVC Web App.
- Click on the "Download Report" hyperlink created in the above step.
- Your browser should either prompt you to save an Excel file to your computer, or automatically save the file to the designated "Downloads" folder.
- Open the downloaded Excel report with Microsoft Excel.