NPOI 读取 excel通用类

using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Data;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;

namespace NPOI2
{
    /// <summary>
    /// Excel读取类（将excel数据转换成DataTable/DataSet返回)   
    /// </summary>
    public class ExcelReader
    {
        HSSFWorkbook hssfworkbook;
        public ExcelReader(string xlsPath)
        {
            InitializeWorkbook(xlsPath);
        }
        void InitializeWorkbook(string path)
        {
            //read the template via FileStream, it is suggested to use FileAccess.Read to prevent file lock.
            //book1.xls is an Excel-2007-generated file, so some new unknown BIFF records are added. 
            using (FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read))
            {
                hssfworkbook = new HSSFWorkbook(file);
            }
        }

        /// <summary>
        /// 将excel的数据转换成dataset返回
        /// </summary>
        /// <returns></returns>
        public DataSet ReadAllSheet()
        {
            DataSet ds = new DataSet();
            for (int i = 0; i < hssfworkbook.NumberOfSheets; i++)
            {
                ds.Tables.Add(ConvertToDataTable(i));
            }
            return ds;
        }

        /// <summary>
        /// 将第sheetIndex个工作簿转换成DataTable
        /// </summary>
        /// <param name="sheetIndex">工作簿索引</param>
        /// <returns></returns>
        public DataTable ConvertToDataTable(int sheetIndex)
        {
            ISheet sheet = hssfworkbook.GetSheetAt(sheetIndex);
            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();

            DataTable dt = initDT();
            while (rows.MoveNext())
            {
                IRow row = (HSSFRow)rows.Current;
                DataRow dr = dt.NewRow();

                for (int i = 0; i < row.LastCellNum && i < 255; i++)
                {
                    ICell cell = row.GetCell(i);
                    if (cell == null)
                    {
                        dr[i] = null;
                    }
                    else
                    {
                        dr[i] = cell.ToString();
                    }
                }
                dt.Rows.Add(dr);
            }
            return dt;
        }

        /// <summary>
        /// 构造一个255列的空表
        /// </summary>
        /// <returns></returns>
        private DataTable initDT()
        {
            DataTable dt = new DataTable();
            for (int j = 0; j < 255; j++)
            {
                dt.Columns.Add("F" + (j + 1).ToString());
            }
            return dt;

        }
    }
}

如果您觉得好用，请进入下面的网址，付费支持作者 ~

https://me.alipay.com/tommysz

谢谢！

注：需要引用NPOI.dll