Wiki
Clone wikiAspose for OpenXML / Insert / Delete Rows / Columns
Whether we are creating a new worksheet from scratch or we are working on an existing worksheet, we may need to add extra rows or columns into the worksheet to accomodate more data or for some other reason. Inversely, it may also be required to delete rows or columns from specified positions of the worksheet.
Managing Rows/Columns
Aspose.Cells provides a class, Workbook that represents an Excel file. Workbook class contains a Worksheets collection that allows to access each worksheet in the Excel file. A worksheet is represented by the Worksheet class. Worksheet class provides a Cells collection that represents all cells in the worksheet.
Cells collection provides several methods to manage rows or columns in a worksheet, few of these are discussed below in more detail.
Inserting a Row
Developers can insert a row into the worksheet at any location by calling the InsertRow method of the Cells collection. InsertRow method takes the index of the row where the new row will be inserted.
//Creating a file stream containing the Excel file to be opened FileStream fstream = new FileStream("Row and Column Operation.xls", FileMode.Open); //Instantiating a Workbook object //Opening the Excel file through the file stream Workbook workbook = new Workbook(fstream); //Accessing the first worksheet in the Excel file Worksheet worksheet = workbook.Worksheets[0]; //Inserting a row into the worksheet at 3rd position worksheet.Cells.InsertRow(2); //Saving the modified Excel file workbook.Save("Inserting Row.xls"); //Closing the file stream to free all resources fstream.Close();
Inserting Multiple Rows
Sometimes, developers may need to insert multiple rows into the worksheet. It can be done by calling the InsertRows method of the Cells collection. InsertRows method takes two parameters:
- Row Index, the index of the row from where the new rows will be inserted
- Number of Rows, total number of rows that need to be inserted
//Creating a file stream containing the Excel file to be opened FileStream fstream = new FileStream("Row and Column Operation.xls", FileMode.Open); //Instantiating a Workbook object //Opening the Excel file through the file stream Workbook workbook = new Workbook(fstream); //Accessing the first worksheet in the Excel file Worksheet worksheet = workbook.Worksheets[0]; //Inserting 10 rows into the worksheet starting from 3rd row worksheet.Cells.InsertRows(2, 10); //Saving the modified Excel file workbook.Save("Inserting Mutiple Rows.xls"); //Closing the file stream to free all resources fstream.Close();
Deleting a Row
Developers can delete a row from the worksheet at any location by calling the DeleteRow method of the Cells collection.DeleteRow method takes the index of the row that needs to be deleted.
//Creating a file stream containing the Excel file to be opened FileStream fstream = new FileStream("Row and Column Operation.xls", FileMode.Open); //Instantiating a Workbook object //Opening the Excel file through the file stream Workbook workbook = new Workbook(fstream); //Accessing the first worksheet in the Excel file Worksheet worksheet = workbook.Worksheets[0]; //Deleting 3rd row from the worksheet worksheet.Cells.DeleteRow(2); //Saving the modified Excel file workbook.Save("Deleting Rows.xls"); //Closing the file stream to free all resources fstream.Close();
Deleting Multiple Rows
If developers need to delete multiple rows from the worksheet, It can also be done by calling the DeleteRows method of the Cells collection. DeleteRows method takes two parameters:
- Row Index, the index of the row from where the rows will be deleted.
- Number of Rows, total number of rows that need to be deleted.
//Creating a file stream containing the Excel file to be opened FileStream fstream = new FileStream("Row and Column Operation.xls", FileMode.Open); //Instantiating a Workbook object //Opening the Excel file through the file stream Workbook workbook = new Workbook(fstream); //Accessing the first worksheet in the Excel file Worksheet worksheet = workbook.Worksheets[0]; //Deleting 10 rows from the worksheet starting from 3rd row worksheet.Cells.DeleteRows(2, 10); //Saving the modified Excel file workbook.Save("Deleting Mutiple Rows.xls"); //Closing the file stream to free all resources fstream.Close();
Inserting a Column
Developers can also insert a column into the worksheet at any location by calling the InsertColumn method of the Cells collection. InsertColumn method takes the index of the column where the new column will be inserted.
//Creating a file stream containing the Excel file to be opened FileStream fstream = new FileStream("Row and Column Operation.xls", FileMode.Open); //Instantiating a Workbook object //Opening the Excel file through the file stream Workbook workbook = new Workbook(fstream); //Accessing the first worksheet in the Excel file Worksheet worksheet = workbook.Worksheets[0]; //Inserting a column into the worksheet at 2nd position worksheet.Cells.InsertColumn(1); //Saving the modified Excel file workbook.Save("Inserting Column.xls"); //Closing the file stream to free all resources fstream.Close();
Deleting a Column
To delete a column from the worksheet at any location, developers can call DeleteColumn method of the Cells collection. DeleteColumn method takes the index of the column to delete.
//Creating a file stream containing the Excel file to be opened FileStream fstream = new FileStream("Row and Column Operation.xls", FileMode.Open); //Instantiating a Workbook object //Opening the Excel file through the file stream Workbook workbook = new Workbook(fstream); //Accessing the first worksheet in the Excel file Worksheet worksheet = workbook.Worksheets[0]; //Deleting a column from the worksheet at 2nd position worksheet.Cells.DeleteColumn(1); //Saving the modified Excel file workbook.Save("Deleting Column.xls"); //Closing the file stream to free all resources fstream.Close();
Download
Updated