Working with Cells GridWeb

Accessing Cells in the Worksheet

This topic discusses cells, looking at GridWeb’s most basic feature: accessing cells.

Each worksheet contains a GridCells object, a collection of GridCell objects. A GridCell object represents a cell in Aspose.Cells.GridWeb. It is possible to access any cell using GridWeb. There are two preferred methods:

Below, each approach is discussed.

Using Cell Name

All cells have a unique name. For example, A1, A2, B1, B2, etc. Aspose.Cells.GridWeb allows developers to access any desired cell by using the cell name. Simply pass the cell name (as an index) to the GridCells collection of the GridWorksheet.

For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
//Accessing the worksheet of the Grid that is currently active
GridWorksheet sheet = gridweb.getWorkSheets().get(gridweb.getActiveSheetIndex());
//Accessing "B1" cell of the worksheet
GridCell cell = sheet.getCells().get("B1");

Using Row & Column Indices

A cell can also be recognized by its location in terms of row and column indices. Just pass a cell’s row and column indices to the GridCells collection of the GridWorksheet. This approach is more faster than the above one.

For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
//Accessing the worksheet of the Grid that is currently active
GridWorksheet sheet = gridweb.getWorkSheets().get(gridweb.getActiveSheetIndex());
//Accessing "B1" cell of the worksheet using its row and column indices
GridCell cell = sheet.getCells().get(0, 1);

Accessing and Modifying the Value of a Cell

Accessing Cells in the Worksheet discussed accessing cells. This topic extends that discussion to show how to access and modify cell values using the GridWeb API.

Accessing & Modifying a Cell’s Value

String Values

Before accessing and modifying the value of a cell, you need to know how to access cells. For details about the different approaches for accessing cells, refer to Accessing Cells in the Worksheet.

Each cell has a property named getStringValue(). Once a cell is accessed, developers can access the getStringValue() method to access the cells string value.

For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
//Accessing the worksheet of the Grid that is currently active
GridWorksheet sheet = gridweb.getWorkSheets().get(gridweb.getActiveSheetIndex());
//Accessing "B1" cell of the worksheet
GridCell cell = sheet.getCells().get("B1");
//Inserting & modifying the string value of "B1" cell
cell.putValue("Hello Aspose.Grid");

All Types of Values

Aspose.Cells.GridWeb also provides a special method, putValue, for each cell. With this method, it is possible to insert or modify any type of value (Boolean, int, double, DateTime and string) in a cell.

For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
//Accessing the worksheet of the Grid that is currently active
GridWorksheet sheet = gridweb.getWorkSheets().get(gridweb.getActiveSheetIndex());
//Accessing "B1" cell of the worksheet
GridCell cell = sheet.getCells().get("B1");
//Putting a value in "B1" cell
cell.putValue(Calendar.getInstance());

There is also an overloaded version of the putValue method that can take any kind of value in string format and convert it to a proper data type automatically. To make it happen, pass the Boolean value true to another parameter of the putValue method as shown below in the example.

For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
//Accessing the worksheet of the Grid that is currently active
GridWorksheet sheet = gridweb.getWorkSheets().get(gridweb.getActiveSheetIndex());
//Accessing "B1" cell of the worksheet
GridCell cell = sheet.getCells().get("B1");
//Putting a numeric value as string in "B1" cell that will be converted to a suitable data type automatically
cell.putValue("19.4", true);

Adding Formulas to the Cells

The most valuable feature offered by Aspose.Cells.GridWeb is support for formulas or functions. Aspose.Cells.GridWeb has its own Formula Engine that calculates the formulas in worksheets. Aspose.Cells.GridWeb supports both in-built and user-defined functions or formulas. This topic discusses adding formulas to cells using Aspose.Cells.GridWeb API in detail.

How to Add & Calculate a Formula?

It is possible to add, access and modify formulas in cells by using a cell’s Formula property. Aspose.Cells.GridWeb supports user-defined formulas ranging from simple to complex. However, a large number of built-in functions or formulas (similar to Microsoft Excel) are also supplied with Aspose.Cells.GridWeb. To see the full list of built-in functions, please refer to this list of supported functions.

For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
//Accessing the worksheet of the Grid that is currently active
GridWorksheet sheet = gridweb.getWorkSheets().get(gridweb.getActiveSheetIndex());
//Putting some values to cells
sheet.getCells().get("A1").putValue("1st Value");
sheet.getCells().get("A2").putValue("2nd Value");
sheet.getCells().get("A3").putValue("Sum");
sheet.getCells().get("B1").putValue(125.56);
sheet.getCells().get("B2").putValue(23.93);
//Calculating all formulas added in worksheets
gridweb.getWorkSheets().calculateFormula();
//Adding a simple formula to "B3" cell
sheet.getCells().get("B3").setFormula("=SUM(B1:B2)");

Formula added to B3 cell but not calculated by GridWeb

todo:image_alt_text

In the above screenshot, you can see that a formula has been added to B3 but has not been calculated yet. To calculate all formulas, call the GridWeb control’s GridWorksheetCollection ’s calculateFormula method after adding formulas to worksheets as shown below.

For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
//Calculating all formulas added in worksheets
gridweb.getWorkSheets().calculateFormula();

Users can also calculate formulas by clicking Submit.

Clicking Submit button of GridWeb

todo:image_alt_text

IMPORTANT: If a user clicks the Save or Undo buttons, or the sheet tabs, all formulas are calculated by GridWeb automatically.

Formula result after calculation

todo:image_alt_text

Referencing Cells from Other Worksheets

Using Aspose.Cells.GridWeb, it is possible to reference values stored in different worksheets in their formulas, creating complex formulas.

The syntax for referencing a cell value from a different worksheet is SheetName!CellName.

For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
//Accessing the worksheet of the Grid that is currently active
GridWorksheet sheet = gridweb.getWorkSheets().get(gridweb.getActiveSheetIndex());
//Adding a bit complex formula to "A1" cell
sheet.getCells().get("A1").setFormula("=SUM(F1:F7)/ AVERAGE (E1:E7)-Sheet1!C6");

Create Data Validation in a GridCell of GridWeb

Aspose.Cells.GridWeb allows you to add Data Validation using the GridWorksheet.getValidations().add() method. Using this method, you have to specify the Cell Range. But if you want to create a Data Validation in a single GridCell then you can do it directly using GridCell.createValidation() method. Similarly, you can remove Data Validation from a GridCell using the GridCell.removeValidation() method.

The following sample code creates a Data Validation in a cell B3. If you enter any value which is not between 20 and 40, the cell B3 will show Validation Error in the form of Red XXXX as shown in this screenshot.

todo:image_alt_text

For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
//Access first worksheet
GridWorksheet sheet = gridweb.getWorkSheets().get(0);
//Access cell B3
GridCell cell = sheet.getCells().get("B3");
//Add validation inside the gridcell
//Any value which is not between 20 and 40 will cause error in a gridcell
GridValidation val = cell.createValidation(GridValidationType.WHOLE_NUMBER, true);
val.setFormula1("=20");
val.setFormula2("=40");
val.setOperator(OperatorType.BETWEEN);
val.setShowError(true);
val.setShowInput(true);

Creating Custom Command Buttons

Aspose.Cells.GridWeb contains special buttons like Submit, Save, and Undo. All these buttons perform specific tasks for Aspose.Cells.GridWeb. It is also possible to add custom buttons that performs custom tasks. This topic explains how to use this feature.

The following sample code explains how to create a custom command button and how to handle its click event. You can use any icon for your custom command button. For illustration purpose, we used this image icon.

todo:image_alt_text

As you can see in the following screenshot, when the user clicks the custom command button, it adds a text in cell A1 saying “My Custom Command Button is Clicked."

todo:image_alt_text

For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
//Instantiating a CustomCommandButton object
CustomCommandButton button = new CustomCommandButton();
//Setting the command for button
button.setCommand("MyButton");
//Setting text of the button
button.setText("MyButton");
//Setting tooltip of the button
button.setToolTip("My Custom Command Button");
//Setting image URL of the button
button.setImageUrl("icon.png");
//Adding button to CustomCommandButtons collection of GridWeb
gridweb.getCustomCommandButtons().add(button);

Event Handling of Custom Command Button

The following sample code explains how to perform event handling of custom command button.

For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
//Create custom command event handler to handle the click event
CustomCommandEventHandler cceh=new CustomCommandEventHandler(){
public void handleCellEvent(Object sender, String command){
//Identifying a specific button by checking its command
if (command.equals("MyButton"))
{
//Accessing the cells collection of the worksheet that is currently active
GridWorksheet sheet = gridweb.getWorkSheets().get(gridweb.getActiveSheetIndex());
//Putting value to "A1" cell
sheet.getCells().get("A1").putValue("My Custom Command Button is Clicked.");
sheet.getCells().setColumnWidth(0, 50);
}
}
};
//Assign the custom command event handler created above to gridweb
gridweb.CustomCommand = cceh;

Formatting cells for GridWeb

Possible Usage Scenarios

GridWeb now supports users to enter cell data in percentage format like 3% and the data in the cell will automatically be formatted as 3.00%. However, you will have to set the cell style to Percentage Format which is either GridTableItemStyle.NumberType a 9 or 10. The number 9 will format 3% as 3% but the number 10 will format 3% as 3.00%.

Enter Cell Data of GridWeb Worksheet in Percentage Format

The following sample code sets the cell A1 GridTableItemStyle.NumberType as 10, therefore the input data 3% automatically be formatted as 3.00% as shown in the screenshot.

todo:image_alt_text

Sample Code

For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
//Access cell A1 of first gridweb worksheet
GridCell cellA1 = gridweb.getWorkSheets().get(0).getCells().get("A1");
//Access cell style and set its number format to 10 which is a Percentage 0.00% format
GridTableItemStyle st = cellA1.getStyle();
st.setNumberType(10);
cellA1.setStyle(st);