How to Create Excel XLSX Using a .NET C# Server-Side API
0 Comments
Document Solutions for Excel (DsExcel, previously 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, DsExcel can deploy to Azure without any Excel dependencies.
With DsExcel, 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.
First, we'll create a basic ASP.NET Core MVC application. You can also use DsExcel in any ASP.NET Core application when using Web API, Angular, or React.js.
Add References to the DsExcel NuGet Package
Right-click Dependencies and choose Manage NuGet Packages.
Under the Browse tab, type GrapeCity.Documents.Excel and add the package.
'Document Solutions' was previously know as 'GrapeCity Documents', the older product name currently remains on our NuGet packages.
Add DsExcel 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 DsExcel 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 - 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
If you have edited an Excel file in the SpreadJS editor, you can export the file to Excel using DsExcel 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]publicFileResultExportExcel(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 =newWorkbook();
workbook.FromJson(HttpContext.Request.Body);MemoryStream stream =newMemoryStream();
workbook.Save(stream);
stream.Seek(0, SeekOrigin.Begin);byte[] bytes =newbyte[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));returnFile(bytes, contentType, donwloadFile);}
Programming and Generating Server-Side Excel Spreadsheets
You can program with DsExcel and generate server-side Excel spreadsheets without the need of MS Excel.
In a function, create a workbook using DsExcel
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 DsExcel on the server-side.
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 DsExcel as a server-side Excel API.
There are many more features that can be programmed with DsExcel on the server-side. For a complete listing, visit the Document Solutions for Excel page.