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

DsExcel Template Enhancements

Enhanced Template language with better performance

In our recent efforts, we have undertaken the task of refactoring DsExcel Templates, emphasizing enhancing performance and stability. Our objective has been to address a wider spectrum of user requirements and usage scenarios, ensuring a seamless and efficient experience. The recent improvements include -

1. Addressing the absence of a data source by treating it as null.
2. Enabling OverwriteFillFormat functionality to seamlessly operate in both Pagination and non-Pagination modes.
3. Enhancing template processing capabilities to include support for Picture & Shapes.
4. Enhanced Template performance when processing templates with merged cells.
5. Ensuring the seamless continuation of features from the old template design without introducing any breaking changes.

Check out our demos for enhanced experience.

Help| Demo

Custom sort order and multi-column sorting

DsExcel Templates has long been supporting the sorting of template data using syntax to define the sort direction in template cells. Nevertheless, there is a practical need to sort cells based on the values of other cells and additionally incorporate the capability to sort data using multiple cell values. In response to this need, DsExcel broadens the syntax by enabling the inclusion of multiple sort conditions simultaneously rather than executing the template multiple times with different sort conditions.

Multiple scenarios supported are -

1. Sort a column based on the ascending/descending order of other column
2. Sort a column based on the ascending/descending order of the other column and further on the ascending/descending order of the third column
3. Sort a column based on a specified sequence of data

The following example sorts the data in column A in ascending order by date (column C) and then in descending order by sales in (column D).

Learn more about enhanced Sort syntax and scenarios supported in the resources below.

Help | Demo

Support for interrupting the execution of the ProcessTemplate method

In instances where the execution of the processTemplate method extends for a prolonged period due to a large volume of data or without a clear understanding of the underlying cause, there is a delay in showcasing the populated data. Hence, it will be useful if there is a way to interrupt the processTemplate method and revert to the initial state of an unfilled data template so that users have the option to limit the volume of the data source and process the template again.

DsExcel now supports interrupting the processTemplate method by calling Workbook.ProcessTemplate(CancellationToken) overload that accepts CancellationToken as a parameter. There are three ways in which you can interrupt the template processing -

• Use CancellationTokenSource.Cancel to request for cancellation immediately.
• Use CancellationTokenSource.CancelAfter to request for cancellation after the specified delay time.
• Use CancellationTokenSource.Dispose (.NET) or close (Java) to release resources.

The Workbook.ProcessTemplate method will throw an OperationCanceledException (in the case of the .NET System class) or a CancellationException (for Java Platform SE 8) under the following conditions:

1. The CancellationTokenSource associated with CancellationToken has initiated a cancellation request.
2. The internal data structures of Workbook.ProcessTemplate remain in a consistent state, ensuring the workbook object's safe use when handling the cancellation request.
3. Workbook.ProcessTemplate is actively engaged in its processing tasks.

In the event of an exception, the caller is responsible for deciding whether to accept the partially expanded template or revert to the previous state. If the decision is to revert, the caller must serialize the workbook before invoking the Workbook.ProcessTemplate method, subsequently deserializing the workbook after canceling the operation.

The following code uses the workbook in the current thread and cancels in another thread.

using (CancellationTokenSource cancellation = new CancellationTokenSource())
{
    void cancelHandler(object sender, ConsoleCancelEventArgs e)
    {
        // Exit process gracefully
        e.Cancel = true;
        cancellation.Cancel();
    };
    Console.CancelKeyPress += cancelHandler;
    cancellation.CancelAfter(TimeSpan.FromSeconds(10));
    Console.WriteLine("Start ProcessTemplate.");
    try
    {
        workbook.ProcessTemplate(cancellation.Token);
        Console.WriteLine("ProcessTemplate finished.");
    }
    catch (OperationCanceledException ex) when (ex.CancellationToken == cancellation.Token)
    {
        Console.WriteLine("ProcessTemplate was canceled.");
    }
}

Please look at the following resources to learn more about the feature.

Help | Demo

CalculationMode Options

This functionality is specifically designed for SpreadJS users who employ DsExcel in the backend to generate substantial Excel files. In some instances, users wish to avoid automatic calculation to reduce the time it takes to open worksheets. In certain scenarios, users only want to export specific sheets rather than the entire workbook. Notably, the Excel files may include cross-sheet formulas, and users prefer that these formulas remain uncalculated during the export process. As a result, users are searching for an option to enable/disable manual calculation for better control over these aspects.

To address this, DsExcel introduces the Workbook.Options.Formulas.CalculationMode property. This property allows setting one of the calculation mode enums on the worksheet, providing control over formula calculation when the worksheet is opened. The property supports the following enums:

• Automatic: Executes all calculations.
• Semiautomatic: Performs calculations for everything except Data Tables and Python formulas.
• Manual: Does not perform any calculations.

The property is supported during Excel I/O, SpreadJS I/O, and SSJSON I/O.

The following code demonstrates changing the calculation mode to Manual, where formulas are not automatically calculated when opening exported XLSX files in MS Excel.

IWorksheet worksheet = workbook.Worksheets[0];
worksheet.Range["A1:F7"].Value = data;
worksheet.Range["A:F"].ColumnWidth = 15;

//add table.
worksheet.Tables.Add(worksheet.Range["A1:F7"], true);

//show totals
worksheet.Tables[0].ShowTotals = true;
worksheet.Tables[0].Columns[4].TotalsCalculation = TotalsCalculation.Average;
worksheet.Tables[0].Columns[5].TotalsCalculation = TotalsCalculation.Average;

var comment = worksheet.Range["F8"].AddComment("Please press F9 to calculate the formula.");
comment.Visible = true;

//set calculation mode to manual
workbook.Options.Formulas.CalculationMode = CalculationMode.Manual;

// Save to an excel file
workbook.Save("CalculationOptions.xlsx");

Help | Demo

Support Table Reference in Cross Workbook Formulas

In large and complex Excel files, it is typical for formulas to incorporate references to data in other workbooks, whether internal or external. The conventional method of manually opening external workbooks, copying data, and integrating it into the Excel file is unnecessary. Instead, a more efficient approach involves directly referencing the data in the external workbook. In the v7.1 release, DsExcel supports referencing external workbooks with the Table formula.

workbook.Worksheets[0].Range["B1"].Formula = "=SUM('[Sales.xlsx]'!Table1[Sales])";

Help | Demo

Ignore Errors in Range

To empower users to bypass errors and prevent the display of the green triangle at the top-left corner of a cell in Excel, DsExcel introduces the IgnoredError property within the IRange interface. This property, coupled with the IgnoredErrorType enumeration, allows users to dismiss errors like invalid formula results, numbers stored as text, inconsistent formulas in adjacent cells, and more, selectively within a specific cell range in Excel.

worksheet.Range["A8:F11"].IgnoredError = IgnoredErrorType.All;

Help | Demo

Export Barcodes as Pictures in Excel Files 

It is now feasible to preserve Excel files containing barcodes generated from SpreadJS or DsExcel. DsExcel adds the Workbook.ConvertBarcodeToPicture(ImageType) method, enabling the conversion of barcodes to images across all worksheets for seamless storage in Excel files, eliminating the occurrence of '#Ref' values. In cases where no ImageType parameter is explicitly specified, the default is set to the SVG image type.

// Convert all barcode formula results to pictures.
workbook.ConvertBarcodeToPicture(ImageType.PNG);

 Help | Demo

Search Cells with Tags

DsExcel has been supporting the Tag property, which functions exclusively with SpreadJS. This property can be affixed to cells, rows, columns, or sheets containing specific types of data, along with options and suggestions pertaining to that data. Additionally, you can import/export a tag with uncomplicated data using SSJSON. In the v7.1 release, DsExcel introduces the SpecialCellType.Tags and IRange.SpecialCells(SpecialCellType.Tags) overload, facilitating the identification of cells with tags within a designated range.

The following code finds cells with tags and sets them to Red:

IRange allTagCells = worksheet.UsedRange.SpecialCells(SpecialCellType.Tags);
allTagCells.Font.Color = Color.Blue;
foreach (var area in allTagCells.Areas)
{
    for (int i = 0; i < area.Cells.Count; i++)
    {
        if (area.Cells[i] != null && area.Cells[i].Tag is string && int.Parse(area.Cells[i].Tag as string) % 10 == 0)
        {
            area.Cells[i].Interior.Color = Color.Red;
        }
    }
}

Help | Demo

SpreadJS Compatibility Features

DsExcel integrates additional SpreadJS features, improving compatibility with the client-side SpreadJS product. It's essential to highlight that these new features function exclusively with SpreadJS and are incompatible with Microsoft Excel.

• Get and set cell background images - Help | Demo 
• Support lossless .sjs/ssjson I/O of GanttSheet - Help | Demo
• Support lossless .sjs/ssjson I/O of ReportSheet - Help | Demo

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

In tandem with our commitment to continuous improvement, GrapeCity Documents has rebranded to Document Solutions. This change extends to the APIs and controls within the Document Solutions suite. Below, you'll find the updated nomenclature:

Document Solutions for Excel (DsExcel .NET) previously GrapeCity Documents for Excel, .NET (GcExcel .NET

We've made it easy to upgrade your existing packages to the new packages using the Documents Migration tool. This tool is included in the trial Downloads zip file found on the above product links. For the v7.0 release cycle, packages with old names will be provided separately, ensuring access to the same v7 new feature updates. This approach is designed to facilitate a seamless transition for users.

It's important to emphasize that despite the adoption of new package names, only the package names themselves are altered. The namespace and type names remain unchanged, eliminating the need for any modifications in your C#/VB user codes.

Async User-Defined Function Support

Excel supports asynchronous calculations through the use of custom functions that leverage asynchronous programming techniques. Suppose you have a scenario where a custom function needs to fetch data from an external source or perform a time-consuming computation. Traditionally, synchronous functions would halt Excel's responsiveness until the calculation is complete, causing delays in the user interface. With asynchronous calculations, you can improve responsiveness by allowing other operations to continue while the time-consuming task is in progress. 

In DsExcel, a user-defined function derived from the new AsyncCustomFunction class now supports asynchronous calculations. Additionally, the CalcError type introduces a 'Busy' enum, signifying that a cell is currently engaged in the calculation of an asynchronous formula. This enhancement in DsExcel empowers users to leverage asynchronous calculations within their custom functions, providing flexibility and efficiency in scenarios involving complex computations.

Have a look on following resources to view usage of this new class.

Help  | Demo

Enhancements in DsExcel Templates

Maintain Image Aspect Ratio

Preserving the aspect ratio of images is crucial for several reasons. Firstly, it ensures that certain images, such as country flags, maintain their proportional relationship between width and height, preventing them from appearing distorted. Secondly, it prevents images from appearing smaller and losing important details. In the v7 release, new property namely image.keepaspect or image.ka, have been introduced to DsExcel Templates. When set to true, these properties ensure that the aspect ratio of the image is maintained, allowing it to fit within the cell size regardless of its height or width. 

Example: Following code sets image.keepaspect to true for a Flag image

{{ds.BikeSeries.CountryImage(image=true,image.keepaspect=true)}}

Note that the image stretches to the cell along the height and width when image.keepaspect is False, while when True, the image maintains it’s aspect ratio within the cell.

Help | Demo 

Repeat Shapes and Images in Pagination Mode

DsExcel Templates has been providing support for pagination mode enabling the pagination of worksheets within a report. This feature is particularly useful for reports with a consistent layout on each sheet but varying data, such as invoices, progress reports, and medical test reports. In such reports, there is often a need to repeat shapes and images, including logos or graphics, along with the data. In the v7 release, DsExcel Templates introduces enhanced support for the repetition of shapes and images with data in an Excel file when the Excel template is processed. This enhancement ensures a more dynamic handling of shapes and images, adding flexibility to your report generation process.

The shapes or images will repeat as per the parent of the top left cell from where the image or shape starts.

Example: In following snapshot, the image repeats to 10 rows (CP=10) as per the parent cell of D12 which is B12

Help

Export Excel to HTML with CSS Inline Option

DsExcel has long supported the export of Excel spreadsheets to HTML, offering the flexibility to include a separate CSS file as a single entity. Notably, the CSS Inline option becomes crucial in scenarios where content needs to be shared as HTML email or integrated into a CMS (Content Management System). In such instances, the styling tags are directly embedded within the content.

In the latest update, DsExcel introduces a new feature to export Excel to HTML with the CSS Inline option. This functionality allows you to export an Excel file with style attributes embedded directly within HTML elements. Accompanying this capability, DsExcel introduces the HtmlSaveOptions.CssExportType enum, providing three values for exporting Excel files to HTML with different CSS options:

1. External: Export css to separate file. 
2. Internal: Export css with the style tag in HTML. 
3. Inline: Export css with the style attribute inside HTML elements.

Example: Following code exports Excel workbook to HTML with CssExportType.Inline option. Note that the style tags are embedded within the HTML file.

Help | Demo

Set First Page Number to 'Auto' in Page Setup

When printing an Excel sheet, users can incorporate the 'FirstPageNumber' property within the Page Setup Dialog to introduce numbering to the pages. To utilize a default numbering system, there exists an option to set this property to 'Auto.' 

For programmatic use, DsExcel introduces the IPageSetup.IsAutoFirstPageNumber property, facilitating the retrieval and modification of whether the first page number is set to 'Auto' when printing. The default value is true; however, if the IPageSetup.FirstPageNumber property is explicitly set, the IPageSetup.IsAutoFirstPageNumber property becomes false and requires resetting.

Example: The following code snippet demonstrates how to set the IPageSetup.IsAutoFirstPageNumber property to true for a worksheet that already has the FirstPageNumber set to 3. This ensures that the first page number is automatically determined when printing, even if it was previously set to a specific value.

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

var fileStream = GetResourceStream("xlsx\\ConfigIsAutoFirstPageNumber.xlsx");
workbook.Open(fileStream);
IWorksheet worksheet = workbook.Worksheets[0];

worksheet.PageSetup.CenterFooter = "&P";

//Set auto page number, original first page number is 3.
worksheet.PageSetup.IsAutoFirstPageNumber = true;
        
// Save to an excel file
workbook.Save("SetIsAutoFirstPageNumber.xlsx");

Help | Demo

Enhanced Formatting for Trendline Equations in Charts (and Export)

When graphing data in a chart, it is often crucial to visually represent the underlying trends. Trendlines in Excel serve as a valuable tool for analyzing data and predicting future values based on existing trends. DsExcel has been supporting the addition of trendline equations to charts using the ITrendline interface, specifically through the ITrendline.DisplayEquation and ITrendline.DisplayRSquared properties.

With the v7 release, to enhance the visual interpretation of trendlines, DsExcel introduces formatting properties to the ITrendline interface. A new property, DataLabel, is added to the ITrendline interface, allowing users to access the data label associated with the trendline. This feature enables users to utilize properties such as Font, Format, NumberFormat, Orientation, Direction, and AutoText from the IDataLabel interface to format the trendline equation label.

Furthermore, the v7 release includes the ITrendline.Delete method, providing a means to remove the trendline equation label when needed. Notably, trendline equations will now be supported in exports to PDF, HTML, and images, ensuring consistency across various formats. These enhancements contribute to a more comprehensive and visually appealing representation of trendline data in DsExcel.

Example: In the following snapshot, a trendline equation is formatted using the new properties of IDataLabel interface.

Help | Demo

Specify Default Value for Cell

Users can now have the option to establish a default value for a cell. If the regular value is unspecified, the default value will be shown and included in the calculations. DsExcel adds IRange.DefaultValue property to get or set the default value of cell.

Example: Following code adds a default value for cell range C5:C8 but overrides formula for cells C6 & C8. The calculation takes into account Default values for C4, C5 & C7.

Help | Demo

Support Smooth Lines in Chart in PDF Export

In pursuit of a visually appealing representation and a comprehensive understanding of the charted data, DsExcel now offers support for smooth chart lines during the export of Excel files to PDF. 

The following chart types benefit from this feature:

1. Line Chart
2. Scatter Chart
3. Combo Chart

Help | Demo

Direct Acroform Creation with DsExcel API

The latest update to DsExcel introduces a powerful feature, allowing users to create Acroforms directly using the DsExcel API when exporting spreadsheets to PDF. A new property, PdfSaveOptions.FormFields, has been added. When set to true, this property enables the export of Excel Form controls as PDF Form controls during the export of an Excel file to PDF. The corresponding properties of certain Form controls are mapped to their respective PDF Form control properties.

For a detailed understanding of the mapped properties, limitations and to explore this feature further, please refer to the resources provided following the snapshot below. This enhancement enhances the versatility of DsExcel, providing users with more comprehensive options for creating and managing Acroforms seamlessly.

Help | Demo | Demo Use case

Support Exporting of Funnel Charts to PDF

DsExcel will also now support Funnel Charts on exporting Excel files to PDF. So you can now add a Funnel chart to excel and directly save the file to PDF.

//Create a funnel chart.
IShape shape = worksheet.Shapes.AddChart(ChartType.Funnel, 10, 150, 300, 200);
shape.Chart.SeriesCollection.Add(worksheet.Range["A1:B7"]);

//Set funnel chart style.
shape.Chart.ChartTitle.Text = "Funnel Chart";
foreach (var s in shape.Chart.SeriesCollection)
{
    s.DataLabels.Font.Color.RGB = Color.White;
}
        
// Save to a pdf file
workbook.Save("FunnelChartPdf.pdf");

Help | Demo

Specify Columns to Quote on Exporting to CSV

There are instances where it becomes necessary to export specific columns in Excel with quotation marks. This precaution is taken to prevent any potential misrepresentation of the data when exported to CSV. DsExcel adds new property CsvSaveOptions.QuoteColumns to designate the specific columns requiring quotation marks. The indices of the column can be specified in the property.

If the property is set to null, the behavior is determined by CsvSaveOptions.ValueQuoteType. However, if the property is not null, CsvSaveOptions.ValueQuoteType is overridden. When CsvSaveOptions.QuoteColumns is not null, only the data in the specified column will be enclosed in quotes, leaving the data in other columns unquoted. It's important to note that if a value contains special characters such as quotation marks or separators, it will be enclosed in quotes regardless. 

Example: In the following example, CsvSaveOptions.QuoteColumns property defines the columns requiring quotation marks, before exporting to CSV.

Help | Demo

More Features for SpreadJS Integration

DsExcel incorporates support for additional SpreadJS features, enhancing compatibility with the client-side SpreadJS product. It's important to note that these added features exclusively function with SpreadJS, and are not compatible with Microsoft Excel.

• Support for cell.altText property - Help

• Support for IRange.DefaultValue - Help

• Support for Mask style - Help

• Support for password in the protected sheet - Help