What's New in GrapeCity Documents for Excel v3

We are pleased to announce the v3 release of Documents for Excel and Documents for Excel, Java Edition. The release highlights include the introduction of templates support to generate Excel reports, support for new Excel 2016 chart types, support for charts in PDF export (only in .NET version), new shape and chart features, and more.

Most updates have been made to both .NET and Java versions.

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

Generate Excel Spreadsheets for Purchase Orders

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 .NET | Help Java | Demo .NET | Demo Java

2. 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 .NET | Demo .NET

3. 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 .NET | Help Java | Demo .NET | Demo Java

4. 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 .NET | Help Java | Demo .NET | Demo Java

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 .NET | Help Java | Demo .NET | Demo Java

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 .NET | Help Java | Demo .NET | Demo Java

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 .NET | Help Java | Demo .NET | Demo Java

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 .NET | Help Java | Demo .NET | Demo Java

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 .NET | Help Java | Demo .NET | Demo Java

5. 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 .NET | Help Java | Demo .NET | Demo Java

6. 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 .NET | Help Java | Demo .NET | Demo Java

7. 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 .NET | Help Java | Demo .NET | Demo Java

8. 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 .NET | Help Java | Demo .NET | Demo Java

9. 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 .NET | Help Java | Demo .NET | Demo Java

10. 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 .NET | Help Java | Demo .NET | Demo Java

11. 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 .NET | Help Java | Demo .NET | Demo Java

12. 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 .NET | Help Java | Demo .NET | Demo Java

13. 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 .NET | Help Java | Demo .NET | Demo Java

14. 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 .NET | Help Java | Demo .NET | Demo Java

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

16. License Workbook Instance

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

Help .NET | Help Java

17. 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 .NET | Help Java | Demo .NET | Demo Java

18. 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 .NET | Help Java | Demo .NET | Demo Java

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 .NET | Help Java | Demo .NET | Demo Java

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 .NET | Help Java | Demo .NET | Demo Java

If you have any questions about the new features, please leave them in the comments below.