Aspose CheckCell vs Aspose WorkSheet.Cell[r,c]

Question

My excel has millions of records (approx 1.1M) and when I am trying to read cell by cell using WorkSheet.Cells[row,cloumn] it is very slow.

Rather if I use, WorkSheet.Cells.CheckCell(row, column) performance is very good.

Can you please let me know the difference between these two API's

shakeel · Accepted Answer · 2018-02-19T23:45:27.003

Worksheet.Cells.CheckCell() is the most right way to check if the cell exists or not.

CheckCell method will not instantiate the cell if it does not exist. On the other hand, Cells[r, c] will instantiate the cell if it does not exist.

It means,

CheckCell can return null or cell object.

But Cells[r, c] will never return null and will always return cell object.

Since Cells[r, c] will always return cell object and if it does not already exist, it will create it, this is why, it affects the performance. If you want to iterate all the existing cells in your worksheet without creating new cells, please use Worksheet.Cells.GetEnumerator() method.

Please see the following sample code, its comments and console output for a reference.

C#

//Create a workbook
Workbook wb = new Workbook();

//Access first worksheet
Worksheet ws = wb.Worksheets[0];

//At the moment, Cell B4 does not exist
//Therefore check cell will return null
Cell cell = ws.Cells.CheckCell(3, 1);
Console.WriteLine(cell == null); //<<<<<<<<<< It will print - True

//After this statement, cell B4 will be instantiated and it will exist
var o = ws.Cells[3, 1];

//Now check cell will not return null, but it will return cell B4
cell = ws.Cells.CheckCell(3, 1);
Console.WriteLine(cell == null);//<<<<<<<<<< It will print - False

Console Output

True
False

Update - 1

Please see the following sample code, its comments and console output. It illustrates that GetEnumerator() method returns non-empty and empty cells as well.

C#

//Create a workbook
Workbook wb = new Workbook();

//Access first worksheet
Worksheet ws = wb.Worksheets[0];

//Create empty cells
//Cell A4, B6, C8 and D10 all are empty
var o = ws.Cells["A4"];
o = ws.Cells["B6"];
o = ws.Cells["C8"];
o = ws.Cells["D10"];

//Get cells enumerator
var i = ws.Cells.GetEnumerator();

//Iterate all cells
while(i.MoveNext())
{
    Cell cell = i.Current as Cell;

    //Print cell name
    Debug.WriteLine(cell.Name);
}

Console Output

A4
B6
C8
D10

Note: I am working as Developer Evangelist at Aspose

Thank you. GetEnumerator is really effective, but.my Excel can contain empty cells in a existing data row. I need to copy exact same data in excel, to SQL table. If I use this method these empty cells getting ignored. Is there any workaround to consider empty cells while using GetEnumerator API. — JyothiJ, Feb 19 '18 at 16:54
Please check the update in reply. It explains that GetEnumerator() method returns empty cells as well. Thanks. — shakeel, Feb 19 '18 at 23:46

Aspose CheckCell vs Aspose WorkSheet.Cell[r,c]

1 Answers1