How to get column index by column header name?

Aspose.Cells Product Family
1

Hi,

I have a question, in a worksheet, I want to get all the cell values under certain column header name. however, I don't know this specific column index and it might change in different file. How can I retrieve the cell value by using column header name?

By the way, I am using version 4.7.1.0

Thanks.

2

Hi,
Thanks for your inquiry.

Well, you may use CellsHelper.ColumnNameToIndex() static method your need.

See the sample code below:

Sample code:
Workbook workbook = new Workbook();
workbook.Open(“f:\test\Book1.xls”);
Worksheet sheet = workbook.Worksheets[“Sheet1”];
Cells cells = sheet.Cells;
int maxrow = cells.MaxDataRowInColumn(CellsHelper.ColumnNameToIndex(“S”));
string val1;
for (int i = 0; i <= maxrow; i++)
{
val1 = cells[i, <b>CellsHelper.ColumnNameToIndex(“S”)</b>].StringValue;
}
workbook.Save(“f:\test\outBook1.xls”);


Thank you.

3

Hi,

I tried it, but it returns me invalid column name error message. I tried your example above by changing my column name to S, it works, but not working on my original column name. Is there any limitation to name the column? here's my code sample. please let me know how I can fix it.

Workbook wb = new Workbook();

wb.Open("test.xls");

Cells cell = wb.Worksheets[0].Cells;

int column = CellsHelper.ColumnNameToIndex("ProposalID");

I have attached my test file to you as well.

Thanks.

4

Hi,

Thanks for providing us template file and sharing some details.

Well, since you want to get the column index of your internal custom column ("“ProposalID”") and not the worksheet’s column header(e.g A,B,C etc.), so, using CellsHelper.ColumnNameToIndex(“ProposalID”) will give you invalid column error and rightly so.

For your need, you should search the custom column heading string in the worksheet cells first, then obtain the column index from the found cell, finally you may loop through the cells in that column to obtain/read the values.

See the modified code below, I have tested it using your template file and it works fine. Kindly refer to it.

Sample code:

Workbook workbook = new Workbook();
workbook.Open(“f:\test\test.xls”);
Worksheet sheet = workbook.Worksheets[“test”];
Cells cells = sheet.Cells;
Aspose.Cells.Cell cell = cells.FindString(“ProposalID”,null); //null means it will search the string from the start (A1 cell).
int maxrow = cells.MaxDataRowInColumn(cell.Column);
MessageBox.Show(cell.Column.ToString());
string val1;
for (int i = 1; i <= maxrow; i++)
{
val1 = cells[i, cell.Column].StringValue;
MessageBox.Show(val1);
}
workbook.Save(“f:\test\out_test.xls”);



Thank you.

5

Hi Amjad,
the FindString method is marked obsolete referring to the Find method.

The example Find (FindOptions) Class FindOptions | Aspose.Cells for .NET API Reference
is a rather large chunk of code to simply find the column index by providing a column name (custom column name).

Is there a short cut?

6

@mortenma,

I think as we now provide everything in FindOptions for the Cells.Find(Object string/number/formula/comments_to_find, Cell previousCell, FindOptions findOptions) method, so there is no need to have FindString() method in the APIs list. Please set LookInType and LookAtType enum attributes of FindOptions accordingly for your requirements and use Cells.Find() method, it would work fine.

Well, it is an example to just demonstrate different find options for the users. I think you may skip the irrelevant options for your scenario.

We also recommend you to see the document/article for your further reference:

Let us know if we can be of any further help.