We are pleased to announce the new v4.1 release for GrapeCity Documents for Excel (GcExcel).
Included in this new release are updates to the existing GcExcel .NET Excel Library & API toolkit, as well as performance improvements in Excel template processing. Check out key highlights below!
Performance Improvements in Excel Template Processing
Generating Excel documents from templates is now faster than ever before. The ProcessTemplate() method is now more efficient when processing several records of the datasource. As your number of records increase, so will the speed of processing the template into the final report. This action is now several times faster than before, with up to 100,000 records tested.
Below is a complex report with 100,000 records:
Notice the performance improvement from previous builds:
Try out the reports using the templates demos below with the latest v4.1 GcExcel packages.
Parse Formula Strings into a Syntax Tree
Often times, you only need to modify certain parts of a formula, rather than the whole formula. For example, you may want to change the 'NOW()' function to a different date in the formula below:
=LET(AppUpTime,NOW()-DATE(2020,4,17)+366, YEAR(AppUpTime)-1900-1 & " years"
Required Formula
=LET(AppUpTime,DATE(2021,2,14)-DATE(2020,4,17)+366,YEAR(AppUpTime)-1900-1&" years")
GcExcel v4.1 adds the new GrapeCity.Documents.Excel.Expressions namespace to parse formula expressions into a syntax tree so you can create, parse, and modify formulas. The FormulaSyntaxTree class is the entry of formula expressions API. You can perform the following functions by parsing a formula:
- Generate formula
- Modify formula
- Parse and format options
- Print syntax tree
The example below parses a formula into different parts:
Visit Help .NET | Help Java | Demo .NET | Demo Java
Ignore Formulas When Saving Excel Files
While interacting with SpreadJS, users often need to return an Excel file with values, but no formulas, to the client-side. When saving an Excel file with GcExcel, you can now set XlsxSaveOptions.IgnoreFormulas property. When set, the formula cells will be exported as only value cells.
Visit Help .NET | Help Java | Demo .NET | Demo Java
Support Open Action Script on PdfSaveOptions
Set specific actions when opening the exported PDF file. Examples include converting values to numbers for PDF form fields created with GcExcel templates when the PDF file is opened. You can now set JavaScript code in the OpenActionScript property of PdfSaveOptions class.
The following code converts a textbox field value to a number in Javascript, which is defined in OpenActionScript property.
Workbook workbook = new Workbook();
workbook.Open("SampleTemplate.xlsx");
workbook.ProcessTemplate();
PdfSaveOptions options = new PdfSaveOptions();
options.OpenActionScript="var fld1 = this.getField(\"num\");" +"fld1.value = fld1.value;" +"this.dirty = false;";
workbook.Save("SampleTemplate.pdf", options);
New Overload Method to Load JSON
Use new JSON options to open JSON files using DeserializationOptions. The return value will show an error list when opening the JSON file. The new overload has been added to Workbook.Open method.
var workbook = new Workbook();
var jsonErrors = workbook.Open("file.json", new DeserializationOptions { IgnoreFormula = true });
Visit Help .NET | Help Java | Demo .NET | Demo Java
Improved Calculation Engine Performance When Setting Values
GcExcel adds the new Workbook.DeferUpdateDirtyState boolean property. When changing the value of a cell, and setting Workbook.DeferUpdateDirtyState =True, GcExcel will not update the formula cell dirty state immediately. This improves the Calculation Engine's performance when setting values in an Excel file.
Visit Help Java
More Features for SpreadJS Integration
Support for RangeTemplate Cell Type
The new RangeTemplate class will allow users to define a template of cell ranges as a single cell type and apply that template to a cell. Then, you can load different data into the template, including multiple rows and columns—allowing you to display a card view in one cell.
Once the cell type is applied to a cell or a cell range, the range template will use the cell value (or values) as a data source to resolve the binding path of the specified range. This usage is similar to other cell types.
Visit Help .NET | Help Java | Demo .NET | Demo Java
Apply Custom Objects on CheckboxList and RadioButtonList Cell Types
In a Checkboxlist or RadioButtonlist, users need the flexibility to select multiple items from the list. You can now select multiple options in a CheckboxList or RadioButtonList cell type by applying custom objects on these cell types.
Note: This result is visible only on PDF Export.
Visit Help Java | Demo .NET Checkbox | Demo .NET Radiolist | Demo Java Checkbox | Demo Java Radiolist |
New ToJson and FromJSON Methods to Workbook Elements**
It's not uncommon for SpreadJS users to only need to update certain parts of the workbook. For example, a user might not want to load or save an entire workbook, but simply update the range, shape, table, or more of such data. GcExcel now features FromJson and ToJson methods to different Workbook elements to enable these actions.
For example, you can use IStyle.FromJson(string json) to update an existing named style and IStyle.ToJson() to export the named style to JSON string. These methods are included in the following classes:
- Style
- Range
- Validation
- SparklineGroup, Sparkline
- FormatConditions
- Top10
- AboveAverage
- UniqueValues
- ColorScale
- DataBar
- IconSetCondition
- Table
- Shape
- Slicer
- Comment
- Name
- PageSetup
- ProtectionSettings
The following code imports validation settings from JSON:
var book = new Workbook();
var sheet = book.Worksheets[0];
sheet.Range["A1:B2"].Validation.FromJson("{\"type\":5,\"condition\":{\"conType\":0,\"compareType\":1,\"item1\":{\"conType\":13,\"compareType\":5,\"expected\":\"9:08:09\"},\"item2\":{\"conType\":13,\"compareType\":3,\"expected\":\"19:08:09\"},\"ignoreBlank\":true},\"ranges\":\"A3:B4\",\"highlightStyle\":\"{\\\"type\\\":0,\\\"color\\\":\\\"red\\\"}\"}");
sheet.Range["C1:D2"].Validation.FromJson("{\"inputTitle\":\"Tip\",\"inputMessage\":\"Enter a date between 12/31/2012 and 12/31/2013.\",\"type\":4,\"condition\":{\"conType\":0,\"compareType\":1,\"item1\":{\"conType\":5,\"compareType\":5,\"expected\":\"/OADate(41274)/\",\"ranges\":[{\"row\":0,\"rowCount\":2,\"col\":2,\"colCount\":2}]},\"item2\":{\"conType\":5,\"compareType\":3,\"expected\":\"/OADate(41639)/\",\"ranges\":[{\"row\":0,\"rowCount\":2,\"col\":2,\"colCount\":2}]},\"ignoreBlank\":true,\"ranges\":[{\"row\":0,\"rowCount\":2,\"col\":2,\"colCount\":2}]},\"ranges\":\"C3:D4\",\"highlightStyle\":\"{\\\"type\\\":0,\\\"color\\\":\\\"red\\\"}\"}");
Visit Help .NET | Help Java | Demo .NET | Demo Java
Get and Set Custom Object as Cell Value
Some APIs of SpreadJS support custom data types or user-defined data types. GcExcel now supports custom data types in the Range.Value property for SpreadJS interoperability purposes.
The following code assigns a dictionary to a range:
/create a new workbook
var workbook = new GrapeCity.Documents.Excel.Workbook();
var activeSheet = workbook.ActiveSheet;
IRange a1 = activeSheet.Range["A1"];
var dict = new Dictionary<string, object>()
{
{"TempData1", 1},
{"TempData2", "Temp value 2"},
{"TempData3", 3},
{"TempData4", "Temp value 4"}
};
// Set temporary data to a range
a1.Value = dict;
// Display the custom object later
var obj = (IReadOnlyDictionary<string, object>)a1.Value;
var row = 1;
foreach (var kv in obj)
{
activeSheet.Range["B" + row].Value = kv.Key;
activeSheet.Range["C" + row].Value = kv.Value;
row += 1;
}
// Arrange
activeSheet.Columns.AutoFit();
activeSheet.Columns[0].Hidden = true;
//save to a pdf file
workbook.Save("setcustomrangevalue.pdf");
Visit Help .NET | Help Java | Demo .NET (Set Custom Object to Range) | Demo .NET (Set custom object in custom function) | Demo Java Demo .NET (Set Custom Object to Range) | Demo Java (Set custom object in custom function)
View Supported SpreadJS Feature list in GcExcel
Shilpa Sharma