What's New in Document Solutions for Excel Java v8

Performance Improvements in v8.2

The v8.2 release of DsExcel delivers significant performance enhancements across formulas, style handling, and file operations.

Formula performance has been greatly improved for functions such as VLOOKUP, XLOOKUP, HLOOKUP, LOOKUP, MATCH, XMATCH, and UNIQUE, especially when searching large ranges with mixed data types. Functions like AVERAGEIF and POISSON.DIST now execute much faster, with POISSON.DIST reducing computation time by 83.96% and COMBIN reducing computation time by 70.46%. Memory consumption has also been optimized when processing templates with large data sources using ITableDataSource.

File operations are now faster when handling workbooks with large numbers of DefinedNames. For example, opening and saving a file with over 144,000 DefinedNames is now over 50% faster, reducing the DefinedName retrieval cost from 38% of execution time to just 1%.

Enjoy faster formulas, lighter memory usage, and optimized file operations with DsExcel v8.2.

Fit to Selection for Chart Sheets

The v8.2 release introduces support for setting the zoom of a chart sheet to “Fit to Selection” using the DsExcel API. With this new feature, developers can programmatically adjust the zoom of a chart sheet so that its content maximizes the available viewing area, improving readability and presentation.

This feature is especially helpful for developers building automated Excel reporting solutions, dashboards, or chart-heavy spreadsheets, as it ensures charts automatically fill the screen without manual adjustments.

The new capability is exposed through the FitSelection property in the IWorksheetView interface:

Java: boolean getFitSelection() and void setFitSelection(boolean value)

When set, this option does not modify the value of the regular Zoom property, but instead adjusts the display strategy so the chart sheet fully occupies the Excel window.

Fit Selection	Custom Selection

Help | Demo

Handle Error Values in Custom Functions

In v8.2, Document Solutions for Excel introduces the ability for custom functions to handle error values directly through the new AcceptErrors property on the CustomFunction class.

By default, formulas return an error if any parameter evaluates to an error value (such as #N/A or #VALUE!). With the AcceptErrors setting, developers can now choose to allow error values to be passed into their custom functions instead of having the formula fail immediately. This enables scenarios where developers may want to log, detect, or provide fallback behavior for error conditions rather than stopping execution.

This feature provides several benefits for developers:

• Improved flexibility: User-defined formulas can now gracefully handle invalid or unexpected inputs.
• Robust error handling: Developers can implement fallback logic instead of formulas failing outright.
• Enhanced control: Custom functions can inspect and respond to error values in ways that align with business logic.

The new API is available across platforms:

Java: setAcceptErrors(boolean acceptErrors) and getAcceptErrors()

When AcceptErrors is set to True, error values are passed to the function for evaluation. If left at its default (False), the function automatically returns an error when encountering invalid arguments. The code below highlights how to apply this new feature to create the image shown above.

Java

Workbook workbook = new Workbook();
Workbook.AddCustomFunction(new MyFunctionWithAcceptErrors("MyFuncCanAcceptErrors", true));
Workbook.AddCustomFunction(new MyFunctionWithAcceptErrors("MyFuncNotAcceptErrors", false));
IWorksheet worksheet = workbook.getActiveSheet();

// B3 cell's value is "Exist errors: #Div0"
worksheet.getRange("A3").setValue("=MyFuncCanAcceptErrors(1, 2, 1/0)");
worksheet.getRange("B3").setFormula("=MyFuncCanAcceptErrors(1, 2, 1/0)");
// B4 cell's value is "Exist errors: #Value"
worksheet.getRange("A4").setValue("=MyFuncCanAcceptErrors(1, \"test\" + 1, 3)");
worksheet.getRange("B4").setFormula("=MyFuncCanAcceptErrors(1, \"test\" + 1, 3)");
// B5 cell's value is "Exist errors: #Name, #Num"
worksheet.getRange("A5").setValue("=MyFuncCanAcceptErrors(SUME(1), 2, SQRT(-1), 4)");
worksheet.getRange("B5").setFormula("=MyFuncCanAcceptErrors(SUME(1), 2, SQRT(-1), 4)");

// B9 cell's value is error of #DIV/0!
worksheet.getRange("A9").setValue("=MyFuncNotAcceptErrors(1, 2, 1/0)");
worksheet.getRange("B9").setFormula("=MyFuncNotAcceptErrors(1, 2, 1/0)");
// B10 cell's value is error of #VALUE!
worksheet.getRange("A10").setValue("=MyFuncNotAcceptErrors(1, \"test\" + 1, 3)");
worksheet.getRange("B10").setFormula("=MyFuncNotAcceptErrors(1, \"test\" + 1, 3)");
// B11 cell's value is error #NAME?
worksheet.getRange("A11").setValue("=MyFuncNotAcceptErrors(SUME(1), 2, SQRT(-1), 4)");
worksheet.getRange("B11").setFormula("=MyFuncNotAcceptErrors(SUME(1), 2, SQRT(-1), 4)");

Help | Demo

Shape Alternative Text and Decorative Flag for Accessibility

The v8.2.0 release enhances accessibility support in Document Solutions for Excel by adding the ability to set descriptive text and decorative flags on shapes. This ensures that spreadsheets containing charts, images, slicers, or other visual objects are more accessible to individuals using screen readers or other assistive technologies.

Previously, DsExcel supported IShape.Title to provide a title for accessibility purposes. With this update, developers now have access to:

• IShape.AlternativeText — Get or set a descriptive text string for shapes, pictures, linked pictures, charts, slicers, group shapes, and controls. This text is read by assistive technologies as an alternative to the visual element.
• IShape.Decorative — Indicate that a shape is decorative and does not convey meaningful content. When set to True, both the title and alternative text are cleared automatically.

This feature helps developers create inclusive spreadsheets by providing meaningful descriptions for important visual elements, while marking purely decorative shapes so they can be skipped by screen readers.

Help | Demo

Eta-Reduced Lambda Support

DsExcel now fully supports eta-reduced lambda functions, allowing developers to write more concise and intuitive formulas when passing functions as parameters. Instead of wrapping functions in a full LAMBDA expression (e.g., >=BYROW(A1:A10, LAMBDA(a, ABS(a)))), you can now simplify them to =BYROW(A1:A10, ABS).

This enhancement makes formulas shorter and easier to read, while also enabling advanced new functions such as PIVOTBY and GROUPBY. Eta lambdas can be passed as arguments, used in defined names, and applied with built-in or custom functions. When name conflicts occur, DsExcel resolves them using a prefixing strategy to ensure clarity and consistent behavior.

The following example demonstrates using eta-reduced lambda with BYROW:

var workbook = new Workbook();
var sheet = workbook.Worksheets[0];
sheet.Range["A1:A5"].Value = 1;
sheet.Range["B1"].Formula = "BYROW(A1:A5, SUM)";

Write cleaner, more powerful formulas in Excel with eta-reduced lambda support.

Help | Demo

New Functions: GROUPBY, PIVOTBY, PERCENTOF, and TRIMRANGE

DsExcel v8.2 adds support for several powerful new Excel functions that make summarizing and cleaning data easier and more intuitive.

The GROUPBY function enables grouping along one axis with aggregation, filtering, and sorting. For example, you can summarize sales data by year using a simple formula. The PIVOTBY function extends this capability, supporting grouping along two axes for pivot-style summaries such as sales by state and year. Both functions support explicit or eta-reduced lambda functions like SUM, AVERAGE, COUNT, and the new PERCENTOF function.

PERCENTOF calculates the percentage that a subset contributes to a total, and it integrates seamlessly into GROUPBY and PIVOTBY formulas for percentage-based summaries.

Additionally, the new TRIMRANGE function helps clean up data ranges by automatically removing leading or trailing blank rows and columns. For a more concise syntax, DsExcel also supports Trim References, allowing you to replace the range colon (:) with patterns like .:. to trim both ends.

The following code shows how to use these new functions:

Java

var workbook = new Workbook();
var sheet = workbook.Worksheets[0];
sheet.Range["A2"].Value = 200;
sheet.Range["A4"].Value = 400;
sheet.Range["B2"].Formula2 = "=TRIMRANGE(A:A)";
sheet.Range["C2"].Formula2 = "=A1.:.A10";

Quickly summarize, analyze, and clean up data with the new GROUPBY, PIVOTBY, PERCENTOF, and TRIMRANGE functions in DsExcel v8.2.

Help | Demo GROUPBY | Demo PIVOTBY | Demo TRIMRANGE

New ShapeType Enumeration Values

DsExcel now extends the ShapeType enumeration to include additional shape types for more complete support when working with Excel documents. The newly supported types include Callout, FreeForm, TextBox, Graphic, and Line, enabling developers to accurately identify and manipulate a wider range of shapes without data loss.

These additions align DsExcel more closely with the Excel UI, ensuring that shapes such as callouts, freeform drawings, and vector graphics can be represented programmatically. For example, callouts are supported as AutoShapes with various styles like LineCallout1 through LineCallout4, and graphics currently support SVG vector images.

The following code shows how to create a list of all the shape types using the updated enumeration:

Java

Workbook workbook = new Workbook();
workbook.open("E:\\shapeType.xlsx");
for (IWorksheet item : workbook.getWorksheets()) 
{
    for (IShape shape : item.getShapes()) 
    {
        System.out.println(item.getName() + ": " + shape.getType());
    }
}

Work with a richer variety of shapes in Excel documents using the enhanced ShapeType enumeration.

Help | Demo

Retrieve Sheet Tabs from SpreadJS Workbooks

DsExcel now supports APIs to retrieve information about SpreadJS sheet tabs (including TableSheet, GanttSheet, and ReportSheet) when importing or exporting SJS/SSJSON files from SpreadJS. This enables back-end systems to read sheet tab details for integration with business logic such as role-based permissions and front-end coordination.

The new APIs allow developers to:

• Use IWorkbook.SheetTabs (or Workbook.SheetTabs) to access the sheet tab collection.
• Get the total number of sheet tabs with ISheetTabs.Count.
• Access a tab by index or name using ISheetTab[index] and ISheetTab[name].
• Retrieve tab details such as index (ISheetTab.Index), name (ISheetTab.Name), and type (ISheetTab.SheetType).

The following code shows how to retrieve and iterate through sheet tabs in a SpreadJS workbook:

Java

Workbook workbook = new Workbook();
workbook.open("sheettabs.sjs");
ISheetTabs sheetTabs = workbook.getSheetTabs();
System.out.println("The count of sheet tabs: " + sheetTabs.getCount());
for (ISheetTab item : workbook.getSheetTabs()) 
{
    System.out.println(item.getName());
}

Easily integrate sheet tab metadata into your back-end logic with the new SheetTabs API in DsExcel.

Help

Support for Dynamic Array Formulas with Evaluate2

DsExcel now introduces the IWorksheet.Evaluate2(string formula) method to support the evaluation of dynamic array formulas, aligning with Excel 2021/365 behavior. While the existing Evaluate method remains compatible with Excel 2019 and earlier (returning a single value), the new Evaluate2 method returns all values for formulas that spill into multiple cells.

With Evaluate2, developers can now handle formulas such as {1,2,3}, =A1#, or =UNIQUE(A1:A5), and retrieve full results across the spilled range. When a formula returns a reference (e.g., =A1:A5), both Evaluate and Evaluate2 behave the same, returning an IRange object.

The following example shows how to evaluate a dynamic array formula with Evaluate2, which returns all results at once (instead of just a single value like Evaluate would):

var workbook = new Workbook();
var sheet = workbook.Worksheets[0];
#region Init Data
sheet.Name = "API Evaluate2";
sheet.Range["B3:B11"].Value = new object[,] {
    { "Product" },
    { "Apple"},
    { "Grape"},
    { "Pear"},
    { "Banana"},
    { "Coconut"},
    { "Strawberry"},
    { "Orange"},
    { "Pineapple"} };
ITable table = sheet.Tables.Add(sheet.Range["B3:B11"], true);
ITable table1 = sheet.Tables.Add(sheet.Range["D3:D11"], true);
table.ConvertToRange();
table1.ConvertToRange();
sheet.Range["D3"].Value = "Evaluate2 results:";
sheet.Range["B:D"].AutoFit();
#endregion
var evaluateRes = sheet.Evaluate2("=LEN(B4:B11)");
sheet.Range["D4:D11"].Value = evaluateRes;
workbook.Save("res.xlsx");

The code above will result in the following:

Evaluate modern Excel dynamic array formulas programmatically with the new Evaluate2 method in DsExcel.

Help | Demo

Logging Support

DsExcel now supports a configurable logging system in the Java version, enabling developers to monitor system activity and quickly identify issues. The logging subsystem is built on Apache Commons Logging (JCL), allowing seamless integration with popular frameworks such as Log4j, Logback, and SLF4J. For daily development, using Log4j2 is recommended. 

Developers can configure the logging level to control verbosity, with supported levels being debug, info, warn, and error (in order of priority). When a log level is set, all logs of equal or higher priority are recorded, while lower priority logs are ignored.

Easily integrate DsExcel logging into your existing logging framework to track activity and troubleshoot issues effectively. Below is an example of what the contents of a log file will contain:

...
2025-07-03 16:26:15,463 DEBUG [main] aX.o (null:-1) - Save pdf of the workbook.
2025-07-03 16:26:15,466 DEBUG [main] aX.o (null:-1) - Paginate Start(Workbook)
2025-07-03 16:26:15,538 DEBUG [main] excel.bu (null:-1) - Get instance of MypdfGraphics for fontsFolderPath: null
2025-07-03 16:26:15,539 DEBUG [main] excel.bu (null:-1) - Get instance of MypdfGraphics(Use cache): com.grapecity.documents.excel.bu@54504ecd
2025-07-03 16:26:15,542 DEBUG [main] excel.bu (null:-1) - Get instance of MypdfGraphics for fontsFolderPath: null
2025-07-03 16:26:15,542 DEBUG [main] excel.bu (null:-1) - Get instance of MypdfGraphics(Use cache): com.grapecity.documents.excel.bu@54504ecd
2025-07-03 16:26:15,573 DEBUG [main] bn.dE (null:-1) - GetDigitWidthOfDefaultStyle GraphicsType: Pdf
...

See the help link below to see how to get started with logging in your Java application.

Help

Worksheet Background Image Support

DsExcel now supports Excel-like worksheet background images with full compatibility for SpreadJS SJS/SSJSON file formats. Using the IWorksheet.BackgroundPicture interface, developers can retrieve background images when they are stored in Base64 format and export them to PDF with the PrintBackgroundPicture = true option.

The following code shows how to set and retrieve a worksheet background image:

Java

Workbook workbook = new Workbook();
workbook.open("sheetbackgroundimage.sjs");
byte[] backgroundimage = workbook.getActiveSheet().getBackgroundPicture();
IWorksheet sheet2 = workbook.getWorksheets().add();
sheet2.setBackgroundPicture(backgroundimage);
workbook.save("export.xlsx");
workbook.save("export.sjs");
PdfSaveOptions options = new PdfSaveOptions();
options.setPrintBackgroundPicture(true);
workbook.save("export.pdf", options);

Ensure consistent handling of worksheet background images between SpreadJS and DsExcel with the new BackgroundPicture support.

Help | Demo

Add Cell Addresses to Exported Tables in HTML

In the v8.1 release, DsExcel adds the CellAttributeOptions dictionary property in the HtmlSaveOptions class, allowing cell attributes to be set using the CellAttribute enumeration. The CellAttribute enumeration includes the Address option, which enables DsExcel to export worksheet cell elements with the address attribute in HTML. This helps to locate each cell element in the exported HTML file.

// Create a zip file stream
FileStream outputStream = new FileStream("SaveHtmlWithCellAttributes.zip", FileMode.Create);
//create a new workbook
var workbook = new GrapeCity.Documents.Excel.Workbook();
Stream fileStream = this.GetResourceStream("xlsx\\NetProfit.xlsx");
workbook.Open(fileStream);
HtmlSaveOptions options = new HtmlSaveOptions();
// Use CellAttributeOptions to add the cell element's address attribute to the exported html file
options.CellAttributeOptions.Add(CellAttribute.Address, "address");
workbook.Save(outputStream, options);
// Close the zip stream
outputStream.Close();

Help | Demo

Clone Workbook

DsExcel Java now supports cloning a workbook using the new Workbook.clone() method. The clone() method clones the current workbook and returns a new instance of the workbook.

// Create a new workbook
Workbook workbook = new Workbook();
// Open an excel file
InputStream fileStream = this.getResourceStream("xlsx/BreakEven.xlsx");
workbook.open(fileStream);
// Clone the workbook
IWorkbook clone = workbook.clone();
// Changes made to the cloned workbook will not affect the original workbook.
clone.getWorksheets().get(0).delete();
// Save to an excel file
workbook.save("WorkbookClone.xlsx");

Help | Demo

Performance Improvements for VLOOKUP and UNIQUE Functions

In the v8.1 release, the performance of VLOOKUP and UNIQUE Functions has been improved significantly as compared to the v8 version. Have a look at the new performance numbers below and try it out for yourself with our demos -

VLOOKUP Demo | UNIQUE Demo

Features for SpreadJS Compatibility

The following SpreadJS features are supported as lossless in SJS and SSJSON file format --

1. Data charts
2. Data charts in Report sheet
3. Support for binding data manager table as a data source
4. Support SpreadJS DataRange

Have a look at the supported SpreadJS supported feature list in DsExcel.

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.

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 -

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. 

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 -

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");

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 -

1. The IWorksheet.AutoMergeRangesInfo property can retrieve information about all auto merge ranges in the current worksheet. 
2. 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. 
3. 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.
4. The AutoMergeDirection enum supports Row direction, Column direction, ColumnRow direction, RowColumn direction, None direction.
5. 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();

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})";

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";

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:

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);

Help Arrow | Help Parquet | Demo Arrow | Demo Parquet

Enhanced performance of loading large data in CSV files

Loading performance of large data in CSV files has been enhanced in v8. Large data can now be loaded in seconds. Have a look at the demo below. 

Demo