How to list each Sheet and and each Column name in Workbook??

May 1, 2011 at 2:51 PM
Edited May 1, 2011 at 3:25 PM

I want to list each worksheet, then list each column name for each worksheet. Below is the code I've come up with but it seems hacky. I would have expected a GetAllSheets and GetColumns, or something along those lines instead of what I've done below.

Actually in the workbook object I see that there's a _sheets field that's private. Wonder why that's not exposed? It would be very handy.

            HSSFWorkbook workbook = new HSSFWorkbook(fs, true);
            var sheets = new List<Sheet>();
            for(int i=0; i<workbook.NumberOfSheets;i++)
                sheets.Add(workbook.GetSheetAt(i));

            var x = sheets[0].GetRow(0);

            var cellEnum =x.GetCellEnumerator();
            while (cellEnum.MoveNext())
            {
                var cell = (Cell)cellEnum.Current;
                var cellVal = cell.StringCellValue;
            }

 

Thanks!!

May 6, 2011 at 8:46 PM

@ericblair: I've been using the library for about 3 weeks or so now and haven't found anything better as far as loading the sheets. The GetSheetAt() method seems to be the most effective means of gathering this data. In looking at the POI library, I found a reference to http://people.apache.org/~nick/Talks/ApacheConEu09/WhatsNewWithPOI.pdf that mentions getting all sheets via an iterator. However, in my dives into the NPOI source code, I don't remember seeing this so it might not be implemented yet. The method that you're using is essentially what I'm using in my code and it's pretty fast, especially if you've been using the Excel Interop. Not sure about the memory footprint for this use, but I haven't noticed any problems at my end.

Coordinator
May 8, 2011 at 4:55 AM

In fact, column is not a real instance in xls file. How about two column rows put in one sheet instance of xls? Or how about the colume row appearring at the second line or the third line? So I don't agree on providing a GetColumns method in HSSFSheet class.

GetSheetAt(x) is a good way to get the sheet you want in a loop. x here is a zero-based index. x must be less than NumberOfSheets.