What's New in GrapeCity Documents for Excel v2 Service Pack 2

With GrapeCity Documents v2, we are pleased to release Documents for Excel and Documents for Excel, Java Edition service pack 2! The main highlight of this service pack is the ability to control pagination and other features while exporting spreadsheets to PDF. This is useful when working with large amounts of data in single or multiple workbooks.

Read the full GcDocs release

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

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.

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

Print Specific Pages of Excel Spreadsheet to a PDF

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.

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

Print Multiple Worksheets to One Page in PDF

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:

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.

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.

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.

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.

If you have any questions about the new features, please leave them in the comments below.