What's New in Documents for Excel Java v6

GrapeCity Documents for Excel, Java Edition (GcExcel)

SpreadJS .sjs file format support in GcExcel Java

With v6.2 release, GcExcel adds support for SpreadJS file format ‘.sjs’ in it’s Java edition. The format helps to import/export spreadsheet features from SpreadJS ‘.sjs’ files to Excel or export of large files of popular formats such as xlsx, xlsm, csv, ssjson, etc., to ‘.sjs’ in lesser time and smaller in size.

The existing methods of GcExcel Java - open and save of the Workbook class have been enhanced to support .sjs files. While loading or saving a ‘.sjs’ file, you can use the new enum option “Sjs” in OpenFileFormat and SaveFileFormat enums.

With the support of the new file format, the following features will be supported -

• Faster conversion of large Excel files to .sjs format.

• Save exported files with a smaller footprint.

• Import/Export Excel/SpreadJS features to/from SpreadJS.

• Generate a single JSON string from the JSON files zipped in the .sjs file.

• Customize opening and saving of SpreadJS .sjs files using various options available.

New classes SjsOpenOptions and SjsSaveOptions, have also been added, using which you can control which spreadsheet features to include/exclude while importing/exporting the ‘.sjs’ files.

Following code loads SpreadJS .sjs file, turns off Formulas and Styles in using SjsOpenOptions and opens/saves the file using GcExcel.

// Create a new workbook
Workbook workbook = new Workbook();
InputStream stream = this.getResourceStream("sjs\\LoanDetails.sjs");

// OpenOptions for .sjs file format
SjsOpenOptions openOptions = new SjsOpenOptions();
openOptions.setIncludeFormulas(false);
openOptions.setIncludeStyles(false);

// GcExcel support using OpenOptions to open .sjs file format.
workbook.open(stream, openOptions);
    
// Save to a .sjs file
workbook.save("OpenSjsWithOpenOptions.sjs");

Have a look at the following resources to learn more about this format.

Help | Demo 

Styles support in SpreadJS .sjs file format

GcExcel also supports SpreadJS styles on import and export of .sjs files. Following new properties will be supported.

• Style

  • buttonBackColor

  • hoverBackColor

  • watermark

  • Ellipsis

  • Cell Buttons

  • Dropdowns

  • Cell Padding

  • Label

• Cell Types

  • Button Cell Type

  • CheckBoxCell Type

  • Check Box List Cell Type

  • Radio Button List Cell Type

  • Button List Cell Type

  • Range Template Cell Type

  • Combo Box Cell Type

  • Hyper Link Cell Type

Help

Alignment options for Shape Text

GcExcel adds new TextAlignment property in ITextRange interface that gets or sets the alignment of a text range or paragraph in a shape. This property sets the text alignment to left, right, center, distribute, and justify. This is helpful in scenarios where you have to align text for professional reports as per UI design rules, or align text as per data formats like text to left or numbers to right.

Following code sets alignment of multiple paragraphs in a shape to Center and Left -

IShape shape = worksheet.Shapes.AddShape(AutoShapeType.RoundedRectangle, (double)10, (double)10, (double)320, (double)150);
shape.TextFrame.TextRange.TextAlignment = TextAlignmentAnchor.Left;
shape.TextFrame.TextRange.Font.Name = "Calibri";
shape.TextFrame.TextRange.Font.Size = 16;
shape.TextFrame.TextRange.Font.Color.RGB = Color.Black;
shape.TextFrame.TextRange.Font.Underline = TextUnderlineType.None;

shape.TextFrame.TextRange.Paragraphs.Add("Quarterly Results");
shape.TextFrame.TextRange.Paragraphs[0].TextAlignment = TextAlignmentAnchor.Center;
shape.TextFrame.TextRange.Paragraphs[0].Font.Size = 28;
shape.TextFrame.TextRange.Paragraphs[0].Font.Underline = TextUnderlineType.Single;
shape.TextFrame.TextRange.Paragraphs.Add("");

shape.TextFrame.TextRange.Paragraphs.Add("Business Domain: E-Commerce");
shape.TextFrame.TextRange.Paragraphs[2].TextAlignment = TextAlignmentAnchor.Left;

shape.TextFrame.TextRange.Paragraphs.Add("Quarter: Q4");
shape.TextFrame.TextRange.Paragraphs[3].TextAlignment = TextAlignmentAnchor.Left;

Help | Demo

Set Vertical text direction in a Shape and Chart

In certain documents, it is desired to set the text orientation in vertical direction. GcExcel adds Direction property to API of shape and chart -

• IShape.TextFrame.Direction to get or set the text direction of the shape.

• ITickLabels.Direction to get or set the text direction of the tick labels on a chart axis.

• IChartTitle.Direction or IChartTitle.TextFrame.Direction to get or set the text direction of the chart title.

• IAxisTitle.Direction or IAxisTitle.TextFrame.Direction to get or set the text direction of the axis title.

• IDataLabels.Direction to get or set the text direction of the data labels for the specified series.

• IDataLabel.Direction or IDataLabel.TextFrame.Direction to get or set the text direction of the data label on a chart point.

The Direction property accepts TextDirection enum option that helps to set orientation of text in following directions -

• TextDirection.Horizontal indicates the text runs horizontally.

• TextDirection.Vertical indicates the text runs vertically.

• TextDirection.Rotate90 indicates the text will be rotated 90 angle.

• TextDirection.Rotate270 indicates the text will be rotated 270 angle.

• TextDirection.Stacked indicates the text will be stacked and the text reading order is from left to right.

• TextDirection.StackedRtl indicates the text will be stacked and the text reading order is from right to left.

Following code sets TextDirection of JP text to Stacked direction -

var shape = worksheet.Shapes.AddShape(AutoShapeType.Rectangle, worksheet.Range["C2:F12"]);
shape.TextFrame.TextRange.Add("グレープシティへようこそ");

//Set the text direction to be stacked, and the text reading order from right to left.
shape.TextFrame.Direction = TextDirection.Stacked;

Help  | Demo Shape | Demo Chart 

GrapeCity Documents Data Viewer

Load SpreadJS .sjs files

In last release GcExcel .NET added support of import and export of SpreadJS .sjs files and with v6.2 release, we now extend this support to GcDataViewer. You can open the SpreadJS .sjs files through UI of the viewer or through client-side code.

SpreadJS .sjs file opened through UI

In the ‘Select Data file’ (Open) dialog of the viewer, Data Type now accepts SJS file type. The file Source can be Local or Remote.

Help | Demo

SpreadJS file opened through client-side API

The ‘openFile’ method has been updated to accept FileType.SJS enum value in the second parameter, as well as new ‘SjsOpenOptions’ in the openOptions (third) paramenter. The new SjsOpenOptions have been added to client-side API which helps to provide which SpreadJS features to load while opening the .sjs file. Following client-side code opens .sjs file through code.

var viewer;
var sjs_OpenOptions = {
    showHiddenSheets: true,
    showHiddenRows: false,
    showHiddenColumns: true,
    keepRowGroups: false,
};

function loadFile(fileUrl) {
     fetch(fileUrl).then(response => {
         response.blob().then(res => {
             viewer.openFile(res, FileType.SJS, sjs_OpenOptions);
         });
     })            
 }
 window.onload = function () {
     viewer = new GcDataViewer("#viewer");
     loadFile("/Files/12-month cash flow statement.sjs");
 }

Help | Demo

Keyboard shortcuts

GcDataViewer supports several keyboard shortcuts which can make working with the viewer and cell operations more efficient. Please refer to list of Keyboard shortcuts supported in below Help link.

Help

Numerical Count aggregation in status bar

GcDataViewer now supports numerical count aggregation in status bar. When a file is loaded and cells are selected, GcDataViewer will show the count of cells having numerical data. Following snapshot shows a cell range selected where 3 cells have numerical data.

Help

GrapeCity Documents for Excel, Java Edition (GcExcel)

Support for XLTX File Format

You can now directly load Xltx files in GcExcel, modify and save them back. GcExcel supports new Xltx file format on I/O. Workbook.open and Workbook.save methods now accept new XltxOpenOptions and XltxSaveOptions classes to load an XLTX workbook or save it back to workbook. You can either load and save Xltx from or to a file or file stream.

Following code opens and saves a .xltx file with GcExcel.

// Create a new workbook.
var workbook = new GrapeCity.Documents.Excel.Workbook();
// Open xltx file.
workbook.Open(Path.Combine("Resources", "excel-loan-calculator.xltx"), OpenFileFormat.Xltx);
// Save workbook as xltx file.
workbook.Save("Exported.xltx", SaveFileFormat.Xltx);

Have a look on following resources.

Help | Demo

Export options in ToImage() method

GcExcel already provides a way to convert a worksheet, any specified range, and various shape types to images using ToImage method. 

GcExcel also now adds ImageSaveOptions class providing various properties to modify and adjust the image when exporting a worksheet, a range, or a shape to an image file.

1. ScaleX and ScaleY

2. Resolution

3. BackgroundColor

4. ShowRowHeadings

5. ShowColumnHeadings

6. ShowGridlines

7. GridlinesColor

8. ShowDrawingObjects

9. BlackAndWhite

Following code sets background color of the exported worksheet image and sets gridline color -

ImageSaveOptions options = new ImageSaveOptions();
// Set the background color of the exported image
options.BackgroundColor = Color.FromArgb(226, 231, 243);
// Set the gridlines of the exported image
options.ShowGridlines = true;
options.GridlineColor = Color.FromArgb(145, 167, 214);
worksheet.ToImage(outputStream, Drawing.ImageType.PNG, options);

Have a look on following resources to know more about these options.

Help | Demo

Copy/move multiple sheets at once

GcExcel adds new methods - Copy, CopyBefore, CopyAfter, Move, MoveBefore, and MoveAfter methods in the IWorksheets interface that would help to copy or move multiple worksheets to the desired locations or at the end of workbook at once.

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

// Load template file FlowChartsFile.xlsx from resource
var fileStream = this.GetResourceStream("xlsx\\FlowChartsFile.xlsx");
workbook.Open(fileStream);

// Copy the selected sheets to the end of current workbook
workbook.Worksheets[new string[] { "FlowChart1", "FlowChart2" }].Copy();
        
// Save to an excel file
workbook.Save("CopyMultipleWorksheets.xlsx");

Help | Demo Copy multiple worksheets | Demo Move multiple worksheets

Support for SJS features

• Support Form Controls on JSON I/O - Demo

• Support for allowResize property on JSON I/O - Help

• Addition of ExportSharedFormula of GcExcel to set whether to export formula as shared formula or not, when converting workbook to JSON -  Help

GrapeCity Documents Data Viewer (GcDataViewer)

Introducing Standard and Professional licenses

GcDataViewer now includes two types of production licenses: Standard and Professional.

Standard License

The standard license provides access to all the standard view features, such as opening documents, zooming in and out, working with viewer themes, displaying cell and formula results, etc. For more information about these features in the standard license, see GcDataViewer License Options.

Professional License

The professional license provides access to all the features of the standard license, along with interactive operations such as filter and sort, slicer, and data visualization objects such as charts, barcodes, pictures, shapes, SJS barcode formula results, etc. For more information about these features in the professional license, see GcDataViewer License Options.

Contact us.sales@grapecity.com for the license type you need.

Have a look on these topics for more information about new features supported -

1. Tables - Help | Demo

2. Charts - Help | Demo

3. Pictures - Help | Demo

4. Shapes - Help | Demo

5. Slicers - Help | Demo

6. Barcodes - Help | Demo

7. Sorting & Filtering - Help | Demo

Following snapshot shows new Sort and Filter support as well as Slicers displayed GcDataViewer.

Help | Demo

Customize toolbar options

GcDataViewer now adds ability to modify the toolbar layout and hide or reorder the buttons for the three view modes: default (desktop), mobile, and full screen, using the GcDataViewer.toolbarLayout interface which returns DataToolbarLayout object. You can perform following to customize the toolbar -

• Get information about current toolbar buttons

• Modify the current layout of the toolbar by hiding or reordering the buttons

• Remove or reorder the buttons in the toolbar

• Customize toolbar specific to these view modes - default (desktop), mobile, and full screen

Code below modifies default layout of the toolbar with limited set of buttons.

let viewer = GcDataViewer.findControl("#root");

// Modify the toolbar layout for default view mode.
viewer.toolbarLayout = { default : ['open', 'zoom', 'fullscreen', 'theme-change']};

Have a look on following resources.

Help | Demo

Load data files from remote URL

GcDataViewer now supports loading data files from remote URL either from UI or through code. In UI, ‘Remote’ option has been added to Source dropdown.

You can provide both absolute and relative URLs. Since GcDataViewer is a pure front-end product, you need to make sure the target URL file is accessible across domains.

The same can be provided through code, either as URL string or object. Both absolute and relative URLs can be provided through code.

//URL string
viewer.openFile("http://localhost:5005/Family monthly budget1.xlsx");

//URL object
let url = new URL("http://localhost:5005/Family monthly budget1.xlsx");
viewer.openFile(url);

//Absolute URL
viewer.openFile("http://localhost:5005/Family monthly budget1.xlsx");

//Relative URL. The base URL is the URL of the current page.
viewer.openFile("Family monthly budget1.xlsx");

Help | Demo

Show/Hide all Notes

GcDataViewer adds Show/Hide Notes toggle button to show or hide all the notes in the spreadsheet (XLSX and SSJSON) with one button click. The toggle button displays all the notes in a spreadsheet if all of the notes are hidden, or it hides all the notes if they are set to always display.

The Show/Hide Notes toggle button is especially useful in complex input form documents, such as loan calculation forms, where users might require clues to fill out the form. Hence, customers can show or hide notes using the toggle button as per their requirements. 

Help | Demo

GrapeCity Documents for Excel, Java 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 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. 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 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 v6 release onwards. GcExcel will also now no longer require addition of GcExcel Extension package to the project. Please refer to following topics with updated details about JDK 8 support.

• Getting Started | Documents for Excel, Java Edition Documentation

• GcExcel Dependencies | Documents for Excel, Java Edition Documentation

• Quick Start | Documents for Excel, Java Edition Documentation 

• Configure JDK 8 Date Time API | Documents for Excel, Java Edition Documentation

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

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

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