What's New in GrapeCity Documents for Excel in v6

December 15 2022

GrapeCity Documents for Excel (GcExcel) v6 release is now LIVE! The release introduces new JavaScript-based Data Viewer control, features, and template enhancements to GcExcel .NET and Java APIs and more features for SpreadJS compatibility. Have a look at the key highlights below.

Introducing GrapeCity Documents Data Viewer^NEW

GrapeCity Documents introduces new GrapeCity Documents Data Viewer (GcDataViewer) control- a JavaScript-based client-side viewer that can load and preview data-related documents like XLSX, CSV, and SpreadJS .json files. It is a read-only cross-platform solution for viewing data files with many Excel and CSV options. GcDataViewer allows data viewing capabilities that can seamlessly be integrated into existing systems or in new applications. The viewer can find its use in any data-driven business or company such as Sales, E-Commerce, Stock Market, Healthcare, Consultation, etc., that are looking for a solution to:

integrate a client-side viewer in their existing systems to view data files
to build a web-based data viewer for its customers/users (e.g., SaaS company)

It can be used by developers looking for an option to open data files generated by the server side of their applications and view the data.

GcDataViewer can be integrated easily with .NET 7, Java, or JS frameworks such as NodeJS, AngularJS, React, Preact, Express, and others. Apart from this, one can view data files on Windows, Mac, Linux, iOS, and Android devices, and it works in all modern browsers, including Edge, Chrome, Firefox, Opera, and Safari.

Document Solutions Data Viewer

Excited to see our new features? Download GrapeCity Documents for Excel now!

Key Highlights

Unified solution - It is a unified Javascript-based client-side viewer that can load and preview any data-related document. Currently, it supports Excel (.xlsx and .xlsm), CSV, and SSJSON. Users are not required to look for different/separate tools to view these data files. GcDataViewer allows viewing them all in a single application, eliminating the need to switch to multiple tools to view their content
UI for GcExcel - Customers using GcExcel .NET or Java can have a graphical interface to view the data file generated by its server-side APIs
Cross-platform solution - Being a JS product, GcDataViewer can be integrated easily with .NET 7, Java, or JS frameworks such as NodeJS, AngularJS, React, Preact, Express, and others. Apart from this, one can view data files on Windows, Mac, Linux, iOS, and Android devices, and it works in all modern browsers, including Edge, Chrome, Firefox, Opera, and Safari
Responsive UI - The user interface of the GcDataViewer is responsive for viewing on different devices, screen orientations, and sizes
Light-weight Data Viewer - Less server-side dependency, and simple syntax and semantics make it a suitable choice for lightweight applications
Extensive support for Excel features - GcDataViewer supports a read-only view of the majority of Excel features such as Tables, Pivot Tables, Formulas, Conditional Formatting, DataBar, Icons, Outline Groups, Filters, and many more
CSV options: Configure loading of CSV document using various available settings
Themes - Support for built-in themes to meet different UI appearance needs
API integration - Support for public APIs that can be used to integrate and embed the viewer in existing applications
Localize to match your market - GcDataViewer can adapt its display according to the browser language. Currently supported languages are - English, Chinese, and Korean

GcDataViewer is available with GcExcel .NET and Java packages, or you can install it from npm.

npm install @grapecity/gcdataviewer

View the detailed blog for the full feature list supported in the viewer and how to configure in ASP.NET Core application.

View Help | Demo | License Info

Excel Workbook Size Optimization

You may often have Excel files with unused formatting, defined names, or empty region cells. Such cells may have data and formatting initially but are not used over time, with data deleted but formatting retained. The presence of a large number of such cells in the workbook causes the performance to slow down as well as increases the size of the workbook. With GcExcel, you can now exclude such cells and reduce the size of your workbook while saving it. GcExcel provides new options in XLSXSaveOptions() class to optimize your workbook using the Workbook.Save(..) method. Three new options are introduced -

XlsxSaveOptions.ExcludeUnusedStyles{ get; set; } - Indicates whether to exclude user-defined but never used Cell styles. The default value is false
XlsxSaveOptions.ExcludeUnusedNames{ get; set; } - Indicates whether to exclude user-defined but never used Define name. The default value is false
XlsxSaveOptions.ExcludeEmptyRegionCells{ get; set; } - Indicates whether to exclude any empty cells (cells with no data or only style) outside the used data range

The code below excludes any unused styles, names, or empty region cells in the workbook, thereby reducing the workbook size.

//create a new workbook
var workbook = new GrapeCity.Documents.Excel.Workbook();
Stream fileStream = this.GetResourceStream("xlsx\\File needs to be optimized.xlsx");
workbook.Open(fileStream);

XlsxSaveOptions options = new XlsxSaveOptions
{
    ExcludeEmptyRegionCells = true,
    ExcludeUnusedStyles = true,
    ExcludeUnusedNames = true
};

using (MemoryStream outputStream = new MemoryStream())
{
    workbook.Save(outputStream, options);
    outputStream.Position = 0;
    workbook.Open(outputStream, OpenFileFormat.Xlsx);
}
        
// Save to an excel file
workbook.Save("OptionsToOptimizeFileSize.xlsx");

Excel Workbook Size Optimization

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

GcExcel Java now targets JDK 8

Since JDK 6 and JDK 7 have reached end of life, GcExcel will no longer support JDK 6 & 7 and will now target JDK 8 from the v6 release onwards. GcExcel will also now no longer require the addition of the GcExcel Extension package to the project. Please refer to the following topics with updated details about JDK 8 support.

GcExcel Templates Enhancements

Paginated Spreadsheet reports new enhancements

In the last release, GcExcel Templates introduced 'TemplateOptions.PaginationMode' and CountPerPage property (CP) properties to manage the pagination of the workbook into separate worksheets with a fixed number of rows in the worksheet layout.

In v6, a new API is introduced to accomplish additional layouts. With the Paginated Templates feature, you can create the following types of layouts with the new API:

Configure header/footer for page or group in Paginated spreadsheets

New API RepeatType, NoRepeatAction, and RepeatWithGroup have been added to repeat cell value (s) within a group or configure page/group header/footer for each page.

RepeatType property determines how to repeat a cell value within a group when the RepeatWithGroup property is set
NoRepeatAction property determines how to set the deletion mode of common content when it is not displayed on the current page
RepeatWithGroup property specifies the cell reference in the template that repeats with a group in the generated report

In the following snapshot, RepeatType is set to FirstPage for Patient and Specimen information, and RepeatWithGroup is set to D3, which means the Page Header range A6:I18 is repeated for every new Patient ID set in D3.

DsExcel Template Enhancements

Page number and page count for groups

In fixed row layout, new properties have been introduced - PageNumber to set the page number of the current page and PageCount to set the count of pages of the current group. This will help to indicate the current page number for a group and the total number of pages in a group. The two properties can be defined using syntax - PageNumber(string cell) and PageCount(string cell). In the layout below, information for Patient ID P001 is paginated into four pages, and the current page number can be shown.

Page number and page count for groups

Paginate spreadsheets based on Page size

Till now, you have been paginating worksheets based on the CountPerPage (CP) property which generates a fixed number of rows in a worksheet layout and paginates into multiple worksheets. However, reports can be paginated based on page size, which is determined by Page Setup settings in Excel. In this case, if the CP property is not set or is set to *, and TemplateOptions.PaginationMode is true. The report would be paginated into multiple worksheets based on Page size.

In this type of pagination, new properties RepeatOutput, KeepTogether, and AttachTo have been added that help insert page break after a maximum number of lines per page, irrespective of group and subtotals of each group. In this case, the maximum number of lines depends on the paper size.

RepeatOutput property specifies whether the value of merged cells appears only on the first page or on each page of the report
KeepTogether property ensures the cell, and its descendants appear on the same page. The property allows you to choose if you want to keep the cells together with horizontal pagination or vertical pagination
AttachTo property allows binding a cell template with another cell to ensure that it does not appear alone on a page. You can use this property to prevent orphaned headers/footers in the document

The example below shows a report that paginates based on page size. As the rows reach the maximum number of lines in the worksheet based on page setup settings of Excel, report paginates to the next worksheet, irrespective of template group settings. Here, ‘RepeatOutput’ is set on a merged cell which we can choose to repeat or not on each worksheet.

Paginate spreadsheets based on Page size

To check out the Excel Template layouts you can accomplish with the above properties, look at this detailed blog.

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

Keep the original template or process specific Template worksheets

Till now, you could process the whole workbook template into a report. However, GcExcel now introduces Workbook.GenerateTemplate() method that returns a new workbook instance without altering the original template workbook.

Additionally, your workbook may have more than one template, and you may only need to process a particular template worksheet. GcExcel adds a provision to process specific template worksheets from many templates. You can pass this specific template worksheet to the new Workbook.GenerateTemplate() method. See the code below:

IWorkbook workbook = workbook.GenerateReport(new IWorksheet[] { sheet });

The method returns a new workbook instance, processing only the template passed as a parameter without altering the original template. If no parameter is provided, GenerateTemplate() method will process all worksheets.

In the following example, only the specific worksheet ‘PurchaseOrder’ is processed out of the three worksheets.

// Add data source
workbook.AddDataSource("dt", salesData);
		
// Init template global settings
workbook.Names.Add("TemplateOptions.KeepLineSize", "true");
		
// Process the template and return the specified report
IWorkbook report = workbook.GenerateReport(workbook.Worksheets["PurchaseOrder"]);
		
// Save the report as xlsx to a stream
report.Save(outputStream);

PurchaseOrder

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

Get used range in the selected area

GcExcel has provided the UsedRange property and GetUsedRange method in the worksheet to get all used ranges in a worksheet. You may also want to get used range within a selected range instead of the whole worksheet for used range. GcExcel adds the UsedRange property and GetUsedRange method to the IRange interface.

The following code finds the used range within a selected range and changes cell background color of the used range.

// Init data.
IRange range = sheet.Range["B2:D4"];
range.Value = "Unused";

// Select range.
IRange selectedRange = sheet.Range["C3:E5"];
selectedRange.Select();

// Get the used range from selectedRange.
IRange usedRange = selectedRange.UsedRange;
usedRange.Value = "Used";
usedRange.Interior.Color = Color.LightBlue;

UsedRange

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

Add Shape text with range reference or defined name

Suppose you have a large volume of employee sales data and want to highlight the Gross sales of the month using any graphical images. You can link the shapes with the data-bound formulas, which can represent the sales of the month in the selected shapes dynamically. In certain situations, shapes in a worksheet can have text referencing a range or a defined name. This text can change as per data in the range or defined name. GcExcel adds IShape.Formula property to set or remove the range reference or defined name of the current shape. You can set IShape.Formula=”=A1” (to a cell/range) or IShape.Formula = “=refname” (to a defined name).

The code below sets the formula to a shape, which references cell G8 with a formula:

// set shape formula to G8
IShape shapeResult = worksheet.Shapes.AddShape(AutoShapeType.Rectangle, worksheet.Range["B7:D8"]);
shapeResult.Formula = "=G8";

Add Shape text

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

Add shape/picture to cell/cell range using the direct method

To simplify the process of adding an IShape to a range, a series of direct methods are provided to add an IShape to a range without obtaining its coordinates. GcExcel adds five new methods to the IShape interface -

AddPicture() - Creates a picture from an existing file at the specified range on the current sheet
AddShape() - Returns the object representing the new AutoShape at the specified range on the current sheet
AddChart() - Creates a chart at the specified range on the current sheet
AddConnector() - Creates a connector at the specified range on the current sheet
AddCameraPicture() - Creates a picture from the reference range to the target range on the current sheet

The following code adds pictures directly to a range, along with connectors:

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

IWorksheet worksheet = workbook.Worksheets[0];

// Place the same picture to two range
System.IO.Stream stream = this.GetResourceStream("puffins.jpg");
IShape smallPicture = worksheet.Shapes.AddPicture(stream, ImageType.JPG, worksheet.Range["B2:B4"]);
IShape bigPicture = worksheet.Shapes.AddPicture(stream, ImageType.JPG, worksheet.Range["D4:F12"]);

worksheet.Shapes.AddConnector(ConnectorType.Straight, worksheet.Range["C2:F3"]);
worksheet.Shapes.AddConnector(ConnectorType.Straight, worksheet.Range["B5:C12"]);
        
// Save to an excel file
workbook.Save("AddPictureToRange.xlsx");

Add shape/picture

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

Support for new Lambda function including Help functions

If your worksheet contains complex calculations, you must know them in detail. This may be prone to errors and difficult to maintain. Why not just add a complex calculation once, give it a simple name, and re-use it in your worksheet? GcExcel provides support for programmatically adding the LAMBDA function. It helps abstract processes into functions and create custom, reusable functions, calling them with a familiar name. The syntax is simple. Just add the function to the Names collection of Workbook.

=LAMBDA([parameter1, parameter2, …,] calculation)

The above syntax adds a LAMBDA function with parameters to pass to the function, such as a cell reference, string, or number. You can enter up to 253 parameters. This argument is optional. The other parameter is the formula to execute and return as the result of the function.

The code below names a LAMBDA function as ‘CountWords’ in the workbook.Names collection. The LAMBDA function executes a long calculation for calculating the count of words in a text. The function is given a simple name, ‘CountWords’, which can be reused multiple times in the worksheet instead of repeating the long calculation function.

workbook.Names.Add("CountWords", "=LAMBDA(text,IF(LEN(TRIM(text))=0,0,LEN(TRIM(text))-LEN(SUBSTITUTE(TRIM(text),\" \",\"\"))+1))");

New LAMBDA Function

The LAMBDA function support in GcExcel includes support for seven new Helper functions that help the LAMBDA function to work with arrays:

BYROW
BYCOL
SCAN
REDUCE
MAP
MAKEARRAY
ISOMITTED

Have a look at Demo .NET and Java of each function.

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

New Text and Array manipulation Excel functions

GcExcel now supports the following set of functions:

Text Manipulation functions

Previously, in order to extract a part of the text, you had to use multiple functions. For example, to extract the First Name from a text, say ‘Mark Taylor’ in a cell, you had to use FIND or SEARCH function to find the position of the first space in a list, then use LEFT, MID or RIGHT combined with LEN to extract the data. However, the new Text manipulation functions help extract or combine various pieces of text and spill the result to other cells using a single function. Following new Text manipulation functions are supported.

TEXTSPLIT, TEXTBEFORE and TEXTAFTER

The following code finds text before a word in a sentence using the TEXTBEFORE function:

sheetOfTEXTBEFORE.Range["A3"].Value = "Little red Riding Hood's red hood";

sheetOfTEXTBEFORE.Range["A4"].Value = "Formulas";
sheetOfTEXTBEFORE.Range["A4"].Font.Bold = true;
sheetOfTEXTBEFORE.Range["B4"].Value = "Results";
sheetOfTEXTBEFORE.Range["B4"].Font.Bold = true;
sheetOfTEXTBEFORE.Range["B5"].Formula2 = "=TEXTBEFORE(A2,\"Red\")";
sheetOfTEXTBEFORE.Range["A5"].Formula = "=FORMULATEXT(B5)";
sheetOfTEXTBEFORE.Range["B6"].Formula2 = "=TEXTBEFORE(A3,\"red\",2)";
sheetOfTEXTBEFORE.Range["A6"].Formula = "=FORMULATEXT(B6)";
sheetOfTEXTBEFORE.Range["B7"].Formula2 = "=TEXTBEFORE(A3,\"red\",-2)";
sheetOfTEXTBEFORE.Range["A7"].Formula = "=FORMULATEXT(B7)";
sheetOfTEXTBEFORE.Range["B8"].Formula2 = "=TEXTBEFORE(A3,\"Red\")";
sheetOfTEXTBEFORE.Range["A8"].Formula = "=FORMULATEXT(B8)";
sheetOfTEXTBEFORE.Range["B9"].Formula2 = "=TEXTBEFORE(A3,\"Red\",,1)";
sheetOfTEXTBEFORE.Range["A9"].Formula = "=FORMULATEXT(B9)";
sheetOfTEXTBEFORE.Range["B10"].Formula2 = "=TEXTBEFORE(A3,\"Riding\")";
sheetOfTEXTBEFORE.Range["A10"].Formula = "=FORMULATEXT(B10)";

Text Manipulation

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

Array Manipulation functions

Array Manipulation functions will help combine and shape ranges and spill out ranges for your formulas. If your worksheets have dynamic array formulas, these new functions will make your spreadsheets more compelling and support advanced and optimized operations with dynamic arrays. The following new functions have been added:

HSTACK, VSTACK, TOROW, TOCOL, WRAPROWS, WRAPCOLS, TAKE, DROP, CHOOSEROWS, CHOOSECOLS, and EXPAND

The following code converts the data of an array into one row:

sheetOfTOROW.Range["A7"].Value = "Result";
sheetOfTOROW.Range["A7"].Font.Bold = true;
sheetOfTOROW.Range["A8"].Formula2 = "=TOROW(A2:D4)";

Array Manipulation

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

Range Intersection, Union, and Offset

GcExcel has been supporting API to find the intersection/union/offset of two ranges. With v6, we make this operation simpler by introducing direct methods:

IRange.Intersect(IRange range2) or IRange.Intersect(params IRange[] ranges) to get the intersection of the current range and one or more range parameters
IRange.Union(IRange range2) or IRange.Union(params IRange[] ranges) to get the union of the current range and one or more range parameters
IRange.Offset(int rowOffset, int columnOffset) to get the offset of the current range

The code below performs the Intersection and Union of two ranges, with interior colors set for the intersected and union ranges, respectively:

// Set the intersection of two range value and style.
var intersectRange = worksheet.Range["A2:E6"].Intersect(worksheet.Range["C4:G8"]);
intersectRange.Interior.Color = Color.FromArgb(56, 93, 171);

var firstUnionRange = worksheet.Range["A11:D13"];
firstUnionRange.Merge();
firstUnionRange.Interior.Color = Color.FromArgb(56, 93, 171);
firstUnionRange.HorizontalAlignment = HorizontalAlignment.Center;
firstUnionRange.VerticalAlignment = VerticalAlignment.Center;

var secondUnionRange = worksheet.Range["D14:G16"];
secondUnionRange.Merge();
secondUnionRange.Interior.Color = Color.FromArgb(145, 167, 214);
secondUnionRange.HorizontalAlignment = HorizontalAlignment.Center;
secondUnionRange.VerticalAlignment = VerticalAlignment.Center;
// Set the union of two range value and font style.
var unionRange = worksheet.Range["A11:D13"].Union(worksheet.Range["D14:G16"]);

Range Intersection, Union, and Offset

Option to control Auto Fit

GcExcel has been supporting auto fit of row/column, when an Excel file is opened. However, with v6, a new option is added to the API to control this behavior. GcExcel now supports XlsxOpenOptions.DoNotAutoFitAfterOpened property to get/set whether autofit the row height after opening the file. The default value is false.

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