How to determine size of a column with data + retrieval of said data.

[Cheong Ze Wei]

Hi, I have an excel worksheet with a number of columns, each with a different amount of rows.

I can't find the code in xlsio to get the length of each column with data. Also, is my understanding correct that we can store the data into an array?

Any help would be appreciated, thanks.

[Konduru Keerthi Konduru Ravichandra Raju]

Hi Cheong, 

Greetings from Syncfusion.  

You can get the last row in each column in the used range using the below code snippet. 

IWorksheet worksheet = workbook.Worksheets[0]; 

worksheet.UsedRangeIncludesFormatting = false; 

IRange range = worksheet.UsedRange;                 

foreach(IRange column in range.Columns) 

{ 

    string columnName = Syncfusion.XlsIO.Implementation.RangeImpl.GetColumnName(column.Column); 

    int lastRow = 0; 

    for (int i = column.LastRow; i >= 1; i--) 

    { 

        if (!column.Rows[i - 1].IsBlank) 

        { 

            lastRow = i; 

            break; 

        } 

    } 

    Console.WriteLine("LastRow in Column "+columnName+" is " +lastRow); 

} 

Console.ReadLine(); 

Regarding retrieval of data into array, Syncfusion XlsIO do not have direct support to export data from worksheet to array. Instead you can export data from worksheet to data table and then load the data from data table to array.  

Please look into the below link to know more about loading data from Excel worksheet to DataTable. 

https://help.syncfusion.com/file-formats/xlsio/working-with-data?cs-save-lang=1&cs-lang=csharp#exporting-from-worksheet-to-data-table  

Kindly try the suggestions and let us know if these helps. 

Regards, 

Keerthi.