What's New in Documents for Excel v4
GcExcel for .NET v4 - December 10, 2020
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 -
Fig I. PDF Form with custom inputs designed through Excel Template
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.
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:
.NET
Java
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.
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.
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.
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.
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.
