What's New in GrapeCity Documents for Excel v3.1
We are pleased to announce the new v3.1 release for GrapeCity Documents for Excel. GcExcel allows you to generate and work with spreadsheets with fast performance, a small footprint, and in .NET Standard 2.0 and Java applications.
The main highlights of this release are enhanced Templates support to generate Excel reports with charts, images, conditional formatting and more. In addition, this release adds Chart enhancements, exporting specific Excel objects directly to images, enhanced printing of Excel spreadsheets to PDF and much more. Most updates have been made to both .NET and Java versions. Check out the new features below.
Enhanced Templates Support
In the 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.
Most 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.
Visit Help .NET | Help Java | Demo .NET | Demo Java
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)}}
Visit Help .NET | Help Java | Demo .NET | Demo Java
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.
Visit Help .NET | Help Java | Demo .NET | Demo Java
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.
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.
Workbook workbook = new Workbook();
workbook.open("Template.xlsx");
//Init template global settings
workbook.getNames().add("TemplateOptions.KeepLineSize", "true");
workbook.getNames().add("TemplateOptions.InsertMode", "EntireRowColumn");
//Add data source
workbook.addDataSource("ds", ds);
//Invoke to process the template
workbook.processTemplate();
workbook.save("Report.xlsx");
Visit Help .NET | Help Java | Demo .NET | Demo Java
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.
Exporting all Excel objects (except charts) to an image format is also supported in GcExcel Java. For more information, refer to to our documentation.
Visit Help .NET | Help Java | Demo .NET | Demo Java
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.
Visit Help .NET | Help Java | Demo Workbook .NET | Demo Worksheet .NET | Demo Workbook Java | Demo Worksheet Java
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.
Visit Help .NET | Help Java | Demo .NET | Demo Java
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.
Visit Help .NET | Help Java | Demo .NET | Demo Java
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.
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.
Visit Help .NET | Help Java | Demo .NET | Demo Java
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.
Visit Help .NET | Help Java | Demo .NET | Demo Java
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
Visit Help .NET | Help Java | Demo .NET | Demo Java
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
var workbook = new Workbook();
IWorksheet worksheet = workbook.Worksheets[0];
//Create shape with custom name
IShape shape = worksheet.Shapes.AddShape("rectangle", AutoShapeType.Rectangle, 50, 50, 200, 200);
//Get the shape by name
IShape rectangle = worksheet.Shapes["rectangle"];
rectangle.Fill.Color.RGB = Color.Red;
Visit Help .NET | Help Java | Demo .NET | Demo Java
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.
Visit Help .NET | Help Java | Demo .NET | Demo Java
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.
Visit Help .NET | Help Java | Demo .NET | Demo Java
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.
Visit Help .NET | Help Java | Demo .NET | Demo Java
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.
string fileName = "UserFile";
string source = "D:\\" + fileName + ".xlsx";
//Open user file
Workbook workbook = new Workbook();
workbook.Open(source);
//Open the same user file
Workbook workbook_save = new Workbook();
workbook_save.Open(source);
foreach (IWorksheet worksheet in workbook.Worksheets)
{
//Do any change of worksheet
//...
string json = worksheet.ToJson();
workbook_save.Worksheets[worksheet.Name].FromJson(json);
}
//Save
workbook_save.Save("D:\\" + fileName + "_output.xlsx");
Visit Help .NET | Help Java | Demo .NET
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.
Visit Help .NET | Help Java | Demo .NET | Demo Java
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
var workbook = new GrapeCity.Documents.Excel.Workbook();
#region Define custom classes
//public class SalesRecord
//{
// public string Area;
// public string Salesman;
// public string Product;
// public string ProductType;
// public int Sales;
//}
#endregion
#region Init data
var record = new SalesRecord
{
Area = "NorthAmerica",
Salesman = "Hellen",
Product = "Beverages",
ProductType = "Tea",
Sales = 120
};
#endregion
IWorksheet worksheet = workbook.Worksheets[0];
// Set binding path for cell.
worksheet.Range["A1"].BindingPath = "Area";
worksheet.Range["B2"].BindingPath = "Salesman";
worksheet.Range["C2"].BindingPath = "Product";
worksheet.Range["D3"].BindingPath = "ProductType";
// Set data source.
worksheet.DataSource = record;
//save to an excel file
workbook.Save("SalesReport.xlsx");
Visit Help .NET | Help Java | Demo .NET | Demo Java
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.
Read the full GrapeCity Documents v3.1 release.
What do you think about the new features. Share your comments below.
Shilpa Sharma