View Server-Side Excel XLSX in a Client-Side Data Viewer using ASP.NET

January 30 2023

With GrapeCity Documents for Excel v6, we introduced a JavaScript-based Documents Data Viewer(GcDataViewer) that can load and preview Microsoft Excel (XLSX) documents. The blog article and video tutorial describe how GcDataViewer can be added to an ASP.NET Core MVC Application to load your client-side data documents on the web.

However, most large-scale applications require the dynamic display of data, that is, fetch and display data when needed. For this purpose, programmers choose server-side frameworks to help them build data storage from where they can pull out the desired data (or document) and send it to be displayed to the client via some coding.

When GcDataViewer is integrated or embedded in such applications, it must open the data documents stored on the server storage. In this blog, we’ll see how the JS-based Data Viewer can open a server-side XLSX document generated using GrapeCity Documents for Excel (GcExcel) in an ASP.NET Core MVC Application.

Ready to Try it Out? Download GrapeCity Documents for Excel .NET or Java!

Use-case

Suppose you are creating a server-side application to fetch data from a database and prepare an analysis report in Microsoft Excel (XLSX) format. You also want your business users to see these analysis reports within your application without needing Microsoft Excel or any other third-party API.

GcExcel offers extensive support for server-side Excel API that can generate, load, save, convert, calculate, format, parse, and export spreadsheets in any .NET targeted application. It also provides a JavaScript-based client-side DataViewer that can load and view popular data documents such as XLSX and Comma Separated Values (CSV). It can be used with any server-side technology, such as ASP.NET Core application using Web API, AngularJS, ReactJS, or NodeJS.

In this blog, we’ll add it to an ASP.NET Core MVC Application for the server-side XLSX generation, and client-side viewing needs using the following simple steps:

Create an ASP.NET Core MVC Project
Create XLSX File on Server-side
View XLSX file in Client-side viewer
Configure to load the static HTML page
Launch the application

Step 1 - Create an ASP.NET Core MVC Project

Let’s begin with a basic ASP.NET Core MVC Application in Visual Studio 2022 (or later). Give the project a name and configure it for the target .NET Framework, HTTPS, and other required settings.

Create an ASP.NET Core MVC Project

Step 2 - Create an XLSX file on Server-side

Loading, generating, and saving an XLSX file programmatically using GcExcel in the ASP.NET Core MVC Application includes the following steps:

Import the GcExcel NuGet package from the NuGet Package Manager to the application.
Add a controller class for GcExcel operations and import GrapeCity.Documents.Excel. Then create a function that returns a FileResult object. In this function, add the business logic to generate your XLSX file (for example, connecting with the database to fetch data and populating it in the XLSX file).

Here is the code to generate an Income Statement XLSX using GcExcel API:

using Microsoft.AspNetCore.Mvc;
using GrapeCity.Documents.Excel;

namespace Excel_generate_and_view.Controllers
{
    public class ExcelServiceController : Controller
    {
        public FileResult SampleFile()
        {
            //create a new workbook
            var workbook = new GrapeCity.Documents.Excel.Workbook();

            //Load template file Income statement.xlsx from resource
            var fileStream = this.GetResourceStream("Income statement.xlsx");

            workbook.Open(fileStream);

            var worksheet = workbook.ActiveSheet;

            //set values
            object[,] RevData = new object[,]
            {
                { 454597, 687802, 469902, 335846, 776667, 806250, 552036, 346734, 787205, 720496, 686963, 835688 },
                { 821687, 679306, 568503, 715827, 627459, 822625, 271114, 797991, 823966, 304521, 758665, 322694 },
                { 426510, 767295, 799314, 408533, 353862, 261403, 344075, 277433, 289814, 386539, 625447, 489178 },
            };

            object[,] CostData = new object[,]
            {
                { 113053, 134668, 192784, 199522, 163169, 109389, 187568, 170346, 130920, 180119, 184725, 162780 },
                { 42667, 14921, 42007, 10264, 48864, 10360, 42378, 31445, 15208, 34418, 37529, 22077 },
            };

            object[,] OpeData = new object[,]
            {
                { 264290, 282301, 266021, 272333, 285359, 256072, 289430, 280307, 278656, 272674, 299784, 284062 },
                { 29536, 28709, 27579, 27862, 26750, 26970, 27847, 27680, 28375, 25474, 27514, 26753 },
                { 23473, 22902, 21680, 19232, 23077, 20839, 19461, 15504, 21117, 20102, 16322, 21160 },
                { 11340, 11514, 11207, 9186, 9243, 10003, 11092, 10761, 9032, 10366, 9571, 11774 },
                { 9562, 10787, 11809, 9949, 9559, 9753, 11761, 10702, 10545, 10347, 10593, 10719 },
                { 9410, 9646, 9969, 10758, 9183, 10777, 11249, 9763, 9445, 11218, 10579, 10203 },
            };

            object[,] EarnData = new object[,]
            {
                { 1038, 1430, 627, 1352, 765, 1190, 1082, 1261, 1240, 645, 552, 669 },
                { 1227, 1372, 544, 1172, 1479, 821, 891, 1361, 1187, 760, 1126, 873 },
                { 661, 1342, 1119, 1085, 887, 1274, 883, 582, 601, 845, 948, 817 },
            };

            worksheet.Range["E10:P12"].Value = RevData;
            worksheet.Range["E15:P16"].Value = CostData;
            worksheet.Range["E21:P26"].Value = OpeData;
            worksheet.Range["E31:P33"].Value = EarnData;

            //set formulars
            worksheet.Range["E13:P13"].Formula = "=SUM(E10:E12)";
            worksheet.Range["E17:P17"].Formula = "=SUM(E15:E16)";
            worksheet.Range["E19:P19"].Formula = "=E13-E17";
            worksheet.Range["E27:P27"].Formula = "=SUM(E21:E26)";
            worksheet.Range["E29:P29"].Formula = "=E19-E27";
            worksheet.Range["E34:P34"].Formula = "=E29-E31+E32-E33";
            worksheet.Range["E36:P36"].Formula = "=E34*0.17";
            worksheet.Range["E38:P38"].Formula = "=E34-E36";

            //Save workbook to stream
            var tempStrm = new MemoryStream();
            workbook.Save(tempStrm);

            tempStrm.Position = 0;
            return File(tempStrm, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        }
    }
}

This is how the XLSX file generated above that appears in MS Excel:

Create an XLSX file on Server-side

Step 3 - View XLSX file in a Client-Side Viewer

Next, let’s configure the client-side viewer to view the XLSX file generated in the previous step. This includes the following:

Import the GrapeCity Documents DataViewer (GcDataViewer) npm package to the wwwroot\lib folder
Add a static HTML page to the wwwroot folder and name it index.html.
In the HTML page, add a reference to the GcDataViewer script file and initialize the DataViewer component as shown below:

<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8" />
    <title>Generate and View Excel </title>
    <script type="text/javascript" src="lib/node_modules/@grapecity/gcdataviewer/gcdataviewer.js"></script>
    //script from next step to be added here
    <style> #root {height: 100vh;} </style>
</head>
<body onload="loadDataViewer('#root')">
    <div id="root"></div>
</body>

</html>

Add an async function to fetch the XLSX file from the response generated by the server-side controller and load it in the viewer as shown in the code below:

<script>
async function loadDataViewer(selector) {
    let viewer = new GcDataViewer(selector, {})
    let response = await fetch("/ExcelService/SampleFile")
    let blob = await response.blob(
    viewer.openFile(blob, FileType.XLSX)     
}
</script>

Step 4 - Configure to load the static HTML page

By default, when the application is executed, the View page Index.cshtml is displayed in the browser. To load the static index.html page, add the middleware UseDefaultFiles in Program.cs as shown below:

var builder = WebApplication.CreateBuilder(args);

// Add services to the container.
builder.Services.AddControllersWithViews();

var app = builder.Build();

// Configure the HTTP request pipeline.
if (!app.Environment.IsDevelopment())
{
    app.UseExceptionHandler("/Home/Error");
}
app.UseDefaultFiles(); 

app.UseStaticFiles();

app.UseRouting();

app.UseAuthorization();

app.MapControllerRoute(
    name: "default",
    pattern: "{controller=Home}/{action=Index}/{id?}");

app.Run();

Alternatively, you can configure the Build Action for index.html to Embedded resource and update the HomeController.cs with the code below:

View XLSX file in a Client-Side Viewer

 public class HomeController : Controller
{
    public object Index()
    {
        var stream = GetType().Assembly.GetManifestResourceStream("Excel_generate_and_view.wwwroot." + "index.html");
        return new ContentResult() { Content = new StreamReader(stream).ReadToEnd(), ContentType = "text/html" };
    }
}