Copying specific range of excel cells from below a specific keyword

Question

I am wanting to write a C# program to copy a specific range of cells below a specific keyword. The code will identify a keyword in Excel and then copy the values of all the cells below the keyword to copy into another range.

I am using Aspose. I attempted to write code to find the keyword and can successfully return the cell the keyword is located in. What I am trying to figure out how to do is copy the range specifically below any keyword into another range. I can successfully copy one range to another range but cannot do it from below a specific keyword.

    Cells cellsOne = worksheet.Cells;
    FindOptions findOptions = new FindOptions();
    findOptions.LookAtType = LookAtType.StartWith;
    Cell cell = cellsOne.Find("Accounting", null, findOptions);
    //Printing the name of the cell found after searching worksheet
    Console.WriteLine("Name of the cell containing String: " + cell.Name);
    //if cell is found/value is returned
    if (cell.Name.Contains("Accounting"))
    {
         //return cell value ?
         //copy all below values (will need the cell keyword is in to do that)
         //paste below values into specific columns
         //doing it manually
         Aspose.Cells.Range range1 = cellsOne.CreateRange("A2:A10"); 
         Aspose.Cells.Range range2 = cellsOne.CreateRange("B28:B34"); 
         range1.Copy(range2);
    }

I have visited Aspose website but am struggling to copy a range BELOW a specific keyword. Thank you.

Answer 1

I don't know how to do it in C#, but I would advise you to find the cell which has the value, then in your "Range" statement below you could start at that cell you found before.

For example, Cell cell = cellsOne.Find("Accounting", null, findOptions).Row;

You get for example "320", then your next line goes to that 320. Using your code:

  Aspose.Cells.Range range1 = cellsOne.CreateRange("A2:A10"); 
  Aspose.Cells.Range range2 = cellsOne.CreateRange("B" & cell & ":B34"); 
  range1.Copy(range2);`

That's what I would do in VBA.

Hope it helps!

Answer 2

Checking your code segment a bit, I thought your destination range is "A2:A10". Your code needs some tweaks. See the updated (complete) sample code with comments to accomplish your task for your reference. I evaluated the source range (below the searched keyword) dynamically using CellsHelper static class.
e.g.

Sample code:

Workbook workbook = new Workbook("e:\\test2\\Book1.xlsx");
Worksheet worksheet = workbook.Worksheets[0];
Cells cellsOne = worksheet.Cells;
FindOptions findOptions = new FindOptions();
findOptions.LookAtType = LookAtType.StartWith;
Cell cell = cellsOne.Find("Accounting", null, findOptions);
//Printing the name of the cell found after searching worksheet
Console.WriteLine("Name of the cell containing String: " + cell.Name);
//if cell is found/value is returned
if (cell != null)
{
   //I thought this is your destination range 
   Aspose.Cells.Range range1 = cellsOne.CreateRange("A2:A10");

   //Evaluate the the next cell after (found) cell's row and column indices.
   int startRow = cell.Row +1; //we add "1" to get the next in the same column
   int startCol = cell.Column;
   string startCell = CellsHelper.CellIndexToName(startRow, startCol);
   //Set and evaluate your end cell for the range.
   string endCell = CellsHelper.CellIndexToName(startRow + 6, startCol);
   //Create your dynamic source range based on your startCell and endCell values
   Aspose.Cells.Range range2 = cellsOne.CreateRange(startCell, endCell);
   
   //Copy the source range to destination range
   range1.Copy(range2);

}

workbook.Save("e:\\test2\\out1.xlsx");

Hope, this helps a bit.

You may also see the document on copying ranges for your further reference.

PS. I am working as Support developer/ Evangelist at Aspose.