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

GcExcel v2 Service Pack 2

Export Spreadsheets with Shapes to PDF

GcExcel provides an extensive API to work with shapes in spreadsheets. You can insert arrows, lines, pictures, and general shapes (including the MS Excel in-built shapes). This helps in creating worksheets that can transform textual data into interesting figures and emphasize important facts. You can now export spreadsheets with shapes to a PDF.

The shape properties supported in PDF include:

  • Fill styles
  • Line styles
  • Shapes with text
  • Shapes with pictures
  • Rotated shapes
  • Flipped shapes
  • Grouped shapes

Export Spreadsheets with Shapes to PDF

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

Control Pagination and Spreadsheet Content

GcExcel introduces a PrintManager class to control pagination and spreadsheet content while exporting the spreadsheets to PDF. This feature helps control the printing of specific parts of Excel to PDF documents by providing the user specific information for pagination results in the PageInfo object, and a list of pages generated from the Excel file through the PrintManager.Paginate method. It also performs operations like:

  • Add/delete/modify pages
  • Change page number, page count, page content and page settings

This process involves following steps:

This feature helps solve different scenarios while converting Excel files to PDF. Visit our Documentation to learn more use cases about the PrintManager class.

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

Render Excel Ranges Inside a PDF

Reports are a combination of textual descriptions and numbers. A full financial report is a mix of business activities and financial performance of the company. In terms of numbers in Excel, a full financial report is a mix of business activities and the financial performance of the company. A PDF report is often generated for distribution to clients because it is a non-editable format that preserves the quality of the original document.

Render Excel Ranges Inside a PDF

This report should contain a company cash flow report and show net income data from the Excel file.

Many parts of the report display data in the form of tables (that are generally managed in an Excel file), while the textual data is already described in the PDF file. When handling such documents through an API, it is important to extract tables from the Excel file and render inside a PDF file at some location to generate a complete report.

Users will now be able to extract this range of data from Excel using the GcExcel PrintManager class and print it to PDF in combination with the GcPdf API.

Note: To use this use case, you will need a license for both GrapeCity Documents for PDF and GrapeCity Documents for Excel products (.NET version).

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

At times, printing all of the pages is not needed, and only specific information needs to be extracted from the Excel file. For example, your company has a flat list of products and sales for the year and analyses it through a Pivot table. You need to see the amount of sales for a specific customer and total product-wise sales. You must also print the specific analysis of the Pivot table that is adjoining the flat list into PDF and omit the flat list of products in order to distribute only the analysis to the stake holders.

The document below, the company wants to print Page 5, 6, and 7.

Also, in the final document, it wants to print continuous page numbers 1, 2, 3 etc. instead of 5, 6, 7.

Print Specific Pages of Excel to PDF

You can pick specific pages from the PageInfo object and then use PrintManager.UpdatePageNumberAndPageSettings(..) method to set continuous page numbers.

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

Keep Certain Ranges of Data Together While Printing

At times, data needs to be printed together, not printed separately on individual pages. Especially, when the cells are merged, users may not want the merged rows to split on different pages while printing. The KeepTogetherRanges class can help you set the range which you need to keep together.

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

Excel sheets have data on multiple worksheets. For example, in a company's monthly budget details, the monthly summary is on the first worksheet, while the monthly budget details are on a separate worksheet. In this case, it would be ideal to be able to distribute the data on a single page to company shareholders.

Printing multiple worksheets to a single page in PDF would be useful and the PrintManager class of GcExcel, in combination with GcPdf API, can help accomplish that task. The PageInfo object of PrintManager can get all pages, then PrintManager.Draw(..) can draw the pages to a page in GcPdf Document, in the layout of n rows and n columns.

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

Manage Headers on Different Pages

When printing a PDF, if there are multiple tables in a worksheet, table headers may need to be repeated on all pages. Users can specify the header row that should repeat when respective tables overflow to other pages.

Use the RepeatSettings class. This gives access to Title/Tail Row, Column, start, and end for the specific ranges. You can send RepeatSettings in the PrintManager.Paginate(..) method and much more.

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

Export Different Excel Workbooks into Only One PDF

In any industry, there are hundreds of Excel reports generated to analyze data. To deliver to the client, companies prefer a PDF format because it cannot be edited and PDFs preserve the original document's quality and formatting.

If several reports belong to a single project, you may not want to deliver several PDFs to the client. With this release, it is possible to export different Excel workbooks into a single PDF file though a simple code.

The PrintManager class can directly save different workbooks to a PDF.

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

Repeat Rows at Bottom and Columns at Right in a PDF

Repeating rows/columns at bottom or left is helpful in many scenarios. For example, your company may need to repeat a disclaimer or a note at the bottom of every printed page:

Example of a WHO Report

We utilized the WHO report as an example. It collects regional health technology information as part of the National Health Program, or regions with only a health technology center that is not part of the National Health Program. We utilized Yes1 and Yes2 values and meanings as a legend that will be repeated at the bottom of every page.

You may need different areas of your Pivot Table to be formatted differently to emphasize which data belongs where; specifically, areas such as: an organization’s monthly budget report.

The report will be printed in 3 pages, with every page repeating the message at the end.

Microsoft Excel does not have a direct way of repeating rows or columns at bottom or right, it only has the option to repeat rows or columns at the top or left. To repeat rows at the bottom, one of the ways is using Footer on every page, but it is limited for large texts.

In the the new GcExcel workbook's PageSetup API addition, there is a direct way to repeat rows at bottom or repeat columns on right while exporting to PDF. Use IPageSetup.PrintTailRows/PrintTailColumns to the row or column that needs to be repeated during PDF Export.

Help .NET (Repeat rows at bottom) | Help .NET (Repeat Columns at right) | Help Java (Repeat rows bottom) | Help Demo (Repeat Columns at right)

Demo .NET (Repeat rows at bottom) | Demo .NET (Repeat Columns at right) | Demo Java (Repeat rows at bottom) | Demo Java (Repeat Columns at right)

Format Pivot Tables with Pivot Table Style and Pivot Field's Number Format

Pivot Tables are summarization tools with complex data grouped and arranged in a number of rows and columns. You need different areas of Pivot Table to be formatted differently to emphasize which data belongs where, specifically areas such as:

  • Row/Column Headers
  • Alternate rows/columns
  • Grand totals column style
  • Sub-total rows/column styles
  • Pivot Fields number format

This is required in several scenarios, for instance, a sales report displays data arranged in a Pivot table, with grouping on Product ID, thus displaying sales data quarterly. This table should highlight each row header and sub-row header in different backgrounds, and display banded rows to display information separately.

New API features for PivotTable enable this type of control. Once you set these, the repeating rows/column styles in header, subtotal, and grand total will have the same style (as defined in the API). You won't need to style each row/column separately. On formatting the Pivot table, the Pivot table with styles and number format can be exported to PDF, without loss of any style.

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

Customize Row/Column/Value Delimiter When Importing/Exporting .CSV Files

A comma-separated values file (.csv) for tabular data is often used to manage information in Excel files.

For example, product, inventory, customer and data management is managed in .csv. It is easy to add information to the file. Sometimes this data is imported from a third-party distributor. In this case, it's not necessary for the data to be separated using only commas. Any string or character can be used to separate rows or columns for an Excel file, or cells can be separated using a character.

Until this release, only column delimiters were supported by GcExcel (which could also be customized). With the new API additions, GcExcel supports importing and exporting .csv files with any row/cell/column delimiters.

var openOption = new CsvOpenOptions();
openOption.ColumnSeparator = ",";
openOption.RowSeparator = "\r\n";
openOption.CellSeparator = '"';
workbook.Open(@"D:\test.csv", openOption);

var saveOption = new CsvSaveOptions();
saveOption.ColumnSeparator = "-";
saveOption.RowSeparator = "\n";
saveOption.CellSeparator = '\'';
workbook.Save(@"D:\test_out.csv", saveOption);

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

Support for Paste Options

Excel files are known for formatted data and number formats with hundreds of formulas applied. This data is arranged in rows and columns with specific height and width. Once a complex worksheet is created, there is a need for duplicating the content on other worksheets. There are different scenarios where you may want to copy only the content, copy content together with all formatting and formulas applied, or just the formatting.

For example, an organization's monthly budget report. Every month, the formulas for calculations remain the same; however, formatting may be modified. The purpose of this example reflects how you can copy the formulas only. Values and formatting can be different.

Support for Paste Options

Support for Paste Options

Previously with GcExcel, all data in a range (with all settings except column width/row height) got copied by default. GcExcel now includes various PasteType options through which multiple scenarios involved in copy/pasting content would be covered though Excel files, for example:

  1. Paste formulas and format
  2. Paste values only
  3. Paste formulas only
  4. Paste number formats only
  5. Paste column widths only
  6. Paste row height only
  7. Paste combination of values and number formats
  8. Paste combination of formulas and number formats

Use the following code to copy formulas only:

var workbook = new Workbook();
var worksheet = workbook.ActiveSheet;
worksheet.Range["A1"].Value = "2019/3/08";
worksheet.Range["A1"].Font.Size = 20;

worksheet.Range["A1"].Copy(worksheet.Range["D1"], PasteType.Formulas);

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

Control Adjusting Page Breaks when Inserting/Deleting Rows/Columns

Until this release, page breaks remained fixed when rows/columns were inserted in Excel sheets. In some situations, page breaks need to be adjusted with additions to data. For example, a company prints its sales record from Excel to PDF so each region can appear on a separate page. The page breaks are inserted before a region starts. Some rows may be added depending on the sales, so the page breaks should always be adjusted so each region always begins on a new page.

Use FixedPageBreaks property of a GcExcel worksheet to control adjustments on Page breaks (on row/column insertion/deletion). You'll need to adjust the page breaks even if some rows are added in between. This way, every region still continues to start from a new page. In order to adjust page breaks on row/column insertion deletion, set FixedPageBreaks to false.

If you still want to keep the page break at same location, set FixedPageBreak to true.

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

Copy and Move Worksheets

Copying and moving worksheets to different locations in the workbook (or to other workbooks) is used when working with a large number of worksheets and master templates. For example, a company's budget worksheet contains complex data and several formulas that need to be filled in quarterly, and repeated for every year. There is a master template that each department uses to create budget reports. This task can be automated through an Excel API.

GcExcel API can copy and move worksheets programmatically. The worksheet.Copy method can copy the worksheet to same or different location.

Workbook workbook = new Workbook();
var worksheet = workbook.ActiveSheet;

var worksheet_copy_sameWorkbook = worksheet.Copy();

var newWorkbook = new Workbook();
var worksheet_copy_otherWorkbook = worksheet.Copy(newWorkbook);

Help .NET | Help Java | Demo-Copy Worksheet .NET | Demo-Move Worksheet .NET | Demo Copy Worksheet Java | Demo Move Worksheet Java

Cut and Copy Range Between Workbooks

It's now be possible to cut and copy ranges between workbooks.This makes it easy to cut/copy selective data in any range, and then re-use in other workbooks. This is particularly helpful if the same data is to be used across several hundreds of workbooks.

Cut and Copy Range Between Workbooks

Sample code required for above example:

 Workbook workbook = new Workbook();
var worksheet = workbook.ActiveSheet;

var newWorkbook = new Workbook();

//Copy range to another workbook
worksheet.Range["B2:F21"].Copy(newWorkbook.ActiveSheet.Range["D5"]);

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

Find and Replace Text

When data needs to be updated to several spreadsheets, it's crucial to be able to quickly find the information you want. Scanning through hundreds of rows and columns is time-consuming, but you can simplify this task in a few seconds using an Excel API.

For example, a company wants to update the yearly budget spreadsheet that is divided into different spreadsheets with monthly budget data. There is a new addition in the monthly expenses, because the formulas need to be changed on every sheet. Every department in the company has its own budget sheet for the year, divided monthly. It is needed to find the specific formula in every worksheet and update the range over which the total is to be performed.

GcExcel's Find and Replace API offers all of these options and more.

Following code shows how you can find and replace formula in a worksheet:

FindOptions fd = new FindOptions();
fd.LookIn = FindLookIn.OnlyFormulas;

foreach (var worksheet in workbook.Worksheets)
{
    IRange range = null;
    do
    {
        range = worksheet.UsedRange.Find("X", range, fd);
        if (range != null)
        {
            range.Formula = range.Formula.Replace("X", "Y");
        }
    } while (range != null);
}

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

Support for FormulaArrayR1C1 in Cell Range

GcExcel now supports IRange.FormulaArrayR1C1() that makes it possible to reference R1C1 style formula for any cell range.

Example:

worksheet.Cells["A11"].R1C1Formula = "=SUM(R[-10]C[0]:R[-7]C[0])";

Support Shrink to Fit for Wrapped Text While Exporting to PDF

GcExcel now allows you to shrink the text within the cell, even when the text is wrapped, to be visible when exported to PDF.

In Microsoft Excel, 'Shrink to fit' settings get disabled for wrapped text. When this document is exported to PDF, the text's visibility is incomplete with existing row height.

GcExcel introduces a new API - PdfSaveOptions, that provides ShrinkToFitSettings to users. With these settings, users can shrink the wrapped text within the cell with existing row height/column width, while exporting to PDF. Users can also set a minimum font size or an ellipsis string to show longer text.

The data shrinks to fit in the cell.

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

Support Import Flags for XlsxOpenOptions

GcExcel has been supporting NoFlag, Data, and Formulas Import flags while importing Excel spreadsheets. Now, you can read additional types of data to support importing maximum features while reading spreadsheets:

  • Table
  • MergeArea
  • Style
  • ConditionalFormatting
  • DataValidation
  • PivotTable
  • Shapes

Help .NET | Help Java

Support Auto Fit/Row Height/Column Width API

GcExcel introduces the option to auto fit row height and column width of any range that you provide, and adjust the size as needed depending on the data. The API is simple to use, just use 'AutoFit()' method with any range, range rows or range columns.

Sometimes data is too long to fit inside an Excel cell, for text can span over the other cells. Imagine you have thousands of rows with varying lengths of text. You would have to scan through entire data to check the row heights and column widths, just to make sure the data is completely visible. It would be very convenient if the Excel API automatically determines how much to widen or narrow the column and expand or collapse the row/column to match the data size.

Before AutoFit

After AutoFit

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

Preserve OLEObjects While Excel I/O

You can now load, modify, and save Excel spreadsheets with OLEObjects through GcExcel. These objects would now be preserved during I/O operations.

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

Preserve Japanese Ruby Characters

Preserve Japanese ruby characters while Excel I/O using GcExcel. The Japanese Ruby characters will be preserved even after certain API operations like: Insert/Delete/Copy/Cut/Merge/Clear/Sort.

Preserve Japanese Ruby Characters

GcExcel v2 Service Pack 1

Add rich text to spreadsheet cells

GcExcel adds support for adding rich text to a cell, to achieve multiple styles on different parts of the text within a cell. You can set various font properties on the cell text through the API, and the rich text is supported during Excel I/O, JSON I/O and PDF exporting. You can create rich text and then configure the font for a range of text characters; or add, enumerate, operate and configure all text runs of the rich text.

Add rich text to spreadsheet cells

.NET Demo | .NET Documentation | Java Demo | Java Documentation

Load, save, and preserve Excel files containing macros (I/O .xslm)

Now GcExcel can load and save .xlsm files that contain macros. The macros would not be executed, but are preserved when any operation is performed on the Excel file or during import/export of the Excel file.

var workbook = new GrapeCity.Documents.Excel.Workbook();
workbook.Open(this.CurrentDirectory + "macros.xlsm");
workbook.Save(this.CurrentDirectory + "macros-exported.xlsm");

.NET Demo | .NET Documentation | Java Demo | Java Documentation

Add background image to worksheet

With the support of background images, you can set company logos, watermarks, or any other worksheet data-related background image to the worksheet. Simply set one line of code:

//Set a background image for worksheet
worksheet.BackgroundPicture = File.ReadAllBytes(@"logon.png");

Set background image on a worksheet

.NET Demo | .NET Documentation | Java Demo | Java Documentation

Support workbook styles

If you want to base the style of your workbook on an existing style, with some modifications, you can use the style, set additional settings to it, and save it as another style. The following overload helps you define a new style (string name), base it on existing style (IStyle baseOn - the style on which you want to base on), and then add it to the list of workbook styles:

workbook.Styles.Add(string name, IStyle baseOn)

.NET Demo | .NET Documentation | Java Demo | Java Documentation

Export Excel files containing vertical text to PDF

We already support Excel feature export to PDF, and with the new service pack, we improve the support of vertical text when exported to PDF. All settings on vertical text are preserved while exporting to PDF file.

Export Excel files containing vertical text to PDF

.NET Demo | .NET Documentation | Java Demo | Java Documentation

Load and save JSON files with shapes

GcExcel can load and save any SpreadJS JSON files, and these files can be modified and saved back to JSON. GcExcel adds support of shapes while importing/exporting these JSON files.

Load and save JSON files with shapes

.NET Demo | .NET Documentation | Java Demo | Java Documentation

Set JDK 8 datetime types to cell value in Documents for Excel, Java Edition

Java 8 introduced APIs that support many new date/time types and sub-packages to support JSR310 (Java Specification Requests for Date and Time API) in order to address some shortcomings of the older java.util.Date and java.util.Calendar. With the new service pack, GcExcel Java supports these new date/time types that you can use directly to set values in the Excel files. For example: LocalDate, LocalTime, LocalDateTime, Instant, MonthDay, OffsetDateTime, OffsetTime, Year, YearMonth ZonedDateTime, when working with Java 8 or higher.

Java Demo |
Java Documentation

For a list of bug fixes, visit the .NET Release Notes and Java Release Notes.

GCExcel 2.0

No major changes to GcExcel features, but the version has been edited to 2.0. Refer to the changes from previous version in the Release Notes.

GCExcel Service Pack 1.5.0.4

Support for Custom Functions

GcExcel now includes support for adding custom functions to the spreadsheets! You can now define your own functions for your spreadsheets, aside from what's already included in the built-in Excel functions. Read about custom functions in spreadsheets

GcExcel now uses System.Drawing.Color

Until now GcExcel had its own color type (GrapeCity.Documents.Excel.Color), so it wasn't possible to give a standard standard color to GcExcel, or vice versa. GcExcel now internally uses System.Drawing.Color, so you can now assign a standard color for various kinds of formatting on spreadsheets.

GCExcel Service Pack 1.5.0.3

The first service pack for GCExcel adds new improvements, APIs and utility methods with the new service pack.

Performance improvement

GcExcel is now faster than ever. We made several major performance enhancements over the previous release that you can start using today. GcExcel now performs better in following scenarios

  • Getting/setting value for single cell
  • Setting an array of double/int/float values to range, for example, Range.Value = new double[,]{ {1d, 2d}, {3d, 4d}}
  • Getting/setting style for single cell

Check out the performance blog for more information.

New APIs

CellInfo class

While working with range of cells, you may need to convert column/row index to expressions. GcExcel adds a new utility class to work with range of cells:

public class CellInfo  
{  
    public static string RowIndexToName(int index);  
    public static int RowNameToIndex(string name);  
    public static string ColumnIndexToName(int index);  
    public static int ColumnNameToIndex(string name);  
    public static string CellIndexToName(int row, int column);  
    public static void CellNameToIndex(string name, out int row, out int column);   
}

Other API additions

  1. You can now get or set standard width of columns and height of rows in the worksheet.
  2. You can reset adjacent range's border when setting border for a range.

Demos update

We have added new demos to demonstrate various range operations that can be performed with GcExcel. Have a look on the new Range Operations demos:

Download the new GcExcel package 1.5.0.3