读取Excel数据到DataTable

 

    /// <summary>
    /// 获取指定路径、指定工作簿名称的Excel数据:取第一个sheet的数据
    /// </summary>
    /// <param name="FilePath">文件存储路径</param>
    /// <param name="WorkSheetName">工作簿名称</param>
    /// <returns>如果争取找到了数据会返回一个完整的Table，否则返回异常</returns>
    public DataTable GetExcelData(string astrFileName)
    {
        string strSheetName = GetExcelWorkSheets(astrFileName)[0].ToString();
        return GetExcelData(astrFileName, strSheetName);
    }

 

    /// <summary>
    /// 返回指定文件所包含的工作簿列表;如果有WorkSheet，就返回以工作簿名字命名的ArrayList，否则返回空
    /// </summary>
    /// <param name="strFilePath">要获取的Excel</param>
    /// <returns>如果有WorkSheet，就返回以工作簿名字命名的ArrayList，否则返回空</returns>
    public ArrayList GetExcelWorkSheets(string strFilePath)
    {
        ArrayList alTables = new ArrayList();

        OleDbConnection odn = new OleDbConnection(GetExcelConnection(strFilePath));
        odn.Open();

        DataTable dt = new DataTable();

        dt = odn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

        if (dt == null)
        {
            throw new Exception("无法获取指定Excel的架构。");
        }

        foreach (DataRow dr in dt.Rows)
        {
            string tempName = dr["Table_Name"].ToString();

            int iDolarIndex = tempName.IndexOf('$');

            if (iDolarIndex > 0)
            {
                tempName = tempName.Substring(0, iDolarIndex);
            }

            //修正了Excel2003中某些工作薄名称为汉字的表无法正确识别的BUG。
            if (tempName[0] == '\'')
            {
                if (tempName[tempName.Length - 1] == '\'')
                {
                    tempName = tempName.Substring(1, tempName.Length - 2);
                }
                else
                {
                    tempName = tempName.Substring(1, tempName.Length - 1);
                }

            }
            if (!alTables.Contains(tempName))
            {
                alTables.Add(tempName);
            }
        }

        odn.Close();

        if (alTables.Count == 0)
        {
            return null;
        }

        return alTables;
    }

 

    /// <summary>
    /// 获取指定路径、指定工作簿名称的Excel数据
    /// </summary>
    /// <param name="FilePath">文件存储路径</param>
    /// <param name="WorkSheetName">工作簿名称</param>
    /// <returns>如果争取找到了数据会返回一个完整的Table，否则返回异常</returns>
    public DataTable GetExcelData(string FilePath, string WorkSheetName)
    {
        DataTable dtExcel = new DataTable();
        OleDbConnection con = new OleDbConnection(GetExcelConnection(FilePath));
        OleDbDataAdapter adapter = new OleDbDataAdapter("Select * from [" + WorkSheetName + "$]", con);

        //读取
        con.Open();
        adapter.FillSchema(dtExcel, SchemaType.Mapped);
        adapter.Fill(dtExcel);
        con.Close();
        dtExcel.TableName = WorkSheetName;

        //返回
        return dtExcel;
    }

 

     /// <summary>
    /// 获取链接字符串
     /// </summary>
    /// <param name="strFilePath"></param>
    /// <returns></returns>
    public string GetExcelConnection(string strFilePath)
    {
        if (!File.Exists(strFilePath))
        {
            throw new Exception("指定的Excel文件不存在！");
        }
        return "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFilePath + ";Extended properties=\"Excel 8.0;Imex=1;HDR=Yes;\"";

         //@"Provider=Microsoft.Jet.OLEDB.4.0;" +
         //@"Data Source=" + strFilePath + ";" +
         //@"Extended Properties=" + Convert.ToChar(34).ToString() +
         //@"Excel 8.0;" + "Imex=1;HDR=Yes;" + Convert.ToChar(34).ToString();        
    }