What's New in Documents for Excel JAVA v5

GcExcel for Java v5.2 - August 17, 2022

New API to add Excel Form Controls 

One way in which you can collect and present data in a spreadsheet is through the use of Form Controls added to a worksheet. Controls such as list boxes, check boxes, and buttons help structure a worksheet and manage its data. GcExcel now supports API to add Excel Form controls programmatically. You can create Excel forms with standard Form controls, customise them and additionally collect data from your users or programmatically retrieve Form control values. Also, if several of your Excel spreadsheets include Form controls, the spreadsheet can load into GcExcel object model and the Form controls can be modified.

GcExcel introduces IControlCollection which adds all generic members of Form Controls of type IControl. You can use Worksheet.Controls to add or access the controls. Following controls are supported:

  • Button
  • DropDown
  • CheckBox
  • Spinner
  • ListBox
  • OptionButton
  • GroupBox
  • Label
  • ScrollBar

With this support, you can also use following features -

  • Each control has its own set of properties. You can set those using specific API of each control. For example, following code shows how to set properties specific to the button control -
    var workbook = new GrapeCity.Documents.Excel.Workbook();       
    IWorksheet ws = workbook.Worksheets["Sheet1"];
             
    var button = ws.Controls.AddButton(50, 30, 120, 40);
    button.Text = "Submit";
    button.PrintObject = true;
    button.HorizontalTextAlignment = HorizontalAlignment.Center;
    button.VerticalTextAlignment = VerticalAlignment.Center;
                     
    // Save to an excel file
    workbook.Save("ButtonsBasicUsage.xlsx");​
     
  • Modify Form controls and Form control values - Access the controls using Worksheet.Controls collection. LinkedCell property of CheckBox, OptionButton, ListBox, DropDown, Scrollbar Form Controls helps in binding the value of the controls to the linked cell and vice versa. You can get value of the controls using the LinkedCell property.

  • Use Form controls as Shapes - A new option FormControl has been added to ShapeType enum which can check if Form control is of Shape type or not. Also use <IControl>.ShapeRange to use some of the Shape features on Form controls like duplicate a control shape or copy shapes on cells. 
  • Use Form controls with Excel I/O, JSON I/O.
  • Export to PDF, HTML and Images (exported as Images).

View following resources for more info.

HelpDemo

Enhancements to GcExcel Templates

Paginated Templates 

If you have a certain template, where you want fixed number of rows on a page and need the same layout to repeat on next worksheet, you can choose to paginate your workbook into separate worksheets. GcExcel Templates introduces 'TemplateOptions.PaginationMode', a global boolean property which if true, can paginate your workbook into separate worksheets, while if false will follow traditional rules of Template layout and pagination. When true, you can define the new CountPerPage property (CP) for the cell whose data needs to be repeated. The property will limit the number of instances (records) of a template cell generated on a page. When the number of records exceeds the value of CP, a new worksheet is automatically created (or paginated) with the same layout as the template. “CountPerPage” property refers to count of grouped records on a worksheet. 

Following snapshot shows CP value as 10 set for a Template cell. FM=O indicates that when the template cell expands, the new instances will override the cells below.

There are certain conditions in which CountPerPage (CP) property will work. For example, when Group (G) property is set to Normal/Merge/Repeat, the records are grouped and count refers to the number of groups created. And if Group (G) property is set to List, then the records are not grouped and the Count refers to actual records count. Know more details in resources below.

Help  | Demo

Debug mode for template language 

Now it will no longer be necessary to open the original Template file to check whether the template has expanded correctly or not. GcExcel supports a defined name TemplateOptions.DebugMode in TemplateOptions. When DebugMode is true, original template sheet would appear in Sheet 1, next to expanded Template Sheet 2.

 

 

Help | Demo

Support for Chart Data Table 

A data table in a chart is useful in order to view/analyze data in the chart conveniently. In Excel, data tables are displayed beneath an Excel chart. This is specially useful so that both the data and chart can be analyzed together, rather than scrolling at different location within the sheet or other sheets to match chart data with the source data. Due to such advantages, GcExcel introduces two new properties - IChart.HasDataTable that sets true or false, whether to add Data Table to Chart or not, IChart.DataTable that represents the data table of the chart which can provide various options to set in Data Table like Font, Formatting, Showing Legend Key etc.

Data Table in snapshot following the code is added using the new API. The formatting of Data Table can be customized. Have a look on the code below: 

IWorksheet worksheet = workbook.Worksheets[0];
 
// Set data.
worksheet.Range["A1:C3"].Value = new object[,]
{
    {"BUDGET TOTALS", "ESTIMATED", "ACTUAL"},
    {"Income", 63300, 57450},
    {"Expenses", 54500, 49630}
};
 
//Create chart.
GrapeCity.Documents.Excel.Drawing.IShape shape = worksheet.Shapes.AddChart(GrapeCity.Documents.Excel.Drawing.ChartType.ColumnClustered, 250, 0, 350, 250);
shape.Chart.SeriesCollection.Add(worksheet.Range["A1:C3"]);
shape.Chart.ChartTitle.Text = "Estimated vs Actual";
 
//Display the data table.
shape.Chart.HasDataTable = true;
 
//Config the data table.
GrapeCity.Documents.Excel.Drawing.IDataTable datatable = shape.Chart.DataTable;
datatable.Format.Line.Color.ObjectThemeColor = ThemeColor.Accent6;
datatable.Font.Color.ObjectThemeColor = ThemeColor.Accent2;
datatable.Font.Size = 9;
         
// Save to an excel file
workbook.Save("chartdatatable.xlsx");

 Help | Demo

Add Calculated Item in Pivot Table

Calculated item helps to add custom formulas in Pivot Table performing custom calculations on other items that do not exist in the source data. GcExcel introduces following API -

  • IPivotField.CalculatedItems() to get a CalculatedItems collection that represents all the calculated items in the specified PivotTable field.
  • ICalculatedItems.Add(string name, string formula) to create a new calculated item.
  • ICalculatedItems.Remove(string name) to remove the calculated fields by name.
  • IPivotItem.Formula to get or set calculate item formula.

If the value in a PivotTable cell is affected by two or more calculated items, the value is determined by the last formula in the solve order. With GcExcel you can use following API to set the solve order -

  • IPivotFormula is a object that represents the content and solve order of the calculated items.
  • Use IPivotTable.IPivotFormulas() to get a IPivotFormula collection that represents all the IPivotFormula in the specified PivotTable field.
  • Use IPivotFormula.Index{get;set;} to get or set the index of current PivotFormula.

The spreadsheet below sets Country and Product as Calculated Items. 'Country' calculated item adds the Sales value of countries that come under the same continent, while Product Calculated Item sets all products with value $2500 as iPhone13 and later sets Visible to False for such items. 

ICalculatedItems countryCalcItems = calculatedItemTable.PivotFields["Country"].CalculatedItems();
ICalculatedItems productCalcItems = calculatedItemTable.PivotFields["Product"].CalculatedItems();
 
// add some calculated items
countryCalcItems.Add("Oceania", "=Australia+NewZealand");
countryCalcItems.Add("Europe", "=France+Germany");
countryCalcItems.Add("America", "=Canada");
 
productCalcItems.Add("IPhone 13", "=2500");
 
// hide the duplicate normal item.
IPivotItems countrys = calculatedItemTable.PivotFields["Country"].PivotItems;
countrys["United Kingdom"].Visible = false;
countrys["United States"].Visible = false;
 
IPivotItems products = calculatedItemTable.PivotFields["Product"].PivotItems;
products["IPhone 13"].Visible = false;
calculatedItemSheet.Range["A:I"].AutoFit();
 
calculatedItemSheet.Activate();
         
// Save to an excel file
workbook.Save("Calculateditem.xlsx");

Help | Demo

Support JSON as DataSource in data binding

You can now bind Excel spreadsheets with JSON string as a data source. The worksheet.DataSource can now be directly bound to JsonDataSource class which accepts a Json string as data source. Following example code imports data from Json file and binds it to worksheet. 

string jsonStr = "[{\"name\":\"jack\",\"age\":12},{\"name\":\"alice\",\"age\":25}]";
Workbook workbook = new Workbook();
IWorksheet worksheet = workbook.Worksheets[0];
worksheet.DataSource = new JsonDataSource(jsonStr);
worksheet.Range["A1"].BindingPath = "name";
workbook.Save("DataSource.xlsx");

Help | Demo

IsVolatile property support in Custom function 

When using custom functions, GcExcel stores cache of the formulas in the same column which have same name and parameters. This makes the same formula to be be evaluated only once in the same column. GcExcel supports IsVolatile boolean property while defining the custom function. The property will control whether to use cache when using custom function or not. 

Following example demonstrates how to create a custom function for generating GUID. To generate a unique GUID every time, custom function should not be using cache. Hence, example code sets the IsVolatile property to true, so that a new GUID is generated on every call.

//create a new workbook
var workbook = new GrapeCity.Documents.Excel.Workbook();
 
GrapeCity.Documents.Excel.Workbook.AddCustomFunction(new GeneralID());
 
IWorksheet worksheet = workbook.Worksheets[0];
worksheet.Range["A1"].Formula = "GeneralID()";
var valueA1Before = worksheet.Range["A1"].Value;
worksheet.Range["A2"].Formula = "GeneralID()";
// A1's value has changed.
var valueA1After = worksheet.Range["A1"].Value;
 
/* Implementation of GeneralID
 
public class GeneralID : CustomFunction
{
    public GeneralID() : base("GeneralID", FunctionValueType.Object)
    {
        this.IsVolatile = true;
    }
 
    public override object Evaluate(object[] arguments, ICalcContext context)
    {
        return Guid.NewGuid().ToString("N");
    }
}
 
*/

HelpDemo

SVG Image support in Spreadsheets

GcExcel adds IShapes.AddPicureInPixel(..) method to add images in SVG format. In addition, the ToImage(..) method supports exporting to SVG picture for following -

  • Use IRange.ToImage(string filename) method to export range to SVG picture.

  • Use ISheet.ToImage(string filename) method to export sheet to SVG picture.

  • Use IShape.ToImage(string filename) method to export shape to SVG picture.

GcExcel will also support spreadsheets with SVG images in following situations -

  • Lossless import and export of excel files 

  • JSON I/O

  • Export to PDF/HTML/Image file.

Note: In order to use SVG image with GcExcel API in Java, extension package batik (1.14) and gcexcel-extension-5.2.0.jar should be included in the project. 

View following resources for more info.

Help | Demo

New Excel functions

LET Function

In your spreadsheets, you may be using several formulas which may be complex. The LET function applies a user-friendly name to a range/cell or calculation and helps to improve performance of formula calculations. A lengthy formula that makes the same calculation over and over can be calculated only once and the result can be stored with an easy to understand name. This name can then be used further within the LET function, thereby making calculation only once, while re-using the result later. You no longer need to remember what the underlying logic of the reference is. GcExcel adds this LET function to it's list of supported Functions. If you have several sheets repeating calculations over and over, you can update those formulas to use the LET function. An example of such an improvement to the spreadsheet is given below. 

In the above example, LET function reduces the number of times VLOOKUP is calculated from 4 times to 2 times by using the LET function and defining the output of VLOOKUP to 'name' and 'points' variables and re-using them in the formula.

Following code shows how to set the function in code -

ws.Range["$F$9"].Formula2 = "=LET(name,VLOOKUP($G$5,$B$5:$D$16,2,0),points,VLOOKUP($G$5,$B$5:$D$16,3,0),"Ho, "&name&", you have "&points&" points. "IF(points>300, "Great job, "&name&"!",""))

View following resources for more info.

Help | Demo

GetPivotData Formula Supports Spilled data

In last release, GcExcel introduced GETPIVOTDATA(...) function and the new IRange.GenerateGetPivotDataFunction(IRange destination = null) method to generate the GETPIVOTDATA function for different worksheets.

In v5.2, we extend the capability of this function, to support spilled data similar to Dynamic Array Formulas.The syntax now supports both array items as well as reference - "=GETPIVOTDATA(data_field, pivot_table, [field1, item1, field2, item2], ...)". So in example below, GETPIVOTDATA function set in one of the cells using GcExcel, takes in two cities together in an Array {'New York', 'Boston'}, to find data in one go from Pivot Table as compared to the previous implementation of the function. 

Help | Demo

Debug better with additional details in InvalidFormulaException

In order to provide more details to customer to solve the errors in the spreadsheet, GcExcel adds more info to the InvalidFormulaException. The exception would now include cell's location and formula text to trace the error. So if you ever encounter this exception, you will have more info to solve the problem in your spreadsheet.

Features for SpreadJS Integration

Support for CASCADESPARKLINE formula

You can now add CASCADESPARKLINE formula to your spreadsheets programmatically using GcExcel. Cascade Sparkline is a feature of SpreadJS. In GcExcel, Cascade Formula is now supported on SSJSON I/O. You can also add the formula to the spreadsheet and export the spreadsheet to PDF/HTML/Image. A cascade sparkline is generally used to analyze a value over time like yearly sales, total profit, net tax etc. It is used widely in finance, sales, legal and construction sectors, to name a few. 

The syntax of the Formula is - '=CASCADESPARKLINE(pointsRange, pointIndex, labelsRange, minimum, maximum, colorPositive, colorNegative, vertical, itemTypeRange, colorTotal)'

The code below adds a Cascade Sparkline Formula with Horizontal Bars by setting the 'vertical' property to False.

// Add a cascade sparkline with Horizontal Bars.
for (int i = 1; i < 8; i++)
{
    worksheet.Range[i, 2].Formula = "'=CASCADESPARKLINE(D5:D20, SEQUENCE(COUNTA(D5:D20),1,1,1),P5:P20,,,"#8CBF64","#D6604D",false,A5:A20)')";
}

Help | Demo

 

Read the release blog for full details.


 

GcExcel for Java v5.1 - April 22, 2022

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");
JsonDataSource bound to Report Templates using GrapeCity Documents for Excel Java v5.1

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");
Import Data Function to Import Table, Range, or Worksheet from Excel Files using GrapeCity Documents for Excel Java v5.1

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

  • If a source file has a large number of formulasImportData(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 dataImportData(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.Cell Function using GrapeCity Documents for Excel Java v5.1

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.

Modify Password of Excel Documents using GrapeCity Documents for Excel Java v5.1

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:

CSV Custom Parser using GrapeCity Documents for Excel Java v5.1

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 TableGcExcel 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:

Specify 'ShowValuesAs' Option for 'Values' Field in Pivot Table using GrapeCity Documents for Excel Java v5.1

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.

Calculated Field in Pivot Table using GrapeCity Documents for Excel Java v5.1

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:

Pivot Table Views in SpreadJS JSON I/O using GrapeCity Documents for Excel Java v5.1

// 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 SpreadJSTableSheet 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

TableSheet from SpreadJS in JSON I/O using GrapeCity Documents for Excel Java v5.1

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 PDFHTML 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

Numbers Fit Mode using GrapeCity Documents for Excel Java v5.1

Help | Demo 


GcExcel for Java v5.0 - December 14, 2021

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

Import Data Only from Excel Spreadsheets using GrapeCity Documents for Excel Java v5.0

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.

Formula2 Property to Set Dynamic Array Formula using GrapeCity Documents for Excel Java v5.0

HelpDemo

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.

Threaded Comments using GrapeCity Documents for Excel Java v5.0

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");

Linked Pictures using GrapeCity Documents for Excel Java v5.0

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");

Workbook Views using GrapeCity Documents for Excel Java v5.0

HelpPage Break Preview DemoCustom 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

Select Product Version...