We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

How to get rowIndex of record- searching by 2 columns' values

Aspose.Cells Product Family
#1

Hi,

I'm new to Aspose Cells .NET and I'm stuck with an issue. Please share some inputs on how to solve this issue. It would be grateful if you could reply me on this ASAP.

I have an excel worksheet something like the below.

Worksheet1

Column1 Column2 Column3
1 a 1
2 a 2
4 b 4
3 b 3
5 a 5

I have to get the rowindex of the record with Column2 value = 'b' and Column3 value = '4'. How do I get a record's rowIndex, by passing 2 columns' values of that record.

Can you please let me know how do i get it. Thanks a lot for helping me on this.

Thanks.

#2

Hi,


Well, please first see the topic:
http://www.aspose.com/docs/display/cellsnet/Find+or+Search+Data

You need to first find out the cell (with row, col indices using cell.row and cell.column properties) that contains "b" value. Then you will browse other columns in the same row (where you found "b" value of the cell). If you find "4", then that's it, the row index is final, if not, then you have to search "b" again for other cells.... etc. You may implement your desired logic accordingly using Aspose.Cells APIs.

Thank you.

#3

Thanks a lot for your response. This approach is fine if I have very less records in the worksheet. But if i have a worksheet with 10 lakhs records this approach will lead to performance issue. is there any way to search for a record with multiple column values at a time. Thanks again for clarifying this.

#4

Hi,


Well, Aspose.Cells’ Find methods are efficient enough and it may search your data in the huge data list in very quick time, so there will be no performance issue no matter how many records (e.g 1 million records) are there in a worksheet. The Find functions provided by Aspose.Cells are equally matched with Find options provided by MS Excel regarding performance.

I think for your case, as you will first find a value e.g “b”, when you found the value and get the Cell object (you will already have cell.Row index to know the cell’s row index for the found value), now you may either compare your other value (in the same row) scanning the next column or even looping through all other columns (if you want and according to your needs) for your needs. Here, you can even use another nested if/select case and use Find method again to search the other value (e.g “4”) (giving the search criteria/range equal to same row) to find the value more efficiently if you have lots of columns to be search for your other value.

Moerover, currently, neither Aspose.Cells nor MS Excel has any feature that could search a record based on multiple columns values. So, in short, you have to use your own code (accordingly) following my mentioned approach (using Aspose.Cells Find methods), there should be no performance issue.

Thank you.
#5

I understand. Thanks a lot for your detailed explanation.

#6

Hi,

Thanks for using Aspose.Cells.

You cannot search records using the multiple column values and you can only search with a single column value and will have to devise your own algorithm to achieve this as mentioned above.

If you still have any questions or you face any issues regarding Aspose.Cells, please feel free to post, we will be glad to help you further.

#7

Hi,

We have further looked into your issue and found that there is an alternative approach to it.

You can make use of DGET formula, it will enable you to find a value based on multiple column keys.

Please see the attached sample xlsx file.

Once, you will change the input values, it will find the new searched value against your entered key.

#8

Hi Amjad,

since we are same kind of requirement, instead of creating new thread i am using the same ticket. Here my requirement is to findout the row index based on 3 column values(previously it was “b” and “4”). Based on my requirement, Do we have any new method available in aspose to perform this search or we need to follow your suggestion only ?

if we need to follow your suggestion could you please share the same code.

Regards,
Harnath

#9

@nagaharnath,
I am afraid that there is no such special function which can be used to fulfil your requirements. Please share your sample file and steps to perform the same task in MS Excel. We will try to provide you with assistance to perform the same steps using Aspose.Cells.

Regarding the sample code, please try to implement logic as per your needs and if face some problem, please share the runnable console application along with the sample data for our testing. We will analyse the code and provide assistance accordingly.