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

We are thrilled to introduce the latest version of our Document Solutions for Excel (DsExcel) v7.1. DsExcel represents our state-of-the-art API library designed to facilitate the rapid creation of Microsoft Excel spreadsheets. This new release includes improvements for DsExcel .NET and Java APIs, introducing features that ensure seamless compatibility with SpreadJS, our enterprise JavaScript spreadsheet. It also offers enhanced support for DsExcel Templates, along with various other updates. Explore the comprehensive details of this release below.

Ready to Check Out the Release? Download Document Solutions for Excel Today!

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 .NET | Help Java | Demo .NET | Demo Java

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 .NET | Help Java | Demo .NET | Demo Java

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 .NET | Help Java | Demo .NET | Demo Java

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 .NET | Help Java | Demo .NET | Demo Java

Use Custom Fonts via Font Streams on PDF Export (DsExcel Java)

DsExcel Java has long supported the Workbook.FontsFolderPath interface that allows users to designate the directory holding the required font files. It's important to note that FontsFolderPath must represent an absolute disk path. However, certain environments pose limitations, preventing users from storing fonts directly on the disk. In such cases, users can only supply font streams. For example, when customers deploy their applications as a war package in Java, fonts are packaged within the war package. Consequently, DsExcel must offer an interface that accommodates font streams in these scenarios.

In the Workbook class of DsExcel, the Workbook.FontProvider field has been incorporated to empower users in supplying fonts via font streams for tasks such as Auto Fit, PDF export, and Image export. Users can use the IFontProvider interface and its methods, namely getFontFilePaths and getFont, to implement font streams.

The getFontFilePaths method serves to retrieve all font file paths. On the other hand, the getFont method is responsible for returning the font stream corresponding to a given font file path. When a user uses the FontProvider class, DsExcel will exclusively search for font paths within the font streams. Without FontProvider implementation, DsExcel will default to searching in the FontsFolderPath.

The following example shows how to set Fonts through font streams:

// Create a new workbook.
Workbook workbook = new Workbook();
IWorksheet sheet = workbook.getWorksheets().get(0);
// Set style of the font.
sheet.getRange("A1").setValue("Sheet1");
sheet.getRange("A1").getFont().setName("Arial");
sheet.getRange("A1").getFont().setColor(Color.GetRed());
sheet.getRange("A1").getInterior().setColor(Color.GetGreen());
// Implement FontProvider.
Workbook.FontProvider = new IFontProvider() {
    @Override
    public List<String> getFontFilePaths() {
        return new ArrayList<>(Arrays.asList(
                "fonts\\arial.ttf",
                "fonts\\arialbd.ttf",
                "fonts\\ariali.ttf"
        ));
    }
    @Override
    public InputStream getFont(String fontFilePath) {
        return getClass().getClassLoader().getResourceAsStream(fontFilePath);
    }
};
// Save the workbook.
workbook.save("FontStreaming.pdf", SaveFileFormat.Pdf);

Help Java | Demo Java

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 .NET | Help Java | Demo .NET | Demo Java

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 .NET | Help Java | Demo .NET | Demo Java

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 .NET | Help Java | Demo .NET | Demo Java

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 .NET | Help Java | Demo .NET | Demo Java

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 .NET | Help Java | Demo .NET | Demo Java
• Support lossless .sjs/ssjson I/O of GanttSheet - Help .NET | Help Java | Demo .NET | Demo Java
• Support lossless .sjs/ssjson I/O of ReportSheet - Help .NET | Help Java | Demo .NET | Demo Java

Ready to Check Out the New Features of Document Solutions? Download a Free 30-Day Trial Today!