How can I query a worksheet- and return a cell reference?

Given: The ExportDataTable method can be very handy when you want to query for data in a worksheet.

Question: What if you want to query a worksheet, and return the cell reference(s) of the location for which your data resides?

For Example:

Let's say I have a very simple sheet with header columns in the 1st row:

A B
1 First_Name Last_Name
2 Peyton Manning
3 Tom Brady
4 Philip Rivers

And I want to know the cell reference for 'First_Name' where 'Last_Name' is 'Manning'. (A2 in this example)

Can the Aspose Cells API help me?

Hi,

Well, there is no direct way to query a worksheet in Cells.ExportDataTable method. But I think there are number of ways to do it, For example, once you fill a data table from a worksheet using ExportDataTable method, you may find your desired record filtering the data table using .NET APIs.
e.g

Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
workbook.Open(@“e:\test\querytable.xls”);
Aspose.Cells.Worksheet worksheet = workbook.Worksheets[0];
DataTable dt = worksheet.Cells.ExportDataTable(0, 0, worksheet.Cells.MaxRow + 1, worksheet.Cells.MaxColumn + 1, true);
DataRow[] dr = dt.Select(“Last_Name=‘Manning’”);

int index = workbook.Worksheets.Add();
Aspose.Cells.Worksheet worksheet2 = workbook.Worksheets[index];

for(int i = 0; i<dr.Length;i++){

worksheet2.Cells.ImportDataRow(dr[0],i,0);
}
workbook.Save(“e:\test\outquerytable.xls”);


Thank you.