worksheet.Cells.Find stopped working

Aspose.Cells Product Family
1

Hello,

I have the following code block in an application that worked successfully using Aspose.Cells version 20.3.0.0, but after upgrading today to the latest 21.5.0.0 version of Aspose.Cells it has stopped working. Within the do…while loop, the value of foundCell is always null when I know that there are empty cells.

To build the spreadsheet, I am using Workbook workbook = new Workbook(); and then using worksheet.Cells.ImportData(dv.ToTable(), 0, 0, importOptions); to populate the cells. Just after populating the cells, I am running the following code.

                commonNameIndex = worksheet.Cells.Columns[worksheet.Cells.Find("CommonName", null, new FindOptions() { LookAtType = LookAtType.EntireContent }).Column].Index;

                //Sort by CommonName A-Z
                worksheet.AutoFilter.Sorter.Clear();
                worksheet.AutoFilter.Sorter.Key1 = commonNameIndex;
                worksheet.AutoFilter.Sorter.Order1 = Aspose.Cells.SortOrder.Ascending;
                worksheet.AutoFilter.Sorter.Sort();

                //Apply VLOOKUP for blank Common Names

                Cell prevcell = null;
                Cell foundCell = null;
                string stringToFind = string.Empty;
                FindOptions opts = new FindOptions();

                //Create a Cells Area based on the Common Name column (column E) only
                CellArea ca = new CellArea
                {
                    StartRow = 0,
                    StartColumn = commonNameIndex,
                    EndRow = worksheet.Cells.MaxDataRow,
                    EndColumn = commonNameIndex
                };

                //Set cells area for find options
                opts.SetRange(ca);
                opts.LookAtType = LookAtType.EntireContent;
                opts.LookInType = LookInType.Formulas;

                columnLookup = worksheet.Cells.Columns[worksheet.Cells.Find("ShortPolicyNumber", null, new FindOptions() { LookAtType = LookAtType.EntireContent }).Column];

                do
                {
                    foundCell = worksheet.Cells.Find(stringToFind, prevcell, opts);
                    if (foundCell == null)
                        break;
                    if (foundCell.StringValue == "")
                    {
                        foundCell.R1C1Formula = "=VLOOKUP(RC[16],'Common Name'!C[-3]:C[-1],3,FALSE)";
                    }
                    prevcell = foundCell;

                } while (foundCell != null);

Can you spot anything that I am doing wrong?

Thank you!

2

@softwarelicense_aixgroup_com,
Please share your sample file with us for our testing. We will reproduce the problem and provide our feedback after analysis.

3

Hello,

I have built and attached a working console application to demonstrate the issue I’m having. Line 126 is always coming back as “null” however all 10 of the rows in column C are empty.

Aspose.Cells.FindEmptyCells.zip (15.4 KB)

4

@softwarelicense_aixgroup_com,
I have tried this solution with versions 21.3, 21.4 and 21.5 but could not observe any difference as none of the version could find any empty cell. Please note that if you iterate through all the elements in Cells collection of first worksheet, you will observe that there are 23 elements which are all filled and none of them is empty. Note that no row from column C is part of the Cells collection except first row that contains the header value. That is why this code does not find any empty cell. Please analyze this solution further and share exact steps to reproduce this issue here along with the version info that worked fine earlier for our reference.

5

Thank you for taking a look. The previous version of my code was working differently, it was opening an existing Excel file.

Can you please help with a code example that will get this example project working so that it can locate and update the empty cells in column C? I’m not understanding why column C isn’t part of the Cells collection.

Thank you!

6

@softwarelicense_aixgroup_com,
As you are not setting any value to the third column in the data table therefor it is not imported as value in the worksheet. You may try the following change in your code and share your feedback.

for (int i = 0; i < 10; i++)
{
    dataRow = dataTable.NewRow();
    dataRow["Number"] = i;
    dataRow["InsuredName"] = string.Format("Insured Name {0}", i);
    dataRow["CommonName"] = "";//Add this line
    dataTable.Rows.Add(dataRow);
}

Here is the program output after this change.

7

I now see what you’re saying about that field not having a value and I was able to get my code working.

In my original project, my DataTable is being populated from the results of a SQL query, and the particular field contains NULL values. I was able to handle this situation in my query and my Excel file is now correctly finding the empty cells.

Thank you for your help!

8

@softwarelicense_aixgroup_com,
You are welcome.