首页 > 代码库 > C# 读取Excel

C# 读取Excel

c#读取excel

 public void connExcel(string strPath)        {            //string strConn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strPath + ";Extended Properties=‘Excel 8.0;HDR=YES;IMEX=1‘;";            string strConn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strPath + ";Extended Properties=‘Excel 12.0;HDR=YES;IMEX=1‘;";                        OleDbDataReader myDataReader = null;            OleDbConnection myOleDbConnection = new OleDbConnection(strConn);            OleDbCommand myOleDbCommand = new OleDbCommand("SELECT * FROM [Sheet1$]", myOleDbConnection);            try            {                myOleDbConnection.Open();                myDataReader = myOleDbCommand.ExecuteReader();                while (myDataReader.Read())                {                    Console.WriteLine("序号:" + myDataReader.GetValue(0));//列1                    Console.WriteLine("标题:" + myDataReader.GetValue(1));//列2                    Console.WriteLine("预期结果:" + myDataReader.GetValue(2));//列3                }            }            catch (Exception ex)            {                Console.WriteLine(ex.ToString());            }            finally            {                // Always call Close when done reading.                if (myDataReader != null)                    myDataReader.Close();                // Close the connection when done with it.                if (myOleDbConnection != null)                    myOleDbConnection.Close();            }        }
View Code

 

遇到问题:

1.open时,报“找不到可安裝的ISAM解決方法”

解决方法:Excel 8.0;HDR=Yes;IMEX=1;这个要用单引号引起来

2.报错:外部表不是预期的格式错误

错误原因1: 由于Excel 97-2003的连接格式与Excel 2010 的 不同造成。

解决方法:

//2003(Microsoft.Jet.Oledb.4.0)
string strConn = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=‘Excel 8.0;HDR=Yes;IMEX=1;‘", excelFilePath);

//2007 , 2010(Microsoft.ACE.OLEDB.12.0)
string strConn = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=‘Excel 12.0;HDR=Yes;IMEX=1;‘", excelFilePath);

3.Access有密碼設置時,當用C#連接Access Database時可能會出現"找不到可安裝的ISAM"。

解决方法:string dsn = "Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:DataBase Password=123;Data Source=C:\\...\\*.mdb";

C# 读取Excel