What's New in Documents for Excel .NET v5

New API to add Excel Form Controls 

One way in which you can collect and present data in a spreadsheet is through the use of Form Controls added to a worksheet. Controls such as list boxes, check boxes, and buttons help structure a worksheet and manage its data. GcExcel now supports API to add Excel Form controls programmatically. You can create Excel forms with standard Form controls, customize them and additionally 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 GcExcel object model and the Form controls can be modified.

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. Following controls are supported:

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

With this support, you can also use following features -

• Each control has its own set of properties. You can set those using specific API of each control. For example, 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, Scrollbar Form Controls helps in binding the value of the controls to the linked cell and vice versa. You can get 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 duplicate a control shape or copy shapes on cells. 
• Use Form controls with Excel I/O, JSON I/O.
• Export to PDF, HTML and Images (exported as Images).

View following resources for more info.

Help .NET | Demo .NET 

Enhancements to GcExcel Templates

Paginated Templates 

If you have a certain template, where you want fixed number of rows on a page and need the same layout to repeat on next worksheet, you can choose to paginate your workbook into separate worksheets. GcExcel Templates introduces 'TemplateOptions.PaginationMode', a global boolean property which if true, can paginate your workbook into separate worksheets, while 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 count of grouped records on a worksheet. 

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

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 actual records count. Know more details in resources below.

Help .NET | Demo .NET 

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, original template sheet would appear in Sheet 1, next to expanded Template Sheet 2.

Help .NET | Demo .NET 

Support for Chart Data Table 

A data table in a chart is useful in order to view/analyze data in the chart conveniently. In Excel, data tables are displayed beneath an Excel chart. This is specially useful so that both the data and chart can be analyzed together, rather than scrolling at different location within the sheet or other sheets to match chart data with the source data. Due to such advantages, GcExcel introduces two new properties - IChart.HasDataTable that sets true or false, whether to add Data Table to Chart or not, IChart.DataTable that represents the data table of the chart which can provide various options to set in 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 Data Table can be customized. Have a look on 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 | Demo .NET 

Add Calculated Item in Pivot Table

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

• IPivotField.CalculatedItems() to get a CalculatedItems collection that represents 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 following API to set the solve order -

• IPivotFormula is a object that represents the content and solve 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 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 value $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 | Demo .NET 

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 JsonDataSource class which accepts a Json string as data source. Following example code imports data from 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 | Demo .NET

IsVolatile property support in Custom function 

When using custom functions, GcExcel stores cache of the formulas in the same column which have same name and parameters. This makes the same formula to be be 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 custom function or not. 

Following example demonstrates how to create a custom function for generating GUID. To generate a unique GUID every time, custom function should not be using cache. Hence, 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 | Demo .NET 

Get accurate range boundary

GcExcel .NET adds the new CellInfo.GetAccurateRangeBoundary(..) method that returns more accurate value of range boundary as 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 picture for following -

• Use IRange.ToImage(string filename) method to export range to SVG picture.

• Use ISheet.ToImage(string filename) method to export sheet to SVG picture.

• Use IShape.ToImage(string filename) method to export shape to SVG picture.

GcExcel will also support spreadsheets with SVG images in following situations -

• Lossless import and export of excel files 

• JSON I/O

• Export to PDF/HTML/Image file.

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

View following resources for more info.

Help .NET | Demo .NET

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 to improve performance of formula calculations. 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 then be used further within the LET function, thereby making calculation only once, while re-using the result later. You no longer need to remember what the underlying logic of the reference is. GcExcel adds this LET function to it's list of supported Functions. If you have several sheets repeating calculations over and over, 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, LET function reduces the number of times VLOOKUP is calculated from 4 times 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.

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 following resources for more info.

Help .NET | Demo .NET 

GetPivotData Formula Supports Spilled data

In 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 example below, 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

In order to provide more details to customer to solve the errors in the spreadsheet, GcExcel adds more info to the InvalidFormulaException. The exception would now include 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 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 | Demo .NET

GcExcel Report Templates Enhancements

With the new v5.1 release, we have made enhancements to GcExcel Report Templates to cover more scenarios and Excel features. Also, report generation from Templates will be much faster than before.

In addition, it is also now possible to bind Report Templates directly with a JSON data source file. GcExcel introduces JsonDataSource class which will help you directly bind JSON file as a data source for GcExcel Report Template. 

//create a new workbook
var workbook = new GrapeCity.Documents.Excel.Workbook();
 
//Load template file Template_FamilyInfo.xlsx from resource
var templateFile = GetResourceStream("xlsx\\Template_FamilyInfo.xlsx");
workbook.Open(templateFile);
 
//Get data from json file
string jsonText = string.Empty;
using (Stream stream = GetResourceStream("Template_FamilyInfo.json"))
using (StreamReader reader = new StreamReader(stream))
{
    jsonText = reader.ReadToEnd();
}

// Create a JsonDataSource
var datasource = new JsonDataSource(jsonText);
 
//Add data source
workbook.AddDataSource("ds", datasource);

//Invoke to process the template
workbook.ProcessTemplate();
 
// Save to an excel file
workbook.Save("jsonfile.xlsx");

Help | Demo 

Import Data Function to Import Table, Range, or Worksheet from Excel Files

In last release, we added a new function ImportData to import specific range data from an Excel file, without loading the whole Excel file. This function works twice faster than importing the whole Excel file just for specific range of data. Have a look on the performance numbers here.

Consider making it easy for users to add a Table Name reference as an imported range, instead of a range dimension. It may be possible that users may only know the workbook path to be imported, but not the specific worksheet name or table name. In v5.1 release, we make it possible to use Workbook.ImportData(string fileName, string sourceName) function to import data from a specified source. The source can be sheet name, table name, or range name and these can be obtained from a new interface Workbook.GetNames(string fileName) that returns an array of names which includes the names of all worksheets, tables, and defined names that evaluate to a range reference.

Following example imports table data from an Excel file, by using the source name from GetNames method. 

//create a new workbook
var workbook = new GrapeCity.Documents.Excel.Workbook();
 
// Open an excel file.
var fileStream = GetResourceStream("xlsx\\AgingReport.xlsx");
 
// Get the possible import names in the file.
// The names[0] and names[1] are sheet names: "Aging Report", "Invoices".
// The names[2] and names[3] are table names: "'Aging Report'!tblAging", "Invoices!tblInvoices".
var names = GrapeCity.Documents.Excel.Workbook.GetNames(fileStream);
 
// Import the data of a table "'Aging Report'!tblAging" from the fileStream.
var data = GrapeCity.Documents.Excel.Workbook.ImportData(fileStream, names[2]);
 
// Assign the data to current workbook.
workbook.Worksheets[0].Range[0, 0, data.GetLength(0), data.GetLength(1)].Value = data;
         
// Save to an excel file
workbook.Save("importdatafortable.xlsx");

It will be faster to use ImportData function in situations like following:

• If a source file has a large number of formulas, ImportData(Sheet) only reads data.
• If source file has multiple sheets, but the user only wants the data of one of the sheets, ImportData(Sheet) will only read the data of this sheet.
• If source file has a lot of data, ImportData(Range) only reads part of it.
• and many more..

Read more about ImportData function in following resources.

Help | Demo 

New CELL function support

Sometimes you may want to perform certain operations in your Excel file by finding the Cell information of a particular cell. The information you want could be of any nature like cell address, color, contents, filename, formats etc.  For example, you may want know cell address corresponding to the value returned by a function. GcExcel introduces CELL function with syntax "=CELL(info_type, [reference])" which can help you find relevant cell information by providing the info type you want and the reference type.

For more info, please find below resources.

Help | Demo 

Convert Table to Range

Having data as a Table in Excel allows you to enjoy advanced features when working with data. However, sometimes those features may interfere when manipulating your data the way you want. Tables can also be slower to work with because of advanced functionality. Many Excel files store data only for archival purposes or may only need style and data, without the Table functionality.  There are also cases where you need to apply Dynamic Array Formulas in a Table column, but since Dynamic Array Formulas are not supported in a Table, you will have to convert the Table to a normal range. This calls for need to convert a table to a range in Excel files.

Imagine you have 1000s of such files where you want to work with the Table data but cannot do so until it is converted to a normal range. It will be difficult to manually perform this task of converting Table to range. Catering to this need, GcExcel provides new API ITable.ConvertToRange() to convert Table to range programmatically. Once you convert Table to a range, the table features are no longer available in the range of data however style and data would be retained. The table reference in formulas on the worksheet will be converted to cell reference. See the cell references in following example.

workbook.Open("Orders.xlsx");
IWorksheet worksheet = workbook.Worksheets[0];
// Add table.
ITable table = worksheet.Tables.Add(worksheet.Range["A9:D14"], true);
 
// Convert table to range.
table.ConvertToRange();
 
// Save to an excel file
workbook.Save("converttorange.xlsx");

Help | Demo 

Modify Password of Excel Documents

When an Excel file is shared with users, they can easily edit the spreadsheet. Sometimes, if you have some data that you do not want to be tampered, Excel provides an option to protect it and make it read-only. GcExcel adds IWorkbook.WriteProtection class to set protect workbook options when saving a workbook. With this new feature, you can:

• Save workbook as WriteProtection.ReadOnlyRecommended - If true, MS Excel will recommend to open the file as read-only.
• Add Protect Options when saving a workbook
• Check whether document has a Modify Password set
• Set a Modify Password
• Set the name of the user who has write permission for the workbook
• Check if password is same as one set in Modify Password.

Help | Demo 

CSV Custom Parser

CSV files are a very common file format that different applications and systems use to share data. Applications store CSV files as simple text characters; a comma separates each data element, such as a name, phone number or dollar amount, from its neighbouring cells. Because of CSV’s simple format, and sharing data among different applications, certain data needs to be parsed and converted to different formats as per requirements of different applications or cultures.

GcExcel introduces ICsvParser for users to parse data as per their own rules.  The ICsvParser.Parse(CsvParseResult csvParseResult, CsvParseContext context) method can pass the desired format which user wants to customize in CsvParseResult object, while the CsvParseContext object contains the location and value information of the cell. 

Following example converts Date Format mm/dd/yyy to yyyy/mm/dd:

public class CustomParser : ICsvParser
{
    public void Parse(CsvParseResult csvParseResult, CsvParseContext context)
    {
        if (context.Text.StartsWith("00"))
        {
            csvParseResult.Value = context.Text;
        }
        else if (csvParseResult.NumberFormat.Equals("mm/dd/yyyy"))
        {
            csvParseResult.NumberFormat = "yyyy/mm/dd";
        }
    }
}

Help | Demo 

Specify 'ShowValuesAs' Option for 'Values' Field in Pivot Table

GcExcel now supports 'Show Values As' option of MS Excel, which helps in displaying values in different ways in a Pivot Table. GcExcel introduces:

• IPivotField.Calculation to get or set a value that represents the type of calculation performed by the specified field.
• Use IPivotField.BaseField to get or set the base field for a custom calculation. This property is valid only for data fields.
• Use IPivotField.BaseItem to get or set the item in the base field for a custom calculation. This property is valid only for data fields.

In following example, '% of Grand Total' option has been set on the data of Pivot Table:

Help | Demo 

Support for Calculated Field in Pivot Table

If you want to add more power to your Pivot Table calculations, when you are not able to achieve calculations using simple summary functions, a Calculated Field can help you perform calculations on one or more other fields in your data source. GcExcel adds ICalculatedFields interface which you can use to create or remove calculated fields in a Pivot table. Additionally, IPivotTable.CalculatedFields() method can get CalculatedFields collection with all the Calculated Fields in the specified PivotTable report while the new IPivotField.Formula can get or set the Calculated Field formula.

Check out more functions of new ICalculatedFields API.

Help | Demo 

Support for "Show #N/A as an empty cell" in Chart

At times, you have some data where cells have #N/A value. So that you can choose whether this data is plotted as an empty cell on chart or not plotted, GcExcel introduces the new IChart.DisplayNaAsBlank property which can be set true of false. Check out the difference between the two in the following example:

Help | Demo

More features for SpreadJS Integration

Support for Pivot Table Views (JSON I/O)

GcExcel supports JSON I/O of Pivot Table Views which is a feature of SpreadJS control. SpreadJS stores the current view of the Pivot Table as a Pivot View which the pivot table uses to return the state of the pivot table's view records. The snapshot you see below is of SpreadJS which changes the Pivot View. Such a SpreadJS JSON file can now be supported during I/O with GcExcel:

//create a new workbook
var workbook = new GrapeCity.Documents.Excel.Workbook();
 
Stream fileStream = this.GetResourceStream("json\\PivotTableViews.json");
workbook.FromJson(fileStream);
         
// Create a ssjson file stream
FileStream outputStream = new FileStream("pivottableviews.ssjson", FileMode.Create);
 
workbook.ToJson(outputStream);
 
// Close the ssjson stream
outputStream.Close();

Help | Demo 

Support for TableSheet (JSON I/O)

GcExcel supports JSON I/O of TableSheet which is a feature of SpreadJS. TableSheet is a fast, data-bound table view with grid-like behavior and a spreadsheet user interface (see this blog for details). Snapshot below is a TableSheet in SpreadJS: 

Code below shows simple I/O of ssjson file above.

//create a new workbook
var workbook = new GrapeCity.Documents.Excel.Workbook();
 
Stream fileStream = this.GetResourceStream("json\\TableSheets.json");
workbook.FromJson(fileStream);
         
// Create a ssjson file stream
FileStream outputStream = new FileStream("tablesheets.ssjson", FileMode.Create);
 
workbook.ToJson(outputStream);
 
// Close the ssjson stream
outputStream.Close();

Help | Demo 

Numbers Fit Mode

You may have often experienced that when a column width is not enough to fit data, it shows '###' in the cell. This can be annoying at times when you have to manually adjust several columns in the Excel file, to view the data completely. GcExcel now supports  IWorkbookView.NumbersFitMode enum with Mask and Overflow options. The Mask option would replace data with '###', while Overflow option will overflow data if neighboring cell is empty. 

This option works only with SpreadJS JSON I/O or when exporting to PDF, HTML or Image. Following snapshot shows the NumbersFitMode property applied on Column B with Overflow enum, using GcExcel API. The snapshot shows the property as being reflected in SpreadJS: 

Help | Demo