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

Last edited Mar 3, 2014 at 6:42 AM by asposemarketplace, version 2