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