Inserting, Deleting Rows and Columns

Introduction

Whether creating a new worksheet from scratch or working on an existing worksheet, we may need to add extra rows or columns to accommodate more data. Inversely, we may also need to delete rows or columns from specified positions in the worksheet. To fulfill these requirements, Aspose.Cells provides a very simplest set of classes and methods, discussed below.

Managing Rows and Columns

Aspose.Cells provides a class, IWorkbook, that represents a Microsoft Excel file. The IWorkbook class contains an IWorksheets collection that allows access to each worksheet in an Excel file. A worksheet is represented by the IWorksheet class. The IWorksheet class provides an ICells collection that represents all cells in the worksheet.

The ICells collection provides several methods managing rows and columns in a worksheet. Some of these are discussed below.

When rows or columns are added, the content in the worksheet is shifted down or to the right, and if rows or columns are removed, the content is shifted up or the left.

Insert a Row

Insert a row into the worksheet at any location by calling the InsertRow method of the ICells collection. The InsertRow method takes the index of the row where the new row will be inserted.

//For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-C

//Path of input excel file
StringPtr sampleInsertingDeletingRowsAndColumns = dirPath->StringAppend(new String("sampleInsertingDeletingRowsAndColumns.xlsx"));

//Path of output excel file
StringPtr outputInsertingDeletingRowsAndColumns = outPath->StringAppend(new String("outputInsertingDeletingRowsAndColumns.xlsx"));

//Read input excel file
intrusive_ptr<IWorkbook> workbook = Factory::CreateIWorkbook(sampleInsertingDeletingRowsAndColumns);

//Accessing the first worksheet in the Excel file
intrusive_ptr<IWorksheet> worksheet = workbook->GetIWorksheets()->GetObjectByIndex(0);

//Inserting a row into the worksheet at 3rd position
worksheet->GetICells()->InsertRow(2);

//Save the Excel file.
workbook->Save(outputInsertingDeletingRowsAndColumns);

Inserting Multiple Rows

To insert multiple rows into a worksheet, call the InsertRows method of the ICells collection. The InsertRows method takes two parameters:

• Row index, the index of the row from where the new rows will be inserted.
• Number of rows, the total number of rows that need to be inserted.

//For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-C

//Path of input excel file
StringPtr sampleInsertingDeletingRowsAndColumns = dirPath->StringAppend(new String("sampleInsertingDeletingRowsAndColumns.xlsx"));

//Path of output excel file
StringPtr outputInsertingDeletingRowsAndColumns = outPath->StringAppend(new String("outputInsertingDeletingRowsAndColumns.xlsx"));

//Read input excel file
intrusive_ptr<IWorkbook> workbook = Factory::CreateIWorkbook(sampleInsertingDeletingRowsAndColumns);

//Accessing the first worksheet in the Excel file
intrusive_ptr<IWorksheet> worksheet = workbook->GetIWorksheets()->GetObjectByIndex(0);

//Inserting 10 rows into the worksheet starting from 3rd row
worksheet->GetICells()->InsertRows(2, 10);

//Save the Excel file.
workbook->Save(outputInsertingDeletingRowsAndColumns);

Deleting Multiple Rows

To delete multiple rows from a worksheet, call the DeleteRows method of the ICells collection. The DeleteRows method takes two parameters:

• Row index, the index of the row from where the rows will be deleted.
• Number of rows, the total number of rows that need to be deleted.

//For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-C

//Path of input excel file
StringPtr sampleInsertingDeletingRowsAndColumns = dirPath->StringAppend(new String("sampleInsertingDeletingRowsAndColumns.xlsx"));

//Path of output excel file
StringPtr outputInsertingDeletingRowsAndColumns = outPath->StringAppend(new String("outputInsertingDeletingRowsAndColumns.xlsx"));

//Read input excel file
intrusive_ptr<IWorkbook> workbook = Factory::CreateIWorkbook(sampleInsertingDeletingRowsAndColumns);

//Accessing the first worksheet in the Excel file
intrusive_ptr<IWorksheet> worksheet = workbook->GetIWorksheets()->GetObjectByIndex(0);

//Deleting 10 rows from the worksheet starting from 3rd row
worksheet->GetICells()->DeleteRows(2, 10);

//Save the Excel file.
workbook->Save(outputInsertingDeletingRowsAndColumns);

Insert a Column

Developers can also insert a column into the worksheet at any location by calling the InsertColumn method of the ICells collection. InsertColumn method takes the index of the column where the new column will be inserted.

//For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-C

//Path of input excel file
StringPtr sampleInsertingDeletingRowsAndColumns = dirPath->StringAppend(new String("sampleInsertingDeletingRowsAndColumns.xlsx"));

//Path of output excel file
StringPtr outputInsertingDeletingRowsAndColumns = outPath->StringAppend(new String("outputInsertingDeletingRowsAndColumns.xlsx"));

//Read input excel file
intrusive_ptr<IWorkbook> workbook = Factory::CreateIWorkbook(sampleInsertingDeletingRowsAndColumns);

//Accessing the first worksheet in the Excel file
intrusive_ptr<IWorksheet> worksheet = workbook->GetIWorksheets()->GetObjectByIndex(0);

//Inserting a column into the worksheet at 2nd position
worksheet->GetICells()->InsertColumn(1);

//Save the Excel file.
workbook->Save(outputInsertingDeletingRowsAndColumns);

Delete a Column

To delete a column from the worksheet at any location, call the DeleteColumn method of the ICells collection. The DeleteColumn method takes the index of the column to delete.

//For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-C

//Path of input excel file
StringPtr sampleDeleteColumn = dirPath->StringAppend(new String("sampleInsertingDeletingRowsAndColumns.xlsx"));

//Path of output excel file
StringPtr outputDeleteColumn = outPath->StringAppend(new String("outputInsertingDeletingRowsAndColumns.xlsx"));

//Read input excel file
intrusive_ptr<IWorkbook> workbook = Factory::CreateIWorkbook(sampleDeleteColumn);

//Accessing the first worksheet in the Excel file
intrusive_ptr<IWorksheet> worksheet = workbook->GetIWorksheets()->GetObjectByIndex(0);

//Deleting a column from the worksheet at 2nd position
worksheet->GetICells()->DeleteColumn(4);

//Save the Excel file.
workbook->Save(outputDeleteColumn);