What's New in Documents for Excel JAVA v5

GcExcel Report Templates Enhancements

With the new v5.1 release, we have made enhancements to GcExcel Report Templates to cover more scenarios and Excel features. Also, report generation from Templates will be much faster than before.

In addition, it is also now possible to bind Report Templates directly with a JSON data source file. GcExcel introduces JsonDataSource class which will help you directly bind JSON file as a data source for GcExcel Report Template. 

// Create a new workbook
Workbook workbook = new Workbook();
//Load template file Template_FamilyInfo.xlsx from resource
InputStream templateFile = this.getResourceStream("xlsx/Template_FamilyInfo.xlsx");
workbook.open(templateFile);

//Get data from json file
String jsonText = "";
try {
    InputStream stream = this.getResourceStream("Template_FamilyInfo.json");

    ByteArrayOutputStream result = new ByteArrayOutputStream();
    byte[] buffer = new byte[1024];
    int length;
    while ((length = stream.read(buffer)) != -1) {
        result.write(buffer, 0, length);
    }

    jsonText = result.toString("UTF-8");
} catch (IOException e) {
    e.printStackTrace();
}

// Create a JsonDataSource
JsonDataSource datasource = new JsonDataSource(jsonText);

//Add data source
workbook.addDataSource("ds", datasource);
//Invoke to process the template
workbook.processTemplate();
    
// Save to an excel file
workbook.save("JsonFile.xlsx");

Help | Demo 

Import Data Function to Import Table, Range, or Worksheet from Excel Files

In last release, we added a new function ImportData to import specific range data from an Excel file, without loading the whole Excel file. This function works twice faster than importing the whole Excel file just for specific range of data. Have a look on the performance numbers here.

Consider making it easy for users to add a Table Name reference as an imported range, instead of a range dimension. It may be possible that users may only know the workbook path to be imported, but not the specific worksheet name or table name. In v5.1 release, we make it possible to use Workbook.ImportData(string fileName, string sourceName) function to import data from a specified source. The source can be sheet name, table name, or range name and these can be obtained from a new interface Workbook.GetNames(string fileName) that returns an array of names which includes the names of all worksheets, tables, and defined names that evaluate to a range reference.

Following example imports table data from an Excel file, by using the source name from GetNames method. 

// Create a new workbook
Workbook workbook = new Workbook();
// Open an excel file.
InputStream fileStream = this.getResourceStream("xlsx\\AgingReport.xlsx");

// Get the possible import names in the file.
// The names[0] and names[1] are sheet names: "Aging Report", "Invoices".
// The names[2] and names[3] are table names: "'Aging Report'!tblAging", "Invoices!tblInvoices".
String[] names = Workbook.getNames(fileStream);

// The InputStream of the Java platform cannot be read repeatedly, so you need to create another one.
InputStream fileStream2 = this.getResourceStream("xlsx\\AgingReport.xlsx");

// Import the data of a table "'Aging Report'!tblAging" from the fileStream.
Object[][] data = Workbook.importData(fileStream2, names[2]);

// Assign the data to current workbook.
workbook.getWorksheets().get(0).getRange(0, 0, data.length, data[0].length).setValue(data);
    
// Save to an excel file
workbook.save("ImportDataForTable.xlsx");

It will be faster to use ImportData function in situations like following:

• If a source file has a large number of formulas, ImportData(Sheet) only reads data.
• If source file has multiple sheets, but the user only wants the data of one of the sheets, ImportData(Sheet) will only read the data of this sheet.
• If source file has a lot of data, ImportData(Range) only reads part of it.
• and many more..

Read more about ImportData function in following resources.

Help | Demo 

New CELL function support

Sometimes you may want to perform certain operations in your Excel file by finding the Cell information of a particular cell. The information you want could be of any nature like cell address, color, contents, filename, formats etc.  For example, you may want know cell address corresponding to the value returned by a function. GcExcel introduces CELL function with syntax "=CELL(info_type, [reference])" which can help you find relevant cell information by providing the info type you want and the reference type.

Help | Demo

Convert Table to Range

Having data as a Table in Excel allows you to enjoy advanced features when working with data. However, sometimes those features may interfere when manipulating your data the way you want. Tables can also be slower to work with because of advanced functionality. Many Excel files store data only for archival purposes or may only need style and data, without the Table functionality.  There are also cases where you need to apply Dynamic Array Formulas in a Table column, but since Dynamic Array Formulas are not supported in a Table, you will have to convert the Table to a normal range. This calls for need to convert a table to a range in Excel files.

Imagine you have 1000s of such files where you want to work with the Table data but cannot do so until it is converted to a normal range. It will be difficult to manually perform this task of converting Table to range. Catering to this need, GcExcel provides new API ITable.ConvertToRange() to convert Table to range programmatically. Once you convert Table to a range, the table features are no longer available in the range of data however style and data would be retained. The table reference in formulas on the worksheet will be converted to cell reference. See the cell references in following example:

Workbook workbook = new Workbook();
workbook.Open("Orders.xlsx");
IWorksheet worksheet = workbook.getWorksheets().get(0);

// Add table.
ITable table = worksheet.Tables.Add(worksheet.getRange("A9:D14"), true);
 
// Convert table to range.
table.convertToRange();
 
// Save to an excel file
workbook.Save("converttorange.xlsx");

Help | Demo 

Modify Password of Excel Documents

When an Excel file is shared with users, they can easily edit the spreadsheet. Sometimes, if you have some data that you do not want to be tampered, Excel provides an option to protect it and make it read-only. GcExcel adds IWorkbook.WriteProtection class to set protect workbook options when saving a workbook. With this new feature, you can:

• Save workbook as WriteProtection.ReadOnlyRecommended - If true, MS Excel will recommend to open the file as read-only.
• Add Protect Options when saving a workbook
• Check whether document has a Modify Password set
• Set a Modify Password
• Set the name of the user who has write permission for the workbook
• Check if password is same as one set in Modify Password.

Help | Demo 

CSV Custom Parser

CSV files are a very common file format that different applications and systems use to share data. Applications store CSV files as simple text characters; a comma separates each data element, such as a name, phone number or dollar amount, from its neighbouring cells. Because of CSV’s simple format, and sharing data among different applications, certain data needs to be parsed and converted to different formats as per requirements of different applications or cultures.

GcExcel introduces ICsvParser for users to parse data as per their own rules.  The ICsvParser.Parse(CsvParseResult csvParseResult, CsvParseContext context) method can pass the desired format which user wants to customize in CsvParseResult object, while the CsvParseContext object contains the location and value information of the cell. 

Following example converts Date Format mm/dd/yyy to yyyy/mm/dd:

public class CustomParser implements ICsvParser  {
    @Override
    public void Parse(CsvParseResult csvParseResult, CsvParseContext csvParseContext) {
        if (csvParseContext.getText().startsWith("00")) {
            csvParseResult.setValue(csvParseContext.getText());
        }
        else if(csvParseContext.getColumn() == 5 || csvParseContext.getColumn() == 6) {
            csvParseResult.setNumberFormat("#.00");
        }
        else if(csvParseResult.getNumberFormat().equals("m/d/yyyy h:mm")){
            csvParseResult.setNumberFormat("m/d/yyyy");
        }
    }
}

Help | Demo 

Specify 'ShowValuesAs' Option for 'Values' Field in Pivot Table

GcExcel now supports 'Show Values As' option of MS Excel, which helps in displaying values in different ways in a Pivot Table. GcExcel introduces:

• IPivotField.Calculation to get or set a value that represents the type of calculation performed by the specified field.
• Use IPivotField.BaseField to get or set the base field for a custom calculation. This property is valid only for data fields.
• Use IPivotField.BaseItem to get or set the item in the base field for a custom calculation. This property is valid only for data fields.

In following example, '% of Grand Total' option has been set on the data of Pivot Table:

Help | Demo 

Support for Calculated Field in Pivot Table

If you want to add more power to your Pivot Table calculations, when you are not able to achieve calculations using simple summary functions, a Calculated Field can help you perform calculations on one or more other fields in your data source. GcExcel adds ICalculatedFields interface which you can use to create or remove calculated fields in a Pivot table. Additionally, IPivotTable.CalculatedFields() method can get CalculatedFields collection with all the Calculated Fields in the specified PivotTable report while the new IPivotField.Formula can get or set the Calculated Field formula.

Check out more functions of new ICalculatedFields API.

Help | Demo 

Support for "Show #N/A as an empty cell" in Chart

At times, you have some data where cells have #N/A value. So that you can choose whether this data is plotted as an empty cell on chart or not plotted, GcExcel introduces the new IChart.DisplayNaAsBlank property which can be set true of false. Check out the difference between the two in the following example:

Help | Demo

More features for SpreadJS Integration

Support for Pivot Table Views (JSON I/O)

GcExcel supports JSON I/O of Pivot Table Views which is a feature of SpreadJS control. SpreadJS stores the current view of the Pivot Table as a Pivot View which the pivot table uses to return the state of the pivot table's view records. The snapshot you see below is of SpreadJS which changes the Pivot View. Such a SpreadJS JSON file can now be supported during I/O with GcExcel:

// Create a new workbook
Workbook workbook = new Workbook();
InputStream fileStream = this.getResourceStream("json/PivotTableViews.json");
workbook.fromJson(fileStream);
    
// Create to a ssjson file stream
FileOutputStream outputStream = null;
try {
    outputStream = new FileOutputStream("PivotTableViews.ssjson");
} catch (FileNotFoundException e) {
    e.printStackTrace();
}

workbook.toJson(outputStream);

// Close the ssjson stream
try {
    outputStream.close();
} catch (IOException e) {
    e.printStackTrace();
}

Help | Demo 

Support for TableSheet (JSON I/O)

GcExcel supports JSON I/O of TableSheet which is a feature of SpreadJS. TableSheet is a fast, data-bound table view with grid-like behavior and a spreadsheet user interface (for more details, see this excellent blog post). Snapshot below is a TableSheet in SpreadJS: 

Code below shows simple I/O of ssjson file above.

// Create a new workbook
Workbook workbook = new Workbook();
InputStream fileStream = this.getResourceStream("json/TableSheets.json");
workbook.fromJson(fileStream);
    
// Create to a ssjson file stream
FileOutputStream outputStream = null;
try {
    outputStream = new FileOutputStream("TableSheets.ssjson");
} catch (FileNotFoundException e) {
    e.printStackTrace();
}

workbook.toJson(outputStream);

// Close the ssjson stream
try {
    outputStream.close();
} catch (IOException e) {
    e.printStackTrace();
}

Help | Demo 

Numbers Fit Mode

You may have often experienced that when a column width is not enough to fit data, it shows '###' in the cell. This can be annoying at times when you have to manually adjust several columns in the Excel file, to view the data completely. GcExcel now supports  IWorkbookView.NumbersFitMode enum with Mask and Overflow options. The Mask option would replace data with '###', while Overflow option will overflow data if neighboring cell is empty. 

This option works only with SpreadJS JSON I/O or when exporting to PDF, HTML or Image. Following snapshot shows the NumbersFitMode property applied on Column B with Overflow enum, using GcExcel API. The snapshot shows the property as being reflected in SpreadJS: 

Help | Demo 

Import Data Function

You don't typically need to extract all of the data from an Excel file—just specific data. However, you may not want to load the whole Excel file in your Excel API, which makes the process more tedious and time-consuming. GcExcel adds the new ImportData() function that imports data alone from Excel files, without loading the whole object model.

This function makes it faster and efficient to import data from several Excel files simultaneously. You can either import all the data or specify the range of data to import from the worksheet.

// Create a new workbook
Workbook workbook = new Workbook();

InputStream fileStream = this.getResourceStream("xlsx\\AgingReport.xlsx");

// Import data of a range from the fileStream
Object[][] data = Workbook.importData(fileStream, "Aging Report", 8, 2, 21, 7);

// Assign the data to current workbook
workbook.getWorksheets().get(0).getRange(0, 0, 21, 7).setValue(data);

// Save to an excel file
workbook.save("ImportData.xlsx")

Help | Demo

New Formula2 Property to Set Dynamic Array Formula

Microsoft Excel launched the concept of dynamic array formulas in 2018 to return multiple results to a range of cells based on one formula, also called spilled-range functionality.

These formulas are used to create a list of unique values by removing duplicates, sorting lists, outputting a filtered range of data, and more. Existing functions can utilize this same spill-range functionality.

GcExcel adds extensive support for adding dynamic array formulas to Excel files through code. The new IRange.Formula2 property allows you to define a dynamic array formula in a worksheet. The property also allows you to specify a formula without automatically adding the intersection operator.

The following code sets a dynamic array formula using the IRange.Formula2 property. It also uses the filter function to filter data from a range and then "spills" the result into a range of cells.

Help | Demo

Threaded Comments

For better discussion and responses, Excel threaded comments allow you to reply to inline or nested comments, as well as string several comments, together to form a conversation style thread. GcExcel has supported adding comments to Excel documents, but, by adding comment threading to GcExcel, we now have complete support for threaded comments.

GcExcel adds the IWorksheet.CommentsThreaded collection to work with threaded comments in Excel documents—specifically adding replies, deleting comments, navigating comments, and more. The new IRange.AddCommentThreaded(..) method will help add threaded comments to the range and IRange.ClearCommentsThreaded() will help clear threaded comments from the range.

Suppose two or more users have a common comment for several Excel files (as shown in the example below). The new API will help add comments programmatically to cover several Excel documents.

Help | Demo

Linked Pictures

Combining charts, data tables, conditional formatting, etc. all in one sheet can be complex. Since the size of these elements is often not uniform, presenting them in a single dashboard sheet can be difficult to accommodate. This is where a linked picture can be beneficial. A linked picture can be created to the actual data/table/chart in your final dashboard.

The advantage is not only to accommodate size but also that when your data changes, the data in your linked picture also changes. This feature is also sometimes referred to as Camera Picture. GcExcel now supports adding linked pictures through various overloads to the new method—IShapes.AddCameraPicture(..). The following new properties have also been added:

• IPictureFormat.TransparentBackground { get; set; }: Set whether the specified picture format uses a transparent background
• IPictureFormat.Reference { get; set; }: Set the reference of current picture

The following code adds a camera picture of a range of data:

// Create a new workbook
Workbook workbook = new Workbook();
IWorksheet ws = workbook.getWorksheets().get(0);

Object[][] data = new Object[][]
{
    {"Name", "City", "Birthday", "Eye color", "Weight"},
    {"Richard", "New York", new GregorianCalendar(1968, 6, 8), "Blue", 67},
    {"Nia", "New York", new GregorianCalendar(1972, 7, 3), "Brown", 62},
    {"Jared", "New York", new GregorianCalendar(1964, 3, 2), "Hazel", 72}
};

ws.getRange("A1:E4").setValue(data);
ws.getRange("A:E").getEntireColumn().setColumnWidth(ws.getRange("A:E").getEntireColumn().getColumnWidth() * 1.5);

ws.getRange("A1:E1").getInterior().setColor(Color.FromArgb(68, 114, 196));
ws.getRange("A1:E1").getFont().setColor(Color.GetWhite());
ws.getRange("A1:E4").getBorders().setColor(Color.FromArgb(91, 155, 213));
ws.getRange("A1:E4").getBorders().setLineStyle(BorderLineStyle.Thin);

//add camera picture
ws.getShapes().addCameraPicture("$A$1:$E$4", 398, 0, 347, 58);
    
// Save to an excel file
workbook.save("AddCameraPicture.xlsx");

Note how data gets changed in the Linked Picture when changed at the source.

Help | Demo

Workbook Views

Users normally work in the standard Microsoft Excel view, However, worksheet views can be changed depending on what you want to analyze. For example, the Page Layout view helps you visualize how a document will look when printed, while Page Break view shows you where the page breaks will appear.

If several of your documents need to have a common view when they are opened, GcExcel now lets control that programatically. The IWorksheetView.ViewType helps define the three predefined views: Normal, Page Layout, and Page Break Preview.

In Excel, users can also customize the views. With a custom view, you can establish certain display and print settings, such as no gridlines or headings, specific margins, and a specific Workbook view.

The view can then be saved to easily apply it to the spreadsheet at any time. We are also excited to introduce a new ICustomView interface and collection IWorkbook.CustomViews in GcExcel to add custom views to the worksheet.

The following code sets the default view type of the worksheet to PageBreakPreview:

// Create a new workbook
Workbook workbook = new Workbook();

IWorksheet worksheet = workbook.getWorksheets().get(0);

worksheet.getRange("J12").setValue(1);

// Set the view mode of the worksheet to PageBreakPreview.
worksheet.getSheetView().setViewType(ViewType.PageBreakPreview);
// Modify the zoom of the PageBreakPreview to 80%.
worksheet.getSheetView().setZoom(80);
    
// Save to an excel file
workbook.save("ConfigPageBreakPreview.xlsx");

Help | Page Break Preview Demo | Custom Views Demo 

Support for the GETPIVOTDATA Function

Pivot tables showcase large amounts of data, but users may run into instances where they only need to retrieve specific data based on the pivot table structure, rather than just cell reference.

The GETPIVOTDATA function works even when a pivot table changes, as long as the referenced field(s) is still present. GcExcel now supports the GETPIVOTDATA(...) function and the new IRange.GenerateGetPivotDataFunction(IRange destination = null) method to generate the GETPIVOTDATA function for different worksheets.

Help | Demo

GrapeCity SpreadJS Integration

With every release, GcExcel adds even more support for SpreadJS features—improving its compatibility with the client-side spreadsheet product.

Please note that these features work only with SpreadJS and PDF Export and are not supported in Microsoft Excel.

Help | Demo

Support for the Table expandBoundRows API

GcExcel now supports the SpreadJS API for expanding bound rows in a table. The new ITable.ExpandBoundRows property sets the policy when the data bound to the table changes.

If set as true, the entire rows will be added or deleted to adjust the row count when data is altered. If set as false, the Microsoft Excel policy will be used to expand the tables so the addition or deletion of rows will not be allowed to adjust data source changes.

Help | Demo