What's New in GrapeCity Documents for Excel v5.2

Here we are with another set of features to generate Excel spreadsheets with advanced features, all in a single GrapeCity Documents for Excel (GcExcel) API. We have a new Excel function added to the API, Pivot Table enhancements, a whole new set of Excel Form controls, support for JSON as a data source, and many more new additions with similar parity in .NET and Java. Check out the major highlights below!

New API to add Excel Form Controls 

One way to collect and present data in a spreadsheet is through Form Controls added to a worksheet. Controls such as list boxes, checkboxes, and buttons help structure a worksheet and manage its data. GcExcel now supports APIs to add Excel Form controls programmatically. You can create Excel forms with standard Form controls, customize them, collect data from your users, or programmatically retrieve Form control values. Also, if several of your Excel spreadsheets include Form controls, the spreadsheet can load into the GcExcel object model, and the Form controls can be modified.

Download GrapeCity Documents for Excel .NET and JAVA now!

GcExcel introduces IControlCollection, which adds all generic members of Form Controls of type IControl. You can use Worksheet.Controls to add or access the controls. The following controls are supported:

• Button
• DropDown
• CheckBox
• Spinner
• ListBox
• OptionButton
• GroupBox
• Label
• ScrollBar

With this support, you can also use the following features -

• Each control has its own set of properties. You can set those using the specific API of each control. For example, the following code shows how to set properties specific to the button control:

var workbook = new GrapeCity.Documents.Excel.Workbook();       
IWorksheet ws = workbook.Worksheets["Sheet1"];
         
var button = ws.Controls.AddButton(50, 30, 120, 40);
button.Text = "Submit";
button.PrintObject = true;
button.HorizontalTextAlignment = HorizontalAlignment.Center;
button.VerticalTextAlignment = VerticalAlignment.Center;
                 
// Save to an excel file
workbook.Save("ButtonsBasicUsage.xlsx");

• Modify Form controls and Form control values - Access the controls using Worksheet.Controls collection. LinkedCell property of CheckBox, OptionButton, ListBox, DropDown, and Scrollbar Form Controls helps bind the controls' value to the linked cell and vice versa. You can get the value of the controls using the LinkedCell property
• Use Form controls as Shapes - A new option, FormControl has been added to ShapeType enum, which can check if Form control is of Shape type or not. Also, use <IControl>.ShapeRange to use some of the Shape features on Form controls like duplicating a control shape or copy shapes on cells
• Use Form controls with Excel I/O and JSON I/O
• Export to PDF, HTML, and Images (exported as Images)

View the following resources for more info.

Help .NET | Help Java | Demo .NET | Demo Java

Enhancements to GcExcel Templates

Paginated Templates 

If you have a certain template where you want a fixed number of rows on a page and need the same layout to repeat on the following worksheet, you can choose to paginate your workbook into separate worksheets. GcExcel Templates introduces 'TemplateOptions.PaginationMode', a global boolean property which, when true, can paginate a workbook into separate worksheets. At the same time, if false will follow traditional rules of Template layout and pagination. When true, you can define the new CountPerPage property (CP) for the cell whose data needs to be repeated. The property will limit the number of instances (records) of a template cell generated on a page. When the number of records exceeds the value of CP, a new worksheet is automatically created (or paginated) with the same layout as the template. “CountPerPage” property refers to the count of grouped records on a worksheet. 

The following snapshot shows the CP value as 10, set for a Template cell. FM=O indicates that the new instances will override the cells below when the template cell expands.

There are certain conditions in which CountPerPage (CP) property will work. For example, when Group (G) property is set to Normal/Merge/Repeat, the records are grouped, and count refers to the number of groups created. And if Group (G) property is set to List, then the records are not grouped, and the Count refers to the actual record count. Get more details in the resources below.

Help .NET | Help Java | Demo .NET | Demo Java

Debug mode for template language 

Now it will no longer be necessary to open the original Template file to check whether the template has expanded correctly or not. GcExcel supports a defined name TemplateOptions.DebugMode in TemplateOptions. When DebugMode is true, the original template sheet would appear in Sheet 1, next to expanded Template Sheet 2.

Help .NET | Help Java | Demo .NET | Demo Java

Support for Chart Data Table 

A data table in a chart is helpful to view/analyze data conveniently. In Excel, data tables are displayed beneath an Excel chart. This is especially useful so that the data and chart can be analyzed together, rather than scrolling to a different location within the sheet or other sheets to match chart data with the source data. To help with this, GcExcel introduces two new properties - IChart.HasDataTable sets true or false, whether to add Data Table to Chart or not, IChart.DataTable represents the chart's data table, which can provide various options to set in the Data Table like Font, Formatting, Showing Legend Key, etc.

Data Table in snapshot following the code is added using the new API. The formatting of the Data Table can be customized. Have a look at the code below: 

IWorksheet worksheet = workbook.Worksheets[0];
 
// Set data.
worksheet.Range["A1:C3"].Value = new object[,]
{
    {"BUDGET TOTALS", "ESTIMATED", "ACTUAL"},
    {"Income", 63300, 57450},
    {"Expenses", 54500, 49630}
};
 
//Create chart.
GrapeCity.Documents.Excel.Drawing.IShape shape = worksheet.Shapes.AddChart(GrapeCity.Documents.Excel.Drawing.ChartType.ColumnClustered, 250, 0, 350, 250);
shape.Chart.SeriesCollection.Add(worksheet.Range["A1:C3"]);
shape.Chart.ChartTitle.Text = "Estimated vs Actual";
 
//Display the data table.
shape.Chart.HasDataTable = true;
 
//Config the data table.
GrapeCity.Documents.Excel.Drawing.IDataTable datatable = shape.Chart.DataTable;
datatable.Format.Line.Color.ObjectThemeColor = ThemeColor.Accent6;
datatable.Font.Color.ObjectThemeColor = ThemeColor.Accent2;
datatable.Font.Size = 9;
         
// Save to an excel file
workbook.Save("chartdatatable.xlsx");

Help .NET | Help Java | Demo .NET | Demo Java

Add Calculated Item in Pivot Table

Calculated items help to add custom formulas in Pivot Table, performing custom calculations on other items that do not exist in the source data. GcExcel introduces the following API:

• IPivotField.CalculatedItems() to get a CalculatedItems collection representing all the calculated items in the specified PivotTable field
• ICalculatedItems.Add(string name, string formula) to create a new calculated item
• ICalculatedItems.Remove(string name) to remove the calculated fields by name
• IPivotItem.Formula to get or set calculate item formula

If the value in a PivotTable cell is affected by two or more calculated items, the value is determined by the last formula in the solve order. With GcExcel, you can use the following API to set the solve order:

• IPivotFormula is an object representing the content and solves the order of the calculated items
• Use IPivotTable.IPivotFormulas() to get a IPivotFormula collection that represents all the IPivotFormula in the specified PivotTable field
• Use IPivotFormula.Index{get;set;} to get or set the index of the current PivotFormula

The spreadsheet below sets Country and Product as Calculated Items. 'Country' calculated item adds the Sales value of countries that come under the same continent, while Product Calculated Item sets all products with a value of $2500 as iPhone13 and later sets Visible to False for such items. 

ICalculatedItems countryCalcItems = calculatedItemTable.PivotFields["Country"].CalculatedItems();
ICalculatedItems productCalcItems = calculatedItemTable.PivotFields["Product"].CalculatedItems();
 
// add some calculated items
countryCalcItems.Add("Oceania", "=Australia+NewZealand");
countryCalcItems.Add("Europe", "=France+Germany");
countryCalcItems.Add("America", "=Canada");
 
productCalcItems.Add("IPhone 13", "=2500");
 
// hide the duplicate normal item.
IPivotItems countrys = calculatedItemTable.PivotFields["Country"].PivotItems;
countrys["United Kingdom"].Visible = false;
countrys["United States"].Visible = false;
 
IPivotItems products = calculatedItemTable.PivotFields["Product"].PivotItems;
products["IPhone 13"].Visible = false;
calculatedItemSheet.Range["A:I"].AutoFit();
 
calculatedItemSheet.Activate();
         
// Save to an excel file
workbook.Save("Calculateditem.xlsx");

Help .NET | Help Java | Demo .NET | Demo Java

Support JSON as DataSource in data binding

You can now bind Excel spreadsheets with JSON string as a data source. The worksheet.DataSource can now be directly bound to a JsonDataSource class which accepts a JSON string as a data source. The following example code imports data from a JSON file and binds it to worksheet:

string jsonStr = "[{\"name\":\"jack\",\"age\":12},{\"name\":\"alice\",\"age\":25}]";
Workbook workbook = new Workbook();
IWorksheet worksheet = workbook.Worksheets[0];
worksheet.DataSource = new JsonDataSource(jsonStr);
worksheet.Range["A1"].BindingPath = "name";
workbook.Save("DataSource.xlsx");

Help .NET | Help Java | Demo .NET | Demo Java

IsVolatile property support in Custom function 

When using custom functions, GcExcel stores a cache of the formulas in the same column with the same name and parameters. This makes the same formula evaluated only once in the same column. GcExcel supports IsVolatile Boolean property while defining the custom function. The property will control whether to use cache when using the custom function or not. 

The following example demonstrates how to create a custom function for generating GUID. To generate a unique GUID every time, the custom function should not use cache. Hence, the example code sets the IsVolatile property to true so that a new GUID is generated on every call.

//create a new workbook
var workbook = new GrapeCity.Documents.Excel.Workbook();
 
GrapeCity.Documents.Excel.Workbook.AddCustomFunction(new GeneralID());
 
IWorksheet worksheet = workbook.Worksheets[0];
worksheet.Range["A1"].Formula = "GeneralID()";
var valueA1Before = worksheet.Range["A1"].Value;
worksheet.Range["A2"].Formula = "GeneralID()";
// A1's value has changed.
var valueA1After = worksheet.Range["A1"].Value;
 
/* Implementation of GeneralID
 
public class GeneralID : CustomFunction
{
    public GeneralID() : base("GeneralID", FunctionValueType.Object)
    {
        this.IsVolatile = true;
    }
 
    public override object Evaluate(object[] arguments, ICalcContext context)
    {
        return Guid.NewGuid().ToString("N");
    }
}
 
*/

Help .NET | Help Java | Demo .NET | Demo Java

Get Accurate Range Boundary

GcExcel .NET adds the new CellInfo.GetAccurateRangeBoundary(..) method that returns a more accurate value of range boundary than RectangleF. 

Following code gets accurate range boundary of a range and adds a shape at that exact location. 

// Get the absolute location and size of the Range["G1"] in the worksheet.
IRange range = worksheet.Range["F1:G1"];
RectangleF rect = GrapeCity.Documents.Excel.CellInfo.GetAccurateRangeBoundary(range);
// Add the image to the Range["G1"]
System.IO.Stream stream = this.GetResourceStream("logo.png");
worksheet.Shapes.AddPictureInPixel(stream, GrapeCity.Documents.Excel.Drawing.ImageType.PNG, rect.X, rect.Y, rect.Width, rect.Height);
         
// Save to an excel file
workbook.Save("getrangeboundary.xlsx");

Help .NET | Demo .NET

SVG Image Support in Spreadsheets

GcExcel adds IShapes.AddPicureInPixel(..) method to add images in SVG format. In addition, the ToImage(..) method supports exporting to SVG pictures for the following:

• Use IRange.ToImage(string filename) method to export a range to SVG picture
• Use ISheet.ToImage(string filename) method to export a sheet to SVG picture
• Use IShape.ToImage(string filename) method to export a shape to SVG picture

GcExcel will also support spreadsheets with SVG images in the following situations:

• Lossless import and export of excel files 
• JSON I/O
• Export to PDF/HTML/Image file

Note: To use SVG image with GcExcel API in Java, the extension package batik (1.14) and gcexcel-extension-5.2.0.jar should be included in the project. 

View the following resources for more info.

Help .NET | Help Java | Demo .NET | Demo Java

New Excel functions

LET Function

In your spreadsheets, you may be using several formulas, which may be complex. The LET function applies a user-friendly name to a range/cell or calculation and helps improve formula calculation performance. A lengthy formula that makes the same calculation over and over can be calculated only once, and the result can be stored with an easy-to-understand name. This name can be used further within the LET function, making calculations only once while re-using the result later. You no longer need to remember the reference's underlying logic. GcExcel adds this LET function to its list of supported Functions. If several sheets repeat calculations repeatedly, you can update those formulas to use the LET function. An example of such an improvement to the spreadsheet is given below. 

In the above example, the LET function reduces the number of times VLOOKUP is calculated from 4 to 2 times by using the LET function and defining the output of VLOOKUP to 'name' and 'points' variables and re-using them in the formula.

The following code shows how to set the function in code:

ws.Range["$F$9"].Formula2 = "=LET(name,VLOOKUP($G$5,$B$5:$D$16,2,0),points,VLOOKUP($G$5,$B$5:$D$16,3,0),"Ho, "&name&", you have "&points&" points. "IF(points>300, "Great job, "&name&"!",""))

View the following resources for more info.

Help .NET | Help Java | Demo .NET | Demo Java

GetPivotData Formula Supports Spilled Data

In the last release, GcExcel introduced GETPIVOTDATA(...) function and the new IRange.GenerateGetPivotDataFunction(IRange destination = null) method to generate the GETPIVOTDATA function for different worksheets.

In v5.2, we extend the capability of this function to support spilled data similar to Dynamic Array Formulas. The syntax now supports both array items as well as reference - "=GETPIVOTDATA(data_field, pivot_table, [field1, item1, field2, item2], ...)". So, in the example below, the GETPIVOTDATA function set in one of the cells using GcExcel, takes in two cities together in an Array {'New York', 'Boston'}, to find data in one go from Pivot Table as compared to the previous implementation of the function. 

Help .NET | Help Java | Demo .NET | Demo Java

Debug better with additional details in InvalidFormulaException

To provide more information to customers to solve the errors in the spreadsheet, GcExcel adds more info to the InvalidFormulaException. The exception would now include the cell's location and formula text to trace the error. So if you ever encounter this exception, you will have more info to solve the problem in your spreadsheet.

Features for SpreadJS Integration

Support for CASCADESPARKLINE formula

You can now add the CASCADESPARKLINE formula to your spreadsheets programmatically using GcExcel. Cascade Sparkline is a feature of SpreadJS. In GcExcel, Cascade Formula is now supported on SSJSON I/O. You can also add the formula to the spreadsheet and export the spreadsheet to PDF/HTML/Image. A cascade sparkline is generally used to analyze a value over time like yearly sales, total profit, net tax, etc. It is used widely in finance, sales, legal, and construction sectors, to name a few. 

The syntax of the Formula is - '=CASCADESPARKLINE(pointsRange, pointIndex, labelsRange, minimum, maximum, colorPositive, colorNegative, vertical, itemTypeRange, colorTotal)'

The code below adds a Cascade Sparkline Formula with Horizontal Bars by setting the 'vertical' property to False.

// Add a cascade sparkline with Horizontal Bars.
for (int i = 1; i < 8; i++)
{
    worksheet.Range[i, 2].Formula = "'=CASCADESPARKLINE(D5:D20, SEQUENCE(COUNTA(D5:D20),1,1,1),P5:P20,,,"#8CBF64","#D6604D",false,A5:A20)')";
}

Help .NET | Help Java | Demo .NET | Demo Java

Download GrapeCity Documents for Excel .NET and JAVA now!