Document Solutions for Excel, .NET Edition 8.0.0

Released: Dec 11, 2024

Updates in 8.0.0

Features

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 in C#/.NET/Java, relational databases, .NET controls, 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.
    • 2D array (.NET only).
    • 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.
    • DataTable or DataView (.NET only).
    • Selected DataColumns.
    • LINQ result (.NET only).
  • 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.

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.