What's New in Documents for Excel .NET v3

New Template Features to Generate Excel Reports

Last year, GrapeCity introduced Excel template support with full syntax and an API to generate data-bound Excel reports with advanced layouts. Generate comprehensive professional reports with additional Excel template support. Design PDF forms in Excel by using the new template syntax for form fields and related properties.

Generate PDF Forms from Excel Templates

Generate fillable PDF forms from Excel templates. GcExcel introduces the 'form' property to define PDF form fields and related properties within Excel templates. The Field settings can be conveniently set through a JSON string in an Excel file. Upon template processing, these form fields convert to PDF form fields, fully compatible with Adobe Acrobat specifications. You can achieve professional and well-designed PDF forms by using these features:

Specify PDF Form Fields and Design a Form in Excel

Define a textbox form field in an Excel cell.

{{(form={'type': 'textbox', 'name': 'txt1','font':{'size':10.5}, 'justification': 'centered'})}}

Set form fields and designate the labels in different cells. Easily design a complex PDF form without third-party tools. Generate a professional layout in a PDF form with correctly spaced and sized form fields with minimum effort.

See the complete template of a US W4 Tax form (below) and the different form fields set to the textbox. They are specified as template cells. Related properties are defined together with the various labels used in the form.

Add PDF Form Fields of Various Types and Set Form Settings in JSON Format

GcExcel v3.2 supports adding all PDF form fields in Excel templates.

Form fields include:

• Textbox
• Checkbox
• Radio button
• Combo box
• Listbox
• Push button
• Signature Field

Specific settings are supported for each form field and standard settings that apply to all the form fields. Define these settings in JSON format.

{{(form={'type': 'textbox', 'name': 'txt1','font':{'size':10.5}, 'justification': 'centered'})}} 

Refer to the full PDF form field settings in the API list here.

Process the Template in Code and Save Directly to PDF

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

//Load template file from resource  
var templateFile = GetResourceStream("xlsx\\Template_USTaxForm.xlsx"); 
workbook.Open(templateFile);    

//Invoke to process the template 
workbook.ProcessTemplate();    

//save to an excel file 
workbook.Save("USTaxForm.pdf"); 

View, Edit, Fill, and Submit a PDF Form in Javascript-based GrapeCity Documents PDF Viewer

Generate Bound PDF Forms

Design PDF forms bound with database values that can be edited after generating the form.

Define the form property within the database field declaration in the Excel file.

{{ds.Name(form={"type": "textbox", "name": "name","font":{"color": "#000000", "bold": true}})}}

Upon processing, the template expands with the database data. Each field in the final report is textbox fields, which the user can later edit and save in a PDF file.

For more details and the full sample implementation, see the links below:

Help | Demo 

Embed Fonts for Form Fields

Sometimes specific fonts are necessary for a PDF form that is not supported by your browser or printer.

Use the TemplateOptions.EmbedFontForFormFields option in the template file because this helps maintain the form field aesthetics and file size of the PDF.

• When set to True, the font files used in form fields are embedded in the PDF. This action helps place arbitrary characters correctly, even if the machine or browser does not install the corresponding font.

Note: This may increase the file size, especially in PDF forms using East Asian characters.

• If set to False, the font files are not embedded. The generated PDF document will be small in size. However, if you input a character in the form, the visual output may not appear correctly. This scenario occurs when your machine/browser does not install corresponding fonts.

Help | Demo 

Expand Templates With Sparklines

Sparklines add mini charts within the boundaries of a cell. These charts display a trend in values and point out patterns in data. You can define the data binding of sparklines with template cells. So when the data expands, sparklines are added to each row and automatically attached to the data.

In a standard Excel file with a large amount of data, sparklines are manually added, or a range is set and copied to each row. If the data range changes, it is necessary to manually review the sheet again and modify the range of the sparklines in each row.

With Excel templates, you only need to define the template range once. The sparklines expand with the updated range of data, essentially automating the file.

Help | Demo 

Expand Templates With Excel Tables

Sorting and filtering flat data in Excel spreadsheets is easy. Different types of data require separate tables.

When you work with a broad set of data, using tables is ideal in Excel. It helps to convert a list of data into a formatted Excel table, with many features automatically enabled, such as a total row, Banded columns, and sorting and filtering. Using tables in Excel makes it faster and more convenient to organize and work with data than on a flat Excel sheet.

It is for this reason, we thought of supporting Excel tables with templates. Several features (formatting, sorting, filtering, etc.) can now automatically apply to the tables once the data expands. With GcExcel, just define the template cells within a table area. The GcExcel templates would automatically grow with the built-in table formatting and features into a full Excel report.

Help | Demo 

Define Fixed Layout for Excel Report and Fill Data in a Specific Range

Some Excel documents use a standard template to adhere to company standards, like having a fixed number of rows and columns. Companies generating reports like invoices, purchase orders, and bills want to limit their layout and size in the final report.

GcExcel v3.2 introduces two new properties: FillMode (FM) and FillRange (FR). These properties control the layout of Excel reports.

Define the fixed template with a certain number of rows. The FillMode set to 'overwrite' option fills the data in that template without any style. The style set in the template is retained, and data loads into it. This method does not insert extra rows. See the example below.

In the final report, the data fits into the range and style already set and defined in the template. This action helps generate a fixed-layout Excel report.

 The other scenario is when data does not fit into the range defined in the template.

This scenario occurs when a data source has a higher number of rows than are defined in the template with the overwrite condition.

In such cases, use FillRange together with the FillModeoverwrite property. Upon processing the template, GcExcel v3.2 duplicates the defined range. If the data still does not fit into that range, GcExcel reproduces the range until the data fits.

In the example below, the data source contains more rows than are defined in the FR=A12: D23 range. The range is duplicated twice. Then the data fits within the template. The formatting is preserved.

 Visit the following for more details and full implementation.

Help | Demo 

Export Excel Spreadsheets and Excel Range to HTML

Export full or partial Excel spreadsheets directly to HTML files and present reports online.

GcExcel v3.2 supports Excel to HTML conversion. The feature converts Excel reports to HTML and preserves Excel formats during the conversion.

This new feature gives users flexibility because it supports exporting workbooks, worksheets, or any specific range to HTML. GcExcel v3.2 helps businesses convert complex spreadsheets into easy-to-read reports, for display on their website.

//create a zip file stream  
FileStream outputStream = new FileStream('saveworksheettohtml.zip', FileMode.Create);    

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

Stream fileStream = this.GetResourceStream('xlsx\\BreakEven.xlsx'); 
workbook.Open(fileStream);    

HtmlSaveOptions options = new HtmlSaveOptions();    

// Export first sheet  
options.ExportSheetName = workbook.Worksheets[0].Name;    

// Set exported html file name 
options.ExportFileName = "Analysis";    

workbook.Save(outputStream, options); 

//close the zip stream 
outputStream.Close();  

You can find more examples of exporting Excel spreadsheets to HTML in the resources below.

Help | Demo 

Digital Signatures Support in Excel Spreadsheets

Adding digital signatures to Excel workbooks provides significant assurance of the signer's identity and the document's authenticity.

The security architecture of digital signatures links all documents signed digitally to the user's identity. They are easy to validate.

The IWorkbook.Signatures property adds digital signatures to Excel workbooks. A signature line is defined to show the signer how and where to sign. Signatures are visible or invisible, based on user preference for a given document.

Here are some additional options

• Add, cut, copy, delete signature lines
• Sign the signature lines
• Sign signature lines in a signed workbook
• Modify signed workbook content and countersign
• Delete non-visible signatures from a signed workbook
• Remove signatures from the signed signature line
• Export signed workbook to PDF
• List signature lines
• Move signature lines
• Add invisible signature
• Verify signatures

The GcExcel API library provides Excel-like, strongly typed APIs for users to implement digital signature UIs or services in Excel spreadsheets. They are entirely based on Microsoft Office digital signatures support.

Read more about digital signatures in Excel spreadsheets

Help | Demo 

New Pivot Table Enhancements

A new API added to IPivotTable provides additional features when working with pivot tables in Excel.

The new features are listed below.

Create Multiple Data Fields From One Pivot Field

Add one or more fields from a single pivot field. Apply multiple calculations over a field, making it appear numerous times in a pivot table.

In the example below, sum and count functions are applied twice on the single amount pivot field. It can now appear under different columns in the pivot table.

Help | Demo 

Defer Layout Update

Whenever a pivot table data field is added, removed, or moved to a different location, the whole pivot table is recalculated. This action affects the overall performance of the Excel spreadsheet generation.

GcExcel adds the DeferLayoutUpdate property. It defers any updates to the pivot table until the user calls the update method and updates the table. This property makes generating Excel spreadsheets with pivot tables more natural and cleaner.

IWorksheet worksheet = workbook.Worksheets[0]; 
worksheet.Range["A1:F16"].Value = sourceData; 
worksheet.Range["A:F"].ColumnWidth = 15;  
var pivotcache = workbook.PivotCaches.Create(worksheet.Range["A1:F16"]);  
var pivottable = worksheet.PivotTables.Add(pivotcache, worksheet.Range["H7"], "pivottable1");    

// defer layout update 
pivottable.DeferLayoutUpdate = true;    

//config pivot table's fields  
var field_Category = pivottable.PivotFields["Category"]; 
field_Category.Orientation = PivotFieldOrientation.RowField;    

var field_Product = pivottable.PivotFields["Product"]; 
field_Product.Orientation = PivotFieldOrientation.ColumnField;    

var field_Amount = pivottable.PivotFields["Amount"]; 
pivottable.AddDataField(field_Amount, "sum amount", ConsolidationFunction.Sum);    

// must update the pivottable. 
pivottable.Update();    

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

Help | Demo

Support for Pivot Table Options for Layout and Format

GcExcel now includes various pivot table layout and format options for an improved presentation of data. The Excel pivot tables you create often need to be tweaked to get your desired look and feel. GcExcel supports options like:

• IPivotTable.MergeLabels: merges cells with outer row item, column item, subtotal, or grand total labels
• IPivotTable.CompactRowIndent: indents pivot table items
• IPivotTable.PageFieldOrder: orders pivot table fields added to the pivot table layout (DownThenOver or OverThenDown)
• IPivotTable.PageFieldWrapCount: retrieves or sets the number of fields in each column or row in a pivot table report
• IPivotTable.ErrorString: gets or sets the string in cells containing errors
• IPivotTable.NullString: gets or sets the string displayed in cells containing null values

Visit the resources below for more information.

Help | Demo 

Sorting

Sort the data in a PivotTable. Make it easy to find the items for further analysis.

• IPivotField.AutoSort() method: specifies the sort order of the pivot field data

• AutoSortField: Retrieve the name of the data field used to sort the specified pivot table field with property
• Auto SortOrder: Retrieve the sorting order
• Set or retrieve the position of an item in its field using the Position property of the IPivotItem interface.

Help | Demo 

Access Detailed Ranges

Access to only the pivot table object makes it challenging to work with full ranges within it. Additional code to access such ranges and apply other formatting is sometimes necessary. Various operations involve comprehensive ranges.

• Applying individual conditional formatting on different ranges (flagging the data area with overflow values instead of subtotals)
• Using specific text formatting on the subtotals range
• Merging header cells of the subtotals range or grand totals range

Modify the style of the subtotal labels and directly set the background color of the subtotal line cells. GcExcel introduces APIs that access the ranges in the pivot table. The APIs format data or cells as needed. APIs include:

• PivotRowAxis: left bottom of the pivot table
• PivotColumnAxis: top right of the pivot table
• PivotLine: row in the RowAxis or column in the ColumnAxis
• PivotLineCell: a cell in the pivot table
• PivotValueCell: a wrapper of the pivot cell in the value area

The new PivotLineType enum detects the subtotal line type. The user quickly sets the back color of the PivotLine Cells to the required color (example below).

Help | Demo 

Support Multiple Types of Pivot Table Field Layout Settings

GcExcel adds more settings that change the pivot table layout via code.

GcExcel already supports various layouts in a pivot table like a compact, outline, or tabular form. GcExcel v3.2 includes new, additional properties.

The new features allow the user to:

• Insert blank rows
• Set the position of subtotals
• Show all items
• Repeat any item in the pivot table layout

Help | Demo 

Shape Z-order Support

When working with a group of shapes, it's necessary to define which shape is behind or in front of others. GcExcel adds:

• IShape.ZOrder (ZOrderType zOrderType): sets the shape z-order
• IShape.ZOrderPosition: finds the ZOrder position of a shape

Help | Demo 

Support for Image Quality When Exporting to PDF

Sometimes, images added in Excel files become blurry when exported to PDF. On the other hand, if the image has the highest quality, the PDF file size balloons.

GcExcel v3.2 lets users export spreadsheets to PDFs with the highest image quality or optimized PDF file size. The PDFSaveOptions.ImageQuality property makes this possible.

Visit the following for more details.

Help | Demo

Support for Picture Transparency When Adding Images to Excel

GcExcel adds transparency support for images in Excel. Image transparency lets users add images such as logos, watermarks, or document statuses behind report data. Use IPicture.PictureFormat.Transparency and set the desired transparency value for images.

Help | Demo 

More Features for GrapeCity SpreadJS Integration

GcExcel adds the following features to improve compatibility with client-side SpreadJS products.

Note: These features only work with SpreadJS and are not supported in Excel.

Show or Hide Horizontal and Vertical Grid Lines Separately

In Excel, you can set horizontal and vertical grid lines to true or false. Both can be turned on or off in tandem, but not individually.

GcExcel v3.2 includes IWorksheetView.DisplayHorizontalGridlines and IWorksheetView.DisplayVerticalGridlines. This option lets users set gridlines to true or false individually, depending on project needs.

In the example below, vertical gridlines are turned off.

Help | Demo 

Custom Frozen Line Colors

In Excel, frozen line colors cannot be customized. Frozen line colors ensure data is separated and visible at all times. In such cases, customizing the frozen line color enhances report legibility. GcExcel now supports IWorksheet.FrozenLineColor. Set the color of the frozen panes using this option.

//Use sheet index to get worksheet.  
IWorksheet worksheet = workbook.Worksheets[0];  
// freeze pane 
worksheet.FreezePanes(3, 3);  
// Set frozen line color as dark blue. 
worksheet.FrozenLineColor = Color.Red;

Help | Demo 

Freeze Trailing Rows/Columns

In Microsoft Excel, you can freeze initial rows or columns. Sometimes other rows or columns contain essential information like subtotals, data summaries, or control buttons. Those columns or rows should be visible at all times. Adding an option to freeze trailing rows or columns gives users this option. GcExcel v3.2 includes:

• IWorksheet.FreezeTrailingPanes(int row, int column): freezes the trailing frozen pane of the sheet
• IWorksheet.UnfreezeTrailingPanes(): unfreezes the trailing frozen pane of the sheet
• IWorksheet.FreezeTrailingRow: sets the number of trailing frozen rows of the sheet

Example code below:

//Use sheet index to get worksheet.  
IWorksheet worksheet = workbook.Worksheets[0];    

// freeze pane worksheet.FreezePanes(2, 2);    

// freeze trailing pane 
worksheet.FreezeTrailingPanes(2, 3);

Help | Demo

Create Excel Reports using Templates

Never produce a complex Excel report from scratch again. Developers can begin with a template, then bind data from other data sources. GcExcel introduces new template support with a comprehensive syntax and API to bind Excel documents to data to generate Excel reports with advanced layouts.

With flexible language syntax, easy notations for data expansion, and formulas, GcExcel Templates support will be the easiest tool to define Excel templates and generate Excel reports supporting numerous use cases. You can also use the GcExcel API to directly save the Excel reports as .xlsx files or as a PDF.

Developers can create a template in Excel using the template syntax to define the database fields, variables, and formulas etc. So, at each location, if you would like to replace the information, you can define the template fields. You can now add a data source to the workbook through the Workbook.AddDatasource() method and bind the data with the template using the Workbook.ProcessTemplate() method. This template API will detect fields, matching with the database and replace with actual data.

With the combination of Visual Studio Tools for Office (VSTO) based Excel API and the templates support, you can generate full professional Excel reports for data analysis and distribution.

This solution works well on any platform: Windows, Linux, or macOS.

Generate the following reports:

1. Line reports
2. Mail-merge reports
3. Cross-table reports
4. Multiple reports in one report
5. Tablix reports
6. Receipt reports
7. Sales reports
8. Purchase order reports
9. Invoice reports (and more)

Purchase Order Example

In order to remain on budget, purchase orders are a helpful tool to clearly communicate, agreements, prices, and quantities. Purchase orders are generally sent electronically, either through an Excel spreadsheet or a PDF.

Purchase orders are produced daily, and many companies need an automated process of deriving the vendor and shipping information from the database, to generating excel spreadsheets for each vendor.

Developers can create the following template in Excel using the GcExcel Templates support which uses Mustache syntax "{{" and "}} to set the data fields:

Using the following code, the data fields will bind automatically to generate the final purchase order.

Workbook reportBook=new Workbook();
reportBook.Open("template.xlsx");
/*Code to add the data table*/
reportBook.AddDataSource("exp", dt);
reportBook.ProcessTemplate();
reportBook.Save("PurchaseOrder.xlsx");
reportBook.Save("PurchaseOrder.pdf");

Key Features

The templates support several advanced features to create business reports from simple to complex use cases.

• Simple language syntax
• Define data expansion direction
• Define Context for data expansion
• Define range of data
• Add data from multiple data sources
• Group your data
• Outline Group
• Specify nested data fields
• Define Formula functions
• Define Sort expression
• Define Expression fields
• Set Styles
• Multiple reports in one sheet
• Add page breaks
• Set inline fields
• Add template field in Worksheet's name to generate multiple worksheets

Further Reading:

• Introducing Templates to create Excel reports showcases features and uses cases with this support.

• How to generate Excel (.xlsx) & PDF reports with template language in .NET Core and JAVA gives details on the new template language feature.

For additional details and examples:

Help | Demo 

Support Conversion of Excel Spreadsheets Having Charts to PDF

Chart utilization is an essential element in Excel spreadsheets. Most Excel documents for finance, sales, marketing, and healthcare analyze data using Excel to extract the summarized data.

GcExcel can convert Excel documents containing Column, Line, Pie, Bar, Area, XYScatter, Stock, Radar, and Combo charts to PDF.

Note: For this release, this is available in GcExcel .NET Edition only.

Help | Demo

Support Conversion of Excel Spreadsheets having Slicers to PDF

A quick and effective way to filter data, Slicers are easy-to-use visual filters that can filter data residing in tables and pivot tables. Now you can distribute filtered data through a PDF file. With the v3 release, spreadsheets using slicers will now be exported to PDF. (Refer to Help for current limitations).

Help | Demo 

New Excel 2016 Chart Types

With the v3 release, GcExcel adds the ability to add new Excel 2016 chart types to Excel files. Elevate Excel data visualization to represent and analyze hierarchical data easily and arranged than traditional charts. These new Chart types provide additional functionalities that cannot be analyzed with traditional chart types of MS Excel.

In order to add the new chart types, set the ChartType property to the desired chart, set the configuration properties for the chart and the data will be arranged in the preferred chart.

Treemap Charts

Treemap charts display hierarchical information as a cluster of rectangles varying in size and color, depending on their data value. Each category is assigned a rectangle area with their subcategory rectangles nested inside of it. If you have hierarchical data, Treemap charts are ideal to visualize this data.

This chart was created with GcExcel to help to analyze the monthly sales of a company for each sales person. If you hover over any rectangle, the sales amount for each salesperson would be visible.

Additional reading:

Help | Demo 

Sunburst Charts

Like Treemap, Sunburst charts also display the hierarchical data and comparison of relative sizes. They also display the links between the groups and sub-groups. Each level of the hierarchy is represented by one ring or circle with the innermost circle at the top.

With GcExcel, you can create chart like the one below. The chart displays similar data as the Treemap chart above, but arranged in rings with inner most ring displaying the Quarter, the next ring displays the month and the outer ring displays the names of the salespeople. If you hover over the slices in outer ring, the sales amount would be visible for each salesperson.

Help | Demo 

Histogram and Pareto Charts

Histogram charts facilitate quick decision-making by summarizing large data sets graphically, in different intervals. A histogram is a column chart that shows frequency data, each column represents the frequency of elements in a certain range. A Pareto or sorted histogram chart contains both columns sorted in descending order and a line representing the cumulative total percentage. With the Histogram chart type in GcExcel, you can set the bin configuration properties.

GcExcel can now create Histogram and Pareto charts. The example below shows frequency distribution for height of students in a class.

Help | Demo 

Box and Whisker Charts

A Box and Whisker chart show distribution of data into quartiles, highlighting the mean and outliers. The boxes may have lines extending vertically called “whiskers.”

These lines indicate variability outside the upper and lower quartiles, and any point outside those lines or whiskers is considered an outlier.

With GcExcel, you can set the Axis and Series properties to set the Box and Whisker chart like ShowInnerPoints, ShowOutlierPoints, ShowMeanMarkers, ShowMeanLine and QuartileCalculationInclusiveMedian.

Help | Demo 

Waterfall Charts

Also known as Excel bridge chart, these charts are useful for analytical purposes. Evaluating company profit or earnings, or analyzing company sales or inventory data, can be done using the Waterfall chart as it shows how an initial value is affected by negative or positive values. Columns are color coded so you can quickly see differences between positive and negative values.

Following chart created with GcExcel, displays a company's profit analysis. If you apply a Stacked Column chart template to these particular values, you would not get similar results as the waterfall chart.

Help | Demo 

Funnel Charts

The Funnel chart enables users to quickly visualize stages in a linear process. Most often it's used to show sales processes to pinpoint potential problem areas. Funnels consist of the higher part called head (or base), and the lower part is referred to as the neck. The values displayed are in progressively decreasing proportion and amount to 100% in total.

The chart below shows the order fulfilment evaluation of a company in a year.

Help | Demo 

Support Security Options when Saving to PDF

Security options are essential to use in PDF documents. One of the reasons PDF formats are popular is options for security. Security options can be set; sensitive information cannot be shared or modified. Now users can set these options through the PdfSaveOptions.SecurityOptions, converting documents to PDF using GCExcel.

• Owner password
• User Password
• Print Permission
• Fill Form Permission

Help | Demo 

Support Document Properties when Saving to PDF

When documents are archived long-term, there are certain document properties that are defined for a PDF file (for example creator, title, author, producer). These properties provide additional descriptions about the document, particularly useful when documents are searched.

These properties can now be set through PdfSaveOptions.DocumentProperties, while saving an Excel spreadsheet to PDF.

• PdfVersion
• EmbedStandardWindowsFonts
• Title
• Author
• Subject
• Keywords
• Creator
• Producer
• CreationDate
• ModifyDate

Help | Demo 

Protect Workbook

This feature protects the workbook from modification using the IWorkbook.Protect() method. Operations that can be protected are: viewing hidden worksheets, adding, moving, deleting, hiding, or renaming worksheets. Additional operations that can be protected are: resizing or closing the workbook window and hide/unhide windows.

The IWorkbook.Unprotect() method can be used to remove protection from the workbook.

Help | Demo 

Chart Sheet

Sometimes, you may be short on space when accommodating all of your data and charts in a single sheet. With the chart sheet option, you can move the chart to its own sheet. This option helps users to add a chart sheet that contains only the chart in the worksheet. It is also helpful while printing the sheet, the page orientation can be changed to read the chart in detail.

The following features are supported:

• Add a chart sheet with a main chart
• Add user shape for the main chart. User shapes supported are shape, chart, picture, connector
• Create a new copy shape of the main chart or the user shape
• Delete the main chart from the chart sheet, or delete the user shape from the main chart
• Copy and move the chart sheet

Use IWorksheets.Add(SheetType.Chart) to create a chart sheet.

Help | Demo 

Add More Shape Features - Hyperlinks and Group/Ungroup Shapes

In addition to adding shapes to Excel files, you also add different features to shapes like following:

• Add hyperlink to shapes- Hyperlinks on shapes are useful to link a shape to a web page, external file, and range within the document or an email address. This is particularly useful in flowcharts when you want to link externally to a document. With GcExcel, you can add hyperlink to shapes like basic shape, chart, connector, picture and group shape.
• Support group/ungroup shapes - Grouping shapes are a standard MS feature and is mostly used with shapes in a flowchart. This helps to apply shape properties on a group as a whole at one time. You can group/ungroup shapes, pictures or any other object in a particular range using GcExcel API.

Help | Demo 

Calculate Outline Subtotal

This feature adds additional MS Excel features to the API. This feature helps to avoid manual efforts to work with group and sub-total values.

You can automatically group repeating data and define a subtotal by defining simple parameters. Normally, a user would apply group functions first, then add a subtotal field through code at a specific location. using GcExcel, when there is flat data with sorted column, the groups can now be automatically created at runtime, with subtotals calculated on a sorted column and added to a cell in the sheet. All this is done using a single IRange.Subtotal method and by providing simple parameters, such as specifying the group by field, the subtotal function, the list of fields on which to apply the subtotal in the subtotal method.

Additionally, you can replace existing subtotals programmatically, add page breaks after each subtotal to control the layout of the Excel spreadsheet, and specify the summary location row below the subtotal field.

Users can also control removing the subtotals from an existing sheet. Working with hundreds of Excel files and several subtotals, controlling this through an API is an efficient way to work with spreadsheets.

The following code creates subtotal for the range "A1:C9" with the options in the picture.

_worksheet.Range["$A$1:$C$9"].Subtotal(2, ConsolidationFunction.Count, new[] { 2 });

The code generates the following output:

Notice the automatic group creation for repeating values and the count of players is calculated for every group.

Help | Demo 

Get Precedents and Dependents of Formula Cell

A formula can be applied on a specific cell or the formula may reference some cell range. Two new APIs can help retrieve which cell range will affect the current formula cell, or which cell range the current cell will affect.

Use IRange.GetPrecedents() to get all of the ranges that the left top cell of current range depends on and IRange.GetDependents() to get all the ranges that depend on the left top cell of current range.

Help | Demo 

Add Pivot Table's Grand Totals and Report Layout Options Similar to MSExcel

Those who work more with pivot tables will benefit from this feature. You will get more control features to configure the Pivot table settings, additional functionalities and layout control.

1. Control GrandTotal visibility: Once the Pivot table is added, Grand total is available automatically. There may be times when the inclusion of grand totals (on both row or column) doesn't provide the right view of your pivot table report. The GrandTotal options available in MS Excel can turn change the visibility of the row/column/or both grand total. With GcExcel, you can apply similar settings on the pivot table using IPivotTable.RowGrand property and IPivotTable.ColumnGrand property.
2. Report Layout options: MS Excel provides options to improve the display of your pivot table, such as as tabular, column or outline form. Users can choose the option. These options can be set using the PivotTable.SetRowAxisLayout(LayoutRowType) method

Help | Demo 

Shape Adjustment

Shapes can be adjusted using IShape.Adjustments API by setting the adjustment points. The geometry of entire shapes can be changed uniformly.

Help | Demo 

Support Sheet Background Image to PDF

With the support of background images, you can set company logos, watermarks, or any other worksheet data-related background image to the worksheet. Now, a user has the option to include the background image while converting Excel spreadsheet to PDF, or ignore it using simple boolean property: PdfSaveOptions.PrintBackgroundPicture.

Help | Demo 

Export Excel Files with Multiple Images to PDF with Reduced File Size

This feature is introduced to make GcExcel more efficient when working with Excel files with multiple images to PDF. If a picture is used multiple times in an Excel file, GcExcel will have only one copy of the picture when exporting PDF, to reduce the file size.

License Workbook Instance

Now, user can license just one instance of the workbook (not all).

Help 

Rename Pivot Fields and Data Fields

The pivot fields are named as per the data source. Sometimes, additional naming is required for the row, column, page and data fields of a pivot table.

The names set in data source may not necessarily depict the actual meaning of the field. For example, 'Sum of Price' is a data field of Pivot table. But Price here, is name of a row field. If a Pivot table uses 'Total Price' as the name of the summary field, it will be easier to interpret in the final document (the total price of the products). This feature helps renaming the row, column, page and data fields of a Pivot table as per user defined terms using IPivotTable.ColumnFields property, IPivotTable.DataFields, IPivotTable.PageFields and IPivotTable.RowFields properties.

Help | Demo 

Support GrapeCity SpreadJS Features

To better integrate with GrapeCity SpreadJS products, we have added additional properties to the GcExcel API. You can now create Excel documents and apply these properties. These are also supported on Import/Export SSJSON.

a. Add Cell Tags

The Tag property can be attached to spreadsheet cell/row/column/sheet, that contains specific kinds of data with options and suggestions related to that data. You can also import/export a tag with simple data from/to SSJSON.

Help | Demo 

b. Add Cell Types

Excel can have many types of data: number, checkbox (three state), button, combobox, and hyperlink. Cell types define the type of information in a cell, how that information is displayed, and how the user can interact with it.

Cell types supported by GcExcel are supported are checkbox, button, hyperlink and combobox and they can be set to a cell or range, and even retrieve the cell type of a cell or a range using the IRange.CellType API.

Help | Demo 

c. Support Best Fit Columns/Rows API when Exporting Excel Files to PDF

When text is too long in width or height, to fit in a cell, IPageSetup.BestFitColumns or IPageSetup.BestFitRows API helps adjust the column width or row height when exporting the Excel spreadsheet to PDF.

Help | Demo