Wild Card Retrieval from sheet to DataTable


#1

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


#2

Hi Chris,

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

Have you tried the Find methods in Cells object?


#3

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.


#4

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?


#5

Yep, that looks like that would solve this issue.


#6

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.


#7

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)


#8

Yep that is more flexible, and would work.


#9

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