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

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.

Visit Demo .NET | Demo Java

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.

This is supported in .NET only.

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.

Help .NET | Help Java

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.

GcExcel v3

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:

What's new in GrapeCity Documents for Excel in v3

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:

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.

What's new in GrapeCity Documents for Excel in v3

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

Support Conversion of Excel Spreadsheets having Slicers to PDF

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.

What's new in GrapeCity Documents for Excel in v3

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.

What's new in GrapeCity Documents for Excel in v3

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.

What's new in GrapeCity Documents for Excel in v3

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.

What's new in GrapeCity Documents for Excel in v3

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.

What's new in GrapeCity Documents for Excel in v3

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.

What's new in GrapeCity Documents for Excel in v3

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.

What's new in GrapeCity Documents for Excel in v3

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

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.

What's new in GrapeCity Documents for Excel in v3

What's new in GrapeCity Documents for Excel in v3

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

The code generates the following output:

Documents Release

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.

GrapeCity Documents

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.

GrapeCity Documents

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