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.