Reg: Worksheet.Range

Aspose.Cells Product Family
1

Hi,

This is Balaji RJ.

I am facing difficulty in migrating existing VB.NET code which is using MS Excel to Aspose Excel.

1. WorkSheet.Range(“spreadsheet_id”);

2.Dim appChartWorkSheetRange As Excel.Range
appChartWorkSheetRange = appChartWorkSheetRange.Find(“*”, , , Excel.XlLookAt.xlWhole, Excel.XlSearchOrder.xlByColumns, Excel.XlSearchDirection.xlNext, False)

Not able to find the equalant in Aspose. The above uses Ms.Excel

help!!!

2
  1. Please use Worksheets.GetRangeByName method.

    2. Please try Cells.FindString method.
3

Hi,

Thanks for that suggestion. But now i got to search for a token inside a particular range.

I have the MS Excel code as follows

appChartWorkSheet is worksheet(1) of the Excel.application
searchRange Ecel.Range

string range = “$A$1:$G$43"
searchRange = appChartWorkSheet.Range(range)
resultRange = searchRange.Find(”_", , , Excel.XlLookAt.xlWhole, Excel.XlSearchOrder.xlByColumns, Excel.XlSearchDirection.xlNext, False, , )

I tried with Aspose.Excel.

I did created a range object using,
SearchRange = appChartWorkSheet.Cells.CreateRange(A, 1, G , 43)

Suggest me to move forward as equalant to MS Excel as above.

-Balaji RJ

4

@rjbalaji79,
Aspose.Excel is discarded and no more under active development now. It is replaced by Aspose.Cells that is much advanced and feature-rich. It supports searching data in ranges using Regex. Here is an example that can be used to search data in a particular columns only.

string regEx = @"abc-*xyz";
Workbook workbook = new Workbook("Book1.xlsx");
Worksheet worksheet = workbook.Worksheets[0];

FindOptions options = new FindOptions();
options.RegexKey = true;
options.LookInType = LookInType.Values;
options.LookAtType = LookAtType.Contains;

//Find value in first column
options.SetRange(GetCellAreaForColumn(worksheet.Cells, 0));
Cell found = null;
do
{
                
    found = worksheet.Cells.Find(regEx, found, options);
    if (found != null)
        Console.WriteLine(found.Value);
} while (found != null);

Here is the definition of the range creation function used in this example. You can create range as per your needs.

private static CellArea GetCellAreaForColumn(Cells cells, short ColumnNo)
        {
            return new CellArea() { StartColumn = ColumnNo, EndColumn = ColumnNo, StartRow = 0, EndRow = cells.EndCellInColumn(ColumnNo).Row };
        }

For more information about ranges and finding/searching data refer to the following articles:
Find or Search Data
Create Access and Copy Named Ranges

The latest version of this new product can be downloaded here:
Aspose.Cells for .NET(Latest version)

Here is a runnable complete solution that contains a lot of examples to test different features of Aspose.Cells.