Excel文件读取操作方法

　　Excel的连接中，由两个值需要注意。

　　首先是HDR值，该值指示是否将表中的第一行有效（第一个行数据不为空的行）数据当作标题列处理。如果选择是YES，那么通过C#读取出来的数据表中，表的列标题则是对应的第一行有效数据；否则，将所有数据都当作数据处理，此时以F1、F2……Fn为列标题。默认的是YES，见后续的使用。

　　其次是IMEX模式， IMEX 有三种模式，不同的模式代表著不同的读写行为：

• 0 is Export mode：为“汇出模式”，这个模式开启的 Excel 档案只能用来做“写入”用途。 ---输出模式;
• 1 is Import mode：为“汇入模式”，这个模式开启的 Excel 档案只能用来做“读取”用途。---输入模式，始终将“互混”数据列作为文本读取;
• 2 is Linked mode (full update capabilities)：为“连結模式”，这个模式开启的 Excel 档案可同时支援“读取”与“写入”用途。---链接模式(完全更新能力，效率不高)

　　IMEX=1的作用是，当读取Excel中每个单元格的值到DataTable中的时候，不管其在Excel单元格时候是什么数据类型，赋值到DataTable中都强制转化为字符串类型。

　　在没有IMEX=1这个属性的时候，默认的是根据Excel中对应Column的数据类型来决定DataTable中Column的数据类型。这种情况在Excel中某一列的数据类型都是一致的情况下没有问题，是什么类型，就会在DataTable中的对应列设置相应的类型。但是如果Excel中这一列的类型混乱的话，比如说既包括数值型又有字符串型，在运行时创建DataTable的时候，会去先判断Excel中这一列哪种类型的数据占主体，然后给DataTable的列设置为这种类型。比如说，如果一列中既有整数型又有字符型，而整数型单元格占主体，这时DataTable中的列就是整数型。

　　这时又出现另外一个问题，当要把值写入到DataTable的时候，如果该单元格符合DataTable中要求的类型，就会写入，如果不符合的话，系统会去强制转换。比如，如果Excel中是字符串的5，而该单元格所在的列整数型占主体，DataTable中这一列是数值型，这时，系统会把字符串的5强制转为数值型的5然后赋给DataTable相应的地方。但是，此时，如果转换有问题的话，比如，此列中有一单元格中是“NO5”，这时强制转换就会有问题，系统就会给DataTable相应的地方赋值DBNull,现在如果你用DataGridView来显示那个DataTable的时候，这个地方显示出来就是一个空白的格，但要注意的是，并不是DataTable中的这一行这一列是null，而是DBNull.其实，我觉得，跟null的作用是一样的，反正在DataGridView中是不会显示出来。只是我们在写程序如果需要对DataTable的null元素筛选的话，需要注意这个问题。

　　如果Excel中，某一行字符串类型占主体的话，那么DataTable中这一列就会设置为字符串型，而且任何类型都能顺利转换成字符串类型，所以，Excel的类会完整的显示出来，不管这一列中的字符串类型的单元格，还是整数型的单元格，都能完整的显示出来。这是一很特别的地方。但这仅仅是一个特例。

　　所以，如果为了处理的时候数据类型的一致性，如果Excel中数据类型混乱的话，可以使用IMEX=1使DataTable中的所有列都转为字符型。

　　CSV的文件，它的连接字符串会读取该文件所在的文件夹下所有的文本文件数据。

ExcelReader代码：

/// <summary>
/// 连接Excel的模式
/// </summary>
public enum IMEX
{
    /// <summary>
    /// 汇出(输出)模式
    /// </summary>
    Export = 0,
    /// <summary>
    /// 汇入(输入)模式
    /// </summary>
    Import = 1,
    /// <summary>
    /// 链接模式(完全更新能力)
    /// </summary>
    Linked = 2,
}
public sealed class ExcelReader
{

    /// <summary>
    /// 是否将第一行数据作为标题列。如果选择no, 则将第一行数据作为数据读取，则默认F1~开始为标题
    /// </summary>
    public bool IsFirstRowAsColumnNames { get; set; } = true;
    /// <summary>
    /// 连接Excel的模式
    /// </summary>
    public IMEX IMEX { get; set; } = IMEX.Import;
    /// <summary>
    /// 文件的绝对路径
    /// </summary>
    string filePath = string.Empty;

    /// <summary>
    /// 构造对象
    /// </summary>
    /// <param name="hdr">第一行的读取模式：默认是Yes</param>
    /// <param name="imex"></param>
    public ExcelReader(IMEX imex = IMEX.Import)
    {
        IMEX = imex;
    }
    


    FileType fileType = FileType.noset;
    /// <summary>
    /// 获取连接字符串
    /// </summary>
    /// <param name="fileFullPath">文件的绝对路径</param>
    /// <returns>连接字符串</returns>
    public string GetConnectString(string fileFullPath)
    {
        fileType = FileType.noset;
        if (!File.Exists(fileFullPath))//判断文件是否存在
        {
            throw new FileNotFoundException(fileFullPath);
        }
        string hdr = IsFirstRowAsColumnNames ? "YES" : "NO";
        string connString = string.Empty;
        filePath = fileFullPath;
        switch (Path.GetExtension(filePath))
        {
            case ".xls":
                connString = $"Provider=;Data Source={ filePath }; Extended Properties='Excel 8.0;HDR={hdr};IMEX={(int)IMEX};'";
                fileType = FileType.xls;
                break;
            case ".xlsx":
                connString = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={ filePath };Extended Properties='Excel 12.0;HDR={hdr};IMEX={(int)IMEX};'";
                fileType = FileType.xlsx;
                break;
            case ".csv"://filePath.Remove(filePath.LastIndexOf("\\") + 1)
                connString = $"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={Path.GetDirectoryName(filePath)};Extended Properties='Text;FMT=Delimited;HDR={hdr};FMT=Delimited;'";
                fileType = FileType.csv;
                break;
        }
        return connString;
    }

    /// <summary>
    /// 打开连接
    /// </summary>
    /// <param name="fileFullPath">文件的绝对路径</param>
    /// <returns>连接对象</returns>
    public OleDbConnection GetConnection(string fileFullPath)
    {
        string connectString = GetConnectString(fileFullPath);//获取文件连接字符串
        if (fileType == FileType.noset)
        {
            throw new ArgumentException("Incorrect file type.");
        }
        OleDbConnection oleDb = new OleDbConnection();
        try
        {
            oleDb.ConnectionString = connectString;
        }
        catch (Exception ex)
        {
            oleDb.Close();
            throw ex;
        }
        return oleDb;
    }


    /// <summary>
    /// 获取所有表单的名称；注意，表单名顺序不一定是Excel中显示的顺序，是添加表时的先后顺序
    /// </summary>
    /// <param name="fileFullName">文件全名</param>
    /// <returns>表单名称</returns>
    public List<string> GetSheetsName(string fileFullName)
    {
        OleDbConnection connection = GetConnection(fileFullName);//获取连接对象
        List<string> lstSheets = GetSheetsName(connection);
        return lstSheets;
    }

    /// <summary>
    /// 获取所有表单的名称
    /// </summary>
    /// <param name="connection">连接对象</param>
    /// <returns>表单名称</returns>
    public List<string> GetSheetsName(OleDbConnection connection)
    {
        List<string> lstSheets = new List<string>();
        try
        {
            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
            }
            DataTable dt = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);//获取表单
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                lstSheets.Add(dt.Rows[i]["Table_Name"].ToString());
            }
        }
        finally
        {
            connection.Close();
        }

        return lstSheets;
    }

    /// <summary>
    /// 获取文件内的所有Table数据集合
    /// </summary>
    /// <param name="fileFullName">文件全名</param>
    /// <returns>数据集合</returns>
    public DataSet GetDataSet(string fileFullName)
    {
        OleDbConnection connection = GetConnection(fileFullName);//获取连接对象
        return GetDataSet(connection);
    }

    /// <summary>
    /// 获取文件内的所有Table数据集合
    /// </summary>
    /// <param name="connection">连接对象</param>
    /// <returns>数据集合</returns>
    public DataSet GetDataSet(OleDbConnection connection)
    {
        DataSet ds = new DataSet();
        try
        {
            List<string> lstSheets = GetSheetsName(connection);
            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
            }
            OleDbDataAdapter adapter = new OleDbDataAdapter(connection.CreateCommand());//创建读取数据
            for (int i = 0; i < lstSheets.Count; i++)
            {
                adapter.SelectCommand.CommandText = string.Format("SELECT * FROM [{0}]", lstSheets[i].Trim('/'));//查询字符串
                adapter.Fill(ds);//填充数据
                ds.Tables[i].TableName = lstSheets[i];//赋值表名
            }
            adapter.Dispose();
        }
        finally
        {
            connection.Close();
        }
        return ds;
    }

    /// <summary>
    /// 获取文件内的所有Table数据集合
    /// </summary>
    /// <param name="fileFullName">文件全名</param>
    /// <param name="tableName">表名</param>
    /// <returns>数据集合</returns>
    public DataTable GetDataTable(string fileFullName, string tableName)
    {
        OleDbConnection connection = GetConnection(fileFullName);//获取连接对象
        return GetDataTable(connection, tableName);
    }

    /// <summary>
    /// 获取文件内的所有Table数据集合
    /// </summary>
    /// <param name="connection">连接对象</param>
    /// <param name="tableName">表名</param>
    /// <returns>数据集合</returns>
    public DataTable GetDataTable(OleDbConnection connection, string tableName)
    {
        DataSet ds = new DataSet();
        try
        {
            if (!tableName.EndsWith("$"))//检查sheet名称是否是以'$'结尾的，必须以'$'结尾
            {
                tableName += '$';
            }
            connection.Open();
            OleDbCommand cmd = connection.CreateCommand();
            cmd.CommandText = $"Select * from [{tableName}]";//必须加方括号
            using (OleDbDataReader reader = cmd.ExecuteReader())
            {
                ds.Load(reader, LoadOption.OverwriteChanges, tableName);//这儿使用的是DataSet的加载方法
            }
        }
        finally
        {
            connection.Close();
        }
        if (ds.Tables.Count > 0)
        {
            return ds.Tables[0];
        }
        else
        {
            return null;
        }
    }

    /// <summary>
    /// 通过sql语句获取表单
    /// </summary>
    /// <param name="fileFullPath">文件全路径名称</param>
    /// <param name="sql">sql语句</param>
    /// <returns>通过sql查询到的表单</returns>
    /// <remarks>
    /// 如果HDR使用的是NO, 则默认F1类型开始为列标题（相当于数据库的字段名）；
    /// 如果HDR使用的是Yes，则第一行的数据值当作数据库的字段名类型
    /// string sql="select  F1,F2  from [Sheet1$] ";//注意表名称[] 和 $ 都不能少了
    /// </remarks>
    public DataTable GetDataTableBySql(string fileFullPath, string sql)
    {
        OleDbConnection connection = GetConnection(fileFullPath);
        return GetDataTableBySql(connection, sql);
    }

    /// <summary>
    /// 通过sql语句查询表单
    /// </summary>
    /// <param name="connection">连接对象</param>
    /// <param name="sql">查询语句</param>
    /// <returns>通过sql查询到的表单</returns>
    /// <remarks>
    /// 如果HDR使用的是NO, 则默认F1类型开始为列标题（相当于数据库的字段名）
    /// string sql="select  F1,F2  from [Sheet1$] ";//注意表名称[] 和 $ 都不能少了
    /// </remarks>
    public DataTable GetDataTableBySql(OleDbConnection connection, string sql)
    {
        DataSet ds = new DataSet();
        try
        {
            connection.Open();
            OleDbDataAdapter adapter = new OleDbDataAdapter(sql, connection);
            adapter.Fill(ds);
        }
        finally
        {
            connection.Close();
        }
        if (ds.Tables.Count > 0)
        {
            return ds.Tables[0];
        }
        else
        {
            return null;
        }
    }

    /// <summary>
    /// 文件类型
    /// </summary>
    private enum FileType
    {
        noset,
        xls,
        xlsx,
        csv
    }
}

ExcelReader的使用：

private void button1_Click(object sender, EventArgs e)
{
    OpenFileDialog ofd = new OpenFileDialog();
    ofd.Filter = "Execl files (*.xlsx)|*.xlsx|(*.xls)|*.xls*";

    if(ofd.ShowDialog()== DialogResult.OK)
    {
        ExcelReader excel = new ExcelReader();
        List<string> lstSheets = excel.GetSheetsName(ofd.FileName);//获取所有表名
        dataGridView1.DataSource = lstSheets;                
        
        DataSet ds0= excel.GetDataSet(ofd.FileName);//获取Excel文件中的所有数据

        DataTable table = excel.GetDataTable(ofd.FileName,lstSheets.First());//读取Excel中第一张Sheet的值

        //excel.IsFirstRowAsColumnNames =true;
        //string sql = $"select  学号,姓名,性别,备注  from [{lstSheets[0]}] where 姓名='张三'";
        excel.IsFirstRowAsColumnNames = false;
        //string sql =$"select  F1,F2,F3,F6  from [{lstSheets[0]}] where F2='张三'";//查找张三 
        string sql = $"select  *  from [{lstSheets[0]}]";//查找张三 
        dataGridView1.DataSource = excel.GetDataTableBySql(ofd.FileName, sql);
        
    }

}