Released: Dec 11, 2024

Actualizaciones en 8.0.0

Características

Import Data from Object Collections and Data Tables

• When working with Excel sheets programmatically in business applications, one common task is to import data from different data sources, such as classes and objects, relational databases etc. In the v8 release, Document Solutions for Excel (DsExcel) adds the ability to import data from the following types of data sources:
  • Simple enumerable.
  • Multidimensional array.
  • Jagged arrays.
  • Custom objects.
  • Custom objects from weakly typed collection.
  • Custom objects with custom columns.
  • Data from unknown types of custom objects (duck typing).
  • Dynamic objects.
  • Selected DataColumns.
• DsExcel adds new overloads to the IRange.ImportData(..) method that now also enables importing data from a data source to the range. The following overloads take in parameters as per the data source:
  • ImportData(IEnumerable, DataImportOptions) Method - Imports data from a data source to the range.
  • ImportData(DataTable, DataImportOptions) Method - Imports data from a table to the range.
• The DataImportOptions parameter is an enum that provides options to handle the data.

Add and Manage Scenarios in What-If Analysis

• In continuation of the support for adding What-If analysis to spreadsheets, support has been added for Scenarios in the latest version of DsExcel. A scenario in Excel is a set of saved input values for specific cells. By switching between different scenarios, you can see how changes in certain cells impact the results of calculations in your sheet.
• The new IWorksheet.Scenarios interface helps to create and manage scenarios in Excel spreadsheets. You can perform the following operations with this API:
  • Create scenarios in the current worksheet. This allows you to:
    • Define a scenario with a name.
    • Provide the range where the scenario should be added - this range would be the ‘changing cells’ range.
    • Define the list of values for ‘changing cells’.
    • Define a comment attached to the scenario.
    • Set the option to lock a scenario.
    • Set the option to hide a scenario.
  • Apply/show a scenario programmatically - the values of changing cells will be applied to the worksheet, and the formulas that reference the changing cells will be recalculated.
  • Delete the scenario.
  • Get the changing cells for the scenario.
  • Get the values of the changing cells.
  • Change/modify a scenario to have a new set of changing cells and scenario values.
  • Lock a scenario to keep other users from modifying it.
  • Hide a scenario so that certain scenarios are visible only to some users and hidden from others.
  • Edit scenarios on a protected worksheet. This works when the worksheet is protected, with no additional restrictions from DsExcel. Users can customize the operation based on the IProtectionSettings.AllowEditingScenarios and IWorksheet.Protection properties.

Bind Pivot Table Directly with Excel Table as Data Source

• To maximize the effectiveness and flexibility of a Pivot Table, it needs to be bound to a Table. This is crucial when working with dynamic or expanding datasets, as you don't need to manually update the data range for the Pivot Table. In addition, when your data is in a Table, each column has a defined header, so Pivot Table can automatically use these column names to configure Pivot Table fields.
• The IPivotCaches.Create(object) method now supports passing ITable as a parameter. Simply define your table and pass it as a parameter while creating the IPivotCache object.

Set Color in Various String Formats

• In DsExcel, you can customize the appearance of a cell, cell border, tab, and more by using theme colors, standard colors, or custom colors. To apply these colors, DsExcel adds the StringToColor method of the new ColorUtilities class. This API helps to add standard and custom colors using various string formats including:
  • Color name: A predefined standard color name.
  • Example: "red."
  • RGB: rgb(r,g,b).
  • Example: "rgb(255,0,0)" for red color.
  • RGBA: rgba(r,g,b,a).
  • Example: "rgba(255,0,0,0.5)" for red color with 50% opacity.
  • Hexadecimal: #RGB or #RRGGBB.
  • Example: "#F00" or "#FF0000" for red color.
  • Hexadecimal with alpha: #RGBA or #RRGGBBAA.
  • Example: "#F00C" or "#FF0000CC" for red color with 90% opacity.

Support Page Number Calculation Operators

• DsExcel now supports the use of ‘+’ and ‘-’ operators with page numbers and the total number of pages in the headers and footers of worksheets, whether they are exported to PDF or printed on physical printers. This feature is available for all paginated outputs that respect custom headers and footers in the IWorksheet.PageSetup property.
• You can adjust page numbers or the total page count—adding or subtracting—as needed when printing multiple workbooks as a single report.

New APIs to Manage PivotTable

• The Pivot Table features supported in DsExcel have been enhanced with new API options: HasAutoFormat, RefreshOnFileOpen, and ShowPivotTableFieldList from VSTO.
  • HasAutoFormat allows for auto-fit of column width when updating the Pivot Table.
  • RefreshOnFileOpen enables control of whether the pivot table should refresh the pivot cache when the file is opened, ensuring up-to-date information.
  • ShowPivotTableFieldList allows control of whether to show the PivotTable field list.
• These additions make working with pivot tables programmatically more powerful by simplifying data management and enhancing display options.

Support Pivot Table Timeline Slicer

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

[DsExcel Java] Support for Pattern Fill When Rendering to PDF

• DsExcel now supports Pattern Fill for Cells, Charts, and Shapes when exporting to PDF and images.

Features for SpreadJS Compatibility

• AutoMerge Cells
  • SpreadJS includes an Auto Merge Cells feature that automatically merges adjacent cells with duplicate text. On users' request, DsExcel also now adds a new API to support this feature. The following additions have been introduced:
    • The IWorksheet.AutoMergeRangesInfo property can retrieve information about all auto merge ranges in the current worksheet.
    • The IWorksheet.AutoMerge(IRange range, AutoMergeDirection direction = AutoMergeDirection.Column, AutoMergeMode mode = AutoMergeMode.Free, AutoMergeSelectionMode selectionMode = AutoMergeSelectionMode.Source) method can add auto merge information for a range.
    • The AutoMergeMode enum in the above method supports two modes: Free mode and Restricted mode. Read more in the resources below for more information on the two modes.
    • The AutoMergeDirection enum supports Row direction, Column direction, ColumnRow direction, RowColumn direction, and None direction.
    • The API also adds IncludeAutoMergedCells property, which needs to be set to true in order to export to PDF/HTML/Image/Excel/SJS/JSON.
• Image Sparkline Export
  • In SpreadJS, the Image sparkline feature is an enhancement to the Image formula, which enables displaying images in different sizes, thereby accepting more image parameters compared to Excel's Image formula. Additionally, the SpreadJS Image sparkline feature accepts the base64 string as a parameter to define the source of the image. The DsExcel v8 release expands the capabilities of the IMAGE function supported in v7.2 by supporting Image sparkline parameters from SpreadJS.
• Support Cell Decoration API
  • In the v8 release, DsExcel supports SpreadJS’s Cell Decoration feature. The API helps to manage cell decorations, including operations for adding, removing, and replacing cell decorations in the cells. The new ICellDecoration.Decoration property has been added to the IRange interface that helps to set cell decoration properties. In addition, CornerFold and CellDecorationIcon classes help to add corner fold and icons of cell decoration respectively.
• Option to Include/Exclude Binding Data
  • DsExcel adds a new property, IncludeBindingSource, to SjsSaveOptions, XlsxSaveOptions, and SerializationOptions to control whether to export the bound data to the file when exporting to SJS, SSJSON, and XLSX. The option is set to True by default.
  • This enables SpreadJS to display the final results calculated by DsExcel on the backend, eliminating the need for recalculations on the front end and avoiding the transfer of large data volumes. As a result, SpreadJS achieves faster loading times on the front end.
• Multiple Features Supported for Lossless I/O of SpreadJS
  • With the v8 release, MESCIUS have enhanced the compatibility of DsExcel with .sjs and SSJSON file formats of the SpreadJS v18 version (lossless import/export). Several features have been supported on SJS/SSJSON I/O. View the complete list of supported SpreadJS features.

Document Solutions Data Viewer (DsDataViewer)

• View Apache Arrow and Parquet Files
  • Apache Arrow and Parquet files are designed to efficiently handle large-scale data processing. In the v8 release, DsDataViewer now supports viewing Arrow and Parquet files.
• Enhanced Performance of Loading Large Data in CSV Files
  • Loading performance of large data in CSV files has been enhanced in v8. Large data can now be loaded in seconds.