What's New in Document Solutions for Excel Java v8

DsExcel for Java v8 - December 11, 2024

Import data from object collections and Data Tables

When working with Excel sheets programmatically in business applications, one of the common task is to import data from different data sources such as classes and objects in Java. In v8 release, DsExcel adds ability to import data from following type of data sources -

Simple Enumerable
Multidimensional Array
Jagged Arrays
Custom objects
Custom objects from weakly typed collection
Custom objects with custom columns
Data from unknown type of custom objects (duck typing)
Dynamic objects
Selected DataColumns

DsExcel adds new overloads to IRange.ImportData(..) method that now also enables importing data from a data source to the range. Following overloads take in parameters as per the data source -

ImportData(IEnumerable, DataImportOptions) Method - Imports data from a data source to the range.
ImportData(DataTable, DataImportOptions) Method - Imports data from a table to the range.

The DataImportOptions parameter is an enum that provides options to handle the data

Following snapshot shows the code to import data from a data table with selected columns.

Import from multiple sources

Help | Demo

Add and manage Scenarios in What-If analysis

What-If Analysis in Excel spreadsheets is a powerful Excel feature that allows users to explore how changes in certain input values affect the outcomes of formulas within a worksheet. This feature is particularly useful for forecasting, planning, and testing different possibilities before making critical decisions. In last release, we introduced the ability to programmatically add Goal seek to spreadsheets, one of the tools of What-If Analysis.

In continuation to our support of adding What-If analysis to spreadsheets, we are excited to announce the support of Scenarios in the latest version of DsExcel. A scenario in Excel is a set of saved input values for specific cells. By switching between different scenarios, you can see how changes in certain cells impact the results of calculations in your sheet.

The new IWorksheet.Scenarios interface helps to create and manage scenarios in Excel spreadsheets. You can perform following operations with this API -

Create scenarios in the current worksheet. This allows to define:
- A scenario with a name
- Provide the range where the scenario should be added - this range would be the ‘changing cells’ range
- The list of values for ‘changing cells’
- A comment attached to the scenario
- Set option to lock a scenario
- Set option to hide a scenario
Apply/show a scenario programmatically - the values of changing cells will be applied to the worksheet, and the formulas that reference the changing cells would be recalculated.
Delete the scenario.
Get the changing cells for the scenario.
Get the values of the changing cells.
Change/modify a scenario to have a new set of changing cells and scenario values.
Lock a scenario to keep other users from modifying it
Hide a scenario so that certain scenarios are visible only to some users and hidden from others.
Option to edit scenarios on a protected worksheet. This works when the worksheet is protected, but DsExcel without any restrictions. Users can customize the operation based on the IProtectionSettings.AllowEditingScenarios and IWorksheet.Protection properties.

Following snapshot shows how to add multiple scenarios programmatically to a worksheet -

Add and manage Scenarios in What-If analysis in Java Excel Files

The scenarios can be checked in the Excel file created, by opening the Data tab->What-If Analysis->Scenario Manager.

View following resources for full details.

Help | Demo

Bind Pivot Table directly with Excel Table as data source

To maximize the effectiveness and flexibility of a Pivot Table, it needs to be bound to a Table. This is crucial when working with dynamic or expanding datasets, as you don't need to manually update the data range for the Pivot Table. In addition, when your data is in a Table, each column has a defined header, and so Pivot Table can automatically use these column names to configure Pivot Table fields.

The IPivotCaches.Create(object) method now supports passing ITable as a parameter. Just define your table and pass it as a parameter while creating IPivotCache object.

Bind Pivot Table directly with Excel Table as data source

Help | Demo

Set color in various string formats

In DsExcel, you can customize the appearance of a cell, cell border, tab, and more by using theme colors, standard colors, or custom colors. To apply these colors, DsExcel adds the StringToColor method of the new ColorUtilities class. This API helps to add standard and custom colors using various string formats including:

Color name: A predefined standard color name.
Example: "red".
RGB: rgb(r,g,b)
Example: "rgb(255,0,0)" for red color.
RGBA: rgba(r,g,b,a)
Example: "rgba(255,0,0,0.5)" for red color with 50% opacity.
Hexadecimal: #RGB or #RRGGBB
Example: "#F00" or "#FF0000" for red color.
Hexadecimal with alpha: #RGBA or #RRGGBBAA
Example: "#F00C" or "#FF0000CC" for red color with 90% opacity.

Following snapshot shows how the StringToColor method of ColorUtilities class can be used to set colors to different parts of the worksheet -

Set color in various string formats

Help | Demo

Support page number calculation operators

DsExcel now supports the use of ‘+’ and ‘-’ operators with page numbers and total number of pages in the headers and footers of worksheets, whether they are exported to PDF or printed on physical printers. This feature is available for all paginated outputs that respect custom headers and footers in IWorksheet.PageSetup property.

You can adjust page numbers or the total page count—adding or subtracting—as needed when printing multiple workbooks as a single report. See the example below to learn how to increment page numbers and the total page count by 1.

var workbook = new GrapeCity.Documents.Excel.Workbook();
var fileStream = this.GetResourceStream("xlsx\\PageSetup Demo.xlsx");
workbook.Open(fileStream);
IWorksheet worksheet = workbook.Worksheets[0];
worksheet.Range["C3"].Value = "The page numbers and the total page numbers are added by 1.";
// Set page headerfooter.
// Use &P+1 to display the page number plus one.
// Use &N+1 to display the total page number plus one.
worksheet.PageSetup.RightHeader = "Page header example, &P+1 of &N+1 ";
// Save to a pdf file
workbook.Save("ConfigHeaderFooterPageNumberCalc.pdf");

Help | Demo

New APIs to manage PivotTable

The Pivot Table features supported in DsExcel have been enhanced with new API options: HasAutoFormat, RefreshOnFileOpen, and ShowPivotTableFieldList from VSTO.

HasAutoFormat allows for auto-fit of column width when updating the Pivot Table.
RefreshOnFileOpen enables to control whether the pivot table should refresh pivot cache when the file is opened, ensuring up-to-date information.
ShowPivotTableFieldList allows to control whether to show the PivotTable field list.

These additions make working with pivot tables programmatically more powerful by simplifying data management and enhancing display options.

Following code makes the pivot table auto-fit column widths after updating -

var pivotcache = workbook.PivotCaches.Create(worksheet.Range["G1:L16"]);
// When RefreshOnFileOpen is true, the saved file will be automatically refreshed when opened with Excel
pivotcache.RefreshOnFileOpen = true;
// When ShowPivotTableFieldList is true, Excel displays the Pivot Field List when opening the file
workbook.ShowPivotTableFieldList = true;
var pivottable = worksheet.PivotTables.Add(pivotcache, worksheet.Range["A1"], "pivottable1");
worksheet.Range["J1:J16"].NumberFormat = "$#,##0.00";
// Due to the extensive text length measurements performed by autofit,
// updating the pivot table after using the HasAutoFormat interface may result in performance degradation.
// Therefore, it is recommended to use it together with DeferLayoutUpdate.
pivottable.DeferLayoutUpdate = true;
pivottable.HasAutoFormat = true;
var field_Category = pivottable.PivotFields["Category"];
field_Category.Orientation = PivotFieldOrientation.ColumnField;
var field_Product = pivottable.PivotFields["Product"];
field_Product.Orientation = PivotFieldOrientation.RowField;
var field_Amount = pivottable.PivotFields["Amount"];
field_Amount.Orientation = PivotFieldOrientation.DataField;
field_Amount.NumberFormat = "$#,##0.00";
var field_Country = pivottable.PivotFields["Country"];
field_Country.Orientation = PivotFieldOrientation.PageField;
// Because HasAutoFormat is set to true,
// the Update method of the PivotTable will automatically adjust the column widths.
pivottable.Update();
// Save to an excel file
workbook.Save("AutoFitPivotTable.xlsx");

Help | Demo

Support Pivot Table Timeline Slicer

The Timeline Slicer are interactive filters that enable quick data filtering by date, month, quarter, or year. In v8 release, DsExcel supports data and I/O (xlsx, sjs, json) of this feature.

Following code shows loading and saving of xlsx file containing Timeline Slicer -

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

Stream stream = GetResourceStream("xlsx\\TimelineSlicer.xlsx");

//Open the xlsx file that containing timeline slicer.
workbook.Open(stream, OpenFileFormat.Xlsx);
       
// Save to an excel file
workbook.Save("TimelineSlicer.xlsx");

Support Pivot Table Timeline slicer

Demo

Features for SpreadJS Compatibility

AutoMerge Cells

SpreadJS includes an Auto Merge Cells feature that automatically merges adjacent cells with duplicate text. On users' request, DsExcel also now adds new API to support this feature. Following additions have been introduced -

The IWorksheet.AutoMergeRangesInfo property can retrieve information about all auto merge ranges in the current worksheet.
The IWorksheet.AutoMerge(IRange range, AutoMergeDirection direction = AutoMergeDirection.Column, AutoMergeMode mode = AutoMergeMode.Free, AutoMergeSelectionMode selectionMode = AutoMergeSelectionMode.Source) method can add auto merge information for a range.
The AutoMergeMode enum in above method supports two modes - Free mode and Restricted mode. Read more in resources below to know more about the two modes.
The AutoMergeDirection enum supports Row direction, Column direction, ColumnRow direction, RowColumn direction, None direction.
The API also adds IncludeAutoMergedCells property, which needs to be set to true in order to export to PDF/HTML/Image/Excel/SJS/JSON.

Following code saves automatically merged cells as normal merged cells when saving to xlsx file.

// Create an xlsx file stream
FileStream outputStream = new FileStream("AutoMerge.xlsx", FileMode.Create);
//create a new workbook
var workbook = new GrapeCity.Documents.Excel.Workbook();
Stream fileStream = this.GetResourceStream("xlsx\\Regional_Product_List.xlsx");
workbook.Open(fileStream);
var worksheet = workbook.ActiveSheet;
//Add auto merge range with restricted mode and merged selection mode.
//Restricted mode, the cells with identical values are merged with the neighboring cells only if the corresponding cells in the previous column are merged in a similar way.
worksheet.AutoMerge(worksheet.UsedRange, AutoMergeDirection.Column, AutoMergeMode.Restricted, AutoMergeSelectionMode.Merged);

//The IncludeAutoMergedCells is true, the automatically merged cells will become normal merged cells.
XlsxSaveOptions options = new XlsxSaveOptions
{
    IncludeAutoMergedCells = true
};
workbook.Save(outputStream, options);
// Close the xlsx stream
outputStream.Close();

AutoMerge Cells

Help | Demo

Image sparkline export

In SpreadJS, the Image sparkline feature is an enhancement to the Image formula, which enables displaying images in different sizes, thereby accepting more image parameters compared to Excel's Image formula. Additionally the SpreadJS Image sparkline feature accepts the base64 string as a parameter to define the source of the image. The DsExcel v8 release expands the capabilities of the IMAGE function supported in v7.2, by supporting Image sparkline parameters from SpreadJS.

Following two codes add Image Sparklines using the new parameters passed into the IMAGE function. The first code clips the image, while the second code adds the image in original size.

ws.Range["A2"].Formula =
    $"=IMAGE(\"{base64Img}\",\"{altText}\",{sizing},{height},{width},{clipY},{clipX},{clipH},{clipW},{vAlign},{hAlign})";
ws.Range["B2"].Formula = $"=IMAGE(\"{base64Img}\",\"{altText}\",{sizing},{height},{width})";

Image sparkline export

Help | Demo

Support Cell Decoration API

In v8 release, DsExcel supports SpreadJS’s Cell Decoration feature. The API helps to manage cell decorations, including operations for adding, removing , and replacing cell decorations in the cells. New ICellDecoration.Decoration property has been added to IRange interface that helps to set cell decoration properties. In addition CornerFold and CellDecorationIcon classes help to add corner fold and icon of cell decoration respectively.

// Use cell decoration to hightlight the highest sales
ICornerFold cornerFold1 = new CornerFold("red", CornerPosition.LeftTop, 8);
ICellDecorationIcon cellDecorationIcon1 = new CellDecorationIcon(
    "data:image/svg+xml;base64,PHN2ZyB3aWR0aD0iMTIiIGhlaWdodD0iMTIiIHZpZXdCb3g9IjAgMCAxMiAxMiIgZmlsbD0ibm9uZSIgeG1sbnM9Imh0dHA6Ly93d3cudzMub3JnLzIwMDAvc3ZnIj4KPHJlY3Qgd2lkdGg9IjEyIiBoZWlnaHQ9IjEyIiBmaWxsPSJ0cmFuc3BhcmVudCIvPgo8cGF0aCBmaWxsLXJ1bGU9ImV2ZW5vZGQiIGNsaXAtcnVsZT0iZXZlbm9kZCIgZD0iTTcgOUg1TDUgNS45NjA0NmUtMDhIN0w3IDlaTTYgMTBDNi41NTIyOCAxMCA3IDEwLjQ0NzcgNyAxMUM3IDExLjU1MjMgNi41NTIyOCAxMiA2IDEyQzUuNDQ3NzIgMTIgNSAxMS41NTIzIDUgMTFDNSAxMC40NDc3IDUuNDQ3NzIgMTAgNiAxMFoiIGZpbGw9InJlZCIvPgo8L3N2Zz4K",
    12,
    12,
    IconPosition.OutsideRight);
worksheet.Range["C10"].Decoration = new Excel.CellDecoration(cornerFold1, new List<ICellDecorationIcon>() { cellDecorationIcon1 });
worksheet.Range["D10"].Value = "Highest";

Support Cell Decoration API

Help | Demo

Option to include/exclude binding data

DsExcel adds a new property, IncludeBindingSource, to SjsSaveOptions, XlsxSaveOptions and SerializationOptions to control whether to export the bound data to the file when exporting to SJS, SSJSON and XLSX. The option is set to True by default.

The following code demonstrates how to apply the property when saving to .sjs file using SjsSaveOptions -

var sjsSaveOptions = new SjsSaveOptions();
sjsSaveOptions.IncludeBindingSource = false;
workbook.Save("DemoPivot_dotNet.sjs", sjsSaveOptions);

This enables SpreadJS to display the final results calculated by DsExcel on the backend, eliminating the need for recalculations on the front end and avoiding the transfer of large data volumes. As a result, SpreadJS achieves faster loading times on the front end.

Help | Demo

Multiple Features Supported for Lossless I/O of SpreadJS

With the v8 release, we have enhanced the compatibility of DsExcel with .sjs and SSJSON file formats of the SpreadJS v18 version (lossless import/export). Several features have been supported on SJS/SSJSON I/O. View the complete list of supported SpreadJS features.

Document Solutions Data Viewer (DsDataViewer)

View Apache Arrow and Parquet files

Apache Arrow and Parquet files are designed to efficiently handle large-scale data processing. In v8 release, DsDataViewer now supports viewing Arrow and Parquet files.

You can browse .arrow and .parquet files either through UI:

View Apache Arrow files in JS applications

or use the new openFile(file, fileType) method to load the .arrow and .parquet file programmatically through code. The parameter file can be Blob, URL Object or URL string. The parameter fileType should be FileType.ARROW or FileType.PARQUET.

Following code loads .arrow or .parquet files programmatically in the viewer -

let viewer = new DsDataViewer("#root");
let promise = viewer.openFile("https://cdn.mescius.io/onboardingapp/docsol/dsdataviewer/arrow/mtcars.arrow", FileType.ARROW);

let viewer = new DsDataViewer("#root");
let promise = viewer.openFile("https://cdn.mescius.io/onboardingapp/docsol/dsdataviewer/parquet/mtcars.parquet", FileType.PARQUET);

Open parquet Files in JS applications