We are pleased to announce the new v4.2 release for GrapeCity Documents for Excel (GcExcel). Major highlights of this release include support for Dynamic Array Formula, External Workbook links from the web, and new Calculation Engine functions. In addition, many more features have been added to the API. Check out the new features below.
Dynamic Array Formulas
MS Excel launched the concept of dynamic array formulas in 2018 to return multiple results to a range of cells based on one formula, also called the spilled range functionality. These formulas are used to create a list of unique values (remove duplicates), sort lists, output a filtered range of data, and much more. In addition, existing functions can utilize this same spill-range functionality.
GcExcel adds extensive support of adding dynamic array formulas to Excel files through code. The new workbook.AllowDynamicArray will allow you to enable/disable the dynamic array formula in the worksheet. Once enabled, you can use these seven new functions and the dynamic array formula to give the results desired by your applications:
- FILTER
- RANDARRAY
- SEQUENCE
- SINGLE
- SORT
- SORTBY
- UNIQUE
Two new errors have also been added:
#Spill! - Indicates that a formula returns multiple results but can't return these values to neighboring cells.
#Calc! - Occurs when the calculation engine encounters a scenario it does not currently support.
Check out more details and demos: Help .NET | Help Java | Demo .NET | Demo Java
Support for New Calc Engine Functions
GcExcel adds new functions to its feature set to help users use more Excel functions programmatically in your spreadsheets. The following new functions are supported:
- WEBSERVICE: helps to extract data from a webservice. Help .NET | Help Java | Demo.NET | Demo Java
- FILTERXML: returns specific data from XML. Help .NET | Help Java | Demo.NET | Demo Java
- ASC: changes full-width (double-byte) letters or katakana within a character string to half-width (single-byte) characters (helpful for JP and CN text). Help .NET | Help Java | Demo.NET | Demo Java
- DBCS: converts half-width (single-byte) letters within a character string to full-width (double-byte) characters (helpful for JP and CN text). Help .NET | Help Java | Demo.NET | Demo Java
- JIS: changes half-width (single-byte) letters or katakana within a character string to full-width (double-byte) characters. Help .NET | Help Java | Demo.NET | Demo Java
- XLOOKUP: supports approximate and exact matching, reverse search, and wildcards (* ?) for partial matches and searches in horizontal/vertical ranges. Help .NET | Help Java | Demo.NET | Demo Java
- XMATCH: performs a lookup and returns a position in vertical or horizontal ranges.,supports approximate and exact matching, reverse search, and wildcards (* ?) for partial matches. Help .NET | Help Java | Demo.NET | Demo Java
GcExcel introduces FormulaLocal and FormulaR1C1Local properties in IRange interface that work with the new ASC, DBCS, and JIS functions to retrieve or set localized formulas in the cells of a worksheet. These new properties work only on JP and CN cultures.
The following snapshot shows the usage of the new WEBSERVICE and FILTERXML functions that retrieve weather data for a location from a webservice.
Support for External Workbook Links from the Web
In large and complex Excel files, it is common for formulas to reference other (or external) workbooks and the cells/formulas in those workbooks. Previously, GcExcel supported the Folder path for external workbooks. With v4.2, workbooks on the web can also be referenced in an Excel workbook. When these workbooks are on the web, there is no need to manually open the workbooks and copy the data to use in the Excel file. Instead, reference the external workbook, eliminating any need for duplicate data entry or manual updates when data changes.
An excellent example of this; assume multiple different urban centers are providing a variety of services. There is a centralized administrative office for all of these centers. Ultimately, the data needs to be analyzed as a whole. However, each urban center keeps its own set of data in Excel workbooks on the web. Rather than having these workbooks copied or copying portions of the data, it is easier to reference the workbooks and/or cells within each workbook so the calculations at the home office can be done quickly and efficiently, eliminating duplicate data and work. This also eliminates any need for manual updates, as the calculations will update based on the most current data in the referenced workbooks at the time of opening.
GcExcel adds support of adding web path to external workbook links in the workbook. The IWorkbook.GetExcelLinkSources() function now supports the web path. Call the IWorkbook.UpdateExcelLinks() method to update the current workbook if data changes externally. A workbook on the web can be accessed in the following way and used in the formula property:
IWorksheet worksheet = workbook.Worksheets[0];
worksheet.Range["A1"].Formula = "='/documents-api-excel/demos/api/sample/[SourceWorkbook.xlsx]Sheet1'!A1";
Check out more details: Help .NET | Help Java | Demo .NET | Demo Java
Support for Charts on PDF Export Now Supported in GcExcel Java
Chart utilization is an essential part of Excel spreadsheets. Most Excel documents for finance, sales, marketing, and healthcare analyze data using Excel features to extract the summarized data. GcExcel extends the support of charts export to PDF in Java.
Have a look at the below resources to view a tutorial on how to export Excel files with Charts to PDF in Java Apps: Help Java | Demo Java
Document Properties for Workbook
GcExcel now supports new API to set Document Properties on the workbook. Some of these properties will also be supported on exporting to PDF. The new IWorkbook.BuiltInDocumentPropertyCollection is a collection of built-in properties, and users can modify the property value through workbook.BuiltInDocumentProperties. property. The IWorkbook.CustomDocumentProperties is a collection of custom properties, and users can use the 'Add' method to create new custom document property or the 'AddLinkToContent(..)' method to create a new document property that can link to named cells.
Check out more details: Help .NET | Help Java | Demo .NET | Demo Java
Get the Row and Column Grouping Information
If you have a long report in Excel, you may want to make it more manageable. There may be times when you would want to expand or collapse a group in your worksheet to hide certain information or only view detail rows/columns as needed. However, suppose you can programmatically get which rows or columns are grouped in the Excel worksheet. In that case, it may help you to expand/collapse the groups easily, rather than opening the worksheet and doing it manually. GcExcel adds new API to get row, and column grouping information through the List RowGroupInfo and List ColumnGroupInfo list types and with the help of this API, the following functionalities can be achieved:
- Get the start index of the current group
- Get the end index of the group
- Get the level of current group
- Check whether the group is collapsed or not
- Get the parent row/column of the group
- Get child row/column of the group
Finally, the Expand() or Collapse() methods can help to expand or collapse rows or columns.
Check out more details: Help .NET | Help Java | Demo .NET | Demo Java
Copy Hidden Rows to New Range
You may want certain rows hidden; however, when you copy the rows to some other range, these hidden rows/columns should be copied too. GcExcel adds new API IRange.Copy(IRange destination, PasteOption pasteOption) to copy hidden rows/columns. The API includes the additional property AllowPasteHiddenRange, which will control whether to copy the data of hidden rows/columns or not.
Check out more details: Help .NET | Help Java | Demo .NET | Demo Java
Control the Size of Exported JSON File
There are certain Excel files, which, if exported to JSON, produce large-sized files. It will now be possible to control the size of exported JSON files through the new SerializationOptions.IgnoreColumnRowInfoOutOfUsedRange option. This option will let you control whether to export the column row information out of the used range, which will help reduce the size of the exported JSON file.
Check out more details: Help .NET | Help Java
Support Margin Settings for Text in a Shape
The Shape.TextFrame class now supports MarginBottom, MarginLeft, MarginTop, MarginRight properties to set the margins for text in shape. This will give a better look to your Excel file, and in addition, this support brings GcExcel closer to the Shape support provided in MS Excel.
Check out more details: Help .NET | Help Java | Demo .NET | Demo Java
Expand/Collapse Grouped Items in Pivot Table
You can now programmatically Expand or Collapse grouped rows or columns in Pivot Table by using the new IPivotItem.ShowDetail property to expand or collapse a grouped row/column. This will help you only view necessary details in long worksheets.
Check out more details: Help .NET | Help Java | Demo .NET | Demo Java
More features for GrapeCity SpreadJS Integration
GcExcel adds support for the following SpreadJS features adding more compatibility with the client-side SpreadJS product. Note, these features will work only with SpreadJS and PDF Export and are not supported in MS Excel.
- Support RowCount and ColumnCount in GcExcel for JSON I/O - Help .NET | Help Java
- Get URL of a picture in JSON - Help .NET | Help Java
- Support Pivot Table of SpreadJS through JSON I/O, Excel I/O and PDF Export - Help .NET | Help Java
- Support for the following features
- Sheet TabStrip Position and properties - Help .NET | Help Java | Demo .NET | Demo Java
- Set size of Check Box, Check Box List and Radio Box List Cells - Help .NET | Help Java | Demo .NET | Demo Java
- hoverBackColor of Button Cell Type supported on JSON I/O - Help .NET | Help Java
- buttonBackColor supported in JSON I/O and PDF Export - Help .NET | Help Java
- New Options frozenTrailingColumnStickToEdge and frozenTrailingRowStickToEdge on JSON I/O - Help .NET | Help Java
View supported SpreadJS feature list in GcExcel (.NET & Java)
If you have any comments on the new features or want to share how these are helpful for you, drop a comment below.
Tags:
Shilpa Sharma