Tips and Tricks to use the GcExcel Spreadsheet API in C# .NET and Java

Most data on the web is arranged in rows and columns, and Excel spreadsheets are the most common, familiar way for storing, analyzing, calculating, sorting and organizing data. If you're looking for a high-speed solution to handle budgets, transactions, financial reports, and profitability analyses, you likely need more power than a basic web app can provide. GrapeCity Documents for Excel or GcExcel (.NET & Java), a high-speed, small-footprint spreadsheet API, supports most Excel features and allows you to deploy your solution on Windows, Mac, Linux, and cloud.

In this article, you will see a few tips and tricks to use GcExcel for faster and more efficient execution when programmatically working with Excel spreadsheets.

Import Just Data From a Range

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

This function makes it faster and more 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")

The ImportData(..) method is four times faster than the Open method when importing just the data from the workbook.

Use the ImportData Method Without Knowing the Worksheet or Table Name

Sometimes, users only know the workbook path to be imported but not the specific worksheet or table name. In such situations, you can use Workbook.ImportData(string fileName, string sourceName) function imports data from a specified source.

The source can be a sheet name, table name, or range name, which can be obtained from the interface Workbook.GetNames(string fileName), which returns an array of names, including the names of all worksheets, tables, and defined names that evaluate to a range reference.

The following example imports table data from an Excel file using the source name from the GetNames method. 

//create a new workbook
var workbook = new GrapeCity.Documents.Excel.Workbook();

// Open an excel file.
var fileStream = 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".
var names = GrapeCity.Documents.Excel.Workbook.GetNames(fileStream);

// Import the data of a table "'Aging Report'!tblAging" from the fileStream.
var data = GrapeCity.Documents.Excel.Workbook.ImportData(fileStream, names[2]);

// Assign the data to current workbook.
workbook.Worksheets[0].Range[0, 0, data.GetLength(0), data.GetLength(1)].Value = data;

// Save to an excel file
workbook.Save("importdatafortable.xlsx");

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

Import Data - An Alternate Way

Suppose you know what specific data to import, like the style, data, shapes, etc. you can use XLSXOpenOptions.ImportFlags enum together with the Workbook.Open(..) method.

var workbook = new GrapeCity.Documents.Excel.Workbook();

//Use XlsxOpenOptions.ImportFlags to control what you want to import from excel, ImportFlags.Data means only the data will be imported
//Change the path to the real file path when open.
XlsxOpenOptions options = new XlsxOpenOptions
{
    ImportFlags = ImportFlags.Data
};

workbook.Open("source.xlsx", options);

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

Get Used Ranges

You may want to find out which cells have been used in your Excel sheet. For example, you may want to insert data in an Excel file at cell locations that do not come in used ranges. In such cases, it is useful to have an API that can return the used range in a worksheet. With GcExcel, you can use the worksheet.GetUsedRange(..) method, and as per the UsedRangeType enum, you can get specific data like Data, DataValidation, Hyperlink, Sparkline, etc. 

worksheet.Range["A2:C3"].Interior.Color = Color.Green;

//style used range is A2:C3.
var UsedRange_style = worksheet.GetUsedRange(UsedRangeType.Style);
UsedRange_style.Interior.Color = Color.LightBlue;

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

Get Range Address to Retrieve Cell’s Address

Usually, you would have to write some code to get a cell's address. For example:

worksheet.Cells[2, 3].ToString().Substring(7, 4);

However, GcExcel has a direct method to access a cell's address. Use the IRange.Address property to get a string value representing the range reference in the absolute A1 format. Also available is the option of providing parameters through the IRange.GetAddress method  that chooses an absolute or relative reference to a row or column or denotes the reference style as A1 or R1C1. Look at the following code that retrieves cell address relative to a row/col 2,2.

//create a new workbook 
var workbook = new GrapeCity.Documents.Excel.Workbook();   

var mc = workbook.Worksheets['Sheet1'].Cells[0, 0];
Console.WriteLine(mc.Address); // $A$1
Console.WriteLine(mc.GetAddress(rowAbsolute: false)); // $A1
Console.WriteLine(mc.GetAddress(referenceStyle: ReferenceStyle.R1C1)); // R1C1
Console.WriteLine(mc.GetAddress(referenceStyle: ReferenceStyle.R1C1, 
    rowAbsolute: false,
    columnAbsolute: false, 
    relativeTo: workbook.Worksheets[0].Cells[2, 2])); // R[-2]C[-2]

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

Improve Calculation Engine Performance When Setting Values

GcExcel provides Workbook.DeferUpdateDirtyState boolean property. When changing the value of a cell and setting Workbook.DeferUpdateDirtyState =True, GcExcel will not immediately update the formula cell dirty state. This improves the Calculation Engine's performance when setting values in an Excel file.

Visit Help .NET | Help Java

Get Display Format

You can get the displaying style of a cell by using IRange.DisplayFormat property. The display style of a cell may come from its style or table style if the cell is in a table, from conditional format if the cell matches a criterion, and so on. Use the properties of the DisplayFormat object to return the values as they are displayed in the current user interface. The following code sets DisplayFormat.Interior.Color to A1 cell with conditional formatting condition that cells should not be blank.

//create a new workbook
var workbook = new GrapeCity.Documents.Excel.Workbook();

IWorksheet worksheet = workbook.Worksheets[0];

//Range A1's displaying color is table style
worksheet.Tables.Add(worksheet.Range["A1:E5"], true);
var color_A1 = worksheet.Range["A1"].DisplayFormat.Interior.Color;

//Range A1's displaying color will be cell style, yellow.
worksheet.Range["A1"].Interior.Color = Color.Yellow;
var color_A1_1 = worksheet.Range["A1"].DisplayFormat.Interior.Color;

//Range A1's displaying color will be conditional format style, green.
IFormatCondition condition = worksheet.Range["A1"].FormatConditions.Add(FormatConditionType.NoBlanksCondition) as IFormatCondition;
condition.Interior.Color = Color.Green;
var color_A1_2 = worksheet.Range["A1"].DisplayFormat.Interior.Color;

// Save to an excel file
workbook.Save("getdisplayformat.xlsx");

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

Get Special Cells

Special cell ranges refer to the ranges containing specific data types or values. These can be cells containing comments, text values, formulas, blanks, constants, numbers, etc. One of the most beneficial methods in GcExcel is the IRange.SpecialCells method. When used, it returns a range that represents only those types of cells we specify. For example, one can use the SpecialCells Method to return a range containing only formulas.

GcExcel allows you to get special cell ranges using the SpecialCells method of the IRange interface. It takes the following enumerations as parameters:

• SpecialCellType: Specifies the type of cells like formula, constant, blank, etc
• SpecialCellsValue: Specifies cells with a particular type of value like numbers, text values, etc

Following the code, find all special cells with Formulas and Constants and set the background color on those cells.

//create a new workbook
var workbook = new GrapeCity.Documents.Excel.Workbook();

using (var existingFile = GetResourceStream("xlsx\\FinancialReport.xlsx"))
{
    workbook.Open(existingFile);
}

IRange cells = workbook.ActiveSheet.Cells;

// Find all formulas
var allFormulas = cells.SpecialCells(SpecialCellType.Formulas);
// Find all constants
var allConstants = cells.SpecialCells(SpecialCellType.Constants);

// Change background color of found cells
allFormulas.Interior.Color = Color.LightGray;
allConstants.Interior.Color = Color.LightBlue;

// Save to an excel file
workbook.Save("specialcellsinexistingfiles.xlsx");

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

Copy Hidden Rows to New Range

You may want certain rows hidden; however, when you copy the rows to some other range, these hidden rows/columns should be copied too. GcExcel adds a new API IRange.Copy(IRange destination, PasteOption pasteOption) to copy hidden rows/columns. The API includes the additional property AllowPasteHiddenRange, which will control whether to copy the data of hidden rows/columns or not.

Check out more details: Help .NET | Help Java | Demo .NET | Demo Java

Get All the Precedents and Dependents

An Excel spreadsheet sometimes contains multiple formulas. Understanding the relationship between the cells can be a difficult task if you are locating the cause of a calculation error to a cell or a range causing it. For example, formulas in Excel spreadsheets may sometimes return wrong values or even give an error message that should be corrected to obtain the correct values. GcExcel makes it easier to programmatically audit calculation errors by providing an easy-to-use GetPrecedents method by returning cells or ranges directly or indirectly referred to by the formulas in other cells. 

In addition, which cells are impacted if a cell value is modified or calculations are performed can be returned by GetDependents() method. This method returns which cells or ranges contain formulas that refer to other cells directly or indirectly. 

The following code calculates precedents of the Formula applied in E2.

//create a new workbook
var workbook = new GrapeCity.Documents.Excel.Workbook();

IWorksheet worksheet = workbook.Worksheets[0];
worksheet.Range["E2"].Formula = "=sum(A1:A2, B4,C1:C3)";
worksheet.Range["A1"].Value = 1;
worksheet.Range["A2"].Value = 2;
worksheet.Range["B4"].Value = 3;
worksheet.Range["C1"].Value = 4;
worksheet.Range["C2"].Value = 5;
worksheet.Range["C3"].Value = 6;

foreach (var item in worksheet.Range["E2"].GetPrecedents())
{
    item.Interior.Color = Color.Pink;
}

// Save to an excel file
workbook.Save("precedents.xlsx");

Following code highlights all direct or indirect cells which refer the A1 cell as Formula.

//create a new workbook
var workbook = new GrapeCity.Documents.Excel.Workbook();

IWorksheet worksheet = workbook.Worksheets[0];
worksheet.Range["A1"].Value = 100;
worksheet.Range["C1"].Formula = "=$A$1";
worksheet.Range["E1:E5"].Formula = "=$A$1";
foreach (var item in worksheet.Range["A1"].GetDependents())
{
    item.Interior.Color = Color.Azure;
}

// Save to an excel file
workbook.Save("dependents.xlsx");

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

Defer Layout Update in Pivot Table

Whenever a pivot table data field is added, removed, or moved to a different location, the whole pivot table is recalculated. This action affects the overall performance of the Excel spreadsheet generation.

GcExcel adds the DeferLayoutUpdate property. It defers any updates to the pivot table until the user calls the update method and updates the table. This property makes generating Excel spreadsheets with pivot tables more natural and cleaner.

IWorksheet worksheet = workbook.Worksheets[0];
worksheet.Range["A1:F16"].Value = sourceData;
worksheet.Range["A:F"].ColumnWidth = 15; 
var pivotcache = workbook.PivotCaches.Create(worksheet.Range["A1:F16"]); 
var pivottable = worksheet.PivotTables.Add(pivotcache, worksheet.Range["H7"], "pivottable1");   

// defer layout update
pivottable.DeferLayoutUpdate = true;   

//config pivot table's fields 
var field_Category = pivottable.PivotFields["Category"];
field_Category.Orientation = PivotFieldOrientation.RowField;   

var field_Product = pivottable.PivotFields["Product"];
field_Product.Orientation = PivotFieldOrientation.ColumnField;   

var field_Amount = pivottable.PivotFields["Amount"];
pivottable.AddDataField(field_Amount, "sum amount", ConsolidationFunction.Sum);   

// must update the
pivottable.pivottable.Update();   

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

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

Get the Row and Column Grouping Information

If you have a lengthy report in Excel, you may want to make it more manageable. Sometimes you would like to expand or collapse a group in your worksheet to hide certain information or only view detailed rows/columns as needed. However, suppose you want to programmatically retrieve which rows or columns are grouped in the Excel worksheet. In that case, it may help you expand/collapse the groups easily, rather than opening the worksheet and doing it manually. Check out the GcExcel API to get row and column grouping information through the List RowGroupInfo and List ColumnGroupInfo list types. With the help of this API, the following functionalities can be achieved:

• Get the start index of the current group
• Get the end index of the group
• Get the level of the current group
• Check whether the group is collapsed or not
• Get the parent row/column of the group
• Get child row/column of the group

Finally, the Expand() or Collapse() methods can help expand or collapse rows or columns.

Check out more details: Help .NET | Help Java | Demo .NET | Demo Java

Control the Size of Exported JSON File

There are certain Excel files that, if exported to JSON, produce large-sized files. With GcExcel, you can control the size of exported JSON files through the new SerializationOptions.IgnoreColumnRowInfoOutOfUsedRange option. This option will let you control whether to export the column row information out of the used range, which will help reduce the size of the exported JSON file. This feature is supported only when GcExcel is integrated as a Server-side API with the client-side SpreadJS product. 

Check out more details: Help .NET | Help Java

What do you think about these tips and tricks? Please share your comments below. Thanks!