Add and Retrieve Data

In Accessing Cells of a Worksheet, we discussed basic approaches for accessing cells in a worksheet. This article uses one of those approaches to add different types of data to cells.

Adding Data to Cells

Aspose.Cells provides a class, Workbook, that represents a Microsoft Excel file. The Workbook class contains a WorksheetCollection that allows access to each worksheet in the Excel file. A worksheet is represented by the Worksheet class. The Worksheet class provides a Cells collection. Each item in the Cells collection represents an object of the Cell class.

Aspose.Cells allows developers to add data to cells in worksheets by calling the Cell class' setValue property. By using the setValue property, it is possible to add Boolean, string, double, integer or date/time, etc. values to the cell.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(AddingDataToCells.class) + "data/";

// Instantiating a Workbook object
Workbook workbook = new Workbook();

// Accessing the added worksheet in the Excel file
int sheetIndex = workbook.getWorksheets().add();
com.aspose.cells.Worksheet worksheet = workbook.getWorksheets().get(sheetIndex);
com.aspose.cells.Cells cells = worksheet.getCells();

// Adding a string value to the cell
com.aspose.cells.Cell cell = cells.get("A1");
cell.setValue("Hello World");

// Adding a double value to the cell
cell = cells.get("A2");
cell.setValue(20.5);

// Adding an integer value to the cell
cell = cells.get("A3");
cell.setValue(15);

// Adding a boolean value to the cell
cell = cells.get("A4");
cell.setValue(true);

// Adding a date/time value to the cell
cell = cells.get("A5");
cell.setValue(java.util.Calendar.getInstance());

// Setting the display format of the date
com.aspose.cells.Style style = cell.getStyle();
style.setNumber(15);
cell.setStyle(style);

// Saving the Excel file
workbook.save(dataDir + "AddingDataToCells_out.xls");

// Print message
System.out.println("Data Added Successfully");

Improving Efficiency

If you use the setValue property to add a large amount of data to a worksheet, you should add values to the cells, first by rows and then by columns. This approach greatly improves the efficiency of your applications.

While working on worksheets, users may add different types of data in the cells. These data items may include boolean, integer, floating-point, text or date/time values. You can get the appropriate values from the cells according to their data types using Aspose.Cells.

Retrieving Data from Cells

Aspose.Cells provides a class, Workbookthat represents an Excel file. Workbook class contains a WorksheetCollection that allows access to each worksheet in the Excel file. A worksheet is represented by the Worksheet class. The Worksheet class provides a Cells collection. Each item in the Cells collection represents an object of the Cell class.

The Cell class provides several properties that allow developers to retrieve values from the cells according to their data types. These properties include:

• StringValue, the cell’s string value.
• DoubleValue, returns the cell’s double value.
• BoolValue, the cell’s Boolean value.
• DateTimeValue, the cell’s date/time value.
• FloatValue, the cell’s float value.
• IntValue, the cell’s integer value.

Moreover, the type of data contained in a cell can also be checked by using the Type property of the Cell class. In fact, the Cell class' Type property is based on CellValueType enumeration whose pre-defined values are listed below:

Cell Value Types	Description
IS_BOOL	Specifies that the cell value is Boolean.
IS_DATE_TIME	Specifies that the cell value is date/time.
IS_ERROR	Represents that the cell contains an error value
IS_NULL	Represents a blank cell.
IS_NUMERIC	Specifies that the cell value is numeric.
IS_STRING	Specifies that the cell value is a string.
IS_UNKNOWN	Specifies that the cell value is unknown.
You can also use the above pre-defined cell value types to compare with the type of the data present in each cell.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(RetrievingDataFromCells.class) + "data/";

// Instantiating a Workbook object
Workbook workbook = new Workbook();

// Accessing the worksheet
com.aspose.cells.Worksheet worksheet = workbook.getWorksheets().get(0);
com.aspose.cells.Cells cells = worksheet.getCells();

// get cell from cells collection
com.aspose.cells.Cell cell = cells.get("A5");

switch (cell.getType()) {
case com.aspose.cells.CellValueType.IS_BOOL:
	System.out.println("Boolean Value: " + cell.getValue());
	break;
case com.aspose.cells.CellValueType.IS_DATE_TIME:
	System.out.println("Date Value: " + cell.getValue());
	break;
case com.aspose.cells.CellValueType.IS_NUMERIC:
	System.out.println("Numeric Value: " + cell.getValue());
	break;
case com.aspose.cells.CellValueType.IS_STRING:
	System.out.println("String Value: " + cell.getValue());
	break;
case com.aspose.cells.CellValueType.IS_NULL:
	System.out.println("Null Value");
	break;
}