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

April 22 2022

We are pleased to announce the new v5.1 release for GrapeCity Documents for Excel (GcExcel).

This new release includes updates to the existing GcExcel .NET Excel Library & API toolkit, enhancements to Excel template processing, and more features for integrating SpreadJS, our JavaScript spreadsheet. Check out key highlights below!

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 using the JsonDataSource class.

//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");

Report JSON

Check out the following resources and GcExcel Report Templates samples for the latest updates to the feature.

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

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

In the last release, we added the ImportData function to make it possible to import a specific range of data from an Excel file without loading the whole file. This function works twice as fast as importing the entire Excel file for a specific range of data. Have a look at the performance numbers here.

A new interface, Workbook.GetNames(string fileName) makes it easy for users to add a Table Name reference as an imported range instead of a range dimension. Users may only know the workbook path to be imported but not the specific worksheet name or table name. In the 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 a sheet name, table name, or range name, and these can be obtained from a new interface Workbook.GetNames(string fileName) returns an array of names, including the names of all worksheets, tables, and defined names that evaluate to a range reference.

The following example imports table data from an Excel file using the source name from the 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");

Import Data

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

If a source file has a large number of formulas, ImportData(Sheet) only reads data.
If the 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 the source file has a lot of data, ImportData(Range) only reads part of it.
And many more...

Read more about the ImportData function in the following resources.

Help .NET | Help Jav a | Demo .NET | Demo Java

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 to know the cell address corresponding to the value returned by a function.

GcExcel introduces the CELL function with the syntax "=CELL(info_type, [reference])," which can help you find relevant cell information by providing the info type you want and the reference type.

Cell Function Support

For more info, please find the resources below:

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

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. Still, since Dynamic Array Formulas are not supported in a Table, you will have to convert the Table to a normal range. This calls for the 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 won't be easy to manually perform the task of converting a Table to a range. Catering to this need, GcExcel provides a new API ITable.ConvertToRange() to convert Table to range programmatically.

Once you convert the 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 a cell reference. See the cell references in the following example:

Convert Table to Range

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 .NET | Help Java | Demo .NET | Demo Java

Modify Password of Excel Documents

When an Excel file is shared with users, they can easily edit the spreadsheet. Sometimes, a workbook, sheet, or area of a sheet may contain sensitive data that should only be accessed or changed by the appropriate personnel. 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 the workbook as WriteProtection.ReadOnlyRecommended - If true, MS Excel will recommend opening the file as read-only
Add Protect Options when saving a workbook
Check whether the 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 the password is the same as one set in Modify Password

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

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 from its neighboring cells, such as a name, phone number, or dollar amount. 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 the 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 the CsvParseResult object. In contrast, the CsvParseContext object contains the location and value information of the cell.

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

CSV Custom Parser

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 .NET | Help Java | Demo .NET | Demo Java

Specify 'ShowValuesAs' Option for 'Values' Field

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

IPivotField.Calculation to get or set a value representing 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 the following example, the '% of Grand Total' option has been set on the data of the Pivot Table:

Pivot Table

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

Support for Calculated Field in Pivot Table

If you want to add more power to your Pivot Table calculations, when you cannot 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 an 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.

Calculated Field

Check out more functions of the new ICalculatedFields API.

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

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

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

#N/A

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

More features for SpreadJS Integration

Support for Pivot table views (JSON I/O)

GcExcel supports JSON I/O of Pivot Views, a SpreadJS control feature. 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 SpreadJS, which changes the Pivot View. Such a SpreadJS JSON file can now be supported during I/O with GcExcel:

Pivot View

//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 .NET | Help Java | Demo .NET | Demo Java

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. The snapshot below is a TableSheet in SpreadJS:

TableSheet

The code below shows the simple I/O of the JSON 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 .NET | Help Java | Demo .NET | Demo Java

Numbers Fit Mode

You may have often experienced the situation where the number in a column is too wide for the field, resulting in a display that shows only number signs (pound sign "#") in the cell. This is a time-consuming process to adjust columns, especially if multiple columns require an adjustment to see the whole result.

GcExcel now supports IWorkbookView.NumbersFitMode enum with Mask and Overflow options. The Mask option would replace data with '###,' while the Overflow option will overflow data if the neighboring cell is empty.

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

Numbers Fit Mode