What's New in GrapeCity Documents for Excel v4

December 10 2020

We are pleased to announce the new v4 release for GrapeCity Documents for Excel (GcExcel), our high-speed API library for creating Excel spreadsheets. Included in this new release are updates to the existing GcExcel .NET Excel and Java library and API tools as well as the addition of the following tools:

HTML5 input type controls for processing Excel templates to PDFs
Advanced input masks and validations
Pivot Chart support
Support for iterative calculations
Cross-workbook formulas.

Download Now!

Some of the new features are described below with examples to help you include these features in your product's next release!

New PDF Form Custom Input Types in Excel Templates with Advanced Input and Validation Settings

Successful online forms need specific features to make them work well, especially for any automation purposes. An ideal online form application collects user data and validates the fields, ensuring the accurate collection of information without delays that result from incomplete form submissions. Additionally, many PDF forms like invoices, booking forms, lease agreement forms, health consultation forms, etc., require fields like - date, telephone number, email, URL, and more, common in online forms. However, these fields are not part of the standard PDF specification. Because of this, you may struggle to find the right API to help you add these fields to PDF forms. The GcExcel .NET and Java Excel API provides a way to get this done now.

The GcExcel template provides a new syntax defining HTML5 input types and validation settings on the PDF form fields. After processing the Excel template, the results are exported to a PDF file. This PDF can be viewed in the JavaScript-based GrapeCity Documents PDF Viewer (GcPdfViewer), where the PDF form is used to collect advanced inputs from users. Please note that if you want to fill and save the PDF form on the client, you will need an additional GcPdf license.

With this feature, you will get the following:

Ability to Add PDF Form Fields Not Part of the Standard PDF Specifications

In the last release of our .NET and Java Excel library, we could add PDF form fields with properties through Excel templates. In the v4 release, we extend this support by providing new HTML5 input types to PDF forms used in JavaScript apps to collect advanced input from users. The API to add these form fields is not readily available, but the GcExcel .NET and Java Excel API provides an easy way to generate PDF Forms for business applications. The following new input types are supported:

Text
Date
Time
Telephone number
Email ID
URL
Password
Month
Week
Number
Range

Together with these new input types, you can specify the following settings -

Autocomplete
Autofocus
Required
Spell check
Min/max length
String/number patterns
Default Values
and more

To see the full list of new input types and validation settings, please visit the documentation.

Easy Syntax to Define PDF Form Fields Through Excel Templates

GcExcel templates provide simple syntax to define the new PDF form fields and validation messages to display on your form fields within the Excel templates. The method is simple, define the desired input type and add associated properties, all within the "form" mustache syntax -

GcExcel Template Example for use with .NET and Java Excel Library and .NET and Java Excel API

Fig I. PDF Form with custom inputs designed through Excel Template

JavaScript Form Viewer Example from GrapeCity

Fig II. PDF Form viewed in JavaScript-based GrapeCity Documents PDF Viewer

Create an Easy and Versatile Form by Customizing Validation Settings in PDF Viewer

Once you create a PDF form with the new fields, you may want to automate the experience of viewing, filling, and submitting the PDF form through the online JavaScript-based PDF Viewer - GrapeCity Documents PDF Viewer. In the latest release of this viewer, we added support for PDF form filling to the viewer, allowing customization of field labels, fine-tuning behavior of the input controls, and creating additional input validations, even if the PDF does not have inline validation or field label information. Users can then quickly fill PDF forms with advanced input types and dropdowns on desktop, mobile, or any device.

GcPdfViewer Form Filler Example using HTML5 for responsive design

Learn more about PDF form filling in GcPdfViewer: Help | Custom input types demo | Use case demo

Online Help and Demo

Have a look at Excel template examples to read full support –

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

Use Cases

There are various areas to use this new functionality, finance, healthcare, and HR being just a few. We have many examples below to utilize, analyze, and implement within your applications. Check out the examples below and choose the "View PDF" option to see the form in our JavaScript PDF Viewer:

New Pivot Chart Support

While Pivot Tables help summarize large amounts of data, Pivot Charts go a step further in visualizing the Pivot Table data. This visualization is beneficial when a data scenario may have complex calculations.

With this new release, you can add Pivot Charts through code in Excel files to visually analyze your Pivot Tables data. You can also add, modify, or delete a Pivot Chart in existing Excel documents through code. Additionally, use the GcExcel API to customize various elements of Pivot Charts. You can add charts through the IShapes.AddChart method and set the chart's data source to a Pivot Table.

Excel Pivot Table and Pivot Chart Examples of GcExcel .NET Excel Library by GrapeCity

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

Support for Iterative Calculations in Excel Documents

Iterative calculations, most easily defined as a repetitive calculation or a calculation that utilizes previous results in the next and subsequent calculations, are best visualized by compound interest or an amortization schedule for a home mortgage. With Excel, setting the number of times this occurs is helpful, especially for large data sets. As a small example, suppose you would like to calculate the expected return on an investment 21 months from today. It would be easy enough to create the formula and copy through several cells, referencing the last cell for something like this. However, if the data set were more extensive or more complex, it would be cumbersome to do manually. In such cases, setting the options of iterative calculations in Excel is useful.

Let's turn on iterative calculation settings through code in an Excel document. Enable the Iterative Calculation options with GcExcel, through the following properties -

Use IWorkbook.Options.Formulas.EnableIterativeCalculation to get/set whether to enable iterative calculation.
Use IWorkbook.Options.Formulas.MaximumIterations to get/set the maximum iterations for iterative calculation.
Use IWorkbook.Options.Formulas.MaximumChange to get/set the maximum change for iterative calculation.

Example of Iterative Calculations in Excel using GcExcel .NET Excel API

Note: If you open the Excel document in Microsoft Excel, it will re-calculate the iterative calculation again, which will cause the calculation to be different than what you expect.

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

Add Barcodes on PDF, HTML or Image Export

Barcodes are a nearly ubiquitous form of data display and capture around the world. Therefore, it is not unusual for documents like invoices, shipping labels, etc., to contain barcodes for making data entry and sharing more accurate and less cumbersome. There are not many options for adding barcodes to documents, especially API options allowing different barcode versions.

GcExcel includes a full API to set barcodes on cells in Excel with 11 different supported barcode types (and their associated properties) and export to any document format like PDF, HTML, or image files. The API also aids in reading JSON files from SpreadJS barcode data, modifying the settings, and exporting back to JSON:

1. QRCode 2. EAN-13 3. EAN-8 4. Codabar 5. Code39 6. Code93 7. Code128 8. GS1-128 9. Code49 10. PDF417 11. DataMatrix

The way to add a barcode in a cell is simple. Just add a formula to the cell range in the following way -

=BC_<Barcode name>(comma separated Barcode specific settings)

For example, you can set DataMatrix barcode settings in the following way -

API

=BC_DataMatrix(value, color, backgroundColor, eccMode, ecc200SymbolSize, ecc200EndcodingMode, ecc00_140Symbole, structureAppend, structureNumber, fileIdentifier, quietZoneRight, quietZoneTop, quietZoneBottom)

Code

//set formula
for (var i = 4; i < 8; i++)
{
      var value = "CONCAT(B" + i + ",\":\",C" + i + ")";
      worksheet.Range["D" + i].Formula = "=BC_DataMatrix" + "(" + value + ")";
      worksheet.Range["E" + i].Formula = "=BC_DataMatrix" + "(" + value + ", , ,\"ECC000\")";
      worksheet.Range["F" + i].Formula = "=BC_DataMatrix" + "(" + value + ", , ,\"ECC200\")";
}

The snapshot below shows Data Matrix barcodes added to an Excel file for various emergency numbers and exported to PDF.

Image describing use of Barcodes in GcExcel .NET and Java Excel Library V4

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

Support for Cross-Workbook Formulas

It is common for formulas to reference other (or external) workbooks in large and complex Excel files and the cells/formulas in those workbooks. There is no need to manually open the external workbooks, copy the data, and use it in the Excel file. Instead, reference the cells in 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. Rather than having these workbooks copied or copying portions of the data, it is much easier to reference the workbooks and/or cells within each workbook. The calculations at the home office can be done quickly and efficiently, eliminating duplicate data and work. This process 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 for the cross-workbook formula to reference external workbook data through IWorkbook.GetExcelLinkSources() method. Call the IWorkbook.UpdateExcelLinks() method to update the current workbook if data changes externally.

External links in formulas using GcExcel .NET and Java Excel API by GrapeCity

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

Support for Setting Default Values for Template Cells

There are times when there is no data or empty data in a data source and no need to show empty cells in an Excel file after processing the template. In such cases, use the GcExcel defaultValue property to set the default value of a cell - it can be a value or any operator, etc., while defining the template.

Setting blank cells to a default using GcExcel .NET and Java Excel API by GrapeCity

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

Get Range Address to Get Cell's Address

Normally, to get a cell's address, you would have to write some code. For example:

worksheet.Cells[2, 3].ToString().Substring(7, 4);

However, GcExcel adds a direct method to access a cell's address. Use the IRange.Address property to get a string value that represents the range reference in the absolute A1 format. Also available is the option of providing parameters through the IRange.GetAddress method to choose an absolute or relative reference to a row or column or denote the reference style as A1 or R1C1.

//create a new workbook  
var workbook = new GrapeCity.Documents.Excel.Workbook();    

var mc = workbook.Worksheets['Sheet1'].Cells[0, 0]; 
Console.WriteLine(mc.Address); // $A$1 
Console.WriteLine(mc.GetAddress(rowAbsolute: false)); // $A1 
Console.WriteLine(mc.GetAddress(referenceStyle: ReferenceStyle.R1C1)); // R1C1 
Console.WriteLine(mc.GetAddress(referenceStyle: ReferenceStyle.R1C1,  
    rowAbsolute: false, 
    columnAbsolute: false,  
    relativeTo: workbook.Worksheets[0].Cells[2, 2])); // R[-2]C[-2]

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

Add Page Printing Events to Track Progress of Excel to PDF Conversion

Sometimes, it's unnecessary to convert an entire Excel document to PDF, or only individual pages are required while converting to PDF. It's now possible to track the progress of converting long Excel documents into a PDF with the new PagePrinting and PagePrinted events. These events can be captured to track printing progress and provide access to the HasMorePages and SkipThisPage properties, which can skip a page while printing or exit printing after only a few pages have been completed.

var options = new PdfSaveOptions(); 
options.PagePrinting += (sender, e) =>  
{  
    if (e.PageNumber == 2)  
    {  
        e.SkipThisPage = true;  
    }  
};

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

Select Multiple Worksheets

Some Excel files may contain many worksheets. However, there may be operations required on a sub-set of those worksheets, for example printing multiple sheets through one print command or adding standard content together in all worksheets. GcExcel provides a .NET and Java Excel API to select numerous worksheets with the IWorksheet.Select method and a 'replace' option to replace or extend the current selection of worksheets. You can also get the selected sheets collection using the IWorkbook.SelectedSheets property.

GcExcel .NET and Java Excel Library Example of Multiple Worksheet Selection by GrapeCity

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

Get Special Cells in a Range

Cells within Excel can contain data of varying types, including comments, formulas, errors, data validation, conditional formatting, etc. With the new Range.SpecialCells method in GcExcel .NET and Java Excel API, it's possible to find cells by type or value. Potential use cases may highlight specific cells or ranges or find and fix cells with errors.

The following example formats the cells with Dark Gray's constant values and the cells with formulas as Light Gray.

GcExcel .NET and Java Excel API example of special cell formatting

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

Disable Auto Grouping for Date/Times in PivotTable

When using PivotTables, often date/time data requires analysis. The default action of a PivotTable is to automatically group these when added to a row or column dropdown in the Pivot Table. Although this makes it easy to analyze the data across different date/time fields, it is sometimes unnecessary to have these fields grouped. With GcExcel .NET and Java Excel Library, the IWorkbook.Options.Data.AutomaticGroupDateTimeInPivotTable property is added to get/set whether to group date/time fields in PivotTables automatically or not.

GcExcel .NET and Java Excel Library example of disabling auto-grouping by GrapeCity

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 Microsoft Excel.