1、读取Excel文件到DataSet中

/// <summary>
/// 读取Excel文件到DataSet中
/// </summary>
/// <param name="filePath">文件路径</param>
/// <returns></returns>
public static DataSet ToDataTable(string filePath)
{
    string connStr = "";
    string fileType = System.IO.Path.GetExtension(filePath);
    if (string.IsNullOrEmpty(fileType)) return null;

    if (fileType == ".xls")
        connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filePath + ";" + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";
    else
        connStr = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + filePath + ";" + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\"";
    string sql_F = "Select * FROM [{0}]";

    OleDbConnection conn = null;
    OleDbDataAdapter da = null;
    DataTable dtSheetName = null;

    DataSet ds = new DataSet();
    try
    {
        // 初始化连接，并打开
        conn = new OleDbConnection(connStr);
        conn.Open();

        // 获取数据源的表定义元数据                        
        string SheetName = "";
        dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

        // 初始化适配器
        da = new OleDbDataAdapter();
        for (int i = 0; i < dtSheetName.Rows.Count; i++)
        {
            SheetName = (string)dtSheetName.Rows[i]["TABLE_NAME"];

            if (SheetName.Contains("$") && !SheetName.Replace("'", "").EndsWith("$"))
            {
                continue;
            }

            da.SelectCommand = new OleDbCommand(String.Format(sql_F, SheetName), conn);
            DataSet dsItem = new DataSet();
            da.Fill(dsItem, SheetName);

            ds.Tables.Add(dsItem.Tables[0].Copy());
        }
    }
    finally
    {
        // 关闭连接
        if (conn.State == ConnectionState.Open)
        {
            conn.Close();
            da.Dispose();
            conn.Dispose();
        }
    }
    return ds;
}
————————————————
版权声明：本文为CSDN博主「肥嘟嘟D左卫门」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/u012235352/article/details/95589102

 

2、DataSet转DataTable

DataTable dt = (new DataSet())ds.Tables[0];

3、datagridview数据源绑定DataTable

datagridview.DataSource = dt ;