Released: Aug 20, 2024
7.2.0 中的更新
特性
Document Solutions for Excel
- Add Goal Seek to Spreadsheets
- Excel's Goal Seek function enables users to determine the necessary input value to achieve a desired result. By setting a target output, the Goal Seek feature automatically adjusts input values until the desired outcome is reached. This feature is invaluable for scenario analysis and decision-making in Excel modeling.
- DsExcel adds a new API to add the Goal Seek function programmatically to your spreadsheets. The new IRange.GoalSeek(double goal, IRange changingCell) method (Boolean) attempts to achieve the specified goal from the calculated result of the formula in the cell represented by IRange by modifying the specified changingCell. The goal parameter specifies the desired target output, whereas the changingCell parameter specifies the cell whose value will change to achieve the target value.
- Label and Value Filters in Pivot Table
- In this release, DsExcel enhances its Pivot Table Filters support, enabling developers to refine and manage large datasets programmatically in Pivot Tables. The new filter options added in DsExcel help in narrowing down data to focus on specific criteria, making analysis more precise and relevant.
- DsExcel supports a new API to add Label (including Date filters) and Value filters in Pivot Tables. The Label filter API helps to filter data based on the labels in the row or column fields, while the Value filter API shows only the data where values meet certain conditions.
- DsExcel provides a PivotFilters property of the IPivotField interface that enables a user to add Label, Value, or Date filters to the pivot table field using a PivotFilterType enumeration that can be passed as a parameter in the Add method of the IPivotFilters interface.
- The new API additions also include an AllowMultipleFilters property that helps to add both Label and Value filters simultaneously on a field, thereby enabling developers to add multiple filters on a field.
- Also included in the new API additions are ClearLabelFilter, ClearValueFilter, and ClearAllFilters methods to delete the filters.
- Multiple Enhancements in DsExcel Templates
- Filter Data from Single or Multiple Data Sources
- In business scenarios, developers integrate data from multiple data sources. In such applications, there are relationships between the data. Support has been added for representing these relationships by defining Filters in the Excel Template. A single template cell can correspond to multiple records in the data source, and developers can filter the expanded data accordingly. Filters in templates can also be easily modified, allowing the reports to adapt dynamically to changing data. The data can be filtered from System.Data.DataTable or ITableDataSource datasources in .NET and java.sql.ResultSet or ITableDataSource datasources in Java.
- Added the ability to define Conditional and Slice filters using the F or Filter property. The Filter syntax can be added in the desired cell of the Excel Template file. The syntax for the Conditional filter is F/Filter = (field1 > 1 and field2 = 2 or field3 <> 3), enhancing data manipulation and report generation from multiple tables. The Conditional filter allows users to refine their data using operators and keywords like AND, OR, NOT, and LIKE.
- The Slice filter enables data extraction by specifying a range from one index to another. The Slice filter can be added in the desired cell of the Excel Template file.
- Added support for combining the Conditional and Slice filters together that will first filter the records having a Conditional filter and then filter the records as per the Slice filter applied.
- Bind Excel Templates with JSON data source - Excel Templates can now be bound directly with JSON files. In addition, the Filtering support also works with data from multiple JSON data sources.
- Asynchronous IMAGE Function - The IMAGE function in Excel allows users to insert images directly into cells, enhancing the visual appeal of their spreadsheets and making it easier to illustrate points, add logos, or incorporate relevant visuals alongside textual data. With this release, DsExcel now includes the IMAGE function, allowing users to add images within the cells.
- The image types supported are the same as those in the ImageType enum and can display images from online sources via URLs.
- DsExcel also added the Workbook.WebRequestHandler interface to allow users to customize how network requests are sent. The interface allows for the handling of web requests asynchronously and provides a way to send GET requests to a specified URI.
- Enhancement for Asynchronous Calculations - In the v7 release, the AsyncCustomFunction class was introduced, enabling user-defined functions derived from this class to support asynchronous calculations. In this release, this capability has been enhanced by adding the Workbook.WaitForCalculationToFinish() method. This method allows users to ensure that all necessary computations are completed before proceeding with any other operations that depend on the calculation results.
- Multiple Features Supported for SpreadJS Integration
- An Option to Make Filtering Work Similarly to SpreadJS - DsExcel added the option to treat the first row of data as data and not as a header while filtering the data. The API introduces a new Range.AutoFilter() overload, which will help make the DsExcel filter behavior similar to that of SpreadJS. The overload includes the isFirstRowData parameter, which indicates whether the first row of the selection area participates in filtering or not. This feature is reflected only in SpreadJS.
- New Sparkline Functions - DsExcel now supports adding Line, Column, and Win/Loss sparklines through corresponding new functions. The sparklines are compact, simple charts embedded in cells to visualize data trends.
- Multiple Features Supported for Lossless I/O of SpreadJS - With this release, the compatibility of DsExcel has been enhanced with .sjs and SSJSON file formats of the SpreadJS 17.1 version (lossless import/export). Several features have been supported on SJS/SSJSON I/O.
- Pixel-Based Rendering in PDF and Image Export - To enhance the export of spreadsheet content to PDF and images and make it render similar to SpreadJS, DsExcel has added the WorkbookOptions class that offers the property - PixelBasedColumnWidth. If true, the workbook would use pixel-based column width while rendering spreadsheets to PDF and images. Also, this option will make other API behaviors, like Auto-Fit column and other results of PDF and image rendering, more similar to SpreadJS.
- Support for the FromSJSJson API to Load a Single JSON SJS File - DsExcel has added the FromSjsJson method in both the Workbook class and the IWorkbook interface, allowing users to load a JSON file string or stream that is generated from an .sjs file.
- Customize Border Style in Export to PDF - DsExcel now allows you to export PDF documents with a custom border style using the new BorderOptions property of the PdfSaveOptions class. This property uses the BorderWidth and Dashes properties of the CustomBorderStyle class, as well as the BorderLineStyle enumeration, to define the border width, dash length, and line style. The BorderWidth property sets the border width when exporting a PDF document, while the Dashes property determines the length of each segment in the dashed line.
Document Solutions Data Viewer
- Advanced Search Options
- Searching large spreadsheets with specific search terms or patterns is made easier with the new release of DsDataViewer. The Search button is now available by default in the left sidebar. You can search for words within your spreadsheet using the following advanced options:
- "Find What" field represents the string to be searched.
- "Within" option allows you to choose whether to search within the Current Sheet or All Sheets.
- "Match Case" determines whether to ignore case sensitivity. When selected, it will perform a case-sensitive search.
- "Match Exactly" determines whether to perform an exact match. When selected, it will search for an exact match.
- "Use Wildcards" determines whether to use wildcard characters like ?, *, or ~. When selected, you can use wildcard characters for matching.
- The key to add the Search panel programmatically is 'SearchPanel'. To enable users to customize the display of the sidebar, DsDataViewer offers the following API:
- DsDataViewer.showSidebar(boolean): Whether to display the sidebar.
- DsDataViewer.sidebarLayout(string[]): Indicates which sidebars to display.
- Extract Data from Any/Selected Cell Programmatically - You can now programmatically extract the unformatted/formatted value of any/selected cell. DsDataViewer provides a new API that will help to quickly extract specific data without navigating through large spreadsheets. The extracted data can then be consolidated for further analysis.
- DsDataViewer introduces the following new methods to extract data from cells:
- getSheet(index): Fetches the specified sheet based on the index.
- getSheetFromName(name): Fetches the sheet with the specified name.
- getActiveSheet(): Fetches the active sheet.
- The following new methods have been added to the WorkSheet object:
- getSelections(): Retrieves the selections in the current sheet.
- getText(row, column): Retrieves formatted text in the cell based on the desired row and column index.
- getValue(row, column): Retrieves unformatted data from the specified cell based on the desired row and column index.
修補程式
Document Solutions for Excel
- The image size is not same as the PDF file exported by SpreadJS.
- The exported XLSM file is corrupted when opening in MSExcel.
- When a specific xlsx file is loaded and saved, the saved file is corrupted.
- Wrapped text cannot be fully displayed in the exported PDF file.
- The pagination results in the exported PDF file are inconsistent with SpreadJS.
- The rotated circular shapes are distorted in the exported image files.
- The line breaks in the exported PDF file are inconsistent with the PDF file exported by SpreadJS.
- The size of the QR code in the exported PDF file is not same as SpreadJS.
- The borders of some cells are lost in the exported Excel file after loading the SJS file.
- The cell format is changed in the exported SJS file and Excel file after loading the original SJS file.
- The cell format is changed in the exported SJS file after inserting specific rows.
- The page margins in the exported PDF file are inconsistent with SpreadJS.
- The seal is stretched in the exported PDF file.
- The cell background image is inconsistent with SpreadJS in the exported PDF file.
- The filtering status of PivotTable in the exported Excel file is changed.
- The table border in the exported PDF file is thicker than the PDF exported by SpreadJS.
- Some whitespace characters are missing in the exported PDF file.
- The table style is changed in the exported SJS file.
- The border style is changed in the exported SJS file.
- Exception is thrown on saving Excel file containing Charts to PDF file with Norway culture.
- Exception is thrown on refreshing the PivotTable.
- The font size is changed in the exported SJS file after loading the original SJS file.
- The conditional format is incorrect in the exported SJS file.
- Incorrect value returned from array formula when calculation engine is off.
- The table style in the exported PDF file is incorrect.
- Some cell values are not exported in the Excel file when license is not set.
- The result of the LOG function is incorrect.
- Exception is thrown on setting the orientation of PivotField to column field and row field.
- The result of the SEARCH function is incorrect.