Manage data of Excel files

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.

How to Add Data to Cells

Aspose.Cells for Node.js via C++ provides a class, Workbook, that represents a Microsoft Excel file. The Workbook class contains a Worksheets collection 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 the cells in worksheets by calling the Cell class' putValue method. Aspose.Cells provides overloaded versions of the putValue method that lets developers add different kinds of data to cells. Using these overloaded versions of the putValue method, it is possible to add a Boolean, string, double, integer, or date/time, etc. values to the cell.

	const path = require("path");
	const AsposeCells = require("aspose.cells.node");

	// The path to the documents directory.
	const dataDir = path.join(__dirname, "data");

	// Create directory if it is not already present.
	const fs = require("fs");
	if (!fs.existsSync(dataDir)) {
	fs.mkdirSync(dataDir);
	}

	// Instantiating a Workbook object
	let workbook = new AsposeCells.Workbook();

	// Obtaining the reference of the first worksheet
	let worksheet = workbook.getWorksheets().get(0);

	// Adding a string value to the cell
	worksheet.getCells().get("A1").putValue("Hello World");

	// Adding a double value to the cell
	worksheet.getCells().get("A2").putValue(20.5);

	// Adding an integer value to the cell
	worksheet.getCells().get("A3").putValue(15);

	// Adding a boolean value to the cell
	worksheet.getCells().get("A4").putValue(true);

	// Adding a date/time value to the cell
	worksheet.getCells().get("A5").putValue(new Date());

	// Setting the display format of the date
	let style = worksheet.getCells().get("A5").getStyle();
	style.setNumber(15);
	worksheet.getCells().get("A5").setStyle(style);

	// Saving the Excel file
	workbook.save(path.join(dataDir, "output.out.xls"));

view raw Cells-Data-AddDataToCells.js hosted with ❤ by GitHub

How to Improve Efficiency

If you use putValue method to put 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.

How to Retrieve Data from Cells

Aspose.Cells for Node.js via C++ provides a class, Workbook that represents a Microsoft Excel file. The Workbook class contains a Worksheets collection that allows access to worksheets in the 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:

getStringValue(): returns the string value of the cell.
getDoubleValue(): returns the double value of the cell.
getBoolValue(): returns the boolean value of the cell.
getDateTimeValue(): returns the date/time value of the cell.
getFloatValue(): returns the float value of the cell.
getIntValue(): returns the integer value of the cell.

When a field is not filled, cells with getDoubleValue() or getFloatValue() throws an exception.

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

Cell Value Types	Description
IsBool	Specifies that cell value is Boolean.
IsDateTime	Specifies that cell value is date/time.
IsNull	Represents a blank cell.
IsNumeric	Specifies that cell value is numeric.
IsString	Specifies that cell value is a string.
IsUnknown	Specifies that cell value is unknown.

You can also use the above pre-defined cell value types to compare with the Type of data present in each cell.

	const AsposeCells = require("aspose.cells.node");
	const path = require("path");

	// The path to the documents directory.
	const dataDir = path.join(__dirname, "data");

	// Opening an existing workbook
	const workbook = new AsposeCells.Workbook("book1.xls");

	// Accessing first worksheet
	const worksheet = workbook.getWorksheets().get(0);

	var cells = worksheet.getCells();
	var maxRow = cells.getMaxRow();
	var maxColumn = cells.getMaxColumn();
	for (let i = 0; i <= maxRow; i++) {
	for (let j = 0; j <= maxColumn; j++)
	{
	const cell1 = cells.get(i, j);
	// Variables to store values of different data types
	let stringValue;
	let doubleValue;
	let boolValue;
	let dateTimeValue;

	// Passing the type of the data contained in the cell for evaluation
	switch (cell1.getType()) {
	// Evaluating the data type of the cell data for string value
	case AsposeCells.CellValueType.IsString:
	stringValue = cell1.getStringValue();
	console.log("String Value: " + stringValue);
	break;

	// Evaluating the data type of the cell data for double value
	case AsposeCells.CellValueType.IsNumeric:
	doubleValue = cell1.getDoubleValue();
	console.log("Double Value: " + doubleValue);
	break;

	// Evaluating the data type of the cell data for boolean value
	case AsposeCells.CellValueType.IsBool:
	boolValue = cell1.getBoolValue();
	console.log("Bool Value: " + boolValue);
	break;

	// Evaluating the data type of the cell data for date/time value
	case AsposeCells.CellValueType.IsDateTime:
	dateTimeValue = cell1.getDateTimeValue();
	console.log("DateTime Value: " + dateTimeValue);
	break;

	// Evaluating the unknown data type of the cell data
	case AsposeCells.CellValueType.IsUnknown:
	stringValue = cell1.getStringValue();
	console.log("Unknown Value: " + stringValue);
	break;

	// Terminating the type checking of type of the cell data is null
	case AsposeCells.CellValueType.IsNull:
	break;
	}
	}
	}

view raw Cells-Data-RetrieveDataFromCells.js hosted with ❤ by GitHub

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

Advance topics

Managing Ranges with Node.js via C++ Format cells with Node.js via C++