Issue Title: Cell Enumerator Incomplete Iteration in Used Range
Product: Aspose.Cells
Version: 21.5.0.0
Description: I am facing an issue with the Cell Enumerator in Aspose.Cells. In my Excel worksheet, Column C has a used range from C1 to C434. However, when I use the Cell Enumerator to iterate through the cells in this column, it only goes up to C223, instead of continuing through to C434. In contrast, the Row Enumerator successfully processes all rows up to 434.
This discrepancy is causing problems in my data processing logic, as I cannot access the full range of cells in Column C.
Expected Behavior: The Cell Enumerator should iterate through all cells from C1 to C434.
Actual Behavior: The Cell Enumerator only processes cells up to C223.
Additional Information:
- I have attached the relevant Excel file for reference.
- The Row Enumerator works correctly and processes all rows to 434 without issue.
I am also uploading the file which I am having problem.
I am attaching a sample code with the file in it.
Cell_Range_Loop_Test.zip (151.9 KB)
@harshCIPL22,
Thanks for the sample file and sample app.
I checked your code segment and tested your scenario/case using your sample project and sample file. I think it is expected behavior.
Dim rowsEnumerator As IEnumerator = worksheet.Cells.Rows.GetEnumerator()
Please note, for Row enumerator, it will give you rows items which are initialized (even if a single cell is initialized for the whole row, it will count to Row). For Cell enumerator, it will give you cells which are initialized only for the column. So, it seems the results are OK for the scenario/case.
Hi @amjad.sahi ,
Thank you for your response, but I believe there may be a misunderstanding. The issue I am experiencing is not related to the solution provided.
To clarify, my problem specifically involves the Cell Enumerator only iterating up to C223 in a used range that extends to C434. The Row Enumerator works correctly for all rows, so the issue appears to be with the Cell Enumerator itself.
I have attached a screenshot to illustrate the problem more clearly.
I appreciate your help, and I’d be grateful for any further insights you might have on this matter.
Thank you!
image.png (16.3 KB)
@harshCIPL22
For enumerators of row/column/cell, they only provide those Row/Column/Cell objects that have been initialized. That is, you will get those existing objects in the collection only. It is for performance consideration so user may access those existing objects efficiently. If you need to manipulate all cells in the range, please use their indedices to access them one by one.
Thanks for the explanation but if those cells are not initialized which are not coming from the Used range of that C column so i guess that should not also come in the used range too.
I am getting the used range from C1:C434 so its should traverse too C434.
My Concern is also that on which logic the cells are getting eliminate from the used range after C224 Cell! Either the rest of the cells are not Initialized then the used range should come from C1:C224.
@harshCIPL22 ,
The issue is the code in “Row Enumerator”:
Dim cell As Cell = worksheet.Cells(row.Index, columnIndex)
worksheet.Cells(row.Index, columnIndex) will init a new instance even if the cell is not existed. If you move the “Row Enumerator” code before “Column Enumerator” and “Cell Enumerator”, the output of “Column Enumerator” and “Cell Enumerator” will change.
For your logic, Please change worksheet.Cells(row.Index, columnIndex) in “Row Enumerator” to worksheet.Cells.CheckCell(row.Index, columnIndex), it will return null if cell is not existed.
'Row Enumerator
Dim worksheet As Worksheet = objWorkbook.Worksheets(0) 'Raw_Data
Dim columnIndex As Integer = 2 ' Column C
Dim nonEmptyRowCount As Integer
Dim rowsEnumerator As IEnumerator = worksheet.Cells.Rows.GetEnumerator()
While rowsEnumerator.MoveNext()
Dim row As Aspose.Cells.Row = DirectCast(rowsEnumerator.Current, Aspose.Cells.Row)
Dim cell As Cell = worksheet.Cells.CheckCell(row.Index, columnIndex)
If cell IsNot Nothing Then
If Not String.IsNullOrEmpty(cell.StringValue) Then
nonEmptyRowCount += 1
End If
txtRowEnumerator.AppendText(cell.Name & vbCrLf) ' Print the value of the cell in column C
End If
End While
The output of the three "Enumerators will be consistent.
@harshCIPL22
int preRow = range.FirstRow - 1;
int preCol = range.FirstColumn - 1;
Cell lastCell = null;
for(IEnumerator ie = range.GetEnumerator(); ie.MoveNext(); )
{
Cell cell = (Cell)ie.Current;
if(cell.Row != preRow + 1)
{
//there are some skipped empty rows and cells.
//......
preCol = 1;
preRow = cell.Row - 1;
}
if(cell.Column != preCol + 1)
{
//there are some skipped cells.
preCol = cell.Column - 1;
}
lastCell = cell;
//export current cell
preRow = cell.Row - 1;
preCol = cell.Column - 1;
}
if (lastCell == null)
{
//no cells
} else if (lastCell.Row != range.FirstRow + range.RowCount - 1)
{
//there are some empty rows
}
range.GetEnumerator() only returns the cell which has been initialized.
@peyton.xu
I am not using any of these Enumerator of Cells/Rows/Columns. I used these to just show you that the behaviour of each is different and why it behave that is explained by @johnson.shi very well.
I am just getting the range and using the range Enumerator for the my code and in that code the range I am getting is C1:C434 in objRange.GetEnumerator and while I am looping through the objRange.MoveNext, I am just able to get the 225 Cells not the actual total cell 434. That is the main issue for me here. i want to solve that.
I just check with the different Enumerators to verify my code. but the output is different based on the Cells/Rows/Columns.
Dim lstRanges As New List(Of Aspose.Cells.Range)
lstRanges.Add(objWorksheet.Cells.MaxDisplayRange)
If Not IsNothing(lstRanges) Then
For Each objAnalysisRange In lstRanges
'Column Loop - START
For intCol As Integer = objAnalysisRange.FirstColumn To objAnalysisRange.FirstColumn + objAnalysisRange.ColumnCount - 1
Dim strColumnName As String = String.Empty
strColumnName = CellsHelper.ColumnIndexToName(intCol)
arrRange(0) = strColumnName 'Column Name
Dim PrevColName = If(intCol > 0, CellsHelper.ColumnIndexToName(intCol - 1), "")
If Not String.IsNullOrEmpty(PrevColName) Then
sbOutputData.AppendLine(":" & PrevColName & intRowCounter & vbCrLf)
End If
intRowCounter = 1
sbOutputData.Append(strColumnName & "" & intRowCounter)
arrRange(2) = intRowCounter 'Start Range
Dim lngMaxDataRow As Long = objWorksheet.Cells.MaxRow
Dim objRange As Range = Nothing
objRange = objWorksheet.Cells.CreateRange(strColumnName & (objWorksheet.Cells.MinDataRow + 1) & ":" & strColumnName & (lngMaxDataRow + 1))
arrRange(1) = objRange.Address 'Range
Dim objRangeEnumerator As IEnumerator = objRange.GetEnumerator
While objRangeEnumerator.MoveNext()
Dim strCellAddress As String = String.Empty
objCell = CType(objRangeEnumerator.Current, Aspose.Cells.Cell)
strCellAddress = objCell.Name
intRowCounter += 1
End While
arrRange(3) = intRowCounter 'End Range
dtData.Rows.Add(arrRange)
Next
Next
End If
image.png (82.5 KB)
@harshCIPL22
As we said in the previous post, range Enumerator only returns initialized cell(which contains data or style in the file, and if you call cells[string] , cells[row,column], Range[rowOffset, columnOffset] )
So if cell 434 does not contain anything and doest not exist in the file, we will not initialize it,so you can not get it by the range Enumerator. As it’s meanless, you also can ignore it.
If you want to get all cells, please use Range[rowOffset, columnOffset] according to Range.RowCount and Range.ColumnCount, but it will create many useless Cell object.
And you can check codes in Cell Enumerator Incomplete Iteration in Used Range - #7 by simon.zhao. Then you can process those empty cells by yourself.