GrapeCity Documents for Excel (or GcExcel) is a server-side Excel API that can generate, load, save, convert, calculate, format, parse, and export spreadsheets in any .NET Standard targeted application. With full .NET5 support for Windows, Linux, and Mac, GcExcel can deploy to Azure without any Excel dependencies.

With GcExcel, you can easily create, load, and save Excel documents without having Microsoft Excel on your system. With the VSTO-style API, you can create custom styles, import spreadsheets, calculate data, query, generate, and export any spreadsheet. You are also able to add sorting, filtering, formatting, conditional formatting and validate data, add grouping, sparklines, charts, shapes, pictures, slicers, comments, hyperlinks, themes etc. using the same elements as VS Tools for Office.

This article will demonstrate:

1. Importing an Excel file on the server-side.
2. Export your server-side Excel files.
3. Programing with GcExcel on the server-side.
4. Generating Excel spreadsheets on the server-side.

Let's take a look at the AspNetCoreDemo samples.

1. Creating ASP.NET Core MVC Project

• First, we'll create a basic ASP.NET Core MVC application. You can also use GcExcel in any ASP.NET Core application when using Web API, Angular, or React.js.

2. Add a Controller class for GcExcel operations

• Right-click Controllers folder and choose Add new item.
• In the dialog box that opens, choose MVC Controller Class.

3. Add references to the GcExcel nuget package

• Right-click Dependencies and choose Manage Nuget Packages.
• Choose nuget.org from Package Source on top right.
• Under Browse tab, type GrapeCity.Documents.Excel and add the package.

4. Add GcExcel namespace to Controller class

• In your controller class, import following namespace:

using GrapeCity.Documents.Excel;

5. Importing an Excel file on the server-side

You can upload an Excel file on client-side, load/open it on server-side, then return the file in JSON syntax.

• Create IActionResult method in the controller class you just added.
• Create a GcExcel workbook.
• Load the Excel file sent from client-side into the workbook.
• If you want to show this file back on client-side, displaying the file in a viewer/editor, you can use our client-side Viewer and Editor - GrapeCity SpreadJS. The Spread.Sheets uses JSON syntax to display the Excel file, so you can convert the file on the server-side to JSON using the workbook.ToJson() function. Finally, return this JSON (named ssjson in the code) to client-side.

Use this code for the above steps:

[HttpPost]
public IActionResult ImportExcel()
{
    Workbook workbook = new Workbook();
    workbook.Open(Request.Body);
    var ssjson = workbook.ToJson();
    return Ok(ssjson);
}

6. Export Excel file on server-side

If you have edited an Excel file in Spread.Sheets viewer/editor, you can export the file to Excel using GcExcel on the server-side. Extract the JSON (of the edited Excel file) on the server-side using workbook.FromJson. Then, convert the JSON to xlsx, and download on the server-side.

[HttpPost]
public FileResult ExportExcel(string fileName = "")
{
            // create a new Workbook and invoke FromJson to restore workbook from ssjson
            // the ssjson is from spread.sheets by invoking this.spread.toJSON()
            Workbook workbook = new Workbook();
            workbook.FromJson(HttpContext.Request.Body);

            MemoryStream stream = new MemoryStream();
            workbook.Save(stream);
            stream.Seek(0, SeekOrigin.Begin);

            byte[] bytes = new byte[stream.Length];
            stream.Read(bytes, 0, bytes.Length);

            var contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            var donwloadFile = string.Format("attachment;filename={0}.xlsx;", string.IsNullOrEmpty(fileName) ? Guid.NewGuid().ToString() :              WebUtility.UrlEncode(fileName));

            return File(bytes, contentType, donwloadFile);
}

7. Programming and generating server-side Excel spreadsheets

You can program with GcExcel and generate server-side Excel spreadsheets without the need of MS Excel.

• In a function, create a workbook using GcExcel.
• Convert the workbook to JSON and return to the client-side.

Note: We are only creating a BidTracker.xlsx spreadsheet in this example. This sample demonstrates additional features that can be programmed with GcExcel on the server-side.

 [HttpGet("{caseName}")]
        public IActionResult GetSSJsonFromUseCase(string caseName)
        {
            Workbook workbook = CreateWorkbookByCaseName(caseName);
            var ssjson = workbook.ToJson();
            return Ok(ssjson);
        }

        private Workbook CreateWorkbookByCaseName(string caseName)
        {
            switch (caseName)
            {
                case "BidTracker":
                    return GetBidTracker();
                default:
                    break;
            }

            return new Workbook();
        }

Create a function, to generate Excel spreadsheet. This example demonstrates how to use different GcExcel features:

• Creating a workbook & worksheet
• Adding data
• Adding a table
• Named styles and conditional formatting

//**********************Set RowHeight & ColumnWidth**************
worksheet.StandardHeight = 30;
worksheet.Range["1:1"].RowHeight = 57.75;
worksheet.Range["2:9"].RowHeight = 30;
worksheet.Range["A:A"].ColumnWidth = 2.71;
worksheet.Range["B:B"].ColumnWidth = 11.71;
worksheet.Range["C:C"].ColumnWidth = 28;
worksheet.Range["D:D"].ColumnWidth = 22.425;
worksheet.Range["E:E"].ColumnWidth = 16.71;
worksheet.Range["F:F"].ColumnWidth = 28;
worksheet.Range["G:H"].ColumnWidth = 16.71;
worksheet.Range["I:I"].ColumnWidth = 2.71;

//*************************Set Table Value & Formulas********************
ITable table = worksheet.Tables.Add(worksheet.Range["B2:H9"], true);
worksheet.Range["B2:H9"].Value = new object[,]
{
{ "BID #", "DESCRIPTION", "DATE RECEIVED", "AMOUNT", "PERCENT COMPLETE", "DEADLINE", "DAYS LEFT" }
,
{ 1, "Bid number 1", null, 2000, 0.5, null, null }
,
{ 2, "Bid number 2", null, 3500, 0.25, null, null }
,
{ 3, "Bid number 3", null, 5000, 0.3, null, null }
,
{ 4, "Bid number 4", null, 4000, 0.2, null, null }
,
{ 5, "Bid number 5", null, 4000, 0.75, null , null }
,
{ 6, "Bid number 6", null, 1500, 0.45, null , null }
,
{ 7, "Bid number 7", null, 5000, 0.65, null, null }
,
};
worksheet.Range["B1"].Value = "Bid Details";
worksheet.Range["D3"].Formula = "=TODAY()-10";
worksheet.Range["D4:D5"].Formula = "=TODAY()-20";
worksheet.Range["D6"].Formula = "=TODAY()-10";
worksheet.Range["D7"].Formula = "=TODAY()-28";
worksheet.Range["D8"].Formula = "=TODAY()-17";
worksheet.Range["D9"].Formula = "=TODAY()-15";
worksheet.Range["G3:G9"].Formula = "=[@[DATE RECEIVED]]+30";
worksheet.Range["H3:H9"].Formula = "=[@DEADLINE]-TODAY()";

//***************************Set Table Style*******************************

//***************************Set Table Style*******************************
ITableStyle tableStyle = workbook.TableStyles.Add("Bid Tracker");
workbook.DefaultTableStyle = "Bid Tracker";

var wholeTableStyle = tableStyle.TableStyleElements[TableStyleElementType.WholeTable];

//Set WholeTable element style.
wholeTableStyle.Font.Color = Color.FromRGB(89, 89, 89);
wholeTableStyle.Borders.Color = Color.FromRGB(89, 89, 89);
wholeTableStyle.Borders[BordersIndex.EdgeLeft].LineStyle = BorderLineStyle.Thin;
wholeTableStyle.Borders[BordersIndex.EdgeRight].LineStyle = BorderLineStyle.Thin;
wholeTableStyle.Borders[BordersIndex.EdgeTop].LineStyle = BorderLineStyle.Thin;
wholeTableStyle.Borders[BordersIndex.EdgeBottom].LineStyle = BorderLineStyle.Thin;
wholeTableStyle.Borders[BordersIndex.InsideVertical].LineStyle = BorderLineStyle.Thin;
wholeTableStyle.Borders[BordersIndex.InsideHorizontal].LineStyle = BorderLineStyle.Thin;

var headerRowStyle = tableStyle.TableStyleElements[TableStyleElementType.HeaderRow];

//Set HeaderRow element style.
headerRowStyle.Borders.Color = Color.FromRGB(89, 89, 89);
headerRowStyle.Borders[BordersIndex.EdgeLeft].LineStyle = BorderLineStyle.Thin;
headerRowStyle.Borders[BordersIndex.EdgeRight].LineStyle = BorderLineStyle.Thin;
headerRowStyle.Borders[BordersIndex.EdgeTop].LineStyle = BorderLineStyle.Thin;
headerRowStyle.Borders[BordersIndex.EdgeBottom].LineStyle = BorderLineStyle.Thin;
headerRowStyle.Borders[BordersIndex.InsideVertical].LineStyle = BorderLineStyle.Thin;
headerRowStyle.Borders[BordersIndex.InsideHorizontal].LineStyle = BorderLineStyle.Thin;
headerRowStyle.Interior.Color = Color.FromRGB(131, 95, 1);
headerRowStyle.Interior.PatternColor = Color.FromRGB(254, 184, 10);

var totalRowStyle = tableStyle.TableStyleElements[TableStyleElementType.TotalRow];

//Set TotalRow element style.
totalRowStyle.Borders.Color = Color.White;
totalRowStyle.Borders[BordersIndex.EdgeLeft].LineStyle = BorderLineStyle.Thin;
totalRowStyle.Borders[BordersIndex.EdgeRight].LineStyle = BorderLineStyle.Thin;
totalRowStyle.Borders[BordersIndex.EdgeTop].LineStyle = BorderLineStyle.Thin;
totalRowStyle.Borders[BordersIndex.EdgeBottom].LineStyle = BorderLineStyle.Thin;
totalRowStyle.Borders[BordersIndex.InsideVertical].LineStyle = BorderLineStyle.Thin;
totalRowStyle.Borders[BordersIndex.InsideHorizontal].LineStyle = BorderLineStyle.Thin;
totalRowStyle.Interior.Color = Color.FromRGB(131, 95, 1);

//**********************************Set Named Styles****************************
IStyle titleStyle = workbook.Styles["Title"];
titleStyle.Font.Name = "Calibri";
titleStyle.Font.Size = 36;
titleStyle.Font.Color = Color.FromRGB(56, 145, 167);
titleStyle.IncludeAlignment = true;
titleStyle.VerticalAlignment = VerticalAlignment.Center;

IStyle heading1Style = workbook.Styles["Heading 1"];
heading1Style.IncludeAlignment = true;
heading1Style.HorizontalAlignment = HorizontalAlignment.Left;
heading1Style.IndentLevel = 1;
heading1Style.VerticalAlignment = VerticalAlignment.Bottom;
heading1Style.Borders[BordersIndex.EdgeBottom].LineStyle = BorderLineStyle.None;
heading1Style.Font.Size = 14;
heading1Style.Font.Color = Color.White;
heading1Style.Font.Bold = false;
heading1Style.IncludePatterns = true;
heading1Style.Interior.Color = Color.White;

IStyle dateStyle = workbook.Styles.Add("Date");
dateStyle.IncludeNumber = true;
dateStyle.NumberFormat = "m/d/yyyy";
dateStyle.IncludeAlignment = true;
dateStyle.HorizontalAlignment = HorizontalAlignment.Left;
dateStyle.IndentLevel = 1;
dateStyle.VerticalAlignment = VerticalAlignment.Center;
dateStyle.IncludeFont = false;
dateStyle.IncludeBorder = false;
dateStyle.IncludePatterns = false;

IStyle commaStyle = workbook.Styles["Comma"];
commaStyle.IncludeNumber = true;
commaStyle.NumberFormat = "#,##0_);(#,##0)";
commaStyle.IncludeAlignment = true;
commaStyle.HorizontalAlignment = HorizontalAlignment.Left;
commaStyle.IndentLevel = 1;
commaStyle.VerticalAlignment = VerticalAlignment.Center;

IStyle normalStyle = workbook.Styles["Normal"];
normalStyle.HorizontalAlignment = HorizontalAlignment.Left;
normalStyle.IndentLevel = 1;
normalStyle.VerticalAlignment = VerticalAlignment.Center;
normalStyle.WrapText = true;
normalStyle.Font.Color = Color.FromRGB(89, 89, 89);

IStyle currencyStyle = workbook.Styles["Currency"];
currencyStyle.NumberFormat = "$#,##0.00";
currencyStyle.IncludeAlignment = true;
currencyStyle.HorizontalAlignment = HorizontalAlignment.Right;
currencyStyle.IndentLevel = 1;
currencyStyle.VerticalAlignment = VerticalAlignment.Center;

IStyle percentStyle = workbook.Styles["Percent"];
percentStyle.IncludeAlignment = true;
percentStyle.HorizontalAlignment = HorizontalAlignment.Right;
percentStyle.VerticalAlignment = VerticalAlignment.Center;
percentStyle.IncludeFont = true;
percentStyle.Font.Name = "Calibri";
percentStyle.Font.Size = 20;
percentStyle.Font.Bold = true;
percentStyle.Font.Color = Color.FromRGB(89, 89, 89);

IStyle comma0Style = workbook.Styles["Comma [0]"];
comma0Style.NumberFormat = "#,##0_);(#,##0)";
comma0Style.IncludeAlignment = true;
comma0Style.HorizontalAlignment = HorizontalAlignment.Right;
comma0Style.IndentLevel = 3;
comma0Style.VerticalAlignment = VerticalAlignment.Center;

//***********************************Add Conditional Formatting***************
IDataBar dataBar = worksheet.Range["F3:F9"].FormatConditions.AddDatabar();
dataBar.MinPoint.Type = ConditionValueTypes.Number;
dataBar.MinPoint.Value = 1;
dataBar.MaxPoint.Type = ConditionValueTypes.Number;
dataBar.MaxPoint.Value = 0;

dataBar.BarFillType = DataBarFillType.Gradient;
dataBar.BarColor.Color = Color.FromRGB(126, 194, 211);
dataBar.Direction = DataBarDirection.Context;

dataBar.AxisColor.Color = Color.Black;
dataBar.AxisPosition = DataBarAxisPosition.Automatic;

dataBar.NegativeBarFormat.ColorType = DataBarNegativeColorType.Color;
dataBar.NegativeBarFormat.Color.Color = Color.Red;
dataBar.ShowValue = true;

//***************************************Use NamedStyle*************************
worksheet.SheetView.DisplayGridlines = false;
table.TableStyle = tableStyle;
worksheet.Range["B1"].Style = titleStyle;
worksheet.Range["B1"].WrapText = false;
worksheet.Range["B2:H2"].Style = heading1Style;
worksheet.Range["B3:B9"].Style = commaStyle;
worksheet.Range["C3:C9"].Style = normalStyle;
worksheet.Range["D3:D9"].Style = dateStyle;
worksheet.Range["E3:E9"].Style = currencyStyle;
worksheet.Range["F3:F9"].Style = percentStyle;
worksheet.Range["G3:G9"].Style = dateStyle;
worksheet.Range["H3:H9"].Style = comma0Style;

return workbook;

This is how your Excel file will display:

There are many additional features that can be programmed using GcExcel on the server-side. For more information, a complete feature list, and to download a free trial, please visit Documents for Excel.

Download the sample here.