NPOI with ASP.NET (C#)

Question

I am trying to get NPOI to work with ASP.NET (C#) and I want to read an excel file and put it in a DataSet. Here is the code I attempted:

public static DataTable getExcelData(string FileName, string strSheetName)
{
    DataTable dt = new DataTable();
    HSSFWorkbook hssfworkbook;
    using (FileStream file = new FileStream(FileName, FileMode.Open, FileAccess.Read))
    {
        hssfworkbook = new HSSFWorkbook(file);
    }

    ISheet sheet = hssfworkbook.GetSheet(strSheetName);
    System.Collections.IEnumerator rows = sheet.GetRowEnumerator();

    while (rows.MoveNext())
    {
        IRow row = (HSSFRow)rows.Current;

        if (dt.Columns.Count == 0)
        {
            for (int j = 0; j < row.LastCellNum; j++)
            {
                dt.Columns.Add(row.GetCell(j).ToString());
            }

            continue;
        }

        DataRow dr = dt.NewRow();
        for (int i = 0; i < row.LastCellNum; i++)
        {
            ICell cell = row.GetCell(i);

            if (cell == null)
            {
                dr[i] = null;
            }
            else
            {
                dr[i] = cell.ToString();
            }
        }
        dt.Rows.Add(dr);
    }

    return dt;
}

The Error that I get is

+       $exception  {"Object reference not set to an instance of an object."}   System.Exception {System.NullReferenceException}

The odd thing is that this actually works with 2 excel files that I have, but when I put in a third one it crashes with that error.

Answer 1

This returns null if strSheetName isn't found:

ISheet sheet = hssfworkbook.GetSheet(strSheetName);

try:

for( int iSheet = 0; iSheet < hssfworkbook.NumberOfSheets; ++iSheet )
{
    ISheet sheet = hssfworkbook.GetSheetAt(iSheet); // could cast to HSSFSheet
    String strSheetNameActual = sheet.SheetName;
}

Then figure out how you want to compare strSheetName to strSheetNameActual or which sheets you want to process and how.