.NET 浅谈EXCEL上传

 关于excel的上传，我们在编码时候经常会遇到，无非就是我们要获取到excel中的值，然后做操作。关键就是如何获取到excel的值

filePath=“”//excel路径 

DataTable table = getExcelTableName(filePath);

  DataSet ds = ReadExcel(filePath, table.Rows[0]["Table_Name"].ToString());

我们只要获取到了这个DataSet就能执行一系列操作了

   public DataTable getExcelTableName(string excelPath)
    {
        if (String.IsNullOrEmpty(excelPath))
        {
            return null;
        }

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

        OleDbConnection conn = null;

        try
        {
            DataTable table = new DataTable();

           // string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + excelPath + ";" + "Extended Properties=Excel 8.0;";
            string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + excelPath + ";" + "Extended Properties='Excel 12.0 Xml;HDR=YES;'";

            conn = new OleDbConnection(strConn);

            conn.Open();

            table = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

            conn.Close();

            return table;
        }

        catch
        {
            conn.Close();

            MessageBox("解析Excel出錯。可能原因有：\\n\\n1：選擇文件類型非Excel格式文件，正確格式為：XXXX.xls！\\n\\n2：該文件被其他進程佔用！\\n\\n3：该文件不存在！");

            return null;
        }
    }

 

   public DataSet 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
        {
            //strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + excelPath + ";" + "Extended Properties=Excel 8.0;";
            strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + excelPath + ";" + "Extended Properties='Excel 12.0 Xml;HDR=YES;'";
            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;
        }
        catch
        {
            conn.Close();

            MessageBox("解析Excel出錯。可能原因有：\\n\\n1：選擇文件類型非Excel格式文件，正確格式為：XXXX.xls！\\n\\n2：該文件被其他進程佔用！\\n\\n3：该文件不存在！");

            return null;
        }
    }