【一步一步学NPOI】10.导入Excel到DataTable

转自： http://www.cnblogs.com/colder/p/3611906.html

 public static DataTable ExcelToDataTable(string str_FilePath, int int_SheetIndex)
        {
            var extName = Path.GetExtension(str_FilePath);//扩展名
            DataTable dt = new DataTable("MyTable");//创建DataTable

            if (extName == ".xls" || extName == ".xlsx")
            {
                using (var fileStream = new FileStream(str_FilePath, FileMode.Open, FileAccess.Read))
                {
                    HSSFWorkbook workBook = new HSSFWorkbook(fileStream);
                    ISheet sheet = workBook.GetSheetAt(int_SheetIndex);

                    //遍历Excel表头，写入dt
                    foreach (var item in sheet.GetRow(sheet.FirstRowNum).Cells)
                    {
                        dt.Columns.Add(item.ToString(), typeof(string));
                    }

                    IEnumerator enumerator = sheet.GetRowEnumerator();//迭代器
                    while (enumerator.MoveNext())//遍历Excel行
                    {
                        IRow row = enumerator.Current as HSSFRow;//当前行
                        //Excel只有一行
                        if (row.RowNum == sheet.FirstRowNum)
                        {
                            continue;
                        }
                        DataRow dr = dt.NewRow();//datatable行对象
                        foreach (var item in row.Cells)
                        {
                            #region 转换数据类型

                            switch (item.CellType)
                            {
                                case CellType.Boolean:
                                    dr[item.ColumnIndex] = item.BooleanCellValue;
                                    break;
                                case CellType.Formula:
                                    dr[item.ColumnIndex] = item.CellFormula;
                                    break;
                                case CellType.Error:
                                    dr[item.ColumnIndex] = ErrorEval.GetText(item.ErrorCellValue);
                                    break;
                                case CellType.Numeric://数字类型
                                    if (DateUtil.IsCellDateFormatted(item))//日期
                                    {
                                        dr[item.ColumnIndex] = item.DateCellValue.ToString("yyyy/MM/dd");
                                    }
                                    else
                                    {
                                        dr[item.ColumnIndex] = item.NumericCellValue;//普通数字
                                    }
                                    break;
                                case CellType.String:
                                    dr[item.ColumnIndex] = item.StringCellValue;
                                    break;
                                case CellType.Blank:
                                case CellType.Unknown:
                                default:
                                    dr[item.ColumnIndex] = string.Empty;
                                    break;
                            }

                            #endregion

                        }
                        dt.Rows.Add(dr);
                    }
                }
            }
            return dt;
        }