Wild Card Retrieval from sheet to DataTable


Do you have a way to search for a range of data with a wild card?

For Example:

A1:A* would return all data where it is within the manipulated rows of the spreadsheet.

I know that I can replace the * with some large number, ie 65k, but that is not very efficient, and even less efficient when I want to delete the bottom rows that are all null values.

- Chris


Hi Chris,

Could you elaborate your need? I don’t understand it clearly.

Have you tried the Find methods in Cells object?


I have spreadsheets in which the columns are specified, but the number of rows can vary. So, with a column of B1:D*, I would like to search from the first to the last and return the DataTable of all in between. I am taking a look at the find method, but it looks like I have to specify the last cell I am looking for.

OLEDB does this by giving back only the rows on the space that has been manipulated, and then I would delete the last rows that I do not need for the columns that I specified. My client has mentioned that your tool is better than OLEDB for later maintenance.


Well, I will supply a method to let you get the end of cell in a colum or row, such as:

In Cells object,

public Cell EndCellInRow(int rowIndex)
public Cell EndCellInColumn(byte columnIndex)

Could that meet your need?


Yep, that looks like that would solve this issue.


Wait, sorry, would that method just return cells with values? or “end cell”? With the above method, I could do a work around, but that would still involve looping and finding all the end cells of each column in the specified range and then taking the greatest.


How about the following methods?

public Cell EndCellInColumn(int startRow, int endRow, byte startColumn, byte endColumn)

public Cell EndCellInRow(int startRow, int endRow, byte startColumn, byte endColumn)


Yep that is more flexible, and would work.


Now it’s available. Please download the latest hotfix and refer to http://www.aspose.com/Products/Aspose.Excel/Api/Aspose.Excel.CellsMembers.html.