What's New in Document Solutions for Excel v9

We’re excited to introduce Document Solutions for Excel (DsExcel) v9.0, a major release that delivers smarter automation, expanded AI capabilities, and dramatic performance improvements for large-scale spreadsheet processing. This update brings support for AI-powered functions such as QUERY, TRANSLATE, and TEXTSENTIMENT, new Excel-compatible functions like VALUETOTEXT and ARRAYTOTEXT, and full preservation of Power Query tables. DsExcel v9.0 also includes substantial enhancements to calculation speed, copy operations, lookup performance, and dynamic array handling, making it easier than ever to build fast, intelligent, and scalable Excel solutions. Learn more by reading below!

Document Solutions for Excel v9 Includes

• Performance Improvements
• NEW! AI Functions: QUERY, TRANSLATE, & TEXTSENTIMENT
• NEW! Text Conversion Functions: VALUETOTEXT & ARRAYTOTEXT
• Control Shared Formula Export in XLSX Files
• Preserve “Show Hidden Rows and Columns” for SpreadJS ReportSheet
• Lossless Preservation of Excel Power Query Tables

Download the Latest Release of the .NET or Java Excel APIs Today!

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.

[Java] Faster Exporting of Workbooks with Many Pivot Tables

For Java developers, exporting pivot-table-heavy workbooks to Excel is now significantly faster. A representative test case showed a 52% improvement when exporting large files containing dozens of pivot tables (.NET did not require optimization in this area).

With these improvements, DsExcel 9.0 delivers smoother, faster, and more scalable performance across the board, empowering your applications to process larger workbooks, more formulas, and heavier workloads with greater efficiency. See a visual representation of these performance improvements below.

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:

• .NET: Workbook.AIModelRequestHandler
• Java: Workbook.setAIModelRequestHandler(...)

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

Java

Workbook.setAIModelRequestHandler(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 .NET | Help Java

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

Java

Workbook wb = new Workbook();
IWorksheet ws = wb.getWorksheets().get(0);
ws.getRange("A1").setValue("Country");
ws.getRange("A2:A4").setValue(new Object[][] { { "China" }, { "USA" }, { "UK" } });
ws.getRange("B1").setValue("What is the capital of country?");
ws.getRange("B2").setFormula2("=AI.QUERY(B1,A2:A4,\"Only need capital\")");
wb.calculate();
wb.waitForCalculationToFinish();

Help .NET | Demo .NET | Help Java | Demo Java

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

Java

Workbook wb = new Workbook();
IWorksheet ws = wb.getWorksheets().get(0);
ws.getRange("A1").setValue("Country");
ws.getRange("B1").setValue("Translation");
ws.getRange("A2:A4").setValue(new Object[][] { { "China" }, { "USA" }, { "UK" } });
ws.getRange("B2").setFormula2("=AI.TRANSLATE(A2:A4, \"zh-cn\")");
wb.calculate();
wb.waitForCalculationToFinish();

Help .NET | Demo .NET | Help Java | Demo Java

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

Java

Workbook wb = new Workbook();
IWorksheet ws = wb.getWorksheets().get(0);
ws.getRange("A1").setValue("Review");
ws.getRange("B1").setValue("TEXTSENTIMENT");
ws.getRange("A2:A6").setValue(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.getRange("B2").setFormula2("=AI.TEXTSENTIMENT(A2:A6, \"Positive\",\"Negative\",\"Neutral\")");
wb.calculate();
wb.waitForCalculationToFinish();

Help .NET | Demo .NET | Help Java | Demo Java

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 .NET | Demo .NET | Help Java | Demo Java

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 .NET | Demo .NET | Help Java | Demo Java

With VALUETOTEXT and ARRAYTOTEXT now supported in DsExcel .NET and Java 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);

Java

Workbook workbook = new Workbook();
IWorksheet sheet = workbook.getActiveSheet();
sheet.getRange("B1:B10").setFormula("=A1");

XlsxSaveOptions options = new XlsxSaveOptions();
options.setExportSharedFormula(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 .NET | Help Java

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

Java

 workbook = new Workbook();
 workbook.open("HiddenRowsColumns.sjs");
 workbook.toJson(new FileOutputStream("exportJava.json"));
 workbook.save("exportJava.sjs");

With this update, DsExcel .NET and Java 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 and Java 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.

Ready to check out the release? Download the .NET or Java Edition of Document Solutions for Excel today!