Chelsea DevereauxGrapeCity 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 .NET 7 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 can also 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 the following:
- Importing an Excel file on the server-side.
- Export your server-side Excel files.
- Programming with GcExcel on the server-side.
- Generating Excel spreadsheets on the server-side.
Let's take a look at the AspNetCoreDemo samples.
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.
Add References to the GcExcel NuGet Package
- Right-click Dependencies and choose Manage NuGet Packages.
- Choose NuGet Gallery | Home from Package Source on the top right.
- Under the Browse tab, type GrapeCity.Documents.Excel and add the package.
Add GcExcel Namespace to the Controller Class
In your controller class - HomeController.cs, import the following namespace:
using GrapeCity.Documents.Excel;
Importing an Excel File on the Server-Side
You can upload an Excel file on the client-side, load/open it on the server-side, then return the file in JSON syntax.
- Create the IActionResult method in the controller class you just added
- Create a GcExcel workbook
- Load the Excel file sent from the client-side into the workbook
- If you want to show this file back on the client-side, displaying the file in a spreadsheet editor, you can use our client-side Editor - GrapeCity SpreadJS. SpreadJS 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 the 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);
}
Export Excel File on Server-Side
If you have edited an Excel file in the SpreadJS 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 the workbook.FromJson. Then, convert the JSON to xlsx, and download it 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);
}
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 it to the client-side
Note: We are only creating a BidTracker.xlsx spreadsheet in this example. The 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 an 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
private Workbook GetBidTracker()
{
Workbook workbook = new Workbook();
IWorksheet worksheet = workbook.Worksheets[0];
//***********************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, "New Emergency care facility", null, 2000, 0.5, null, null },
{ 2, "Service Contract Extension", null, 3500, 0.75, null, null },
{ 3, "Preventive Maintenance Agreement", null, 5000, 0.8, null, null },
{ 4, "Full restoration contract", null, 4000, 0.2, null, null },
{ 5, "Hampton Inn, Burlington", null, 4000, 1.00, null , null },
{ 6, "New invitation to bid", null, 1500, 0.0, null , null },
{ 7, "Children's hospital - new admin building", 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.FromArgb(89, 89, 89);
wholeTableStyle.Borders.Color = Color.FromArgb(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.FromArgb(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.FromArgb(131, 95, 1);
headerRowStyle.Interior.PatternColor = Color.FromArgb(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.FromArgb(131, 95, 1);
//***********************************Set Named Styles*****************************
IStyle titleStyle = workbook.Styles["Title"];
titleStyle.Font.Name = "Calibri";
titleStyle.Font.Size = 36;
titleStyle.Font.Color = Color.FromArgb(0,0,0);
titleStyle.IncludeAlignment = true;
titleStyle.VerticalAlignment = VerticalAlignment.Center;
IStyle heading1Style = workbook.Styles["Heading 1"];
heading1Style.IncludeAlignment = true;
heading1Style.HorizontalAlignment = HorizontalAlignment.Right;
heading1Style.VerticalAlignment = VerticalAlignment.Bottom;
heading1Style.Borders[BordersIndex.EdgeBottom].LineStyle = BorderLineStyle.None;
heading1Style.Font.Size = 14;
heading1Style.Font.Color = Color.Black;
heading1Style.Font.Bold = false;
heading1Style.IncludePatterns = true;
heading1Style.Interior.Color = Color.FromArgb(255, 255, 255);
IStyle dateStyle = workbook.Styles.Add("Date");
dateStyle.IncludeNumber = true;
dateStyle.NumberFormat = "m/d/yyyy";
dateStyle.IncludeAlignment = true;
dateStyle.HorizontalAlignment = HorizontalAlignment.Right;
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.Right;
commaStyle.VerticalAlignment = VerticalAlignment.Center;
IStyle normalStyle = workbook.Styles["Normal"];
normalStyle.HorizontalAlignment = HorizontalAlignment.Right;
normalStyle.VerticalAlignment = VerticalAlignment.Center;
normalStyle.WrapText = true;
normalStyle.Font.Color = Color.FromArgb(89, 89, 89);
IStyle currencyStyle = workbook.Styles["Currency"];
currencyStyle.NumberFormat = "$#,##0.00";
currencyStyle.IncludeAlignment = true;
currencyStyle.HorizontalAlignment = HorizontalAlignment.Right;
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 = 14;
percentStyle.Font.Bold = true;
percentStyle.Font.Color = Color.FromArgb(89, 89, 89);
IStyle comma0Style = workbook.Styles["Comma [0]"];
comma0Style.NumberFormat = "#,##0_);(#,##0)";
comma0Style.IncludeAlignment = true;
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.FromArgb(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 what the excel file should look like:
To understand the working of the complete MVC workflow (how to display the Excel file back on the client and import the client-side edited Excel file), check out this sample.
We hope you enjoyed the tour of using GcExcel as a server-side Excel API.
There are many more features that can be programmed with GcExcel on the server-side. For a complete listing, visit the Documents for Excel page.
