Learn more about Xeed Workbooks for .NET

Version 1.2 of Workbooks for .NET was released recently, and with it comes new things we can do to customize our xlsx documents. We will go over some of them over this and the next few articles.

Inserting a Worksheet

In our first article we covered how to add a Worksheet to our document using the Add() method on the WorksheetCollection. This allowed us to add a Worksheet but only at the end of the existing worksheets. In v1.2 we added the Insert() method to allow users to specify where they want to add the new Worksheet.

Available overloads:

• Insert(Int32,String) : the destination index and the name of the new Worksheet
• Insert(String,String) : the name of the Worksheet that is currently located where the new Worksheet will be placed, and the name of the new Worksheet
• Insert(Worksheet,String) : the Worksheet that is currently located where the new Worksheet will be placed, and the name of the new Worksheet

// Inserting a new Worksheet
using( var document = Workbook.Load( "testDoc.xlsx" ));
{
	// Start: "Sheet1", "Sheet2", "Sheet3"

	// Inserting at a specific index
	document.Worksheets.Insert(2, "NewSheet1");
	// Result: "Sheet1", "Sheet2", "NewSheet1", "Sheet3"

	// Inserting at the location of a specific Worksheet (by name)
	document.Worksheets.Insert("Sheet2", "NewSheet2");
	// Result: "Sheet1", "NewSheet2", "Sheet2", "NewSheet1", "Sheet3"

	// Inserting at the location of a specific Worksheet (by object)
	var position = document.Worksheets[ 2 ];
	document.Worksheets.Insert(position, "NewSheet3");
	// Result: "Sheet1", "NewSheet2", "NewSheet3", "Sheet2", "NewSheet1", "Sheet3"

	// save the modifications
	document.Save();
}

Inserting Rows

Rows can be inserted by using the InsertRows() method, available on the Worksheet class.

The InsertRows() method has the following parameters:

• rowId: the id of the Row that is currently located where the new Rows will be inserted
• count: the number of Rows that will be inserted, 1 by default

// Inserting rows
using( var document = Workbook.Load( "testDoc.xlsx" ));
{
	var firstSheet = document.Worksheets[ 0 ];

	// Insert 5 rows at the top of the worksheet
	firstSheet.InsertRows(0, 5);

	// save the modifications
	document.Save();
}

Inserting Columns

Columns can be inserted by using the InsertColumns() method, available on the Worksheet class.

The InsertColumns() method has the following parameters:

• columnId: the id of the Column that is currently located where the new Columns will be inserted
• count: the number or Columns that will be inserted, 1 by default

The InsertColumns() method has 2 overloads, both have the same parameter names, but one uses an integer for columnId to specify the column id by its index, and the other uses a string instead to specify the column id by its letter (for example, id 0 is the same as id “A”).

// Inserting columns
using( var document = Workbook.Load( "testDoc.xlsx" ));
{
	var firstSheet = document.Worksheets[ 0 ];

	// Insert 3 columns at the left of the worksheet using the number index
	firstSheet.InsertColumns(0, 3);

	// Insert another 2 columns at the left of the worksheet using the letter index this time
	firstSheet.InsertColumns("A", 2);

	// save the modifications
	document.Save();
}

Deleting Rows

Rows can be deleted by using the DeleteRows() method, available on the Worksheet class.

The DeleteRows() method has the following parameters:

• rowId: the id of the Row that is currently located where the deletion will start
• count: the number of Rows that will be deleted, 1 by default

// Deleting rows
using( var document = Workbook.Load( "testDoc.xlsx" ));
{
	var firstSheet = document.Worksheets[ 0 ];

	// Delete the top 5 rows
	firstSheet.DeleteRows(0, 5);

	// save the modifications
	document.Save();
}

Deleting Columns

Columns can be deleted by using the DeleteColumns() method, available on the Worksheet class.

The DeleteColumns() method has the following parameters:

• columnId: the id of the Column that is currently located where the deletion will start
• count: the number or Columns that will be deleted, 1 by default

The DeleteColumns() method has 2 overloads, both have the same parameter names, but one uses an integer for columnId to specify the column id by its index, and the other uses a string instead to specify the column id by its letter (for example, id 0 is the same as id “A”).

// Deleting columns
using( var document = Workbook.Load( "testDoc.xlsx" ));
{
	var firstSheet = document.Worksheets[ 0 ];

	// Delete the 3 left-most columns using the number index
	firstSheet.DeleteColumns(0, 3);

	// Delete another 2 columns at the left using the letter index this time
	firstSheet.DeleteColumns("A", 2);

	// save the modifications
	document.Save();
}

More to come in Part II, stay tuned!

For more information, please refer to the documentación.