How can I clear current range of all cells in the worksheet

Aspose.Cells Product Family
1

Hi

I want to get the current range of excel and want to clear all cells with updated data.

I’m using below code while using Microsoft.Office.Interop.Excel
Excel.Range xlRange = xlWorksheet.Range[“A1”].CurrentRegion;

Can you help me in this case.

2

@RavinderChauhan,

Thanks for your query.

See the following code segments for your reference:
e.g
Sample code:

//Clear the A1 cell in the first worksheet.

Workbook book = new Workbook();
 book.Worksheets[0].Cells["A1"].PutValue(null);

//Clear all the cell in the A1: E10 range.

//Open an Excel file.
Workbook workbook = new Workbook("Book1.xlsx");
//Get all the worksheets in the book.
WorksheetCollection worksheets = workbook.Worksheets; 
//Get the first worksheet in the worksheets collection.
Worksheet worksheet = workbook.Worksheets[0];
//Create a range of cells.
Range range1 = worksheet.Cells.CreateRange("E12","I12");
//.......

worksheet.Cells.ClearRange(0,4,9,4); 

//Save the excel file.
workbook.Save("out1.xlsx");

Hope, this helps a bit.

3

Dear Amjad,

I don’t know the start and end cell address for Range in advance.

I want a function which can find the cell address of the first and last cell occupied by the cells surrounding the cell in question.

For example in the attached image, If my cursor is at cell C3 then I need the range A1:E8 as the result, exactly the same functionality as provided by CurrentRegion property like this syntax {xlWorksheet.Range[“C3”].CurrentRegion;} of Microsoft.Office.Interop.Excel

Image1.PNG (5.0 KB)

4

@RavinderChauhan,

Thanks for the screenshot and further details.

There is no such attribute in Aspose.Cells APIs but you may easily accomplish the task using the following sample code. The sample code is just for demonstration, so you may add/update it accordingly for your requirements:
e.g
Sample code:

 Workbook workbook = new Workbook("e:\\test2\\Book1.xlsx");
            //Get the first worksheet.
            Worksheet worksheet = workbook.Worksheets[0];

            //The cell to be found.
            //e.g C3 cell
            int r = 2;
            int c = 2;
            Cell ccell = worksheet.Cells[r, c];

            string nameOfRange = null;
            string area = null;

            foreach (var range in workbook.Worksheets.GetNamedRanges())
            {

                for (int i = 0; i < range.RowCount; i++)
                {

                    for (int j = 0; j < range.ColumnCount; j++)
                    {

                        var cell = range[i, j];
                        //If cell is found in the range, then show range name and its area.
                        if (cell.Name.Equals(ccell.Name))
                        {
                            nameOfRange = range.Name;
                            area = range.RefersTo;
                        }


                    }

                }

            }

            Console.WriteLine(nameOfRange);
            Console.WriteLine(area);

Hope, this helps a bit.

5

Dear Amjad,

Thanks for your quick response.

I already know, I’ve to start from cell address C3. I don’t need to find the cell C3.

My Excel workbook doesn’t have any NamedRanges. I need some way to find the cell address of the first and last cell occupied by the cells surrounding the cell C3.

I’m attaching one more image with excel file in the post please use this as example or illustration
please make download the excel file, after that go to C3 and press ctrl + a. This will select cell range a1:e8 that result i want to need from your code.

c3_press_ctrl_a.PNG (6.4 KB)
AposeExcel.zip (6.4 KB)

6

@RavinderChauhan,

Thanks for providing further details, screenshot and sample file.

Well, it looks like this (“press ctrl + a”) is application level command of MS Excel which does not provide any options or source attributes or in the MS Excel file format (e.g its source .xml file) itself (if you know let us know, we will check it). So, there would be no such API to get the relevant region of the underlying cell. I am afraid, you have to browse the cells by yourself (check each cell one by one surrounding to it if it is null/blank or have data in it) to know the region/area for your needs.

7

Thanks Amjad.