Learn more about Xeed Workbooks for .NET

In the previous article, we learned the basics of creating an xlsx document using the Create, Load, Save and SaveAs methods, as well as how to add and retrieve Worksheets.

This time we will learn the basics of how to add and modify the contents of a Worksheet.

What exactly is a Worksheet?

Simply put, a Worksheet is a collection of cells. A Cell represents a rectangle in a Worksheet; each Cell is the intersection of a Row and a Column.

Note:

• A Worksheet can have a maximum of 16,384 columns and 1,048,576 rows.

Accessing Cells within a Worksheet

In order to modify the contents of a Worksheet, we will need to be able to access the Cell(s) to modify.

There are three (3) ways to access a given Cell:

1. Through the Cells Collection:

1. By using an address. This is a combination of a letter (for the column) and a number (for the row). The first column starts at "A" and the first row starts at "1", meaning the top left cell is located at the "A1" address.
2. By using coordinates. This is done by specifying the Row Id first and then the Column Id. In both cases the indexes start at 0, meaning the top left cell is located at the 0,0 coordinates.

2. Through the Cells collection of a given column in the Columns Collection:

1. By using the Row Id index. The index starts at 0, so the top-most cell in a column is located at index 0.

3. Through the Cells collection of a given row in the Rows Collection:

1. By using the Column Id index. The index starts at 0, so the left-most cell in a row is located at index 0.

// Accessing specific cells
	using( var document = Workbook.Load( "testDoc.xlsx" ));
	{
		var worksheet = document.Worksheets[ 0 ];
		
		// accessing cell A1 through the Cells collection
		var cellA1_FromAddress = worksheet.Cells[ "A1" ];
		var cellA1_FromCoordinates = worksheet.Cells[ 0, 0 ];
		
		// accessing cell B2 through the Columns collection
		var cellB2 = worksheet.Columns[ 1 ].Cells[ 1 ];
		
		// accessing cell C3 through the Rows collection
		var cellC3 = worksheet.Rows[ 2 ].Cells[ 2 ];
	}

Notes:

• The top left cell does not start at 1,1 like in Excel because for a developer, it is more intuitive to start the index at 0, and as such the top left cell is at coordinates 0,0
• If performance is important, an access by coordinates is faster than an access by address.

Modifying Cells

The contents of a Cell can be set by using either the Value or Formula properties.

A Value is static, while a Formula can be dynamic, meaning the displayed value can change based on the Formula’s elements (for example, if the Formula is the sum of a group of cells, the value displayed will change if any value in those other cells is changed).

// Modifying the content of Cells
	using( var document = Workbook.Load( "testDoc.xlsx" ));
	{
		var worksheet = document.Worksheets[ 0 ];
		
		// modifying cell A1 through the Cells collection
		worksheet.Cells[ "A1" ].Value = new DateTime( 2021, 7, 1, 10, 22, 33);
		
		// modifying cell B2 through the Columns collection
		worksheet.Columns[ 1 ].Cells[ 1 ].Value = 100;
		
		// modifying cell C3 through the Rows collection
		worksheet.Rows[ 2 ].Cells[ 2 ].Formula "=SUM(A2:A5)";
		
		// save the modifications
		document.Save();
	}

If Cell.Formula is set, the Cell.Value will be filled at two (2) moments only:

1. when MS Excel will be used to opened the saved document.
2. if the user calls the Worksheet.CalculateFormulas() method to calculate the formulas of a Worksheet, or Workbook.CalculateFormulas() to calculate the formulas of all the Worksheets within a Workbook.

Modifying Columns

We can also access the Columns collection to modify a specific Column. At the time of this writing, the supported properties to be modified on a Column are BestFit and Width.

To specify which Column to modify, we specify the index (0 = Column A).

// Modifying Columns
	using( var document = Workbook.Load( "testDoc.xlsx" ));
	{
		var worksheet = document.Worksheets[ 0 ];
		
		// modifying column A
		worksheet.Columns[ 0 ].BestFit = true;
		worksheet.Columns[ 0 ].Width = 65d;
		
		// modifying column B
		worksheet.Columns[ 1 ].BestFit = false;
		worksheet.Columns[ 1 ].Width = 85d;
		
		// save the modifications
		document.Save();
	}

Note:

• The AutoFit() method on Column can be used to adjust the width of the column to the longest content. There are four (4) optional parameters: minimumWidth and maximumWidth (amount of characters under the default font), as well as startRowId and endRowId (to indicate the range of cells whose content will be evaluated to affect the AutoFit).

Modifying Rows

We can also access the Rows collection to modify a specific Row. At the time of this writing, the only supported property to be modified on a Row is Height.

To specify which Row to modify, we specify the index (0 = Row 1).

// Modifying Rows
	using( var document = Workbook.Load( "testDoc.xlsx" ));
	{
		var worksheet = document.Worksheets[ 0 ];
		
		// modifying row 1
		worksheet.Rows[ 0 ].Height = 85d;
		
		// modifying row 2
		worksheet.Rows[ 1 ].Height = 65d;
		
		// save the modifications
		document.Save();
	}

Note:

• An AutoFit() method on Row will be available in the next release of the product, and will function in a similar way to the AutoFit() method on Column.

For more information, please refer to the documentation.