What's New in Documents for Excel, JAVA 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
Workbook workbook = new Workbook();
//Load template file from resource
InputStream templateFile = this.getResourceStream("xlsx/Template_fw4-USTaxForm.xlsx");
workbook.open(templateFile);

//Invoke to process the template
workbook.processTemplate();
    
// Save to an pdf file
workbook.save("USTaxForm_fw4.pdf");

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

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 to a zip file stream
FileOutputStream outputStream = null;
try {
    outputStream = new FileOutputStream("SaveWorksheetToHtml.zip");
} catch (FileNotFoundException e) {
    e.printStackTrace();
}
// Create a new workbook
Workbook workbook = new Workbook();
// Open an excel file
InputStream fileStream = this.getResourceStream("xlsx/BreakEven.xlsx");
workbook.open(fileStream);

HtmlSaveOptions options = new HtmlSaveOptions();

// Export first sheet
options.setExportSheetName(workbook.getWorksheets().get(0).getName());

// Set exported html file name
options.setExportFileName("Analysis");

workbook.save(outputStream, options);
    
// Close the zip stream
try {
    outputStream.close();
} catch (IOException e) {
    e.printStackTrace();
}

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.getWorksheets().get(0);
worksheet.getRange("G1:L16").setValue(sourceData);
worksheet.getRange("G:L").setColumnWidth(15);
IPivotCache pivotcache = workbook.getPivotCaches().create(worksheet.getRange("G1:L16"));
IPivotTable pivottable = worksheet.getPivotTables().add(pivotcache, worksheet.getRange("A1"), "pivottable1");
worksheet.getRange("J1:J16").setNumberFormat("$#,##0.00");

// defer layout update
pivottable.setDeferLayoutUpdate(true);

//config pivot table's fields
IPivotField field_Category = pivottable.getPivotFields().get("Category");
field_Category.setOrientation(PivotFieldOrientation.ColumnField);

IPivotField field_Product = pivottable.getPivotFields().get("Product");
field_Product.setOrientation(PivotFieldOrientation.RowField);

IPivotField field_Amount = pivottable.getPivotFields().get("Amount");
field_Amount.setOrientation(PivotFieldOrientation.DataField);
field_Amount.setNumberFormat("$#,##0.00");

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

worksheet.getRange("A:D").getEntireColumn().autoFit();
    
// Save to an excel file
workbook.save("DeferLayoutUpdate.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 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.

// Create a new workbook
Workbook workbook = new Workbook();

//Use sheet index to get worksheet.
IWorksheet worksheet = workbook.getWorksheets().get(0);

// freeze pane
worksheet.freezePanes(5, 5);

// Set frozen line color as dark blue.
worksheet.setFrozenLineColor(Color.GetDarkBlue());

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:

// Create a new workbook
Workbook workbook = new Workbook();

//Use sheet index to get worksheet.
IWorksheet worksheet = workbook.getWorksheets().get(0);

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

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

Help | Demo 

---

Enhanced Templates Support

In v3 release last year, we introduced Templates support with comprehensive syntax and API to bind Excel documents with data and generate Excel reports with advanced layouts. There are many Excel features that are popular and common to use with spreadsheets. With the new release, we have supported these features together with Excel templates. So you can combine the power of MS Excel features and Excel Templates to generate full professional Excel and PDF Excel reports. Check out the new features below:

Excel Chart Templates

You can now bind Excel Charts with data by defining the Chart template for the series data. You can set the series data of the Chart to the correct template cells, and GcExcel will process the template showing the Chart bound with the data in the spreadsheet. For this, you need to create an Excel template, define the Template fields and formulas, and just configure the Chart Series Name, Values and Axis Label to the Template cells. The Charts will bind to data upon processing of the template.

Mostly Charts in Excel are bound with a range of data, but if the range of data increases, you will have to manually update the range of data in the 'Select data source' dialog. One of the advantages of binding Charts with template cells is that upon Template processing, the final chart will always be updated with the latest data. You will not have to manually update the range of data.

Also, most Excel API vendors provide solutions for binding charts with named ranges. For that, you will have to define the named ranges through code and then bind the Chart series with the named ranges. This will pose a problem when the data in the data source expands. You will manually have to modify the range of data in code. GcExcel Templates support focuses more on designing everything within the template rather than adding an extra step of coding to accomplish a task. With the Chart Templates support, you can directly bind the Chart series with the Template cell, not worrying about the range of data in the underlying data source.

The other advantage of using Chart templates is that you can bind the Chart with differently structured data. For example, your data can exist as an array, JSON, data table or any custom object. In the Excel template, you just have to define the right data field in the Template cell, and then bind that template cell with the Chart. Your chart does not have to worry about the structure of the data, it just follows the data field defined in the template cell and expands with respect to the data field.

Help | Demo 

Bind Excel Templates with Images

Most data sources include Image fields in the database, like employee personal profile data, a catalog of products, etc. that all involve images. If an Excel template is bound with such a database, it is necessary to specify the image field, so that it can be bound with the image field in the database.

In order to create full Excel reports bound with a database with image fields, just set the 'image' property to 'true' for the data field, and the cell will be bound directly with image. You can also set the height and width of the image, so the template expands with similar height and width for the images throughout the rows.

{ds.icon(image=true)} 
 {{ds.icon(image=true, image.height=150px)}}
 {{ds.icon(image=true, image.width=150px)}}

Help | Demo 

Support for Conditional Formatting

Together with displaying data, Excel spreadsheets have the power of using conditional formatting rules on cells based on the values. This feature is used so regularly that you cannot imagine generating Excel reports without having the support of conditional formatting rules.

With the new release, you can now set conditional formatting rules on the template cells. All types of conditional formatting rules are supported. These rules will be applied to all the cells which are expanded from the original template cells.

Consider a report where you want to indicate sales revenue higher than $100,000, between $50,000 and $100,000, and lower than $50,000. You can do so by adding the conditional formatting rule (Icon set) while designing the template on the template cell C14.

The advantage of applying conditional formatting on the template cell is that no matter how much the data gets updated in the database, your final Excel report will always be updated, and the template or final report need not be changed manually to reflect the new data.

Help | Demo 

Export Excel Formula

Formulas applied on a template can now be exported as-is to Excel file. In the final report, if you select a cell with a formula applied, you can check the formula that was applied in the original template, and obtain the range on which the formula is applied.

The formula must refer to the right range of data while defining in the template and must include an "equal to" sign before the formula: {{=Formula}}. This feature is useful when users would want to re-use the formula in existing or another Excel file.

Help | Demo 

Support for Global Settings

GcExcel Templates feature provides the user the ability to specify default settings on the Excel Template, so it applies to entire data when the template expands. Using these settings, you would not have to repeat the properties in each field. This is especially helpful when there are several templates being managed to create Excel reports.

When you define a template cell, it expands into a number of rows and columns. To show equally sized rows and columns, you may want to set a default size of the template cell. Or, you can insert extra rows/columns or cells in your final report.

You can now define the new properties within the template by using a built-in workbook defined names TemplateOptions. The template automatically identifies the default settings and applies them to the template cells.

The following properties can now be set globally within the Excel Template through the Name Manager:

TemplateOptions.KeepLineSize: This property would retain the row height and column width that you set in the template, throughout, when the template expands with data.

TemplateOptions.InsertMode: This property will insert cells, rows & columns, to add extra spaces while expanding the template.

Alternatively, you can also set the defined names through code; this method is equivalent to setting the options in the MS Excel template like above.

// Create a new workbook
Workbook workbook = new Workbook();
//Load template file from resource
InputStream templateFile = this.getResourceStream("xlsx/Template_InsertMode.xlsx");
workbook.open(templateFile);

//Init template global settings
workbook.getNames().add("TemplateOptions.InsertMode", "EntireRowColumn");

//Add data source
workbook.addDataSource("ds", datasource);
//Invoke to process the template
workbook.processTemplate();
    
// Save to an excel file
workbook.save("InsertMode.xlsx");

Help | Demo 

Excel Objects to Image Formats

Sometimes, you may want to share certain portions of the worksheet separately on a web page or other documents. It would be easier to export such objects from the worksheet directly to Image format. GcExcel adds ToImage method to IWorksheet, IRange and IShape.

This feature will help saving shape, chart, worksheet, range, images used in your Excel files directly to image format without extra step or additional API.

Help | Demo

Support Password-Protected Workbook and Worksheet

This feature will enable protecting a workbook/worksheet using a password. The workbook/worksheet can only be unprotected, using the same password. This feature is helpful in preventing other users (without having the password) to view hidden worksheets, add, move, delete, hide, or rename worksheets in the Excel spreadsheet.

Organizations could control sharing confidential Excel spreadsheets to limited members. In case you have a forwarded Excel sheet, by mistake to an unintended recipient, he cannot unprotect the workbook and tamper with the Excel file if he does not have the password of the Excel spreadsheet.

The Protect/Unprotect methods of a workbook/worksheet now include the password strings: IWorkbook.Protect(string password), IWorksheet.Protect(string password): **Use these methods to add a password to a workbook or worksheet. IWorkbook.Unprotect(string password), IWorksheet.Unprotect(string password):** A user would require the same password to unprotect a workbook or a worksheet.

Help | Demo Workbook | Demo Worksheet 

Chart Enhancements

Support Error Bar of Chart

To help show margins of error and standard deviations, GcExcel adds new APIs ISeries.XErrorBar, ISeries.YErrorBar, ISeries.HasErrorBars to add Error Bars to the charts. So along with the complete data visualization, your Excel files having charts can now also show graphical representations of data variability.

There are various operations you can perform with this support:

• Add ErrorBar types
  • Custom
  • FixedValue
  • Percentage
  • StDev (Standard Deviation type)
  • StError (Standard Error type)
• Set Include type and End cap type
• Configure Error Bar's style
• Error Bar direction - X and Y
• Delete error bar

The above example shows data variability in the sales of Mobile phones for the three quarters.

Help | Demo 

Support Text Angle of Chart Title/Axis Tick Label/Data Label

Sometimes you may not want to resize your charts, but if your axis tick, data labels/chart titles are long, they may huddle together. This feature will help you set the custom angle on the axis data or tick labels or Chart titles when displayed on charts. The ChartTitle, DataLabels, and TickLabels can now set Orientation property (integer value ranging -90 to 90 degrees) to set the respective orientation of the data.

Help | Demo 

Add Trendline Name

MS Excel gives a default name to the trendline, but if you want to set a specific name (based on the data pattern you are measuring), you can do so with GcExcel API.

The ITrendline.Name property can help you set the trendline name, and if you have multiple trendlines, you can separately identify the purpose of each trendline by its name.

Help 

Support Alignment of Shape's TextFrame

Excel spreadsheets consisting of shapes use text on the shapes to better describe the data. GcExcel adds support to ITextFrame.HorizontalAnchor/VerticalAnchor to get/set the horizontal/vertical alignment of text in a text frame. This API will help to control the alignment and positioning of the text frame of a shape.

Help | Demo 

Add Image to a Specific Range

Until now, in order to add a picture to the worksheet at a particular location, you needed to know the absolute location and size of the image. Sometimes, you may want to specify the specific range to start rendering the image. To simplify your workload of calculating the location and size of the image, GcExcel adds GetRangeBoundary(IRange) method to its CellInfo class. This will get the position and size of a particular range where you want to add the image. It will simplify your task by not calculating the position yourself, but using the GetRangePosition() to determine the right range position.

Help | Demo 

Support Gradient Fill Type in Shapes

GcExcel has always supported setting gradient properties for filling Shapes. Additionally, GcExcel adds the IFillFormat.GradientPathType enum and IFillFormat.TwoColorGradient method to specify Gradient Style and fill type when filling shapes.

The user can specify the following types of Gradient Fills through an enum conveniently.

• Linear
• Radiant
• Rectangular
• Path

Help | Demo 

Access Chart, Shape, or Image by Name

You can now assign a name to the chart, shape, and image object used in the worksheet. This will help you to access the objects used in the worksheet by names, and further modify the properties instead of traversing through a list of Excel objects to determine the right object to modify.

// Create a new workbook
Workbook workbook = new Workbook();

IWorksheet worksheet = workbook.getWorksheets().get(0);
IShape shape = worksheet.getShapes().addShape("custom parallelogram", AutoShapeType.Parallelogram, 1, 1, 200, 100);

// Get shape by name
IShape parallelogram = worksheet.getShapes().get("custom parallelogram");
parallelogram.getFill().getColor().setRGB(Color.GetRed());
    
// Save to an excel file
workbook.save("AddShapeWithCustomName.xlsx");

Help | Demo

PDF Export Enhancements

Enhanced Background Image Support for Printing to PDF

You can now add multiple background images and set image layouts, transparency, and other properties when exporting Excel files to PDF. GcExcel adds IBackgroundPicture to give you more control over the background image settings that you need in a PDF.

Help | Demo 

Get Pagination Info for Printing to PDF

You can now extract how your Excel file will be paginated in PDF. GcExcel adds GetPaginationInfo methods to PrintManager class to get page boundaries as per the PrintArea of the worksheet. Additionally, in order to control the pagination, you can set certain properties in PageSetup, KeepTogetherRanges and RepeatSettings.

In an Excel spreadsheet, you can view the Page Break view of the spreadsheet, through which you get an idea of how the content will be divided into different pages. The GetPaginationInfo is designed to give you similar information through code so that either you can adjust the data as per the pages, or set what data you want to keep together when printing to PDF.

Help | Demo 

Support Transparent Cell Background Color in PDF

If you have specific content that you want to set the transparent back color on the cell, the transparency may be lost when your Excel spreadsheet is converted to PDF through MS Excel.

With GcExcel, the PdfSaveOptions now includes the PrintTransparentCell property. If set to true, the transparency of the cell is preserved when exporting to PDF. You can add other background content to the cell so that they are visible upon PDF Export. In the example below, a company wants to print the blood test report to PDF, so the company watermark is visible transparently in the background. The cell back color is chosen as transparent so that the background content is visible when exporting the Excel file to PDF.

Help | Demo

Additional Features for SpreadJS Integration

Support Worksheet JSON I/O

Now you can import and export a single worksheet from/to JSON from/to the same or another workbook. It provides a solution for the customer to transfer only a single sheet between frontend & backend for GrapeCity SpreadJS Component.

// Create a new workbook
Workbook workbook = new Workbook();
//ToJson&FromJson can be used in combination with spread.sheets product:http://spread.grapecity.com/spreadjs/sheets/

//GrapeCity Documents for Excel import an excel file.
//Change the path to real source file path.
String source = "source.xlsx";
workbook.open(source);

//Open the same user file
Workbook new_workbook = new Workbook();
new_workbook.open(source);

for (IWorksheet worksheet : workbook.getWorksheets()) {
    //Do any change of worksheet
    //...

    //GrapeCity Documents for Excel export a worksheet to a json string.
    String json = worksheet.toJson();
    //Use the json string to initialize spread.sheets product.
    //Product spread.sheets will show the excel file contents.

    //Use spread.sheets product export a json string of worksheet.
    //GrapeCity Documents for Excel use the json string to update content of the corresponding worksheet.
    new_workbook.getWorksheets().get(worksheet.getName()).fromJson(json);
}

//GrapeCity Documents for Excel export workbook to an excel file.
//Change the path to real export file path.
String export = "export.xlsx";
new_workbook.save(export);

Help | Demo

Support Outline Column to Display Hierarchical Data

GcExcel adds OutlineColumn options to display hierarchical data in a treeview in the worksheet in SpreadJS component. In addition it also supports ShowRowOutline/ShowColumnOutline to show row and column outlines in the worksheet. The outline column can also be exported to PDF and imported or exported to JSON to interact with SpreadJS.

Help | Demo 

Support Data Binding of Range, Table, and Worksheet

You can now bind Range, Table and Worksheet directly with data using the new BindingPath property introduced for IRange, ITable to set the binding path directly with the data source. Additionally, new API IWorksheet.DataSource is introduced to get/set the datasource for the worksheet. The binding path supports JSON I/O to interact with GrapeCity SpreadJS component.

// Create a new workbook
Workbook workbook = new Workbook();

public class SalesData {
    public ArrayList<SalesRecord> sales;
}

public class SalesRecord {
    public String area;
    public String city;
    public String category;
    public String name;
    public double revenue;
}

SalesData datasource = new SalesData();
datasource.sales = new ArrayList<SalesRecord>();

SalesRecord record1 = new SalesRecord();
record1.area = "North America";
record1.city = "Chicago";
record1.category = "Consumer Electronics";
record1.name = "Bose 785593-0050";
record1.revenue = 92800;
datasource.sales.add(record1);

SalesRecord record2 = new SalesRecord();
record2.area = "North America";
record2.city = "New York";
record2.category = "Consumer Electronics";
record2.name = "Bose 785593-0050";
record2.revenue = 92800;
datasource.sales.add(record2);

SalesRecord record3 = new SalesRecord();
record3.area = "South America";
record3.city = "Santiago";
record3.category = "Consumer Electronics";
record3.name = "Bose 785593-0050";
record3.revenue = 19550;
datasource.sales.add(record3);

IWorksheet worksheet = workbook.getWorksheets().get(0);

// Set AutoGenerateColumns as false
worksheet.setAutoGenerateColumns(false);

// Bind columns manually.
worksheet.getRange("A:A").getEntireColumn().setBindingPath("area");
worksheet.getRange("B:B").getEntireColumn().setBindingPath("city");
worksheet.getRange("C:C").getEntireColumn().setBindingPath("category");
worksheet.getRange("D:D").getEntireColumn().setBindingPath("name");

// Set data source
worksheet.setDataSource(datasource.sales);
    
// Save to an excel file
workbook.save("BindManually.xlsx");

Help | Demo 

Return Errors from JSON Import

Now you can check what errors occur when you import data from JSON file into the Workbook. The data can be checked for two errors: FormulaError and DataValidationError. These list of errors will help you correct the errors in your JSON.

Help 

---

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.

// Create a new workbook
Workbook workbook = new Workbook();
//Load template file from resource
InputStream templateFile = this.getResourceStream("xlsx/Template_PurchaseOrder.xlsx");
workbook.open(templateFile);

//Add data source
workbook.addDataSource("po", purchaseOrderInfoList);
workbook.addDataSource("tax", 5);
workbook.addDataSource("ds", orderbasicInfo);
//Invoke to process the template
workbook.processTemplate();
    
// Save to an excel file
workbook.save("PurchaseOrder.xlsx");

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

workbook.getActiveSheet().getRange("A1:C9").subtotal(2, // Side
    ConsolidationFunction.Count,
    new int[] { 2 } // Side
    );

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