What's New in Document Solutions for Excel v7.2

Document Solutions for Excel (DsExcel), .NET and Java editions, continue to offer exciting new features and enhancements in the v7.2 release. This update introduces support for many advanced Microsoft Excel features, providing users with more powerful and flexible tools. Significant improvements in DsExcel Templates enhance the feature set and performance for template creation. Additionally, this release offers new features for integration with SpreadJS, including new method overloads for working with SJS JSON files. The release also adds advanced features to the client-side Document Solutions Data Viewer (DsDataViewer), expanding its data extraction capabilities and incorporating search functionality, a common requirement for any data viewer. Discover the details of the key features in this latest release below. 

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

Important Information: A Shift from ‘GrapeCity Documents’ to 'Document Solutions'

GrapeCity Documents has undergone a marketing rebranding and as a result, the v7.2 Maintenance release in November 2024 will mark the final release under the old GrapeCity Documents product branding.

Starting with version 8.0.0 onwards, the packages will only be released using the new package names and the GrapeCity packages will be phased out.

It is highly recommended to update these new packages as soon as you can. Transitioning to the new packages has been simplified through the Documents Migration tool, conveniently included in the product trial download zip files available for the respective products.

Please note that despite the adoption of new package names, the APIs, namespaces, and type names remain unchanged. You will not need to modify your code. Only the package names and DLL names have been changed; they just require updates to the new package references.

Existing subscriptions will continue to receive the new package updates. If you have any questions or need any assistance with this update, please contact our support teams.

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.

The following code uses the new GoalSeek method to find the interest rate a person needs to secure (changingCell parameter) to meet their target loan goal (goal parameter).

//create a new workbook
var workbook = new GrapeCity.Documents.Excel.Workbook();

//Set MaximumIterations and MaximumChange  
workbook.Options.Formulas.MaximumIterations = 1000;
workbook.Options.Formulas.MaximumChange = 0.000001;

var activeSheet = workbook.ActiveSheet;
activeSheet.Range["A1:A4"].Value = new string[] { "Loan Amount", "Term in Months", "Interest Rate", "Payment" };
// This is the amount that you want to borrow.
activeSheet.Range["B1"].Value = 100000;
activeSheet.Range["B1"].NumberFormat = "$#,##0";
// This is the number of months that you want to pay off the loan.
activeSheet.Range["B2"].Value = 180;
// This is the number of interest rate.
activeSheet.Range["B3"].NumberFormat = "0.00%";
// This formula calculates the payment amount.
activeSheet.Range["B4"].Formula = "=PMT(B3/12,B2,B1)";
activeSheet.Range["B4"].NumberFormat = "$#,##0";
// Use goal seek to calculate the value of cell B3.
activeSheet.Range["B4"].GoalSeek(-900, activeSheet.Range["B3"]);
activeSheet.Range["A1:B4"].AutoFit();
// Save to an excel file
workbook.Save("GoalSeek.xlsx");

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

Label and Value Filters in Pivot Table

In the v7.2 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.

The following code shows how to apply the Label filter on a ‘Product’ data field where the caption contains ‘mi’ in its text.

//config pivot table's fields
var field_Category = pivottable.PivotFields["Category"];
field_Category.Orientation = PivotFieldOrientation.ColumnField;

var field_Product = pivottable.PivotFields["Product"];
field_Product.Orientation = PivotFieldOrientation.RowField;

var field_Amount = pivottable.PivotFields["Amount"];
field_Amount.Orientation = PivotFieldOrientation.DataField;
field_Amount.NumberFormat = "$#,##0.00";

// Filter for products where the product name contains 'mi'.
field_Product.PivotFilters.Add(PivotFilterType.CaptionContains, "mi");

worksheet.Range["A:D"].EntireColumn.AutoFit();
        
// Save to an excel file
workbook.Save("PivotLabelFilter.xlsx");

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

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. DsExcel now supports 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. 

DsExcel adds 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. 

Meanwhile, 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 using the following syntax:

F/Filter = [start:stop:step]

Also supported is 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:

F/Filter = [start:stop:step](field1 > 1 and field2 = 2 or field3 <> 3)

The following example shows a complex business scenario where data is extracted from two tables by applying two filter conditions in respective cells.

Check out our detailed resources below.

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

Bind Excel Templates with JSON data source

Excel Templates can now be bound directly with JSON files. In addition, the above Filtering support also works with data from multiple JSON data sources. 

See our detailed resources below.

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

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 the latest 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.

The following code sets an image programmatically on a cell in a spreadsheet.

// The user needs to set a custom web request handling class,
// and all network requests in GcExcel will use this class to send network requests.
Workbook.WebRequestHandler = new WebRequestHandler();
var workbook = new Workbook();
var sheet = workbook.ActiveSheet;
// Set image function
sheet.Range["A1"].Formula = "=IMAGE(\"https://support.content.office.net/en-us/media/926439a2-bc79-4b8b-9205-60892650e5d3.jpg\")";
// Calculate all formulas so the asynchronous image function will run.
workbook.Calculate();
// Block the current thread until all asynchronous functions have finished.
// If this method is not called, the exported file may contain #BUSY! error.
workbook.WaitForCalculationToFinish();
workbook.Save("D:\\res.pdf"); 

For implementation of the WebRequestHandler class, please see the complete sample below.

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

Enhancement for Asynchronous Calculations

In the v7 release, we introduced the AsyncCustomFunction class, enabling user-defined functions derived from this class to support asynchronous calculations. In the v7.2 release, we enhanced this capability 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.

Check out the following resources to see complete implementation of this method.

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

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.

Refer to the following example code to add a filter to the first row containing data using the isFirstRowData parameter of the AutoFilter method:

// Create a new workbook.
var workbook = new Workbook();

IWorksheet worksheet = workbook.Worksheets[0];

// Add data to the range.
object[,] data = new object[,]{
{"Luigi", "New York", new DateTime(1998, 4, 7), "Blue", 67, 165},
{"Richard", "New York", new DateTime(1968, 6, 8), "Blue", 67, 165},
{"Nia", "New York", new DateTime(1972, 7, 3), "Brown", 62, 134},
{"Jared", "New York", new DateTime(1964, 3, 2), "Hazel", 72, 180},
{"Natalie", "Washington", new DateTime(1972, 8, 8), "Blue", 66, 163},
{"Damon", "Washington", new DateTime(1986, 2, 2), "Hazel", 76, 176},
{"Angela", "Washington", new DateTime(1993, 2, 15), "Brown", 68, 145}
};

worksheet.Range["A1:F7"].Value = data;

// Set column width.
worksheet.Range["A:F"].ColumnWidth = 15;
            
// Apply filter to first row.
worksheet.Range["A1:F7"].AutoFilter(true, 4, "<72");

// Create a file stream to export ssjson file.
FileStream outputStream = new FileStream("HeadersAsData.ssjson", FileMode.Create);

// Export the ssjson file.
workbook.ToJson(outputStream);

// Close the stream.
outputStream.Close();

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

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. The sparklines can be added using the following syntax in DsExcel:

=LINESPARKLINE(data,dataOrientation,[dateAxisData],[dateAxisOrientation],[setting])

=COLUMNSPARKLINE(data,dataOrientation,[dateAxisData],[dateAxisOrientation],[setting])

=WINLOSSSPARKLINE(data,dataOrientation,[dateAxisData],[dateAxisOrientation],[setting])

To learn more about each parameter, refer to the documentation. The 'setting' parameter is used to set various sparkline settings.

Note: The support for adding sparklines to spreadsheets is a feature of SpreadJS, and the result of the functions would be visible in SpreadJS SSJSON I/O, SJS I/O, PDF, Image, and HTML exported files.

worksheet.Range["G3:G5"].Formula = "=COLUMNSPARKLINE(B3:F3,1,,,\"{showMarkers:TRUE}\")";
worksheet.Range["G3:G5"].Formula = "=LINESPARKLINE(B3:F3,1,,,\"{showMarkers:TRUE,lineWeight:1.5,markersColor:#7030a0}\")";
worksheet.Range["G3:G5"].Formula2 = "=WINLOSSSPARKLINE(B3:F3-300000,1,,,\"{showNegative:TRUE}\")";

The following image shows a Column Sparkline added to a spreadsheet in SpreadJS.

Check out the demos to see how to set the Sparkline functions.

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

Multiple Features Supported for Lossless I/O of SpreadJS

With the v7.2 release, we have enhanced the compatibility of DsExcel 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. View the complete list of supported SpreadJS features.

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.

The following code sets the PixelBasedColumnWidth to true in the workbook and exports the workbook to PDF, rendering results similar to SpreadJS.

// Create a new workbook with workbook options
WorkbookOptions workbookOptions = new WorkbookOptions();
// Enable pixel-based column width for the workbook
workbookOptions.PixelBasedColumnWidth = true;
var workbook = new Workbook(workbookOptions);

var fileStream = this.GetResourceStream("sjs\\Event budget.sjs");

workbook.Open(fileStream, OpenFileFormat.Sjs);
        
// Save to a pdf file
workbook.Save("SavePDFWithPixelBasedColumnWidth.pdf");

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

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. 

//create a new workbook
var workbook = new GrapeCity.Documents.Excel.Workbook();

workbook.Open("source.sjs");

// Generate a JSON string containing the contents of .sjs file format.
SjsSaveOptions saveOptions = new SjsSaveOptions();
string sjsJson = workbook.ToSjsJson(saveOptions);

// Generates a workbook from the JSON string containing the contents of .sjs file format.
SjsOpenOptions openOptions = new SjsOpenOptions();
workbook.FromSjsJson(sjsJson, openOptions);

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

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. 

The following code customizes the border style when exporting to PDF.

// Create a pdf file stream
FileStream outputStream = new FileStream("CustomBorder.pdf", FileMode.Create);

//create a new workbook
var workbook = new GrapeCity.Documents.Excel.Workbook();

var templateFile = this.GetResourceStream("xlsx\\CustomBorderStyle.xlsx");
workbook.Open(templateFile);

// Customizing the border style for exporting to PDF.
var pdfSaveOptions = new PdfSaveOptions();
var thinBorderSetting = new CustomBorderStyle { BorderWidth = 0.4 };
var middleBorderSetting = new CustomBorderStyle { BorderWidth = 1.5 };
var dashBorderSetting = new CustomBorderStyle { BorderWidth = 0.4, Dashes = new List<double> { 0.8, 0.8 } };
pdfSaveOptions.BorderOptions.Add(BorderLineStyle.Thin, thinBorderSetting);
pdfSaveOptions.BorderOptions.Add(BorderLineStyle.Medium, middleBorderSetting);
pdfSaveOptions.BorderOptions.Add(workbook.ActiveSheet.Range["B13"].Borders[BordersIndex.EdgeTop].LineStyle, dashBorderSetting);
//Save the workbook into pdf file.
workbook.Save(outputStream, pdfSaveOptions);
        
// Close the pdf stream
outputStream.Close();

This table describes the default values of all the border styles.

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

Document Solutions Data Viewer (DsDataViewer)

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.

The code below uses these options to customize the left sidebar:

 const viewer = new DsDataViewer("#root")
 // Remove all sidebar menu.
 viewer.sidebarLayout = ['']
 // Hide sidebar.
 viewer.showSidebar(false);

Help | Demo

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.

The following video shows our online sample demonstrating the code to extract data from selected cells.

Help | Demo

Ready to check out the new features of Document Solutions? Download a free 30-day trial today!