This project is read-only.

What`s the fastest way to iterate over several cells of excel file

Sep 17, 2009 at 5:56 PM
Edited Sep 17, 2009 at 6:00 PM

hi

   Im a newbiee whith NPOI and whish know what`s the fastest way to iterate over really  Several cells in a sheet, i haven`t the address of the cell until the time to read then it`s need to be arbitrary, because of this i think an iterator may be not userfull. I read about two models to work whith excel in NPOI, one to 97 and another one to most recent office format based in xml, what of two models are fastest ?

 

  I will need read and write a really crazy amount of data between sheets and relational database in a import/export model.

thanks a lot

 

Coordinator
Sep 18, 2009 at 10:40 PM
Edited Sep 24, 2009 at 6:07 AM

 You can use the following code:

HSSFSheet sheet = hssfworkbook.GetSheet(sheetname);
IEnumerator rows = sheet.GetRowEnumerator();
while (rows.MoveNext())
{
      HSSFRow row = (HSSFRow)rows.Current;
       for (int i = 0; i < row.LastCellNum; i++)
       {
               HSSFCell cell = row.GetCell(i);
               if (cell == null)
               {
                    continue;
               }

              <insert your code here to handle the data in every cell>
       }

}

Sep 24, 2009 at 3:58 AM
Edited Sep 24, 2009 at 4:20 AM

Hey, thanks a lot for answer so fast the gui. In code below the variable "rows" is get from "sheet.GetRowEnumerator();" but cant see the origin and the relationship in context of variable "row" in "row.GetCell(i);" its looks not related whith anything there and made me confused. I have the same goal, i need iterate over rows top/down whith an iterator but, over columns i have a hasmap of name/index of field/column and need iterate folowing the order in dynamic map given to method, i received this requirement then i need to do, above its fastway to it too ? I have problem whiht so long time process, 50 colums and more than 2000 rows in one of more small sheet in a web asp.net application. Do you think it in JSP+POI can be faster ?

HSSFSheet sheet = hssfworkbook.GetSheet(sheetname);
IEnumerator rows = sheet.GetRowEnumerator();
while (rows.MoveNext())
{

       for (int i = 0; i < row.LastCellNum; i++)<=   row coming from ??
       {
               HSSFCell cell = row.GetCell(i); <=   row coming from ?? IEnumerator havent GetCell method then I think it was not a variable name mistake
               if (cell == null)                            <=====   when cell can be null ?
               {
                    continue;
               }

              <insert your code here to handle the data in every cell>
       }

}

bye

Coordinator
Sep 24, 2009 at 6:10 AM
Edited Sep 24, 2009 at 6:13 AM

Sorry for the mistake. One line code is missing in the previous version. I changed the above reply.

Do you think it in JSP+POI can be faster ?
It should be same no matter you use NPOI in .NET or POI in Java. Which language and platform you should use totally depends on your requirement but not the performance. If you find any performance issue, please let me know

when cell can be null ?
Since Excel creates row and cell records only when there are data in the rows and cells, if no data or formattings in a cell, then the cell will be null.