What's New in Documents for Excel .NET v6

GcExcel for .NET v6 - December 15, 2022

GrapeCity Documents for Excel, .NET Edition (GcExcel)

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 the time, with data deleted but formatting retained. 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 optimise 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. Default value is false.

  • XlsxSaveOptions.ExcludeUnusedNames{ get; set; } - Indicates whether to exclude user-defined but never used Define name. 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.

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

//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");

View Help | Demo 

GcExcel Templates Enhancements

Paginated Spreadsheet reports new enhancements

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

In v6, new API is introduced to accomplish additional layouts with Paginated Templates feature you can create following types of layouts with new API -

Configure header/footer for page or group in Paginated spreadsheets

New API RepeatType, NoRepeatAction, 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 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.

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 current page number for a group and total number of pages in a group. The two properties can be defined using syntax - PageNumber(string cell), PageCount(string cell). In layout below, information for Patient ID P001 is paginated into 4 pages and current page number can be shown.

Paginate spreadsheets based on Page size

Till now, you have been paginating worksheets based on CountPerPage (CP) property which generates 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 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, AttachTo have been added that help insert page break after maximum number of lines per page, irrespective of group and subtotals of each group. In this case, 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.

Example below shows a report that paginates based on page size. As the rows reach maximum number of lines in the worksheet based on page setup settings of Excel, report paginates to 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.

View Help | Demo 

Keep 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 provision to process specific template worksheet from many templates. You can pass this specific template worksheet to new Workbook.GenerateTemplate() method, see 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 would process all worksheets.

In following example, only 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);

View Help  | Demo 

Get used range in selected area

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

Following code finds 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;

View Help | Demo 

Add Shape text with range reference or defined name

Suppose you have a large volume of employee sales data, and you 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).

Code below sets 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";

View Help | Demo 

Add shape/picture to cell/cell range using 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 IShape interface -

  • AddPicture() - Creates a picture from an existing file at the specified range on the current sheet. 

  • AddShape() - Returns the object that represents 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 reference range to the target range on the current sheet. 

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");

View Help |  Demo 

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 LAMBDA function. It helps abstract processes into functions and create custom, reusable functions, calling them with a friendly name. The syntax is simple. Just add the function to 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 re-used 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))");

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

  • BYROW

  • BYCOL

  • SCAN

  • REDUCE

  • MAP

  • MAKEARRAY

  • ISOMITTED

Have a look at our .NET Demos of each function.

View Help | Demo

New Text and Array manipulation Excel functions

GcExcel now supports following set of functions -

Text Manupulation functions

Previously, in order to extract a part of the text, you have to use multiple functions. For example, to extract First Name from a text say ‘Mark Taylor’ in a cell, you have 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 to extract or combine various pieces of text and also spill the result to other cells using a single function. Following new Text manipulation functions are supported.

  • TEXTSPLIT, TEXTBEFORE and TEXTAFTER

Following code finds text before a word in a sentence using 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)";

View Help | Demo

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. Following new functions have been added.

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

Following code shows data of a range of array as a row.

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

View Help | Demo 

Range Intersection, Union and Offset

GcExcel has been supporting API to find 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.

Code below performs 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"]);

View Help | Demo (Intersect and Union) | Demo (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 open the file. The default value is false.

View Help | Demo

SpreadJS Features support

GcExcel adds new options for supporting additional SpreadJS features:

  • Support 'RowColumnStates' in JSON I/O.

  • Support Cross Workbook Formula - 'externalReference' in JSON I/O.

  • Support writing floating objects and picture shapes as shapes in JSON file.

  • Support for ‘allSheetsListVisible’ field in JSON I/O.

View Help | Demo

Introducing GrapeCity Documents Data ViewerNEW

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.


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, 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 read-only view of 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 install from npm.

npm install @grapecity/gcdataviewer

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

View Help | Demo