What's New in GrapeCity Documents for Excel v3.2

We are pleased to announce the release of GrapeCity Documents for Excel v3.2.

Several highlights of this release include:

• New features in Excel templates
• Add digital signatures to Excel workbooks
• New Excel to HTML conversion support
• Pivot table enhancements

(Both .NET and Java versions include most updates).

Read the full GrapeCity Documents v3.2 release

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

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.

Visit Help .NET | Help Java | Demo .NET | Demo Java

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

What do you think about the new features? Share your comments below.