What's New in Documents for Excel v4

GcExcel for .NET v4.2 - August 19, 2021

Major highlights of this release include support for Dynamic Array Formula, External Workbook links from the web, and new Calculation Engine functions. In addition, many more features have been added to the API. Check out the new features below:

• Add Dynamic array formulas to Workbook
• Support for new Calc Engine functions
• Support for External Workbook Links from the web
• Support for Charts on exporting Excel to PDF now supported in GcExcel Java
• New Document Properties for Workbook
• Get the row and column grouping information
• Copy hidden rows to a new range
• Control the size of an exported JSON file
• Support Margin settings for text in a shape
• Expand/Collapse grouped rows in Pivot Table
• More features for GrapeCity SpreadJS integration

Dynamic Array Formulas

MS Excel launched the concept of Dynamic array formulas in 2018 to return multiple results to a range of cells based on one formula, also called the spilled range functionality. These formulas can be used to create a list of unique values (remove duplicates), sort a list, output a filtered range of data, and so much more. In addition, existing functions can utilize this same spill-range functionality. GcExcel adds extensive support of adding Dynamic array formulas to Excel files through code.

Added Support

• Support for the New Calc Engine Functions GcExcel adds new functions to its feature set that helps you use more Excel functions in your spreadsheets.

• Support for External Workbook Links from the Web In large and complex Excel files, formulas are common to reference other (or external) workbooks and the cells/formulas in those workbooks. With v4.2, workbooks on the web can also be referenced in an Excel workbook. When these workbooks are on the web, there is no need to manually open the workbooks and copy the data to use in the Excel file. Reference the external Workbook, eliminating any need for duplicate data entry or manual updates when data changes.

• Support for Charts on PDF Export Supported in GcExcel Java Chart utilization is an essential part of Excel spreadsheets. Most Excel documents for finance, sales, marketing, and healthcare analyze data using Excel features to extract the summarised data. GcExcel extends the support of Charts export to PDF in Java.

• Support Margin Settings for Text in a Shape The Shape.TextFrame class now supports MarginBottom, MarginLeft, MarginTop, MarginRight properties to set the margins for text in a shape. Resulting in a better Excel file look and GcExcel closer to the Shape support provided in MS Excel.

Expand/Collapse Grouped Items in Pivot Table

Expand or collapse grouped rows or columns in Pivot Table by using the new IPivotItem.ShowDetail property, this helps to view only necessary details in long worksheets.

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

GcExcel for .NET v4.1 - April 30, 2021

• Performance improvements in Excel Template processing - As your number of records increase, so will the speed of processing the template into the final report, which is now several times faster than before.
• Parse formula string into a syntax tree - Added new GrapeCity.Documents.Excel.Expressions namespace to parse the formula expressions into a syntax tree, so that you can create, parse and modify formulas.
• Ignore Formulas when saving Excel files - Set the XlsxSaveOptions.IgnoreFormulas property. When set to true, the Formula cells will be exported as just value cells.
• Support open action script on PdfSaveOptions - Convert values to numbers for PDF Form Fields added with GcExcel templates, when the PDF file is opened.
• New overload method to load JSON - JSON files can now be opened with new JSON options - DeserializationOptions. The return value will be error list of opening the JSON file

Read the full Documents 4.1 release blog.

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

  • Customer Bill Pay Form
  • Hotel Booking
  • Lease Rental Application Form
  • Time off request

• Java

  • Customer Bill Pay Form
  • Hotel Booking
  • Lease Rental Application Form
  • Time off request

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.