What's New in Documents for Excel, JAVA v4

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 are used to create a list of unique values (remove duplicates), sort lists, output a filtered range of data, and 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. The new workbook.AllowDynamicArray will allow you to enable/disable the dynamic array formula in the worksheet. Once enabled, you can use these seven new functions and the dynamic array formula to give the results desired by your applications:

• FILTER
• RANDARRAY
• SEQUENCE
• SINGLE
• SORT
• SORTBY
• UNIQUE

Two new errors have also been added:

#Spill! - Indicates that a formula returns multiple results but can't return these values to neighboring cells.

#Calc! - Occurs when the calculation engine encounters a scenario it does not currently support.

Help | Demo

Support for New Calc Engine Functions

GcExcel adds new functions to its feature set to help users use more Excel functions programmatically in your spreadsheets. The following new functions are supported:

1. WEBSERVICE: helps to extract data from a webservice. Help | Demo
2. FILTERXML: returns specific data from XML. Help | Demo
3. ASC: changes full-width (double-byte) letters or katakana within a character string to half-width (single-byte) characters (helpful for JP and CN text). Help | Demo
4. DBCS: converts half-width (single-byte) letters within a character string to full-width (double-byte) characters (helpful for JP and CN text). Help | Demo
5. JIS: changes half-width (single-byte) letters or katakana within a character string to full-width (double-byte) characters. Help | Demo
6. XLOOKUP: supports approximate and exact matching, reverse search, and wildcards (* ?) for partial matches and searches in horizontal/vertical ranges. Help | Demo
7. XMATCH: performs a lookup and returns a position in vertical or horizontal ranges, supports approximate and exact matching, reverse search, and wildcards (* ?) for partial matches. Help | Demo

GcExcel introduces FormulaLocal and FormulaR1C1Local properties in IRange interface that work with the new ASC, DBCS, and JIS functions to retrieve or set localized formulas in the cells of a worksheet. These new properties work only on JP and CN cultures.

The following snapshot shows the usage of the new WEBSERVICE and FILTERXML functions that retrieve weather data for a location from a webservice.

Support for External Workbook Links from the Web

In large and complex Excel files, it is common for formulas to reference other (or external) workbooks and the cells/formulas in those workbooks. Previously, GcExcel supported the Folder path for external 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. Instead, reference 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 on the web. Rather than having these workbooks copied or copying portions of the data, it is easier to reference the workbooks and/or cells within each workbook so the calculations at the home office can be done quickly and efficiently, eliminating duplicate data and work. This 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 of adding web path to external workbook links in the workbook. The IWorkbook.GetExcelLinkSources() function now supports the web path. Call the IWorkbook.UpdateExcelLinks() method to update the current workbook if data changes externally. A workbook on the web can be accessed in the following way and used in the formula property:

// Create a new workbook
Workbook workbook = new Workbook();

workbook.getWorksheets().get(0).getRange("B1").setFormula("='[SourceWorkbook.xlsx]Sheet1'!A1");
// Create a new workbook as the instance of external workbook
Workbook workbook2 = new Workbook();
workbook2.getWorksheets().get(0).getRange("A1").setValue("Hello, World!");
workbook2.getWorksheets().get(0).getRange("A2").setValue("Hello");
// Update the caches of external workbook data.
for (String item : workbook.getExcelLinkSources()) {
    workbook.updateExcelLink(item, workbook2);
}
// Save to an excel file
workbook.save("CrossWorkbookFormula.xlsx");

Check out more details: Help | Demo

Support for Charts on PDF Export Now 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 summarized data. GcExcel extends the support of charts export to PDF in Java.

Have a look at the below resources to view a tutorial on how to export Excel files with Charts to PDF in Java Apps: 

Help | Demo

Document Properties for Workbook

GcExcel now supports new API to set Document Properties on the workbook. Some of these properties will also be supported on exporting to PDF. The new IWorkbook.BuiltInDocumentPropertyCollection is a collection of built-in properties, and users can modify the property value through workbook.BuiltInDocumentProperties. property. The IWorkbook.CustomDocumentProperties is a collection of custom properties, and users can use the 'Add' method to create new custom document property or the 'AddLinkToContent(..)' method to create a new document property that can link to named cells.

Help | Demo

Get the Row and Column Grouping Information

If you have a long report in Excel, you may want to make it more manageable. There may be times when you would want to expand or collapse a group in your worksheet to hide certain information or only view detail rows/columns as needed. However, suppose you can programmatically get which rows or columns are grouped in the Excel worksheet. In that case, it may help you to expand/collapse the groups easily, rather than opening the worksheet and doing it manually. GcExcel adds new API to get row, and column grouping information through the List RowGroupInfo and List ColumnGroupInfo list types and with the help of this API, the following functionalities can be achieved:

• Get the start index of the current group
• Get the end index of the group
• Get the level of current group
• Check whether the group is collapsed or not
• Get the parent row/column of the group
• Get child row/column of the group

Finally, the Expand() or Collapse() methods can help to expand or collapse rows or columns.

Help | Demo

Copy Hidden Rows to New Range

You may want certain rows hidden; however, when you copy the rows to some other range, these hidden rows/columns should be copied too. GcExcel adds new API IRange.Copy(IRange destination, PasteOption pasteOption) to copy hidden rows/columns. The API includes the additional property AllowPasteHiddenRange, which will control whether to copy the data of hidden rows/columns or not.

Help | Demo

Control the Size of Exported JSON File

There are certain Excel files, which, if exported to JSON, produce large-sized files. It will now be possible to control the size of exported JSON files through the new SerializationOptions.IgnoreColumnRowInfoOutOfUsedRange option. This option will let you control whether to export the column row information out of the used range, which will help reduce the size of the exported JSON file.

Help

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 shape. This will give a better look to your Excel file, and in addition, this support brings GcExcel closer to the Shape support provided in MS Excel.

Help | Demo

Expand/Collapse Grouped Items in Pivot Table

You can now programmatically Expand or Collapse grouped rows or columns in Pivot Table by using the new IPivotItem.ShowDetail property to expand or collapse a grouped row/column. This will help you only view necessary details in long worksheets.

Help | Demo

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.

• Support RowCount and ColumnCount in GcExcel for JSON I/O - Help
• Get URL of a picture in JSON - Help
• Support Pivot Table of SpreadJS through JSON I/O, Excel I/O and PDF Export - Help
• Support for the following features
  • Sheet TabStrip Position and properties - Help | Demo
  • Set size of Check Box, Check Box List and Radio Box List Cells - Help | Demo
  • hoverBackColor of Button Cell Type supported on JSON I/O - Help
  • buttonBackColor supported in JSON I/O and PDF Export - Help
  • New Options frozenTrailingColumnStickToEdge and frozenTrailingRowStickToEdge on JSON I/O - Help

View supported SpreadJS feature list in GcExcel

---

Performance Improvements in Excel Template Processing

Generating Excel documents from templates is now faster than ever before. The ProcessTemplate() method is now more efficient when processing several records of the datasource. As your number of records increase, so will the speed of processing the template into the final report. This action is now several times faster than before, with up to 100,000 records tested.

Below is a complex report with 100,000 records:

Notice the performance improvement from previous builds:

Try out the reports using the templates demos below with the latest v4.1 GcExcel packages.

Demo

Parse Formula Strings into a Syntax Tree

Often times, you only need to modify certain parts of a formula, rather than the whole formula. For example, you may want to change the 'NOW()' function to a different date in the formula below:

=LET(AppUpTime, NOW() - DATE(2020, 4, 17) + 366, YEAR(AppUpTime) - 1900 - 1 & " years"

Required Formula

=LET(AppUpTime, DATE(2021, 2, 14) - DATE(2020, 4, 17) + 366, YEAR(AppUpTime) - 1900 - 1 & " years")

GcExcel v4.1 adds the new GrapeCity.Documents.Excel.Expressions namespace to parse formula expressions into a syntax tree so you can create, parse, and modify formulas. The FormulaSyntaxTree class is the entry of formula expressions API. You can perform the following functions by parsing a formula:

• Generate formula
• Modify formula
• Parse and format options
• Print syntax tree

The example below parses a formula into different parts:

Help | Demo

Ignore Formulas When Saving Excel Files

While interacting with SpreadJS, users often need to return an Excel file with values, but no formulas, to the client-side. When saving an Excel file with GcExcel, you can now set XlsxSaveOptions.IgnoreFormulas property. When set, the formula cells will be exported as only value cells.

Help | Demo

Support Open Action Script on PdfSaveOptions

Set specific actions when opening the exported PDF file. Examples include converting values to numbers for PDF form fields created with GcExcel templates when the PDF file is opened. You can now set JavaScript code in the OpenActionScript property of PdfSaveOptions class.

The following code converts a textbox field value to a number in Javascript, which is defined in OpenActionScript property.

Workbook workbook = new Workbook();
workbook.open("SampleTemplate.xlsx");

workbook.processTemplate();

PdfSaveOptions options = new PdfSaveOptions();
options.setOpenActionScript = "var fld1 = this.getField(\"num\");" + "fld1.value = fld1.value;" + "this.dirty = false;";

workbook.save("SampleTemplate.pdf", options);

Help

New Overload Method to Load JSON

Use new JSON options to open JSON files using DeserializationOptions. The return value will show an error list when opening the JSON file. The new overload has been added to Workbook.Open method.

var workbook = new Workbook(); 
var jsonErrors = workbook.Open("file.json", new DeserializationOptions { IgnoreFormula = true });

Help | Demo 

Improved Calculation Engine Performance When Setting Values

GcExcel adds the new Workbook.DeferUpdateDirtyState boolean property. When changing the value of a cell, and setting Workbook.DeferUpdateDirtyState = True, GcExcel will not update the formula cell dirty state immediately. This improves the Calculation Engine's performance when setting values in an Excel file.

Help 

More Features for SpreadJS Integration

Support for RangeTemplate Cell Type

The new RangeTemplate class will allow users to define a template of cell ranges as a single cell type and apply that template to a cell. Then, you can load different data into the template, including multiple rows and columns—allowing you to display a card view in one cell.

Once the cell type is applied to a cell or a cell range, the range template will use the cell value (or values) as a data source to resolve the binding path of the specified range. This usage is similar to other cell types.

Help | Demo

Apply Custom Objects on CheckboxList and RadioButtonList Cell Types

In a Checkboxlist or RadioButtonlist, users need the flexibility to select multiple items from the list. You can now select multiple options in a CheckboxList or RadioButtonList cell type by applying custom objects on these cell types.

Note: This result is visible only on PDF Export.

Help | Demo Checkbox | Demo Radiolist 

New ToJson and FromJSON Methods to Workbook Elements**

It's not uncommon for SpreadJS users to only need to update certain parts of the workbook. For example, a user might not want to load or save an entire workbook, but simply update the range, shape, table, or more of such data. GcExcel now features FromJson and ToJson methods to different Workbook elements to enable these actions.

For example, you can use IStyle.FromJson(string json) to update an existing named style and IStyle.ToJson() to export the named style to JSON string. These methods are included in the following classes:

• Style
• Range
• Validation
• SparklineGroup, Sparkline
• FormatConditions
  • Top10
  • AboveAverage
  • UniqueValues
  • ColorScale
  • DataBar
  • IconSetCondition
• Table
• Shape
• Slicer
• Comment
• Name
• PageSetup
• ProtectionSettings

The following code imports validation settings from JSON:

//create a new workbook
Workbook workbook = new Workbook();
IWorksheet worksheet = workbook.getActiveSheet();
worksheet.getRange("A1:B2").setValue(new Object[][]{
{1, 10 },
{5, 20 }
});

// validation from json
worksheet.getRange("A1:B2").getValidation().fromJson("{\"inputTitle\":\"tip\",\"inputMessage\":\"Value must be between 5 and 20.\",\"type\":1,\"condition\":{\"conType\":0,\"compareType\":1,\"item1\":{\"conType\":1,\"compareType\":3,\"expected\":\"5\",\"integerValue\":true},\"item2\":{\"conType\":1,\"compareType\":5,\"expected\":\"20\",\"integerValue\":true},\"ignoreBlank\":true},\"ranges\":\"A1\",\"highlightStyle\":\"{\\\"type\\\":0,\\\"color\\\":\\\"red\\\"}\"}");

Help | Demo

Get and Set Custom Object as Cell Value

Some APIs of SpreadJS support custom data types or user-defined data types. GcExcel now supports custom data types in the Range.Value property for SpreadJS interoperability purposes.

The following code assigns a HashMap to a range:

//create a new workbook
Workbook workbook = new Workbook();
IWorksheet activeSheet = workbook.getActiveSheet();
IRange a1 = activeSheet.getRange("A1");
HashMap<String, Object> dict = new HashMap<String, Object>();
dict.put("TempData1", 1);
dict.put("TempData2", "Temp value 2");
dict.put("TempData3", 3);
dict.put("TempData4", "Temp value 4");

// Set temporary data to a range
a1.setValue(dict);

// Display the custom object later
HashMap<String, Object> obj = (HashMap<String, Object>)a1.getValue();
int row = 1;
for (Map.Entry<String, Object> kv : obj.entrySet())
{
activeSheet.getRange("B" + row).setValue(kv.getKey());
activeSheet.getRange("C" + row).setValue(kv.getValue());
row += 1;
}

// Arrange
activeSheet.getColumns().autoFit();
activeSheet.getColumns().get(0).setHidden(true);

//save to an pdf file
workbook.save("SetCustomRangeValue.pdf");

Help | Demo Set Custom Object to Range | Demo Set Custom Object in Custom Function 

View Supported SpreadJS Feature list in GcExcel 

---

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 | Demo

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:

• 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 | Demo

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 | Demo

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 (int i = 4; i < 8; i++) {
    String value = "CONCAT(B" + i + ",\":\",C" + i + ")";
    worksheet.getRange("D" + i).setFormula("=BC_DataMatrix" + "(" + value + ")");
    worksheet.getRange("E" + i).setFormula("=BC_DataMatrix" + "(" + value + ", , ,\"ECC000\")");
    worksheet.getRange("F" + i).setFormula("=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 | Demo

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 | Demo

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.

Help | Demo

Get Range Address to Get Cell's Address

GcExcel v4 adds a new 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.

Workbook workbook = new Workbook();
IRange mc = workbook.getWorksheets().get("Sheet1").getCells().get(0, 0);
System.out.println(mc.getAddress()); // $A$1
System.out.println(mc.getAddress(false, true)); // $A1
System.out.println(mc.getAddress(true, true, ReferenceStyle.R1C1)); // R1C1
System.out.println(mc.getAddress(false, false, ReferenceStyle.R1C1, workbook.getWorksheets().get(0).getCells().get(2, 2))); // R[-2]C[-2]

Help | Demo

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.

PdfSaveOptions options = new PdfSaveOptions();
options.getPagePrintingEvent().addListener((sender, e) -> {
    if (e.getPageNumber() == 2) {
        e.setSkipThisPage(true);
    }
});

Help | Demo 

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.

Help | Demo

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.

Help | Demo

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.

Help | Demo

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.

• Cell padding and label - Help | Demo
• Cell types - Radio Button List and checkbox list - Help | Demo
• Range Template - Help | Demo
• Background image - Help | Demo
• Cell buttons - Help | Demo
• Cell dropdown - Help
• Format string - Help | Demo (PDF Export not supported)
• Barcodes - Help | Demo
• Show Ellipsis on Cell - Help
• Validation style - Help
• Json options - Help | Demo

Learn more about GrapeCity SpreadJS.