C#中使用ExcelDataReader读取Excel文件

ExcelDataReader：项目地址

ExcelDataReader下载：点击下载    ExcelDataReader.DataSet下载：点击下载

一、ExcelDataReader与ExcelDataReader.DataSet

ExcelDataReader基本包：使用 reader接口来读取数据。兼容net20、net45、netstandard1.3和netstandard2.0。

ExcelDataReader.DataSet扩展包：使用AsDataSet()方法来将Excel中的数据填充到System.Data.DataSet中。兼容net20、net35和netstandard2.0。

二、打开Excel文件

ExcelDataReader可以采取4种方式来打开Excel文件，具体代码如下：

var stream = new FileStream(excelFile, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
1.打开xls文件
IExcelDataReader reader = ExcelReaderFactory.CreateBinaryReader(stream);
2.打开xlsx文件
IExcelDataReader reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
3.打开csv文件
IExcelDataReader reader = ExcelReaderFactory.CreateCsvReader(stream);
4.自动检测Excel文件的格式（.xls 或 .xlsx）并使用相应方法打开Excel文件
IExcelDataReader reader = ExcelReaderFactory.CreateReader(stream);

ExcelDataReader可以接受一个可选的配置对象来设置Excel文件打开时相关属性，具体代码如下：

var stream = new FileStream(excelFile, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
var reader = ExcelReaderFactory.CreateReader(stream, new ExcelReaderConfiguration()
{    
    //设置读取Excel文件的编码方式，例如GB2312，用于处理中文字符
    FallbackEncoding = Encoding.GetEncoding("GB2312"),    
    //设置读取Excel文件的密码
    Password = "password",
    //设置读取CSV文件时的分隔符候选数组，reader会自动检测哪个最适合
    AutodetectSeparators = new char[] { ',', ';', '\t', '|', '#' },
    //设置在读取完Excel文件后，底层的Stream是否会被关闭，默认值为false
    LeaveOpen = false,
    //设置一个值，该值指示在CSV中要分析编码、分隔符和字段计数的行数，默认值为0 
    AnalyzeInitialCsvRows = 0,
});

三、获取数据

1、IExcelDataReader的属性和方法：

• Read()：从当前工作表中读取一行数据
• NextResult()：移到下一个工作表
• ResultsCount：返回Excel文件中工作表中的数量
• Name：返回当前工作表的名称
• FieldCount：返回当前表中的列数
• RowCount：返回当前表中的行数。这包括由 AsDataSet （） 排除的终端空行
• HeaderFooter：返回带有有关页眉和页脚信息的对象，如果没有，则返回null
• MergeCells：返回当前表中的合并单元格范围数组
• RowHeight：返回当前行的可视高度(以点为单位)。如果该行被隐藏，则可能为0
• GetColumnWidth()：以字符单位返回列的宽度。如果该列被隐藏，则可能为0
• GetFieldType()：返回当前行的值的类型。总是Excel支持的类型之一:double、int、bool、DateTime、TimeSpan、string，如果没有值，则为null
• IsDBNull()：检查当前行中的值是否为空
• GetValue()：从当前行返回一个值作为对象，如果没有值则返回null
• GetDouble()：将当前行的值转换为它们各自的类型并返回，其他：GetInt32()、GetBoolean()、GetDateTime()、GetString()
• GetNumberFormatString()：返回包含当前行的值的格式化代码的字符串，如果没有值则返回null
• GetNumberFormatIndex()：返回当前行的值的数字格式索引。164以下的索引值表示内置的数字格式，否则表示自定义数字格式。
• GetCellStyle()：返回包含当前行中单元格的样式信息的对象

      除非类型完全匹配，否则Get()方法将抛出InvalidCastException

2、用Read方法来读取数据填充到DataSet，具体代码如下：

查看代码

using (var stream = File.Open(filePath, FileMode.Open, FileAccess.Read))
{ 
    using (var reader = ExcelReaderFactory.CreateReader(stream))
    {
        DataSet ds=new DataSet();
        do
        {
            DataTable dt = new DataTable(reader.Name);
            reader.Read();
            int j = 1; 
            bool bolHeader=false; //是否使用表格首行数据做为列名
            for (int i = 0; i < reader.FieldCount; i++)
            {                                                       
                string strCol = "";
                if (bolHeader)
                {
                    if (reader.GetValue(i) == null)
                    {
                        strCol = "Column" + j.ToString();
                        j++;
                    }
                    else
                    {
                        strCol = reader.GetValue(i).ToString();
                    }
                    if (dt.Columns.Contains(strCol)) //检测是否有重复列名
                    {
                        strCol += i.ToString();
                    }
                }
                else
                {
                    strCol = "Column" + (i+1).ToString();
                }
                dt.Columns.Add(strCol);
            }
            while (reader.Read())//读取一行数据
            {
                DataRow drow = dt.NewRow();
                for (int i = 0; i < reader.FieldCount; i++)
                {
                    drow[i] = reader.GetValue(i);
                }
                dt.Rows.Add(drow);
            }
            ds.Tables.Add(dt);
        } while (reader.NextResult());//移到下一个工作表
    }
}

3、使用AsDataSet方法将数据填充到DataSet，具体代码如下：

查看代码

 using (var stream = File.Open(filePath, FileMode.Open, FileAccess.Read))
{ 
    using (var reader = ExcelReaderFactory.CreateReader(stream))
    { 
        DataSet ds = reader.AsDataSet(new ExcelDataSetConfiguration()
                                      {                   
                                          ConfigureDataTable = (tableReader) => new ExcelDataTableConfiguration()
                                          {
                                              UseHeaderRow = isHeaderRow
                                              }
                                      });
    }
}

AsDataSet()方法接受一个可选的配置对象来修改数据填充到DataSet的相关选项，具体代码如下：

查看代码

ds = reader.AsDataSet(new ExcelDataSetConfiguration()
{
    //设置一个值，该值指示是否设置DataColumn.DataType属性.
    UseColumnDataType = false,
    //设置工作表的筛选条件
    FilterSheet = (tableReader, sheetIndex) => 
    {
        return tableReader.Name == "Sheet1";//只读取工作表名为“Sheet1”的表
    },
    // 设置数据表的配置选项。
    ConfigureDataTable = (tableReader) => new ExcelDataTableConfiguration()
    {
        //设置自动生成列名的前缀。
        EmptyColumnNamePrefix = "Column",
        //设置是否将数据中的标题行（默认为首行）的值用作列名
        UseHeaderRow = true,
        //设置确定哪一行是标题行，只在UseHeaderRow = true时调用
        ReadHeaderRow = (rowReader) =>
        {
            //跳过第一行使用第二行的值作为列名，第一行的数据也不会读取
            rowReader.Read();
        },
        //设置行的筛选条件
        FilterRow = (rowReader) =>
        {
            string strValue = rowReader.GetValue(3).ToString();
            //筛选条件为第4列的值为“430102”，数据类型要一致
            return strValue == "430102";  
        },
        //设置列的筛选条件
        FilterColumn = (rowReader, columnIndex) =>
        {
            return columnIndex==0; //只获取第1列的数据
        }
    }
});

四、完整代码如下：

查看代码

private static IExcelDataReader GetDataReader(string excelFile)
{
    var extension = Path.GetExtension(excelFile).ToLower(); //返回指定的路径字符串的扩展名
    var stream = new FileStream(excelFile, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
    IExcelDataReader reader = null;
    if (extension == ".xls")
    {
        reader = ExcelReaderFactory.CreateBinaryReader(stream);
    }
    else if (extension == ".xlsx")
    {
        reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
    }
    else if (extension == ".csv")
    {
        reader = ExcelReaderFactory.CreateCsvReader(stream);
    }
    return reader;
}

public static DataSet ExcelToDataSet(string excelFile, bool isHeaderRow=true)
{
    DataSet ds; 
    IExcelDataReader reader = GetDataReader(excelFile);
    if (reader==null) return null;
    using (reader)
    {                
        ds = reader.AsDataSet(new ExcelDataSetConfiguration()
                              {
                                  UseColumnDataType = false,
                                  ConfigureDataTable = (tableReader) => new ExcelDataTableConfiguration()
                                  {
                                      UseHeaderRow = isHeaderRow
                                  }
                              });
    } 
    return ds;
}

public static DataTable ExcelToDataTable(string excelFile, bool isHeaderRow = true, string sheetName = "")
{
    DataSet ds = ExcelToDataSet(excelFile, isHeaderRow);
    if (ds == null)
    {
        return null;
    }
    else
    {
        if (sheetName.Length == 0)
        {
            return ds.Tables[0];
        }
        else
        {
            if (ds.Tables.Contains(sheetName))
            {
                return ds.Tables[sheetName];
            }
            else
            {
                return null;
            }
        }
    }
}

/// <summary>
/// 获得Excel中工作表的列表
/// </summary>
/// <param name="excelFile">Excel文件名</param>
/// <returns></returns>
public static IList<string> GetSheetNames(string excelFile)
{
    var sheetList = new List<string>();
    using (IExcelDataReader reader = GetDataReader(excelFile))
    {
        do
        {
            sheetList.Add(reader.Name);
        } while (reader.NextResult());
    }
    return sheetList;
}