using System;
using System.Collections.Generic;
using System.Linq;
using System.Windows.Forms;
using System.Data;
using System.IO;
using System.Web;
using NPOI;
using NPOI.HPSF;
using NPOI.HSSF;
using NPOI.HSSF.UserModel;
using NPOI.POIFS;
using NPOI.Util;
using System.Text;
 
namespace exceltodataset
{
    public static class Program
    {
        /// <summary>
        /// 由Excel导入DataSet,如果有多个工作表,则导入多个DataTable
        /// </summary>
        /// <param name="excelFileStream">Excel文件流</param>
        /// <param name="headerRowIndex">Excel表头行索引</param>
        /// <returns>DataSet</returns>
        public static DataSet ImportDataSetFromExcel(Stream excelFileStream, int headerRowIndex)
        {
            DataSet ds = new DataSet();
            HSSFWorkbook workbook = new HSSFWorkbook(excelFileStream);
            for (int a = 0, b = workbook.NumberOfSheets; a < b; a++)
            {
                HSSFSheet sheet = workbook.GetSheetAt(a);
                DataTable table = new DataTable();                HSSFRow headerRow = sheet.GetRow(headerRowIndex);
                int cellCount = headerRow.LastCellNum;
                for (int i = headerRow.FirstCellNum; i < cellCount; i++)
                {
                    if (headerRow.GetCell(i) == null || headerRow.GetCell(i).StringCellValue.Trim() == "")
                    {
                        // 如果遇到第一个空列,则不再继续向后读取
                        cellCount = i + 1;
                        break;
                    }                    DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
                    table.Columns.Add(column);
                }                for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
                {
                    HSSFRow row = sheet.GetRow(i);
                    if (row == null || row.GetCell(0) == null || row.GetCell(0).ToString().Trim() == "")
                    {
                        // 如果遇到第一个空行,则不再继续向后读取
                        break;
                    }                    DataRow dataRow = table.NewRow();
                    for (int j = row.FirstCellNum; j < cellCount; j++)
                    {
                        if (row.GetCell(j) != null)
                        {
                            dataRow[j] = row.GetCell(j).ToString();
                        }
                    }                    table.Rows.Add(dataRow);
                }
                ds.Tables.Add(table);
            }            excelFileStream.Close();
            workbook = null;            return ds;
        }
 
        [STAThread]
       public static void Main()
        {
                /// <summary>
     /// 由Excel导入DataSet,如果有多个工作表,则导入多个DataTable
     /// </summary>
     /// <param name="excelFilePath">Excel文件路径,为物理路径。</param>
     /// <param name="headerRowIndex">Excel表头行索引</param>
     /// <returns>DataSet</returns>
     public static DataSet ImportDataSetFromExcel(string excelFilePath, int headerRowIndex)
     {
         using (FileStream stream = System.IO.File.OpenRead(excelFilePath))
         {
             return ImportDataSetFromExcel(stream, headerRowIndex);
         }
     }
        }
    }
}