What's New in Document Solutions for Excel .NET v9

New Workbook and Excel I/O Features in Document Solutions for Excel v9.2

Document Solutions for Excel v9.2 expands the types of workbook content developers can preserve and manage programmatically. These improvements include lossless handling of Excel equation objects, public APIs for workbook-level Custom XML parts, visibility control for defined names, and better support for grouped Excel Form Controls.

Support for Excel Equation Symbols and Office Math Structures

Document Solutions for Excel v9.2 improves XLSX round-tripping for workbooks that contain mathematical equations. Developers can open and save an XLSX file containing equation objects while preserving the original Office Math structures and associated Shape Format settings.

Equation objects also remain intact when developers perform common workbook and worksheet operations, including copying worksheets, cloning workbooks, and inserting or deleting rows and columns near anchored equations. This makes it easier to process technical, scientific, educational, and engineering workbooks without losing specialized equation content.

Preserve Excel Equation Objects During XLSX Processing

C#:

var workbook = new Workbook();
workbook.Open("Equations.xlsx");

// Perform workbook or worksheet operations as needed.
workbook.ActiveSheet.Range["A1:A2"].EntireRow.Insert();

workbook.Save("Equations-RoundTrip.xlsx");

Custom XML Part API Support

Document Solutions for Excel v9.2 introduces workbook-level APIs for working with OOXML Custom XML parts. This is useful for applications that store business metadata, integration payloads, macro-related data, or other XML content inside XLSX and XLSM files.

The new ICustomXmlPartCollection and ICustomXmlPart APIs allow developers to add, retrieve, enumerate, update, and remove Custom XML parts. Each part exposes a persistent logical ID and raw byte content. The XML bytes are stored without transformation, helping applications round-trip the original payload without changing its encoding or structure.

Custom XML parts are loaded automatically when a workbook is opened and are saved as standard OOXML package parts. This removes the need to modify the workbook’s ZIP or OPC package directly.

Add and Manage Custom XML Parts in Excel Workbooks

C#:

var workbook = new Workbook();
ICustomXmlPartCollection customXmlParts = workbook.CustomXmlParts;

ICustomXmlPart customerPart = customXmlParts.Add();
customerPart.Data = Encoding.UTF8.GetBytes(
    "<customer><name>Grace Green</name><region>West</region></customer>");

string partId = customerPart.Id;
ICustomXmlPart partById = customXmlParts[partId];

partById.Data = Encoding.UTF8.GetBytes(
    "<customer><name>Grace Green</name><region>East</region></customer>");

workbook.Save("CustomXmlParts.xlsx");

Help | Demo

Support for Hidden Defined Names

Document Solutions for Excel v9.2 adds public API support for controlling whether a defined name is visible in Excel’s Name Manager. Internal helper names can remain available to formulas, templates, validation rules, and automation logic while being hidden from workbook users.

The new IName.Visible property supports both workbook-scoped and worksheet-scoped names. Newly created names remain visible by default. Setting Visible to false maps to the existing hidden state in the XLSX model and preserves that setting when the workbook is reopened in Excel.

Hidden defined-name visibility is not preserved in SJS or SSJSON because SpreadJS does not currently retain that state.

Create Hidden Workbook and Worksheet Defined Names

C#:

// Create a new workbook.
var workbook = new Workbook();
IWorksheet worksheet = workbook.Worksheets[0];
worksheet.Name = "Sheet1";

// Add two workbook-scoped defined names and set one of them to be invisible.
IName workbookName = workbook.Names.Add("HiddenWorkbookName", "=Sheet1!$A$1");
workbookName.Visible = false;
IName visibleWorkbookName = workbook.Names.Add("VisibleWorkbookName", "=Sheet1!$B$1");
visibleWorkbookName.Visible = true;

// Add two worksheet-scoped defined names and set one of them to be invisible.
IName worksheetName = worksheet.Names.Add("HiddenWorksheetName", "=Sheet1!$C$1");
worksheetName.Visible = false;
IName visibleWorksheetName = worksheet.Names.Add("VisibleWorksheetName", "=Sheet1!$D$1");
visibleWorksheetName.Visible = true;

// Save the workbook.
workbook.Save("HiddenDefinedNames.xlsx");

After setting the visibility status to “False”, the worksheet and workbook names will be hidden from Excel’s Name Manager:

Help | Demo

Support for Form Controls Inside Group Shapes

Document Solutions for Excel v9.2 improves support for Excel Form Controls contained inside regular, nested, or mixed shape groups. Group hierarchy, control properties, position, size, and control collection access are preserved across XLSX, SJS, and SSJSON workflows.

Supported grouped controls include Button, CheckBox, OptionButton, DropDown, ListBox, Spinner, ScrollBar, GroupBox, and Label. Form Controls appear in IShape.GroupItems as ShapeType.FormControl, while remaining accessible through IWorksheet.Controls.

Existing APIs now work more consistently with grouped controls. Developers can group controls with other shapes, duplicate or delete a group, copy or cut an individual grouped control, and ungroup the collection back into top-level shapes. Grouped Form Controls are also rendered during PDF, HTML, and image export. When PdfSaveOptions.FormFields is enabled, supported controls inside a group can be exported as interactive PDF form fields.

This enhancement applies to Excel Form Controls. ActiveX controls and OLE objects inside groups are outside the scope of this feature.

Group Excel Form Controls Programmatically

C#:

var workbook = new Workbook();
IWorksheet worksheet = workbook.Worksheets["Sheet1"];

var button = worksheet.Controls.AddButton(30, 35, 110, 28);
button.Text = "Approve";

var checkBox = worksheet.Controls.AddCheckBox(30, 75, 120, 22);
checkBox.Text = "Reviewed";
checkBox.IsChecked = true;

var label = worksheet.Controls.AddLabel(55, 105, 100, 18);
label.Text = "Grouped together";

worksheet.Shapes.Range[new[]
{
    button.ShapeRange[0].Name,
    checkBox.ShapeRange[0].Name,
    label.ShapeRange[0].Name
}].Group();

workbook.Save("GroupFormControlShapes.xlsx");

Help | Demo

---

PivotTable Grouping Support

Group PivotTable Fields by Date, Numeric Range, or Selected Items

Document Solutions for Excel v9.2 adds comprehensive PivotTable grouping support for date fields, numeric fields, and manually selected items. Group definitions are preserved through PivotTable refreshes when the source data remains compatible, and grouping information can round-trip through XLSX, SJS, and SSJSON.

Date grouping uses PivotFieldDateGroupOptions and can group values by seconds, minutes, hours, days, months, quarters, or years. When a date field is located in the row or column area, generated group fields can be added to the same layout automatically. Developers can control this behavior through AddGeneratedFieldsToLayout.

Numeric grouping uses PivotFieldNumberGroupOptions to define the start value, end value, and interval for generated ranges. Numeric grouping does not create an additional field; instead, it replaces the displayed items with range-based buckets.

Manual grouping uses PivotFieldCustomGroupOptions to combine selected PivotItems into a named group. Manual groups can be nested, and developers can use the group name or IPivotItem.Caption to customize displayed labels.

The new IPivotField.Ungroup() method removes grouping and restores the original source layout. When multiple PivotTables share the same IPivotCache, grouping and ungrouping changes are reflected across the dependent PivotTables. Grouping is not supported on a PivotCache containing calculated items.

Example: Group PivotTable Values into Numeric Ranges

C#:

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

object[,] sourceData = new object[,]
{
    { "Order ID", "Product", "Shipping Fee", "Sales" },
    { 1001, "Apple", 18d, 120d },
    { 1002, "Banana", 42d, 95d },
    { 1003, "Carrot", 68d, 150d },
    { 1004, "Dates", 85d, 90d },
    { 1005, "Eggplant", 110d, 180d },
    { 1006, "Fig", 135d, 160d },
    { 1007, "Grapes", 170d, 140d },
    { 1008, "Honey", 205d, 210d },
    { 1009, "Ice Tea", 28d, 80d },
};

IWorksheet worksheet = workbook.Worksheets[0];
worksheet.Range["G1:J10"].Value = sourceData;
worksheet.Range["I2:J10"].NumberFormat = "$#,##0.00";

IPivotCache pivotcache = workbook.PivotCaches.Create(worksheet.Range["G1:J10"]);
IPivotTable pivottable = worksheet.PivotTables.Add(pivotcache, worksheet.Range["A1"], "numberGroupPivot");

IPivotField fieldShippingFee = pivottable.PivotFields["Shipping Fee"];
fieldShippingFee.Orientation = PivotFieldOrientation.RowField;

IPivotField fieldSales = pivottable.PivotFields["Sales"];
fieldSales.Orientation = PivotFieldOrientation.DataField;
fieldSales.NumberFormat = "$#,##0.00";

fieldShippingFee.Group(new PivotFieldNumberGroupOptions
{
    Start = 0d,
    End = 250d,
    AutoStart = false,
    AutoEnd = false,
    Interval = 50d,
});

worksheet.Range["A:J"].EntireColumn.AutoFit();
        
// Save to an excel file
workbook.Save("NumberGroup.xlsx");

From the code above, the following grouping output is produced:

Help | Demo

---

New Export Features in Document Solutions for Excel v9.2

Document Solutions for Excel v9.2 improves exported output for modern Excel charts, SpreadJS cell types, and CJK phonetic annotations. Histogram and Pareto charts can now be exported to PDF, image, and HTML, while File Upload cell types and RubyText receive specialized rendering support.

Support for Histogram Chart Export

Document Solutions for Excel v9.2 adds support for exporting Histogram charts to PDF, image, and HTML. The export engine supports category bins, automatic bins, custom bin width, custom bin count, and configurable overflow and underflow bins.

For category bins, negative source values are excluded to align with Excel’s exported result. Automatic binning may not produce the exact same intervals as Excel because the applications can use different automatic binning heuristics, but Document Solutions for Excel produces stable and visually similar output. Explicit settings such as bin width, bin count, overflow values, and underflow values are handled directly.

Export Excel Histogram Charts to PDF, Image, and HTML

C#:

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

IWorksheet worksheet = workbook.Worksheets[0];

worksheet.Range["A1:B11"].Value = new object[,]
{
    { "Complaint Category", "Complaint Change" },
    { "Too noisy", -180 },
    { "Overpriced", 240 },
    { "Food is tasteless", -75 },
    { "Food is not fresh", 95 },
    { "Food is too salty", 130 },
    { "Not clean", 210 },
    { "Unfriendly staff", -120 },
    { "Wait time", 160 },
    { "No atmosphere", -40 },
    { "Small portions", 320 }
};
worksheet.Range["A:B"].Columns.AutoFit();
worksheet.Tables.Add(worksheet.Range["A1:B11"], true);

// Create a histogram chart.
IShape shape = worksheet.Shapes.AddChart(ChartType.Histogram, worksheet.Range["A13:G28"]);
shape.Chart.SeriesCollection.Add(worksheet.Range["A1:B11"]);
shape.Chart.ChartTitle.Text = "Histogram of Complaint Changes";
shape.Chart.HasLegend = true;

IChartGroup chartGroup = shape.Chart.ChartGroups[0];
chartGroup.BinsType = BinsType.BinsTypeBinSize;
chartGroup.BinWidthValue = 150;
chartGroup.BinsOverflowEnabled = true;
chartGroup.BinsOverflowValue = 200;

ISeries series = shape.Chart.SeriesCollection[0];
series.HasDataLabels = true;
series.DataLabels.ShowValue = true;
        
// Save to a pdf file
workbook.Save("HistogramChartPdf.pdf");

The exported Histogram looks as follows:

Help | Demo

Support for Pareto Chart Export

Document Solutions for Excel v9.2 adds support for exporting Pareto charts to PDF, image, and HTML. Pareto charts combine descending columns with a cumulative percentage line, helping teams identify the categories that contribute most heavily to a result, such as product defects, customer complaints, or process failures.

Pareto charts use the same bin-materialization rules as Histogram charts. Negative values are excluded for category bins, the materialized bar counts are sorted in descending order, and the cumulative percentage line is calculated from the running total of those sorted counts.

Export Excel Pareto Charts to PDF, Image, and HTML

C#:

var workbook = new Workbook();
IWorksheet worksheet = workbook.Worksheets[0];

worksheet.Range["A1:B11"].Value = new object[,]
{
    { "Complaint", "Count" },
    { "Too noisy", 270 },
    { "Overpriced", 789 },
    { "Food is tasteless", -65 },
    { "Food is not fresh", 90 },
    { "Food is too salty", 150 },
    { "Not clean", 300 },
    { "Unfriendly staff", -120 },
    { "Wait time", 109 },
    { "No atmosphere", 450 },
    { "Small portions", 621 }
};

IShape shape = worksheet.Shapes.AddChart(
    ChartType.Pareto, 260, 20, 520, 320);
shape.Chart.SeriesCollection.Add(worksheet.Range["A1:B11"]);

IChartGroup chartGroup = shape.Chart.ChartGroups[0];
chartGroup.BinsType = BinsType.BinsTypeBinSize;
chartGroup.BinWidthValue = 200;
chartGroup.BinsOverflowEnabled = true;
chartGroup.BinsOverflowValue = 700;

workbook.Save("ParetoChart.pdf");

Below is a screenshot of the exported Pareto chart:

Help | Demo 

Support for Exporting SpreadJS File Upload Cell Types

Document Solutions for Excel v9.2 adds PDF, image, and HTML export support for the SpreadJS File Upload cell type. The exporter handles image attachments, non-image attachments, and empty upload states so output more closely reflects the original SpreadJS workbook.

Image attachments are rendered inside the File Upload content area while respecting margins, resized rows and columns, and button-layout constraints. Non-image attachments are represented by a file icon and attachment name, including custom file icons stored through spread.options.builtInFileIcons in SJS or SSJSON. Empty File Upload cells are rendered as rounded, dashed placeholders with localized upload text based on the workbook culture.

Export SpreadJS File Upload Cells to PDF

C#:

Workbook workbook = new Workbook();
workbook.Open("FileUpload.json");
workbook.Save("FileUpload.pdf");

Help | Demo

Support for CJK RubyText in PDF and Image Export

Document Solutions for Excel v9.2 adds PDF and image export support for Excel RubyText, also known as phonetic text, in Chinese, Japanese, and Korean culture contexts. When a workbook already contains RubyText and the cell enables phonetic display, the annotation is rendered with the normal cell text in the exported output.

The exporter supports RubyText alignment, wrapping, rich text, overflow, merged cells, rotation, vertical text, ShrinkToFit, page scaling, and AutoFit scenarios. RubyText uses its own phonetic font styling and is positioned against the final displayed location of the annotated base text after formatting and layout are applied.

The feature uses phonetic data already stored in the workbook. It does not add public APIs for creating RubyText, generating kana or pinyin, or evaluating the PHONETIC() function. HTML export is not included in this enhancement.

Export Excel RubyText to PDF

C#:

var workbook = new Workbook();
workbook.Open("RubyText.xlsx");
workbook.Save("RubyText.pdf");

The Ruby exported text will appear as follows:

Chinese Ruby Text	Japanese Ruby Text	Korean Ruby Text

Help | Demo

---

Security and Formula-Handling Improvements

Configure How Invalid Formulas Are Handled During Workbook Loading

Document Solutions for Excel v9.2 introduces the InvalidFormulaHandling option, giving developers control over how syntactically invalid formulas are handled when XLSX, XLSM, XLTX, SJS, and SSJSON content is loaded.

An invalid formula is one that cannot be parsed into a valid formula structure, such as a formula with mismatched parentheses, an incomplete function call, or an operator without an operand. Previously, XLSX-family files threw an exception when invalid formulas were encountered, while SJS and SSJSON used format-specific discard behavior.

The new enumeration provides four choices:

• Throw raises an exception when an invalid formula is encountered.
• Discard removes both the invalid formula and its cached value.
• DiscardFormulaOnly removes the formula but preserves the cached value.
• Preserve retains the original invalid formula text for round-trip I/O and returns the cached value when one exists; otherwise, the calculated result is #VALUE!.

XlsxOpenOptions, XlsmOpenOptions, and XltxOpenOptions use Throw by default. SjsOpenOptions uses Discard by default. DeserializationOptions.InvalidFormulaHandling is nullable so existing SJS and SSJSON deserialization behavior remains unchanged until a value is selected explicitly.

When an option is set explicitly for JSON deserialization, normal and shared formulas follow the same selected behavior. Invalid formulas preserved through this feature can also be retained when saving back to XLSX, XLSM, XLTX, SJS, or SSJSON.

Open and Preserve a Workbook Containing Invalid Formulas

C#:

var workbook = new Workbook();
var options = new XlsxOpenOptions
{
    InvalidFormulaHandling = InvalidFormulaHandling.Preserve
};

workbook.Open("InvalidFormulas.xlsx", options);
workbook.Save("InvalidFormulas-Preserved.xlsx");

Help | Demo

Help Reduce CSV Formula-Injection Risk During Export

Document Solutions for Excel v9.2 adds the opt-in CsvSaveOptions.EscapeFormulaLikeValues property. This option helps applications export untrusted or user-generated data as CSV without allowing formula-like text to be interpreted automatically as a spreadsheet formula when the file is opened.

When enabled, Document Solutions for Excel checks the first character of each exported value. Values beginning with =, +, -, @, their full-width variants, a tab, a carriage return, or a line feed are prefixed with an apostrophe so spreadsheet applications treat them as text.

The option is disabled by default to preserve existing behavior. It sanitizes the field text before the existing CSV quoting rules are applied and does not change the meaning of QuoteColumns or ValueQuoteType.

Escape Formula-Like Values During CSV Export

C#:

var workbook = new Workbook();
var worksheet = workbook.ActiveSheet;

worksheet.Range["A1"].Value = "=1+1";
worksheet.Range["A2"].Value = "+SUM(1,1)";

var options = new CsvSaveOptions
{
    EscapeFormulaLikeValues = true,
    ValueQuoteType = ValueQuoteType.Always
};

workbook.Save("export.csv", options);

Help | Demo

---

SpreadJS Interoperability Improvements

AI Function Prefix Compatibility Between Document Solutions for Excel and SpreadJS

Document Solutions for Excel v9.2 improves interoperability for AI formulas stored in SJS and SSJSON files. SpreadJS and Document Solutions for Excel use different prefixes for supported AI function names, which previously prevented formulas created by one product from being evaluated correctly by the other.

Document Solutions for Excel now performs the required conversion automatically during import and export. When SJS or SSJSON is imported, SJS.AI.TRANSLATE, SJS.AI.QUERY, and SJS.AI.TEXTSENTIMENT are converted to their AI. equivalents. When a workbook is exported to SJS or SSJSON, the SJS. prefix is added again for SpreadJS compatibility.

This conversion requires no new API and helps preserve working AI formulas in applications that use both Document Solutions for Excel and SpreadJS.

Help

SpreadJS v19.2 Lossless I/O Improvements

Document Solutions for Excel v9.2 adds lossless SJS and SSJSON I/O support for several features introduced or expanded in SpreadJS v19.2. These enhancements focus on preserving workbook data during import, export, and conversion rather than adding new runtime authoring APIs.

DataManager Sources for PivotTables

Document Solutions for Excel can now round-trip PivotTables whose source is a SpreadJS DataManager DataTable or DataView. When the SJS or SSJSON file contains a complete PivotCache and PivotCacheRecords, the workbook can also be exported to XLSX and updated through IPivotTable.Update. Refresh is not available because Document Solutions for Excel cannot retrieve the live DataManager source.

Simplified files that contain only the DataManager source identity can be opened and round-tripped through SJS or SSJSON, but they cannot be exported safely to XLSX or used for PivotTable operations because the full cache cannot be reconstructed.

Focus Cell Options

Workbook-level Focus Cell settings introduced in SpreadJS v19.2 can now be preserved in SJS and SSJSON. Document Solutions for Excel retains the enabled, color, and opacity fields during import, export, and SJS-to-SSJSON conversion.

Focus Cell is a SpreadJS visual state and is not persisted in Excel files or rendered by Document Solutions for Excel during printing or PDF export.

PivotTable Custom Subtotals

Document Solutions for Excel v9.2 preserves PivotTable custom subtotal settings for row and column fields during SJS and SSJSON I/O. Automatic, None, and Custom subtotal states, including the selected subtotal functions, can round-trip without being lost.

Rounded Cell Borders

Rounded cell-border settings from SpreadJS can now be preserved in SJS and SSJSON files. This enhancement is limited to lossless I/O; it does not add runtime APIs or PDF and image rendering for rounded borders.

Watermark Background Image Options

Document Solutions for Excel now preserves the SpreadJS worksheet-level backgroundImage.paintOrder setting, including normal and over-content, during SJS and SSJSON import, export, and cross-format conversion.

Excel does not have an equivalent over-content watermark concept, so this setting is ignored when exporting to Excel formats. Rendering, printing, and PDF export behavior are unchanged.

Cell Scenarios

SJS and SSJSON lossless I/O now preserve SpreadJS cell scenarios used for What-If Analysis. This includes named scenario definitions, input-cell overrides, applied-scenario state, and stored base values required to restore the workbook state.

The SpreadJS-specific scenarioAdditionalInfo structure is preserved where needed even though it has no corresponding OOXML model.

Round-Trip New SpreadJS v19.2 Workbook Features

C#:

var workbook = new Workbook();
workbook.Open("spreadjs-v19-2-features.sjs");
workbook.Save("spreadjs-v19-2-features-roundtrip.sjs");

Help

Performance Benchmark Demo Page and Local Package

Document Solutions for Excel v9.2 introduces new Performance Benchmark demo pages and downloadable local benchmark packages for both Document Solutions for Excel .NET.

The hosted pages provide a static overview of the benchmark scenarios, recommended parameters, measurement methodology, privacy behavior, and result interpretation. Rather than presenting hosted benchmark numbers as authoritative, the pages direct developers to download and run the benchmark package locally. Results can vary based on hardware, operating system, runtime version, installed product package, workbook structure, warmup settings, and repetition count.

The local packages include scenarios for generating a large workbook, opening a generated XLSX, opening and saving XLSX, calculating formulas, exporting PDF, and testing a user-provided workbook. Uploaded workbooks remain on the local machine and can be tested through Open, Open + Save, Open + Calculate, and Open + Export PDF operations.

Benchmark Common Excel API Workflows on Your Own Machine

Demo

---

API Reference Documentation Improvements

Document Solutions for Excel v9.2 includes broad improvements to the .NET and Java API reference documentation. API summaries, remarks, cross-references, and examples have been expanded and standardized to make it easier to identify where a feature belongs and how an API should be used.

Examples have been added where they provide meaningful implementation guidance, while low-value or redundant examples are excluded for enum values, constructors, common object methods, deprecated APIs, and selected internal capability abstractions. Java API references follow standardized Javadoc conventions, while .NET references use consistent XML documentation and cref links for IntelliSense and generated documentation.

---

New File Format Support in Document Solutions Data Viewer v9.2

Support for Loading XLTX Files

Document Solutions Data Viewer (DsDataViewer) v9.2 adds support for loading XLTX files, allowing users to open and view Excel template workbooks directly in the JavaScript component. XLTX is now available as a first-class file type alongside the previously supported Excel formats.

The Open dialog includes XLTX in the Data Type list, and the file picker accepts files with the .xltx extension. Applications can also open XLTX files programmatically by passing FileType.XLTX to the openFile method.

The new XltxOpenOptions type gives developers control over how workbook content is presented. Options include showing hidden and very hidden sheets, displaying hidden rows or columns, showing filters, preserving row and column groups, and supplying a password for protected XLTX files. Existing default behavior keeps hidden sheets, rows, and columns concealed while retaining filters and worksheet groups.

Load Excel Template Files in a JavaScript Data Viewer

import { DsDataViewer, FileType } from '@mescius/ds-dataviewer';

const viewer = new DsDataViewer('#root');
await viewer.openFile('Documents/template.xltx', FileType.XLTX);

Developers can also pass XLTX-specific options when opening a file:

await viewer.openFile(
    'Documents/template.xltx',
    FileType.XLTX,
    {
        showHiddenSheets: true,
        showHiddenRows: true,
        showHiddenColumns: true,
        showFilters: true,
        keepRowGroups: true,
        keepColumnGroups: true,
        password: '123'
    }
);

Help | Demo

New Features Support

Support for Cell Checkboxes

DsExcel v9.1 adds support for cell checkboxes, a more flexible alternative to traditional form controls for interactive lists and forms. Because the checkbox is part of the cell, it moves naturally with sorting, filtering, and row operations, making it much easier to build Excel-like checklist experiences.

Developers can work with checkboxes through IRange.CellControl, using methods such as SetCheckbox() and RemoveControls(), while the checked state is driven by the cell value itself. true, false, and null map to checked, unchecked, and indeterminate states, respectively. Cell checkboxes are also supported in PDF and image export, and they round-trip through SJS, SSJSON, and XLSX.

Workbook workbook = new Workbook();
workbook.ActiveSheet.Range["A1:C2"].CellControl.SetCheckbox();
workbook.ActiveSheet.Range["A1:C2"].Value = false;

Console.WriteLine(workbook.ActiveSheet.Range["A1:C2"].CellControl.Type);

Help | Demo

Support for Waterfall Chart Export

DsExcel v9.1 adds support for Waterfall chart export to PDF, with support extending to image and HTML output as well. This helps developers preserve one of Excel’s most useful financial and variance-analysis chart types when generating reports outside Excel.

Subtotal and total points can be defined using the existing chart object model, and connector lines can be shown or hidden to match the intended chart design. With this improvement, Waterfall charts render much more faithfully in exported output without requiring any new API surface.

//create a new workbook
var workbook = new GrapeCity.Documents.Excel.Workbook();
IWorksheet worksheet = workbook.Worksheets[0];
worksheet.Range["A1:B8"].Value = new object[,]
{
    {"Starting Amt", 130},
    {"Measurement 1", 25},
    {"Measurement 2", -75},
    {"Subtotal", 80},
    {"Measurement 3", 45},
    {"Measurement 4", -65},
    {"Measurement 5", 80},
    {"Total", 140}
};
worksheet.Range["A:A"].Columns.AutoFit();

// Create a waterfall chart.
IShape shape = worksheet.Shapes.AddChart(ChartType.Waterfall, worksheet.Range["A9:H26"]);
shape.Chart.SeriesCollection.Add(worksheet.Range["A1:B8"]);

// Set subtotal&total points.
IPoints points = shape.Chart.SeriesCollection[0].Points;
points[3].IsTotal = true;
points[7].IsTotal = true;

// Connector lines are not shown.
ISeries series = shape.Chart.SeriesCollection[0];
series.ShowConnectorLines = true;

workbook.Save("WaterfallChart.pdf");

Help | Demo

Support for Exporting Bullets to PDF

DsExcel v9.1 improves PDF fidelity by adding support for bullets in shape text, so bullet formatting is no longer lost when exporting workbooks to PDF. This is especially useful for text boxes and other shape-based content that rely on bulleted lists for layout and readability.

The export engine now preserves bullet type, color, and size for symbol bullets, automatic numbering bullets, and custom bullets, helping PDF output more closely match Excel. Image-based bullets are not yet supported, but standard bullet formatting now renders much more accurately.

// Create a new workbook
var workbook = new Workbook();
// Open an excel file
workbook.Open("IncludesBullet.xlsx"); // The Excel file contains Shape ->Textbox ->Bullet.
// Save to a pdf file
workbook.Save("res.pdf");

Help | Demo

Support for Pivot Table Dynamic Cell Styling

DsExcel v9.1 adds support for dynamic pivot table cell styling, improving compatibility with Excel’s pivot formatting behavior. Styles applied to cells within a pivot table are now preserved even after refreshes or layout changes, rather than being tied only to a fixed cell position.

This is especially useful when highlighting important row or column items in reports that may later be refreshed or reconfigured. The feature works through existing pivot table styling behavior and does not require a new public API.

IWorkbook workbook = new Workbook();
IWorksheet worksheet = workbook.getActiveSheet();

// Set up source data
Object[][] sourceData = new Object[][]{
    {"Order ID", "Product", "Category", "Amount"},
    {1, "Carrots", "Vegetables", 4270},
    {2, "Broccoli", "Vegetables", 8239},
    {3, "Banana", "Fruit", 617},
    {4, "Apple", "Fruit", 8384}
};
worksheet.getRange("A1:D5").setValue(sourceData);

// Create pivot cache and pivot table
IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:D5"));
IPivotTable pivotTable = worksheet.getPivotTables().add(pivotCache, worksheet.getRange("F1"));

// Set up pivot table structure
pivotTable.getPivotFields().get("Category").setOrientation(PivotFieldOrientation.ColumnField);
pivotTable.getPivotFields().get("Product").setOrientation(PivotFieldOrientation.RowField);
pivotTable.getPivotFields().get("Amount").setOrientation(PivotFieldOrientation.DataField);

// Apply dynamic style to a cell in the pivot table area
worksheet.getRange("F1").getInterior().setColor(Color.GetRed());

// Refresh pivot table - the style will be preserved
pivotTable.refresh();

// The style is still applied after refresh
System.out.println(worksheet.getRange("F1").getInterior().getColor()); // Output: Color.GetRed()

// Save to Excel - styles are preserved
workbook.save("PivotTableWithStyle.xlsx");

Support for Showing or Hiding Pivot Table Field Headers

DsExcel v9.1 adds support for showing or hiding PivotTable row and column field headers, making it easier to create cleaner reports and dashboard-style summaries. This behavior is controlled through the new IPivotTable.DisplayFieldCaptions property.

When field captions are hidden, DsExcel adjusts the pivot layout automatically, either removing header-only rows or hiding just the header cells where needed. This gives developers more control over final report presentation while keeping pivot table generation simple.

When IPivotTable.DisplayFieldCaptions = True:

When IPivotTable.DisplayFieldCaptions = False:

  object[,] sourceData = new object[,] {
      { "Order ID", "Product",               "Category",              "Amount", "Date",                    "Country" },
      { 1,          "Bose 785593-0050",      "Consumer Electronics",  4270,     new DateTime(2018, 1, 6),  "United States" },
      { 2,          "Canon EOS 1500D",       "Consumer Electronics",  8239,     new DateTime(2018, 1, 7),  "United Kingdom" },
      { 3,          "Haier 394L 4Star",      "Consumer Electronics",  617,      new DateTime(2018, 1, 8),  "United States" },
      { 4,          "IFB 6.5 Kg FullyAuto",  "Consumer Electronics",  8384,     new DateTime(2018, 1, 10), "Canada" },
      { 5,          "Mi LED 40inch",         "Consumer Electronics",  2626,     new DateTime(2018, 1, 10), "Germany" },
      { 6,          "Sennheiser HD 4.40-BT", "Consumer Electronics",  3610,     new DateTime(2018, 1, 11), "United States" },
      { 7,          "Iphone XR",             "Mobile",                9062,     new DateTime(2018, 1, 11), "Australia" },
      { 8,          "OnePlus 7Pro",          "Mobile",                6906,     new DateTime(2018, 1, 16), "New Zealand" },
      { 9,          "Redmi 7",               "Mobile",                2417,     new DateTime(2018, 1, 16), "France" },
      { 10,         "Samsung S9",            "Mobile",                7431,     new DateTime(2018, 1, 16), "Canada" },
      { 11,         "OnePlus 7Pro",          "Mobile",                8250,     new DateTime(2018, 1, 16), "Germany" },
      { 12,         "Redmi 7",               "Mobile",                7012,     new DateTime(2018, 1, 18), "United States" },
      { 13,         "Bose 785593-0050",      "Consumer Electronics",  1903,     new DateTime(2018, 1, 20), "Germany" },
      { 14,         "Canon EOS 1500D",       "Consumer Electronics",  2824,     new DateTime(2018, 1, 22), "Canada" },
      { 15,         "Haier 394L 4Star",      "Consumer Electronics",  6946,     new DateTime(2018, 1, 24), "France" },
  };
  
  // Create a workbook
  Workbook workbook = new Workbook();
  IWorksheet worksheet = workbook.Worksheets[0];
  worksheet.Range["M1:R16"].Value = sourceData;
  worksheet.Range["M:R"].ColumnWidth = 15;

  // Create pivot table with DisplayFieldCaptions = false
  var pivotcache1 = workbook.PivotCaches.Create(worksheet.Range["M1:R16"]);
  var pivottable1 = worksheet.PivotTables.Add(pivotcache1, worksheet.Range["A3"], "pivottable1");
  worksheet.Range["P1:P16"].NumberFormat = "$#,##0.00";

  // Config pivot table's fields
  var field_Category1 = pivottable1.PivotFields["Category"];
  field_Category1.Orientation = PivotFieldOrientation.ColumnField;

  var field_Product1 = pivottable1.PivotFields["Product"];
  field_Product1.Orientation = PivotFieldOrientation.RowField;

  var field_Amount1 = pivottable1.PivotFields["Amount"];
  field_Amount1.Orientation = PivotFieldOrientation.DataField;
  field_Amount1.NumberFormat = "$#,##0.00";

  // Set DisplayFieldCaptions to false
  pivottable1.DisplayFieldCaptions = false;
  
  worksheet.Range["A:K"].EntireColumn.AutoFit();
  workbook.Save("pivotTableWithoutFieldHeaders.xlsx");

Help | Demo

Support for Control Tray Selection in PrintOut

DsExcel .NET v9.1 adds support for selecting a printer’s paper source or input tray during PrintOut operations on Windows. This is useful in real-world office environments where printers may have multiple trays for different paper sizes, types, or workflows.

Developers can now use PrintOutOptions.PaperSourceName alongside ActivePrinter to target a specific tray by name. If no tray is specified, the printer uses its default paper source. This feature is supported in .NET on Windows. This matches the capability available in Microsoft Excel's printer properties dialog:

Workbook workbook = new Workbook();
workbook.Open("report.xlsx");

PrintOutOptions options = new PrintOutOptions();
options.ActivePrinter = "Gestetner MP C3503 PCL 6";
options.PaperSourceName = "Tray 2";
options.Copies = 1;

workbook.ActiveSheet.PrintOut(options);

Help | Demo

Support for Picture In-Cell

DsExcel v9.1 introduces support for Picture in-cell, allowing developers to insert images directly into cells and also retrieve their image data on the server side. This supports both Excel-style in-cell image scenarios and workflows where embedded image data needs to be stored or processed elsewhere.

The main API is IRange.CellPicture, which works with the CellPicture class to store image data and alt text. Alt text is also used in operations such as filtering and sorting, and picture-in-cell content is supported in PDF, HTML, and image export. This gives developers a practical new way to work with rich cell content beyond text and formulas.

Workbook workbook = new Workbook();
IWorksheet sheet = workbook.ActiveSheet;

// Read image data from file
byte[] imageData = File.ReadAllBytes("photo.png");

// Set picture-in-cell with alt text
sheet.Range["A1"].CellPicture = new CellPicture(imageData, "Product Photo");

// Get picture-in-cell
CellPicture retrieved = sheet.Range["A1"].CellPicture;
Console.WriteLine(retrieved.AltText); // "Product Photo"
byte[] data = retrieved.ImageData;    // image binary data

// Remove picture-in-cell
sheet.Range["A1"].CellPicture = null;

Help | Demo

---

Template Support Improvements

DsExcel v9.1 expands the template engine with more expressive filtering and better support for SpreadJS-authored template content. These improvements help developers keep more logic inside the template itself, reducing preprocessing code and making reports easier to maintain.

This release adds support for NULL, DATETIME, and REGEX in template filters, along with support for expandable SpreadJS cell types and styles in template cells. Together, these changes make template-based reporting more flexible for real-world business data and richer SpreadJS-based layouts.

Template Filter Support for NULL

DsExcel v9.1 adds support for the NULL keyword in template filters, making it easier to distinguish missing values from empty strings or placeholder values. This is especially important in real-world reporting, where optional or incomplete data is common.

NULL can be used with = and <> in template expressions, allowing developers to filter for null and non-null values directly in the template using familiar SQL-style logic. This keeps missing-data handling inside the report definition instead of pushing it back into application code.

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

//Load template file FilterNullKeyword.xlsx from resource
var templateFile = GetResourceStream("xlsx\\FilterNullKeyword.xlsx");
workbook.Open(templateFile);

#region datasource
{
    var datasource = new DataTable();
    datasource.Columns.Add(new DataColumn("name", typeof(string)));
    datasource.Columns.Add(new DataColumn("phone", typeof(string)));
    datasource.Columns.Add(new DataColumn("city", typeof(string)));
    datasource.Columns.Add(new DataColumn("amount", typeof(double)));

    datasource.Rows.Add("Alice", "123-456", "New York", 100.0);
    datasource.Rows.Add("Bob", DBNull.Value, "Los Angeles", 200.0);
    datasource.Rows.Add("Charlie", "789-012", "New York", 150.0);
    datasource.Rows.Add("Diana", DBNull.Value, "New York", 300.0);
    datasource.Rows.Add("Eve", "345-678", DBNull.Value, 250.0);
    datasource.Rows.Add("Frank", DBNull.Value, "San Francisco", 175.0);

    workbook.AddDataSource("ds", datasource);
}
#endregion

//Invoke to process the template
workbook.ProcessTemplate();
        
// Save to an excel file
workbook.Save("FilterNullKeyword.xlsx");

Help .NET | Demo .NET

Template Filter Support for DATETIME

DsExcel v9.1 introduces DATETIME(...) support in template filters, allowing date and time comparisons directly inside template expressions. This makes it much easier to build reports for date ranges, time windows, and other time-based business scenarios without relying on fragile string comparisons.

The function accepts Excel-compatible date/time formats as well as common API and database formats such as ISO 8601. Developers can use it with standard comparison operators to build exact matches or range-based filters directly in the template.

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

//Load template file DateTimeFilter.xlsx from resource
var templateFile = GetResourceStream("xlsx\\DateTimeFilter.xlsx");
workbook.Open(templateFile);

#region datasource
{
    var datasource = new DataTable();
    datasource.Columns.Add(new DataColumn("orderId", typeof(string)));
    datasource.Columns.Add(new DataColumn("customer", typeof(string)));
    datasource.Columns.Add(new DataColumn("orderDate", typeof(DateTime)));
    datasource.Columns.Add(new DataColumn("deliveryTime", typeof(DateTime)));
    datasource.Columns.Add(new DataColumn("amount", typeof(double)));

    datasource.Rows.Add("ORD-001", "Alice", new DateTime(2024, 5, 10, 9, 30, 0), new DateTime(2024, 5, 12, 8, 0, 0), 320.0);
    datasource.Rows.Add("ORD-002", "Bob", new DateTime(2024, 5, 22, 14, 0, 0), new DateTime(2024, 5, 25, 12, 30, 0), 750.0);
    datasource.Rows.Add("ORD-003", "Charlie", new DateTime(2024, 6, 1, 10, 15, 0), new DateTime(2024, 6, 3, 9, 0, 0), 180.0);
    datasource.Rows.Add("ORD-004", "Diana", new DateTime(2024, 6, 5, 16, 45, 0), new DateTime(2024, 6, 8, 14, 0, 0), 1200.0);
    datasource.Rows.Add("ORD-005", "Eve", new DateTime(2024, 6, 15, 10, 30, 0), new DateTime(2024, 6, 18, 10, 30, 0), 450.0);
    datasource.Rows.Add("ORD-006", "Frank", new DateTime(2024, 6, 15, 10, 30, 0), new DateTime(2024, 6, 17, 19, 0, 0), 890.0);
    datasource.Rows.Add("ORD-007", "Grace", new DateTime(2024, 6, 20, 8, 0, 0), new DateTime(2024, 6, 22, 7, 30, 0), 2100.0);
    datasource.Rows.Add("ORD-008", "Henry", new DateTime(2024, 6, 28, 11, 0, 0), new DateTime(2024, 7, 1, 15, 45, 0), 560.0);
    datasource.Rows.Add("ORD-009", "Ivy", new DateTime(2024, 7, 3, 13, 30, 0), new DateTime(2024, 7, 5, 20, 0, 0), 3200.0);
    datasource.Rows.Add("ORD-010", "Jack", new DateTime(2024, 7, 10, 9, 0, 0), new DateTime(2024, 7, 12, 11, 0, 0), 175.0);
    datasource.Rows.Add("ORD-011", "Karen", new DateTime(2024, 7, 18, 15, 0, 0), new DateTime(2024, 7, 20, 16, 30, 0), 640.0);
    datasource.Rows.Add("ORD-012", "Leo", new DateTime(2024, 7, 25, 10, 0, 0), new DateTime(2024, 7, 28, 6, 0, 0), 4800.0);
    datasource.Rows.Add("ORD-013", "Mia", new DateTime(2024, 8, 2, 14, 30, 0), new DateTime(2024, 8, 5, 13, 0, 0), 920.0);
    datasource.Rows.Add("ORD-014", "Nathan", new DateTime(2024, 8, 12, 8, 45, 0), new DateTime(2024, 8, 14, 22, 0, 0), 150.0);
    datasource.Rows.Add("ORD-015", "Olivia", new DateTime(2024, 8, 20, 17, 0, 0), new DateTime(2024, 8, 23, 18, 30, 0), 5000.0);

    workbook.AddDataSource("ds", datasource);
}
#endregion

//Invoke to process the template
workbook.ProcessTemplate();
foreach (var worksheet in workbook.Worksheets)
{
    worksheet.UsedRange.EntireRow.AutoFit();
}
        
// Save to an excel file
workbook.Save("DateTimeFilter.xlsx");

Help .NET | Demo .NET

Template Filter Support for REGEX

DsExcel v9.1 adds support for the REGEX keyword in template filters, enabling advanced text pattern matching directly in report templates. This is useful for filtering structured values such as SKUs, invoice numbers, email domains, and other formatted text that goes beyond simple wildcard matching.

Regex matching uses the native engine of each platform, making the feature familiar to .NET developers. This gives template authors a more powerful way to express complex text logic without moving those checks into application code.

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

//Load template file FilterRegex.xlsx from resource
var templateFile = GetResourceStream("xlsx\\FilterRegex.xlsx");
workbook.Open(templateFile);

#region datasource
{
    var datasource = new DataTable();
    datasource.Columns.Add(new DataColumn("pid", typeof(string)));
    datasource.Columns.Add(new DataColumn("name", typeof(string)));
    datasource.Columns.Add(new DataColumn("sku", typeof(string)));
    datasource.Columns.Add(new DataColumn("description", typeof(string)));

    datasource.Rows.Add("P001", "Laptop Pro 15", "LP-2024-001", "High performance laptop");
    datasource.Rows.Add("P002", "Laptop Air 13", "LA-2024-002", "Lightweight laptop");
    datasource.Rows.Add("P003", "Phone X100", "PX-2023-100", "Flagship phone");
    datasource.Rows.Add("P004", "Phone SE", "PS-2024-050", "Budget phone");
    datasource.Rows.Add("P005", "Tablet Max 12", "TM-2024-012", "Large screen tablet");
    datasource.Rows.Add("P006", "Monitor 4K-27", "MK-2023-027", "4K monitor 27 inch");
    datasource.Rows.Add("P007", "Keyboard MX", "KM-2024-001", "Mechanical keyboard");

    workbook.AddDataSource("product", datasource);
}
#endregion

//Invoke to process the template
workbook.ProcessTemplate();
foreach (var worksheet in workbook.Worksheets)
{
    worksheet.UsedRange.EntireRow.AutoFit();
}
        
// Save to an excel file
workbook.Save("FilterRegex.xlsx");

Help | Demo

Expandable Cell Types and SpreadJS Styles in Template Cells

DsExcel v9.1 adds support for expandable SpreadJS cell types and styles in template cells, helping SpreadJS-authored templates behave more naturally during template expansion. This includes support for CellType, DropDown-related behavior, and several other SpreadJS-specific style settings.

This behavior can be enabled through the workbook defined name TemplateOptions.SpreadJSStyleExpandable. Once enabled, supported SpreadJS styles expand with template data in a way similar to Excel styles, preserving more of the original template design in generated output.

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

//Load template file from resource
var templateFile = this.GetResourceStream("sjs\\Template_SpreadJSStyle.sjs");
workbook.Open(templateFile, OpenFileFormat.Sjs);
workbook.Names.Add("TemplateOptions.SpreadJSStyleExpandable", "True");

#region Define custom class
//public class Student
//{
//    public string Name { get; set; }
//    public int Gender { get; set; }
//    public DateTime Birthday { get; set; }
//    public string Hobbies { get; set; }
//    public Student(string name, int gender, DateTime birthday, string hobbies)
//    {
//        Name = name;
//        Gender = gender;
//        Birthday = birthday;
//        Hobbies = hobbies;
//    }
//}
#endregion

#region Init Data
List<Student> students = new List<Student>()
{
    new Student("Emma Johnson", 0, new DateTime(2016, 3, 15), "Drawing and Reading"),
    new Student("Liam Smith", 1, new DateTime(2015, 8, 22), "Soccer and Video Games"),
    new Student("Olivia Brown", 0, new DateTime(2016, 11, 8), "Dancing and Singing"),
    new Student("Noah Davis", 1, new DateTime(2015, 5, 30), "Swimming and Building Legos"),
    new Student("Sophia Wilson", 0, new DateTime(2016, 7, 12), "Playing Piano and Painting")
};
#endregion
//Add data source
workbook.AddDataSource("ds", students);

//Invoke to process the template
workbook.ProcessTemplate();
        
// Save to a .sjs file
workbook.Save("SpreadJSStyle.sjs");

Help | Demo

---

SpreadJS Lossless I/O Improvements

DsExcel v9.1 continues to improve SpreadJS interoperability with a new set of lossless I/O enhancements for SJS and SSJSON workflows. These updates help preserve newer SpreadJS workbook features more accurately during import, export, and format conversion.

This release adds support for Sparkline conditional formatting preservation, Top/Bottom Percent rules, Scrollbar Auto mode, the new DataManager storage format, accounting underline styles, and Named Cell Templates. These improvements help reduce feature loss when moving workbooks between SpreadJS and server-side DsExcel workflows.

Support I/O for Sparkline Rules in Conditional Formatting

DsExcel v9.1 adds lossless SJS and SSJSON I/O support for Sparkline conditional formatting rules, helping preserve these newer SpreadJS visual rules during round-trip processing. Support applies where relevant across Worksheet, TableSheet, and ReportSheet scenarios.

When exporting to XLSX, these Sparkline rules are converted to Data Bar rules, preserving the applied range, data source, and priority, while Sparkline-specific visual settings are not retained. This provides a practical balance between SpreadJS preservation and Excel compatibility.

Support Top/Bottom Percent Rule in Conditional Formatting

DsExcel v9.1 improves SpreadJS compatibility by adding support for Top/Bottom Percent conditional formatting rules in SJS and SSJSON workflows. This aligns better with both SpreadJS behavior and Excel’s Top 10 rule logic by percent.

The enhancement focuses on preserving these rules during import, export, and SJS/SSJSON conversion, without requiring any new public API. This helps developers round-trip ranking-based conditional formatting more reliably across client and server workflows.

Support SpreadJS Scrollbar Auto Mode

DsExcel v9.1 adds support for SpreadJS scrollbar Auto mode in SJS and SSJSON files, preserving the three-state visibility model used by SpreadJS. This helps maintain more of the original workbook UI behavior during round-trip processing.

Because Excel only supports show and hide states, Auto mode is converted to Show during XLSX export. Even with that limitation, the feature improves fidelity when staying within SpreadJS-oriented file formats.

Support the New SJS DataManager Attachments-Per-Table-File Format

DsExcel v9.1 adds support for the newer DataManager attachments-per-table-file format introduced in SpreadJS. In this model, table data is stored as separate attachment files rather than being embedded directly in workbook.json.

DsExcel now supports both the old and new SJS storage models, while preserving the original structure of imported files when saving back to SJS. This improves compatibility with modern DataManager-backed SpreadJS workbooks while avoiding unexpected format conversion.

Support Single and Double Accounting Underline Cell Styles

DsExcel v9.1 adds support for SingleAccounting and DoubleAccounting underline styles in SJS and SSJSON import/export, improving alignment with newer SpreadJS styling behavior. This helps preserve more precise underline formatting when moving content between Excel and SpreadJS formats.

These accounting underline variants are now preserved correctly in JSON-based workflows, although PDF, HTML, and image export still render them the same as standard single and double underlines. Even so, the update improves style fidelity in workbook round-tripping scenarios.

Support I/O for Named Cell Templates

DsExcel v9.1 adds lossless SJS and SSJSON I/O support for Named Cell Templates, helping preserve workbook-level reusable template definitions created in SpreadJS. This is especially useful in template-driven workflows where those definitions should survive server-side processing unchanged.

The feature preserves the namedCellTemplates node during import, export, and SJS/SSJSON conversion, without implementing template authoring behavior directly in DsExcel. This keeps workbook-level definitions intact while maintaining compatibility with SpreadJS-authored content.

---

New Pivot Table Features Support in DsDataViewer

Interact with Pivot Tables in DsDataViewer

Document Solutions DataViewer (DsDataViewer) v9.1 adds support for interacting with PivotTables, giving users more control over pivot-based reports directly in the viewer. With this enhancement, users can work with PivotTables in a more dynamic way by performing actions such as filtering, sorting, using slicers, and changing the layout without leaving the viewing experience.

This feature is available for Professional license users and follows the permissions defined in the source workbook. If sheet protection is disabled, PivotTable interactions are allowed. If protection is enabled, PivotTable interactions depend on whether the workbook’s Use PivotTable option is selected. This helps preserve workbook-level authoring intent while still allowing interactive analysis where permitted.

DsDataViewer supports PivotTable interaction through both the Filter button and the PivotTable panel. When a workbook opens and the active cell is already inside a PivotTable, the panel opens automatically. Clicking within a PivotTable range opens the panel, while clicking outside of it closes the panel. Users can add fields by checking or dragging them, modify the PivotTable layout, apply filters, adjust field settings, and continue working smoothly even when layout changes require DsDataViewer to automatically expand the visible rows and columns to fit the updated PivotTable structure.

Pivot Table Panel Opening/Closing

Pivot Table Add Fields by Checking or Dragging

Modify Pivot Table Layout

Pivot Table Filters

Pivot Table Field Settings

Pivot Table Color Theme Switching

With support for PivotTable interaction, DsDataViewer v9.1 makes it easier for users to explore and adjust PivotTable-based reports directly in the viewer, bringing a more interactive spreadsheet analysis experience to web-based document viewing.

Help | Demo

Performance Improvements in v9.0

As a server-side spreadsheet engine, DsExcel is built for speed, especially in formula calculation and large-scale data processing. In the v9.0 release, we’ve delivered substantial performance gains across a wide range of common operations, including copying large ranges, updating chart-linked values, processing dynamic array formulas, lookup operations, AutoFit behavior, and exporting pivot-table-heavy workbooks. These improvements make DsExcel faster and more efficient for enterprise-scale workloads and high-volume automation scenarios.

Faster Copying of Large Ranges with Complex Formulas

Copying ranges containing complex formulas such as MATCH, SUMIFS, and multi-cell expressions now executes dramatically faster. Several real-world customer scenarios showed improvements of 89% to 98%, with a 40,000-row range dropping from 26.8 seconds to 0.57 seconds.

Reduced Overhead for Frequent Get/Set Operations

Workflows that trigger large numbers of Range.GetValue and Range.SetValue calls, often caused by chart updates or dynamic array recalculations, now experience significant speedups. In v9.0, DsExcel updates chart data only when required and skips unnecessary dynamic-array state updates, producing improvements of 95% to 99%.

Faster Copying of Dynamic Array Formula Ranges

Copying ranges that contain spilled formulas previously caused repeated internal state checks. With a new optimized update mechanism, copying large row ranges is now up to 98% faster, reducing a 78-second operation to just over one second.

Improved Lookup Function Performance with Mixed Data Types

Lookup-type functions (e.g., XLOOKUP, MATCH, LOOKUP) now apply optimized caching even when ranges contain mixed numeric and text values. This results in major improvements, up to 96% faster, when evaluating hundreds of thousands of lookup formulas.

AutoFit Performance and Memory Optimization

AutoFit now uses a more efficient internal strategy, cutting processing time nearly in half and reducing memory usage by over 60% when applied to large (300×300) ranges, all without changing AutoFit results.

---

AI Functions: QUERY, TRANSLATE, and TEXTSENTIMENT

DsExcel v9.0 introduces a new family of AI functions that bring large language model capabilities directly into Excel-like formulas. With built-in support for querying models, translating text, and analyzing sentiment, developers can now wire AI-driven workflows straight into the calculation engine, with no separate batch jobs or glue scripts required. These functions can be used in templates, spilled formulas, or automation scenarios, and are powered by a pluggable request handler so you can connect to the AI provider of your choice.

Because AI models are non-deterministic, recalculating AI formulas may yield different results over time. DsExcel also surfaces common AI-related issues as spreadsheet error codes (for example, #BUSY! while a request is in flight, #CONNECT! for network/handler failures, #VALUE! for execution issues, and #NA! when no handler is configured).

Pluggable AI Model Request Handler

At the core of the new AI features is the IAIModelRequestHandler interface. Rather than baking in a specific AI vendor, DsExcel delegates all model calls to a global handler:

Workbook.AIModelRequestHandler

You implement IAIModelRequestHandler (or use a sample like OpenAIModelRequestHandler) to:

• Build and send requests to your chosen AI API (OpenAI, Azure OpenAI, DeepSeek, Qwen, etc.)
• Manage API keys, endpoints, and model names
• Enforce security, logging, and compliance
• Return an AIModelResponse whose Content is a JSON 2D array that maps cleanly into cells

C#

// Configure once for the entire app
Workbook.AIModelRequestHandler =
new OpenAIModelRequestHandler("https://api.openai.com/v1", "sk-xxxx", "gpt-4.1");

Once set, all AI formulas in any workbook will route their requests through this handler.

Help

AI.QUERY – Ask the Model Questions from Your Grid

AI.QUERY lets you construct prompts from cell values and ranges, then send them to the AI model and return results into the sheet.

Syntax

=AI.QUERY(prompt1, [data1], [prompt2], [data2], ...)

• Prompt: required text describing the task or question
• Data: optional cell or range passed as context

DsExcel concatenates all prompt and data arguments into a single message. For example:

=AI.QUERY("evaluate these reviews ", A6:A13, " based on these categories ", B5:C5)

This builds a prompt similar to: "evaluate [values from A6:A13] based on these following categories [values from B5:C5]" and returns the model’s response as a spill range.

C#

var wb = new Workbook();
var ws = wb.Worksheets[0];
ws.Range["A1"].Value = "Country";
ws.Range["A2:A4"].Value = new object[,] { { "China" }, { "USA" }, { "UK" } };
ws.Range["B1"].Value = "What is the capital of country?";
ws.Range["B2"].Formula2 = "=AI.QUERY(B1, A2:A4, \"Only need capital\")";
wb.Calculate();
wb.WaitForCalculationToFinish();

Help | Demo

AI.TRANSLATE – Translate Ranges into Target Languages

AI.TRANSLATE uses the configured model to translate text into a specified language.

Syntax

=AI.TRANSLATE(array, language)

• Array: required range or array to translate
• Language: required language identifier (locale names like en-US, zh-CN, ja-JP, or clear language names such as English, 中文)

Example:

=AI.TRANSLATE(A6, B6)

This will translate the values in A6 with the target language specified in B6 (Simplified Chinese) and spill results into the corresponding output range.

C#

var wb = new Workbook();
var ws = wb.Worksheets[0];
ws.Range["A1"].Value = "Country";
ws.Range["B1"].Value = "Translation";
ws.Range["A2:A4"].Value = new object[,] { { "China" }, { "USA" }, { "UK" } };
ws.Range["B2"].Formula2 = "=AI.TRANSLATE(A2:A4, \"zh-cn\")";
wb.Calculate();
wb.WaitForCalculationToFinish();

Help | Demo

AI.TEXTSENTIMENT – Classify Sentiment as Positive/Negative/Neutral

AI.TEXTSENTIMENT analyzes text and returns custom values based on whether the sentiment is positive, negative, or neutral.

Syntax

=AI.TEXTSENTIMENT(array, positive, negative, [neutral])

• Array: required input text range
• Positive: value to return when sentiment is positive
• Negative: value to return when sentiment is negative
• Neutral: optional value to return when sentiment is neutral

Example:

=AI.TEXTSENTIMENT(A6:A13, "Positive", "Negative", "Neutral")

DsExcel sends the text in A6:A13 to your AI handler, then writes back the corresponding sentiment labels into the result range.

C#

var wb = new Workbook();
var ws = wb.Worksheets[0];
ws.Range["A1"].Value = "Review";
ws.Range["B1"].Value = "TEXTSENTIMENT";
ws.Range["A2:A6"].Value = new object[,] { 
    {"The restaurant offers a beautiful ambiance and attentive service, perfect for family gatherings."}, 
    {"The food is delicious, but the prices are slightly high, which affects the overall value."}, 
    {"It was noisy with poor service and slow food delivery, making for a disappointing experience."},
    {"Loved the unique dishes and inviting atmosphere！Definitely planning to come back." },
    {"Great flavors and a cozy setting, although the waiting time was a bit too long." }
};
ws.Range["B2"].Formula2 = "=AI.TEXTSENTIMENT(A2:A6, \"Positive\",\"Negative\",\"Neutral\")";
wb.Calculate();
wb.WaitForCalculationToFinish();

Help | Demo

With AI.QUERY, AI.TRANSLATE, and AI.TEXTSENTIMENT, plus the flexible IAIModelRequestHandler abstraction, DsExcel v9.0 turns spreadsheets into an AI-enabled calculation surface, letting you embed intelligent classification, translation, and analysis directly into your existing Excel automation workflows.

---

New Text Conversion Functions: VALUETOTEXT and ARRAYTOTEXT

DsExcel v9.0 adds support for two newer Excel functions, VALUETOTEXT and ARRAYTOTEXT, making it easier to convert values and arrays into text while maintaining full compatibility with Excel’s behavior. These functions are especially useful when you need to normalize mixed data types (numbers, booleans, errors, text) into strings for concatenation, logging, auditing, or dynamic formula generation.

Both functions support a format argument that controls whether the result is a human-friendly, concise representation or a stricter, formula-bar-compatible string with quotes and escape characters.

VALUETOTEXT – Convert Any Value to Its Text Representation

VALUETOTEXT converts a single value, an array, or a range reference into its text form.

Syntax

=VALUETOTEXT(value, [format])

• Value – required. Can be a number, text, boolean, error, empty cell, array, LAMBDA, or range reference.
• Format – optional.
  • 0 (default): concise format, matching how the cell would display under General.
  • 1: strict format, suitable for parsing in the formula bar. Text is wrapped in quotes and internal quotes are escaped.

Examples:

• =VALUETOTEXT(A2, 0) → Hello
• =VALUETOTEXT(A2, 1) → "Hello"

C#

Workbook wb = new Workbook();
IWorksheet activeSheet = wb.ActiveSheet;
activeSheet.Range["A1:E1"].Value = new object[] { "0", "1", 2, 3, 4 };
activeSheet.Range["A3"].Formula2 = "VALUETOTEXT(A1:E1)"; // "0","1","2","3","4"
activeSheet.Range["A3"].Formula2 = "VALUETOTEXT(A1:E1,1)"; // "\"0\"","\"1\"","2","3","4"
activeSheet.Range["A4"].Formula2 = "ValueToText(A1:E1,\"\")"; // #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!

Errors passed as value (e.g., #VALUE! or 10/0) are preserved as error text, while invalid or error format arguments result in standard Excel-style #VALUE! or propagated error behavior. Both value and format can be arrays, allowing you to transform spilled ranges into corresponding text grids in one call.

Help | Demo

ARRAYTOTEXT – Turn Arrays and Ranges into Text Strings

ARRAYTOTEXT converts an entire array or range into a single text string.

Syntax

=ARRAYTOTEXT(array, [format])

• Array – required. The range or array to convert.
• Format – optional.
  • 0 (default): concise format, matching General display.
  • 1: strict format using row delimiters and quotes so the result can be pasted back into the formula bar as an array literal (for example {200;"Tom";;""}).

Examples:

• =ARRAYTOTEXT(A2, 0) → TRUE
• =ARRAYTOTEXT(A2, 1) → {TRUE}
• =ARRAYTOTEXT(A5, 1) → {"Hello"}

Like Excel, ARRAYTOTEXT does not support 3D references (e.g., Sheet2:Sheet3!A1:C3) and will return #VALUE! when used that way. Non-0/1 format values follow Excel’s coercion rules (numeric ranges map to 0 or 1; invalid strings return #VALUE!; boolean TRUE/FALSE maps to 1/0).

C#

var workbook = new Workbook(); 
IWorksheet activeSheet = workbook.ActiveSheet;

sheet.Range["A1"].Value = 200;
sheet.Range["A2"].Value = "Tom";
sheet.Range["A4"].Value = "";

sheet.Range["C2"].Formula2 = "=ARRAYTOTEXT(A1:A4,1)"; // {200;"Tom";;""}

Help | Demo

With VALUETOTEXT and ARRAYTOTEXT now supported in DsExcel .NET v9.0.0, you can build more robust text-processing and inspection workflows while staying fully aligned with modern Excel function behavior, no new API calls required.

---

Control Shared Formula Export in XLSX Files

The v9.0 release of Document Solutions for Excel introduces a new option that gives developers full control over how formulas are written into exported XLSX files. This enhancement is especially valuable for workflows that mix DsExcel with external Excel-processing libraries, such as Python’s openpyxl, which has limited support for Excel’s shared formula structure.

Shared formulas are a space-saving mechanism used by Excel to store repeated formulas once and apply them across multiple cells. While this is efficient, some third-party tools cannot interpret shared formulas correctly, causing formulas to be lost or misread during downstream processing. To address this, DsExcel now provides a way to export individual formulas instead of shared formulas.

Developers can enable this behavior using the new XlsxSaveOptions.ExportSharedFormula property. When set to false, DsExcel expands each shared formula into its full cell-specific version when writing the XLSX file, ensuring maximum compatibility with tools like openpyxl. The trade-off is a larger file size, so this setting should be used only when required for interoperability.

C#

var workbook = new Workbook();
var sheet = workbook.ActiveSheet;
sheet.Range["B1:B10"].Formula = "=A1";

var options = new XlsxSaveOptions { ExportSharedFormula = false };
workbook.Save("testNoSharedFormula.xlsx", options);

With this update, DsExcel v9.0 makes it easier to integrate Excel automation across mixed technology stacks while preserving formula behavior reliably.

Help

---

Preserve “Show Hidden Rows and Columns” for SpreadJS ReportSheet

In many SpreadJS ReportSheet scenarios, designers want to work with rows and columns that are technically hidden in the final report, but still visible and editable while designing the template. SpreadJS supports this design-time experience by showing hidden rows and columns in the editor and only hiding them at preview/runtime. In DsExcel v9.0, we’ve enhanced our SpreadJS integration so this behavior is preserved end-to-end when working with SJS/SSJSON files on the server.

DsExcel now provides lossless SJS/SSJSON I/O for ReportSheet templates that use the “show hidden rows and columns at design time” behavior. When you open an SJS or SSJSON file, any ReportSheet configuration related to hidden rows and columns is retained. When you save or convert the workbook back, those settings are written out unchanged, ensuring design-time visibility continues to work as expected in the SpreadJS UI.

C#

var workbook = new Workbook();
workbook.Open("HiddenRowsColumns.sjs");
using (var fs = File.Create("export.json"))
{
    workbook.ToJson(fs);
} // End Using
workbook.Save("export.sjs");

With this update, DsExcel .NET v9.0 better align with SpreadJS ReportSheet’s design-time experience, while still delivering accurate hidden-row/column behavior at preview or runtime.

---

Lossless Preservation of Excel Power Query Tables

Power Query is a core part of modern Excel reporting, allowing users to load data from databases, files, web APIs, and other external sources into Tables, PivotTables, and charts. In previous versions, when workbooks containing query tables and external data connections were opened and saved through DsExcel, the underlying query-related XML parts were not preserved. This meant that, after a round-trip through DsExcel, Power Query tables could lose their refresh capabilities and metadata inside Excel.

In DsExcel v9.0, we’ve added lossless IO support for Excel external data and query tables when working with XLSX. DsExcel now reads and writes the relevant OOXML parts, such as /xl/queryTables/queryTableX.xml and the associated table attributes, without altering or discarding them. As a result, Power Query tables retain their configuration, field mappings, and connections when you reopen the file in Excel and refresh the data.

This enhancement is designed specifically for preservation, not for editing. DsExcel does not attempt to interpret or modify the query definition; it simply carries it through XLSX open/save operations untouched. With this change, DsExcel .NET v9.0.0 can safely participate in workflows that rely on Excel’s Power Query, while preserving all query definitions and external data connections in the original workbook.