What's New in GrapeCity Documents for Excel v5

December 10 2021

GrapeCity Documents for Excel (GcExcel) is excited to announce the new v5 release. Highlights of both the .NET and Java releases include a faster way to import data from Excel files without loading object models, a new printing API that works on .NET 5 and .NET 6, and more! Discover all of the new features below.

Download Now!

Import Data Function

You don't typically need to extract all of the data from an Excel file—just specific data. However, you may not want to load the whole Excel file in your Excel API, which makes the process more tedious and time-consuming. GcExcel adds the new ImportData() function that imports data alone from Excel files, without loading the whole object model.

This function makes it faster and efficient to import data from several Excel files simultaneously. You can either import all the data or specify the range of data to import from the worksheet.

// Create a new workbook
Workbook workbook = new Workbook();
InputStream fileStream = this.getResourceStream("xlsx\\AgingReport.xlsx");
// Import data of a range from the fileStream
Object[][] data = Workbook.importData(fileStream, "Aging Report", 8, 2, 21, 7);
// Assign the data to current workbook
workbook.getWorksheets().get(0).getRange(0, 0, 21, 7).setValue(data);

// Save to an excel file
workbook.save("ImportData.xlsx")

import data

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

New Formula2 Property to Set Dynamic Array Formula

Microsoft Excel launched the concept of dynamic array formulas in 2018 to return multiple results to a range of cells based on one formula, also called spilled-range functionality.

These formulas are used to create a list of unique values by removing duplicates, sorting lists, outputting a filtered range of data, and more. Existing functions can utilize this same spill-range functionality.

GcExcel adds extensive support for adding dynamic array formulas to Excel files through code. The new IRange.Formula2 property allows you to define a dynamic array formula in a worksheet. The property also allows you to specify a formula without automatically adding the intersection operator.

The following code sets a dynamic array formula using the IRange.Formula2 property. It also uses the filter function to filter data from a range and then "spills" the result into a range of cells.

formula

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

Threaded Comments

For better discussion and responses, Excel threaded comments allow you to reply to inline or nested comments, as well as string several comments, together to form a conversation style thread. GcExcel has supported adding comments to Excel documents, but, by adding comment threading to GcExcel, we now have complete support for threaded comments.

GcExcel adds the IWorksheet.CommentsThreaded collection to work with threaded comments in Excel documents—specifically adding replies, deleting comments, navigating comments, and more. The new IRange.AddCommentThreaded(..) method will help add threaded comments to the range and IRange.ClearCommentsThreaded() will help clear threaded comments from the range.

Suppose two or more users have a common comment for several Excel files (as shown in the example below). The new API will help add comments programmatically to cover several Excel documents.

comment

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

Linked Pictures

Combining charts, data tables, conditional formatting, etc. all in one sheet can be complex. Since the size of these elements is often not uniform, presenting them in a single dashboard sheet can be difficult to accommodate. This is where a linked picture can be beneficial. A linked picture can be created to the actual data/table/chart in your final dashboard.

The advantage is not only to accommodate size but also that when your data changes, the data in your linked picture also changes. This feature is also sometimes referred to as Camera Picture. GcExcel now supports adding linked pictures through various overloads to the new method—IShapes.AddCameraPicture(..). The following new properties have also been added:

IPictureFormat.TransparentBackground { get; set; }: Set whether the specified picture format uses a transparent background
IPictureFormat.Reference { get; set; }: Set the reference of current picture

The following code adds a camera picture of a range of data:

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

var ws = workbook.Worksheets[0];

object[,] data = new object[,]{
    {"Name", "City", "Birthday", "Eye color", "Weight"},
    {"Richard", "New York", new DateTime(1968, 6, 8), "Blue", 67},
    {"Nia", "New York", new DateTime(1972, 7, 3), "Brown", 62},
    {"Jared", "New York", new DateTime(1964, 3, 2), "Hazel", 72}
};

ws.Range["A1:E4"].Value = data;
ws.Range["A:E"].EntireColumn.ColumnWidth *= 1.5;

ws.Range["A1:E1"].Interior.Color = System.Drawing.Color.FromArgb(68, 114, 196);
ws.Range["A1:E1"].Font.Color = System.Drawing.Color.White;
ws.Range["A1:E4"].Borders.Color = System.Drawing.Color.FromArgb(91, 155, 213);
ws.Range["A1:E4"].Borders.LineStyle = BorderLineStyle.Thin;

//add camera picture
ws.Shapes.AddCameraPicture("$A$1:$E$4", 398, 0, 347, 58);

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

Note how data gets changed in the Linked Picture when changed at the source.

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

Workbook Views

Users normally work in the standard Microsoft Excel view, However, worksheet views can be changed depending on what you want to analyze. For example, the Page Layout view helps you visualize how a document will look when printed, while Page Break view shows you where the page breaks will appear.

If several of your documents need to have a common view when they are opened, GcExcel now lets control that programatically. The IWorksheetView.ViewType helps define the three predefined views: Normal, Page Layout, and Page Break Preview.

In Excel, users can also customize the views. With a custom view, you can establish certain display and print settings, such as no gridlines or headings, specific margins, and a specific Workbook view.

The view can then be saved to easily apply it to the spreadsheet at any time. We are also excited to introduce a new ICustomView interface and collection IWorkbook.CustomViews in GcExcel to add custom views to the worksheet.

The following code sets the default view type of the worksheet to PageBreakPreview:

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

IWorksheet worksheet = workbook.Worksheets[0];
worksheet.Open("CustomerProfitabilityAnalysis.xlsx");
worksheet.Range["J12"].Value = 1;

//Set the view mode of the worksheet to PageBreakPreview.
worksheet.SheetView.ViewType = ViewType.PageBreakPreview;
//Modify the zoom of the PageBreakPreview to 80%.
worksheet.SheetView.Zoom = 80;

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

view

Support for the GETPIVOTDATA Function

Pivot tables showcase large amounts of data, but users may run into instances where they only need to retrieve specific data based on the pivot table structure, rather than just cell reference.

The GETPIVOTDATA function works even when a pivot table changes, as long as the referenced field(s) is still present. GcExcel now supports the GETPIVOTDATA(...) function and the new IRange.GenerateGetPivotDataFunction(IRange destination = null) method to generate the GETPIVOTDATA function for different worksheets.

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

Print Documents Directly to a Physical Printer

Until now, cross-platform APIs working in .NET Standard Apps have faced challenges using Print commands. This challenge is mainly due to the limitations that come with printing API availability. In v5, GcExcel adds a new printing API so you can print your Excel documents directly to a physical printer on the Windows platform.

The new interfaces, IWorkbook.PrintOut and IWorksheet.PrintOut, can call the physical printer to print the workbook or worksheet. Not only printing is supported, but there are additional options you can set in advance—all while programmatically sending a command to print the Excel file.

GcExcel now uses GcPdf to output a workbook to a printer. To print a PDF using GcPdf on Windows, GcExcel needs to reference the new package, GrapeCity.Documents.Imaging.Windows.

The code below prints a worksheet to the active printer:

IWorkbook workbook = new Workbook();
IWorksheet worksheet = workbook.Worksheets[0];

worksheet.Range["F11"].Value = 1;
worksheet.Range["E55"].Value = 2;
worksheet.Range["N18"].Value = 3;
worksheet.Range["N53"].Value = 4;

// Create a print options.
PrintOutOptions options = new PrintOutOptions();
// Set the printer name to print.
options.ActivePrinter = "Microsoft Print to PDF";

//Print this workbook to "Microsoft Print to PDF".
workbook.PrintOut(options);

Please note that this feature enhancement only applies to GcExcel for .NET and is not supported by GcExcel Java.

.NET Help | .NET Demo

GrapeCity SpreadJS Integration

With every release, GcExcel adds even more support for SpreadJS features—improving its compatibility with the client-side spreadsheet product.

Please note that these features work only with SpreadJS and PDF Export and are not supported in Microsoft Excel.

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

Support for the Table expandBoundRows API

GcExcel now supports the SpreadJS API for expanding bound rows in a table. The new ITable.ExpandBoundRows property sets the policy when the data bound to the table changes.

If set as true, the entire rows will be added or deleted to adjust the row count when data is altered. If set as false, the Microsoft Excel policy will be used to expand the tables so the addition or deletion of rows will not be allowed to adjust data source changes.

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

Download Now!