c# Read multiple Sheets of an excel file according to NPOI

Everyone knows that NPOI components can be read and created excel files without your local office installed. However, people usually only read the first sheet of an excel file by default. So what should I do if I want to read all sheets of an excel?

Let’s tell you how to operate NPOI to read all sheets of excel.

First, let's explain the operation of a class created separately by excel. I named it EXECLHELP

using ;
using ;
using ;
using System;
using ;
using ;
using ;
using ;
using ;
using ;
 
 
  public class ExcelHelper : IDisposable
        {
            private string fileName = null; //file name            private IWorkbook workbook = null;
            private FileStream fs = null;
            private bool disposed;
 
            public ExcelHelper(string fileName)
            {
                 = fileName;
                disposed = false;
            }
 
            /// &lt;summary&gt;
            /// Import DataTable data into excel            /// &lt;/summary&gt;
            /// <param name="data">Data to be imported</param>            /// <param name="isColumnWritten">Whether the column names of DataTable are imported</param>            /// <param name="sheetName">The name of the sheet of excel to be imported</param>            /// <returns>Import data row count (including which row of column name)</returns>            public int DataTableToExcel(DataTable data, string sheetName, bool isColumnWritten)
            {
                int i = 0;
                int j = 0;
                int count = 0;
                ISheet sheet = null;
 
                fs = new FileStream(fileName, , );
                if ((".xlsx") &gt; 0) // 2007 version                    workbook = new XSSFWorkbook();
                else if ((".xls") &gt; 0) // 2003 version                    workbook = new HSSFWorkbook();
 
                try
                {
                    if (workbook != null)
                    {
                        sheet = (sheetName);
                    }
                    else
                    {
                        return -1;
                    }
 
                    if (isColumnWritten == true) //Write the column name to the DataTable                    {
                        IRow row = (0);
                        for (j = 0; j &lt; ; ++j)
                        {
                            (j).SetCellValue([j].ColumnName);
                        }
                        count = 1;
                    }
                    else
                    {
                        count = 0;
                    }
 
                    for (i = 0; i &lt; ; ++i)
                    {
                        IRow row = (count);
                        for (j = 0; j &lt; ; ++j)
                        {
                            (j).SetCellValue([i][j].ToString());
                        }
                        ++count;
                    }
                    (fs); //Write to excel                    return count;
                }
                catch (Exception ex)
                {
                    ("Exception: " + );
                    return -1;
                }
            }
 
            /// &lt;summary&gt;
            /// Import data from excel into DataTable            /// &lt;/summary&gt;
            /// <param name="sheetName">The name of the excel worksheet</param>            /// <param name="isFirstRowColumn">Is the first row the column name of the DataTable</param>            /// <returns>Returned DataTable</returns>            ///
 
 
                public Dictionary&lt;int,string&gt; ReturnSheetList()
            {
                Dictionary&lt;int, string&gt; t = new Dictionary&lt;int, string&gt;();
                ISheet sheet = null;
                DataTable data = new DataTable();
                int startRow = 0;
                    try
                    {
                        fs = new FileStream(fileName, , );
                        if ((".xlsx") &gt; 0) // 2007 version                            workbook = new XSSFWorkbook(fs);
                        else if ((".xls") &gt; 0) // 2003 version                            workbook = new HSSFWorkbook(fs);
                        int count = ; //Get all SheetName                        for(int i=0;i&lt;count;i++)
                        {
                            sheet = (i);
                            if ( &gt; 0)
                            {
                                (i, (i).SheetName);
                            }
                        }
                        return t;
                    }
                    catch (Exception ex)
                    {
                        throw new Exception();
                    }
                  
 
            }&lt;br&gt;///Index excel's first sheet            public DataTable ExcelToDataTable(int index)
            {
                ISheet sheet = null;
                DataTable data = new DataTable();
                int startRow = 0;
                try
                {
                    fs = new FileStream(fileName, , );
                    if ((".xlsx") &gt; 0) // 2007 version                        workbook = new XSSFWorkbook(fs);
                    else if ((".xls") &gt; 0) // 2003 version                        workbook = new HSSFWorkbook(fs);
                    //int coutnts = ;
 
                    sheet = (index);
                    //string names= ;
                    if (sheet != null)
                    {
                        IRow firstRow = (0);
                        int cellCount = ; //The number of the last cell in a row is the total number of columns 
 
                        for (int i = ; i &lt; cellCount; ++i)
                        {
                            ICell cell = (i);
                            CellType c = ;
                            if (cell != null)
                            {
                                string cellValue = ;
                                if (cellValue != null)
                                {
                                    DataColumn column = new DataColumn(cellValue);
                                    (column);
                                }
                            }
                        }
                        startRow =  + 1;
 
 
                        //The label of the last column                        int rowCount = ;
                        for (int i = startRow; i &lt;= rowCount; ++i)
                        {
                            IRow row = (i);
                            if (row == null) continue; //The default is null for rows without data. 
                            DataRow dataRow = ();
                            for (int j = ; j &lt; cellCount; ++j)
                            {
                                if ((j) != null) // Similarly, cells without data are null by default                                    dataRow[j] = (j).ToString(); 
                            }
                            (dataRow);
                        }
                    }
 
                    return data;
                }
                catch (Exception ex)
                {
                    return null;
                    throw new Exception();
 
                }
            }
 
            public void Dispose()
            {
                Dispose(true);
                (this);
            }
 
            protected virtual void Dispose(bool disposing)
            {
                if (!)
                {
                    if (disposing)
                    {
                        if (fs != null)
                            ();
                    }
 
                    fs = null;
                    disposed = true;
                }
            }
        }&lt;br&gt;&lt;br&gt;

DataTableToExcel This method refers to exporting data to excel, and the parameters are commented in the code, so you can apply it directly. ExcelToDataTable This method mainly imports excel data into databtable. Similarly, the parameters are also in the comments. Let's mainly talk about the ReturnSheetList method. Before reading, we need to determine whether the imported Excel version is a higher or lower version. This is because the operation classes provided by npoi are different. The version greater than 03 and less than 07 provide HSSFWorkbook, and the version less than 07 provides XSSFWorkbook. Then this mainly gets how many sheets there are in an excel file. We read the sheet according to the loop traversal and then pass the name of the sheetname and the corresponding index into a data dictionary to save. So this data dictionary contains all the content sheets and corresponding indexes in the excel file you imported. Using ExcelToDataTable can achieve the purpose of switching to read different sheets of an excel.

The above is the detailed content of c# reading multiple sheets in an excel file according to NPOI. For more information about c# reading sheets in excel, please follow my other related articles!