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

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.