2018-06-04 笔记 Excel 读取所有Sheet 到DataSet 【转载】

---

我爱楠哥！

有关Excel读取的关键字 NOPI
Excel 读取所有Sheet 到DataSet
`namespace Taihe.Platform.Infrastructure.Common.File
{
using System;
using System.Data;
using System.Data.OleDb;
using System.IO;
using NPOI.HSSF.UserModel;
using NPOI.SS.Formula.Eval;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using ClosedXML.Excel;
using System.Collections.Generic;
using System.ComponentModel;
using System.Reflection;
using Attribute.Excel;
using System.Text;

/// <summary>
/// Excel文档辅助类
/// </summary>
public class ExcelHelper
{
/// <summary>
    /// 读取excel所有sheet
    /// </summary>
    /// <param name="fileName">excel文件路径</param>       
    /// <returns></returns>
    public static DataSet ImportExceltoDs(string fileName)
    {
        DataSet ds = new DataSet();
        IWorkbook workbook = null;
        using (var fs = new FileStream(fileName, FileMode.Open, FileAccess.Read))
        {
            if (fileName.IndexOf(".xlsx") > 0) // 2007版本
                workbook = new XSSFWorkbook(fs);
            else if (fileName.IndexOf(".xls") > 0) // 2003版本
                workbook = new HSSFWorkbook(fs);
        }
        for (int i = 0; i < workbook.NumberOfSheets; i++)
        {
            ISheet sheet = workbook.GetSheetAt(i);
            DataTable table = new DataTable();
            table = ImportDt(sheet, 0);
            table.TableName = sheet.SheetName;
            ds.Tables.Add(table);
        }
        return ds;
    }
 /// <summary>
    /// 将制定sheet中的数据导出到datatable中
    /// </summary>
    /// <param name="sheet">需要导出的sheet</param>
    /// <param name="headindex">列头所在行号，-1表示没有列头</param>
    /// <returns></returns>
    static DataTable ImportDt(ISheet sheet, int headindex)
    {
        DataTable data = new DataTable();
        try
        {
            if (sheet != null)
            {
                int startRow = 0;
                IRow firstRow = sheet.GetRow(headindex);
                int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数
                for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                {
                    ICell cell = firstRow.GetCell(i);
                    if (cell != null)
                    {
                        string cellValue = cell.StringCellValue;
                        if (cellValue != null)
                        {
                            DataColumn column = new DataColumn(cellValue);
                            data.Columns.Add(column);
                        }
                        else
                            throw new Exception("列为空！");
                    }
                    else
                        throw new Exception("列为空！");
                }
                startRow = sheet.FirstRowNum + headindex + 1;   

                //最后一列的标号
                int rowCount = sheet.LastRowNum;
                for (int i = startRow; i <= rowCount; ++i)
                {
                    try
                    {
                        IRow row = sheet.GetRow(i);
                        if (row == null || row.FirstCellNum < 0) continue; //没有数据的行默认是null
                        DataRow dataRow = data.NewRow();
                        for (int j = row.FirstCellNum; j < cellCount; ++j)
                        {
                            string cellValue = string.Empty;
                            if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null
                            {
                                switch (row.GetCell(j).CellType)
                                {
                                    case CellType.Numeric:
                                        try
                                        {
                                            if (DateUtil.IsCellDateFormatted(row.GetCell(j)))//日期类型
                                            {
                                                cellValue = row.GetCell(j).DateCellValue.ToString("yyyy-MM-dd");
                                            }
                                            else//其他数字类型
                                            {
                                                cellValue = row.GetCell(j).NumericCellValue.ToString();
                                            }
                                        }
                                        catch (Exception ex)
                                        {
                                            throw ex;
                                        }
                                        break;
                                    case CellType.Blank:
                                        cellValue = string.Empty;
                                        break;
                                    case CellType.Formula:
                                        try
                                        {
                                            #region Formula
                                            var item = row.GetCell(j);
                                            switch (item.CachedFormulaResultType)
                                            {
                                                case CellType.Boolean:
                                                    cellValue = item.BooleanCellValue.ToString();
                                                    break;
                                                case CellType.Error:
                                                    cellValue = ErrorEval.GetText(item.ErrorCellValue);
                                                    break;
                                                case CellType.Numeric:
                                                    if (DateUtil.IsCellDateFormatted(item))
                                                    {
                                                        cellValue = item.DateCellValue.ToString("yyyy-MM-dd hh:MM:ss");
                                                    }
                                                    else
                                                    {
                                                        cellValue = item.NumericCellValue.ToString();
                                                    }
                                                    break;
                                                case CellType.String:
                                                    string str = item.StringCellValue;
                                                    if (!string.IsNullOrEmpty(str))
                                                    {
                                                        cellValue = str.ToString();
                                                    }
                                                    else
                                                    {
                                                        cellValue = null;
                                                    }
                                                    break;
                                                case CellType.Unknown:
                                                case CellType.Blank:
                                                default:
                                                    cellValue = string.Empty;
                                                    break;
                                            }
                                            #endregion
                                        }
                                        catch (Exception ex)
                                        {
                                            throw ex;
                                        }
                                        break;
                                    case CellType.Unknown:
                                        throw new Exception("未识别！");
                                    case CellType.Error:
                                        throw new Exception("错误的类型！");
                                    default:
                                        cellValue = row.GetCell(j).StringCellValue;
                                        break;
                                }
                            }
                            dataRow[j] = cellValue;
                        }
                        data.Rows.Add(dataRow);
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                }
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
        return data;
    }
}
}`

ExcelHelper类需引用DLL的截图