What's New in Document Solutions for Excel Java v7

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 | Demo

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 | Demo

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 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 | Demo

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 | Demo

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 | Demo

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 | Demo

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 isFirstRowDataparameter 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 | Demo

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 | Demo

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 | Demo

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 | Demo

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 | Demo

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

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

Use Custom Fonts via Font Streams on PDF Export 

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 | 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 Java) - previously GrapeCity Documents for Excel, Java (GcExcel Java)

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