1. Home
  2. Forum
  3. ASP.NET Web Forms
  4. Enumerating Columns and wrong values

Enumerating Columns and wrong values

Hello,

I found strange thing. I have Excel file and I need to enumerate all cells to get values and process them.

So I have something like:

for (int i = 1; i < sheet.Rows.Length + 1; i++)

for (int j = 1; j < sheet.Columns.Length + 1; j++)

{

var value = sheet[i, j].Value;

}

Looks fine, but sometimes "sheet.Columns" is wrong. Excel has 47 columns and property shows 45, so some columns are not processed.

I found that there is another property - sheet.UsedRange.LastColumn and value is 47.

And sheet .Columns.Length == 45 and sheet.Columnss.Length == 47.

So what is proper way to enumerate cells per row/col? Why both values are different?

Regards

René


7 Replies
AV Abirami Varadharajan Syncfusion Team May 28, 2018 09:28 AM UTC

Hi Rene, 

Thank you for contacting Syncfusion support. 

Both ‘sheet.Columns.Length’ and ‘sheet.UsedRange.Columns.Length’ property should return the total used column in the worksheet. However, it is recommended to iterate through the used range’s row and column using IMigrantRange to improve better performance while working with large data at your end. 

Please refer to below documentation to know more about usedRange property and accessing the range using IMigrantRange. 


We have also shared simple sample for your reference which can be downloaded from following link. 


If your scenario differs from this, please update the sample as per your scenario and get back to us with the modified sample which will be helpful for us to validate further from our side and to provide prompt solution at the earliest. 

Regards, 
Abirami. 


RS René Spišák May 28, 2018 08:09 PM UTC

Hello,

your sample application says:

Value of column length using worksheet.Columns.Length property:12
Value of column length using worksheet.UsedRange.Columns.Length property:12

But file input.xlsx has 14 columns, A-N, not 12. You can check worksheet.UsedRange.LastColumn to get 14.
I need to enumerate all columns.

How does this Column collection work? Why there are not all Columns? There is another property Columnss with correct length.

Regards
René

AV Abirami Varadharajan Syncfusion Team May 29, 2018 01:06 PM UTC

Hi Rene, 

The column collection returns the count from first used column to last used column. The blank columns before the first used column is not included in the column collection ‘sheet.Columns and ‘sheet.UsedRange.Columns’. This is the behavior.  
 
If you wish to iterate all the cells including blank columns till last used cell in the worksheet, please make use of below code to achieve your requirement. 

Code snippet:  
IWorksheet sheet = workbook.Worksheets[0];  
IMigrantRange migrantRange = sheet.MigrantRange;  
int rowCount = sheet.UsedRange.LastRow;  
int colCount = sheet.UsedRange.LastColumn;  
for (int i = 1; i <= rowCount; i++)  
{  
    for (int j = 1; j <= colCount; j++)  
    {  
        migrantRange.ResetRowColumn(i,j);  
        string value = migrantRange.Value;  
    }  
}  

Regards, 
Abirami 


RS René Spišák May 31, 2018 07:48 AM UTC

Hello,

ok, thnx, I understand.

Just one more question - what is difference between worksheet.Columns and worksheet.Columnss collections?

Regards
René

AV Abirami Varadharajan Syncfusion Team June 1, 2018 01:05 PM UTC

Hi Rene,
 
Thank you for updating us.
 
There is no API worksheet.Columnss in XlsIO. However, the property worksheet.Columns should return the collection of total used columns in the worksheet.
 
Kindly check this at your end and let us know if you have any concerns with valid screenshot illustrating the API.
 
Regards,
Abirami


RS René Spišák June 1, 2018 01:35 PM UTC

Hello,

it is just some internal property. I saw it in debug, so it doesn't matter.



Thnx for help

Regards
René

MC Mohan Chandran Syncfusion Team June 4, 2018 07:15 AM UTC

Hi Rene, 
 
Thank you for the update. 
 
Regards, 
Mohan Chandran. 


Loader.
Up arrow icon