将EXCEL 中的数据导入，转化为DataTable

来源：互联网发布：linux虚拟机共享文件编辑：程序博客网时间：2024/05/17 01:08

    /// <summary>
    /// 讀取EXCEL內容
    /// </summary>
    /// <param name="excelPath">EXCEL路徑</param>
    /// <param name="exceltableName">EXCEL工作表名如sheet1,sheet2，不要加$</param>
    /// <returns>返回DataTable</returns>
    public static DataTable ReadExcel(string excelPath, string exceltableName)
    {
        string strConn = "";

OleDbConnection conn = null;

        if (String.IsNullOrEmpty(excelPath))
        {
            return null;
        }

        if (!System.IO.File.Exists(excelPath))
        {
            return null;
        }

        try
        {
            if (excelPath.EndsWith(".xls"))
            {
                strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + excelPath + ";" + "Extended Properties=Excel 8.0;";
            }
            if (excelPath.EndsWith(".xlsx"))
            {
                strConn = @"Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + excelPath + ";" + "Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1;\"";

            }
            if (strConn.Equals(""))
            {
                throw new Exception("無效的EXCEL文件！");
            }
            conn = new OleDbConnection(strConn);
            conn.Open();
            string strExcel = "";
            OleDbDataAdapter myCommand = null;
            DataSet ds = null;
            strExcel = "select * from [" + exceltableName + "$]";
            myCommand = new OleDbDataAdapter(strExcel, strConn);
            ds = new DataSet();
            myCommand.Fill(ds, "TEMP");
            conn.Close();
            return ds.Tables[0];
        }
        catch
        {
            conn.Close();
            throw new Exception("解析Excel出錯。可能原因有：\\n\\n1：選擇文件類型非Excel格式文件，正確格式為：XXXX.xls！\\n\\n2：該文件被其他進程佔用！\\n\\n3：该文件不存在！");
        }
    }